Cannot update column value in SQL Server Express DB table

Boget1 1 Reputation point
2021-04-20T17:45:40.51+00:00

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.

VB
VB
An object-oriented programming language developed by Microsoft that is implemented on the .NET Framework. Previously known as Visual Basic .NET.
1,848 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Karen Payne MVP 29,236 Reputation points MVP
    2021-04-21T11:40:42.713+00:00

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

  2. Erland Sommarskog 68,536 Reputation points MVP
    2021-04-20T21:45:22.79+00:00

    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?

    No comments