question

Boget1-4437 avatar image
0 Votes"
Boget1-4437 asked karenpayneoregon edited

Cannot update column value in SQL Server Express DB table

I'm transitioning a old app from an ACCESS DB to a SQL Server Express DB. Have been trying to update several columns in an access table. I can retrieve data from the table so I know my connection is correct but I cannot write to it. Here is one example of the code I've tried:

Calling Method -

         ' Add required parameters.
         SQL.AddParam("anonymous user", userID)
         SQL.AddParam("@pw", "%" & PwordTxtBx2.Text & "%")
         ' Build query string & execute query.
         SQL.ExecQuery("UPDATE Profiles " &
                                  "SET Password=@pw " &
                                  "WHERE UserID=anonymous user;")
         ' Was the update successful?
         Select Case SQL.HasException(T)

Execution Method -

     Try
         ' Open a connection to the database.
         DbCon.Open()
         ' Create DB Command
         DbCmd = New SqlCommand(query, DbCon)
         'Load Params into DB Command
         Params.ForEach(Sub(p) DbCmd.Parameters.Add(p))
         ' Clear Params List
         Params.Clear()
         ' Execute Command & Fill Dataset
         DbDt = New DataTable
         DbDa = New SqlDataAdapter(DbCmd)
         RecCnt = DbDa.Fill(DbDt)

     Catch ex As SqlException
         ' Capture thrown error & create an error message.
         ExErr = $"SQL Error: {ex.Message}"
         UserMsg(3, "Update Failure!" & dblLf & ExErr)

     Finally
         ' Close Connection
         If DbCon.State = ConnectionState.Open Then DbCon.Close()
     End Try

DB was built in VS 2019 an placed in the working directory of the project. Any suggestion would be greatly appreciated.



dotnet-visual-basic
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Boget1-4437 ,
Could you provide some related code about 'SQL' in 'SQL.AddParam(...)' and 'Params' here? It will help others analyze your problem and make a test.
We are waiting for your update.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

I have difficulties to piece things together since I only see fragments. The code in the first part looks good, except that I don't recognise the API. What type is the SQL object? Is it some kind of homebrew?

The second part looks like regular .NET, but that seems to be code to retrieve data.

In any case, you tells us that you have not been able to update, but you don't tell us what happens. Any error messages? Unexpected results?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

karenpayneoregon avatar image
1 Vote"
karenpayneoregon answered karenpayneoregon edited

Hello,

As mentioned already, it's difficult to put together your code in respects to seeing if parts not shown may be an issue. The following is a pattern to consider.

  • Use a single local method connection for each operation which uses a static connection string

  • As shown below, this checks for errors and if the primary key was not located

  • Captures the exception if any.

Here the data operation class, Contact class and Enum are all in the same code block but for a real app would be broken apart. The database is server based but that will not matter as in your case you can connect.

 Imports System.Data.SqlClient
    
 Public Class DataOperations
    
     Private Shared ConnectionString As String =
                 "Data Source=.\SQLEXPRESS;" &
                 "Initial Catalog=NorthWind2020;" +
                 "Integrated Security=True"
    
     Public Shared LastException As Exception
     Public Shared HasException As Boolean
    
     Public Shared ReadOnly Property IsSuccessful() As Boolean
         Get
             Return HasException = False
         End Get
     End Property
    
     Public Shared Function Update(pContact As Contact) As UpdateResult
    
         HasException = False
    
         Dim selectStatement =
                 "SELECT COUNT(ContactId) FROM NorthWindAzureForInserts.dbo.Contacts " &
                 "WHERE ContactId = @ContactId"
    
         Dim updateStatement =
                 "UPDATE dbo.Contacts SET FirstName = @FirstName,LastName = @LastName " &
                 "WHERE ContactId = @ContactId"
    
         Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
    
             Using cmd As New SqlCommand With {.Connection = cn}
    
                 cmd.CommandText = selectStatement
    
                 Try
    
                     cn.Open()
    
                     cmd.Parameters.AddWithValue("@ContactId",
                                                 pContact.ContactId)
    
                     Dim count = CInt(cmd.ExecuteScalar())
    
                     If count = 1 Then
    
                         cmd.Parameters.Add(New SqlParameter("@FirstName", SqlDbType.NVarChar)).
                             Value = pContact.FirstName
    
                         cmd.Parameters.Add(New SqlParameter("@LastName", SqlDbType.NVarChar)).
                             Value = pContact.LastName
    
                         cmd.Parameters("@ContactId").Value = pContact.ContactId
    
    
                         cmd.CommandText = updateStatement
    
                         cmd.ExecuteNonQuery()
    
                         Return UpdateResult.Success
    
                     Else
                         Return UpdateResult.NotFound
                     End If
    
                 Catch ex As Exception
                     HasException = True
                     LastException = ex
                     Return UpdateResult.Failed
                 End Try
             End Using
         End Using
     End Function
    
 End Class
    
 Public Class Contact
     Public Property ContactId() As Integer
     Public Property FirstName() As String
     Public Property LastName() As String
     Public ReadOnly Property FullName() As String
         Get
             Return $"{FirstName} {LastName}"
         End Get
     End Property
 End Class
 Public Enum UpdateResult
     Success
     Failed
     NotFound
 End Enum

Then call the above with a mocked up of a Contact instance.

 Dim contact As New Contact With {
         .ContactId = 2,
         .FirstName = "Ana",
         .LastName = "Trujillo"}
    
 Dim success = DataOperations.Update(contact)
    
 If DataOperations.IsSuccessful AndAlso success = UpdateResult.Success Then
     Debug.WriteLine("Contact updated")
 ElseIf success = UpdateResult.NotFound Then
     Debug.WriteLine("Contact not found")
 ElseIf success = UpdateResult.Failed Then
     Debug.WriteLine(DataOperations.LastException.Message)
 End If


Caveats

  • Rather than call the Update method as shown above a better idea is to have a unit test project with several test methods to valid the update method.

  • Consider using Entity Framework 6 code first from existing database which in Visual Studio

Here is a taste for Entity Framework 6

 Imports System.Data.Entity
    
 Public Class DataOperations
    
     Public Shared Function Update(pContact As Contact) As Boolean
    
         Using context As New NorthWindContext
    
             Dim contact = context.Contacts.Find(pContact.ContactId)
    
             If contact IsNot Nothing Then
    
                 contact.FirstName = pContact.FirstName
                 contact.LastName = pContact.LastName
    
                 context.Entry(contact).State = EntityState.Modified
    
                 Return context.SaveChanges() = 1
             Else
                 Return False
             End If
         End Using
    
     End Function
 End Class

Called as follows

 Dim contact As New Contact With {
         .ContactId = 2,
         .FirstName = "Ana",
         .LastName = "Trujillo"}
    
 Debug.WriteLine(DataOperations.Update(contact))


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.