Cannot update column value in SQL Server Express DB table

Question

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.

Answer 1

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))  

Answer 2

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?