Record does not get updated

Raabi Anony

Hello geeks My struggle with Updating data seems never ending. My latest code is as below:

Private Sub StaffInfoAdapterUpdate(ByVal conxnString As String)
Dim cmdText As String
cmdText = "SELECT StaffID, StaffName, blCampuses.CampusName,
Designation, DOB, Gender, NIC, DateOfJoining
FROM tblStaffInfo
INNER JOIN tblCampuses
ON tblStaffInfo.CampusID = tblCampuses.CampusID"

 Using Conxn As SqlConnection = New SqlConnection(conxnString)

 Dim adapter As SqlDataAdapter = New SqlDataAdapter(cmdText, Conxn)
 Dim strUpdate As String = "UPDATE StaffName 
     SET StaffName = @StaffName " &  "WHERE StaffID = @StaffID"

        adapter.UpdateCommand = New SqlCommand(strUpdate, Conxn)
        adapter.UpdateCommand.Parameters.Add("@StaffName", SqlDbType.VarChar, 50, "StaffName")

 Dim parameter As SqlParameter =
 adapter.UpdateCommand.Parameters.Add("@StaffID", SqlDbType.Int)

        parameter.SourceColumn = "StaffID"
        parameter.SourceVersion = DataRowVersion.Original

        Dim StaffInfoTable As New DataTable
        Try
            adapter.Fill(StaffInfoTable)
            adapter.Update(StaffInfoTable)
        Catch ex As Exception
            MsgBox("Error: " & ex.Message)
        End Try
    End Using
End Sub

When I modify the StaffName for testing, the code does not throw any error, but does not update the record either. Please note that StaffID is the Primary Key of tblStaffInfo Please help! I have a point to clarify in this respect: Do I have to include ALL the Fields in the strUpdate; which are included in cmdText string; even if most of the fields are least expected to be modified?

Richard Deeming

Raabi Anony wrote:

Dim StaffInfoTable As New DataTable Try adapter.Fill(StaffInfoTable) adapter.Update(StaffInfoTable)

You create a new DataTable, load the data from the database, and then update the database with the data you've just loaded. There's nothing in that code that changes the data. Are you missing part of your code from the question?

Raabi Anony wrote:

Do I have to include ALL the Fields in the strUpdate; which are included in cmdText string; even if most of the fields are least expected to be modified?

You have to include all of the fields you want to update, along with the primary key. If a field isn't going to be updated, and it's not the primary key, then you don't need to include it.

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Raabi Anony

Thanks Homer, for your attention. I am reading the data in datagridView and modify it there only. Do I need to make changes for this scenario? Please suggest! Regards

Richard Deeming

The code you've posted isn't touching your DataGridView. You load the data from the database, and then immediately update the database with the data you've just loaded. :doh:

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Raabi Anony

Thanks, Richard, for your attention and sorry for leaving out a change in the code, for the test purpose, as below:

 Dim strUpdate As String = "UPDATE StaffName SET StaffName = 'Anonymous'" &  "WHERE StaffID = 1005"

A record with the StaffID = 1005 exists. But the record doesn't get changed, as expected (by me :) ). What mistake I am doing. Please suggest. I have tried the following code, as well:

Private Sub StaffInfoAdapterUpdate()
    Dim ConxnString As String = "Data Source=Dar-e-Arqam\\SQLEXPRESS; Initial Catalog=StaffReport; 
                                 Integrated Security=True"
    Dim Conxn As New SqlConnection(ConxnString)
    Dim cmd As SqlCommand = Conxn.CreateCommand()
    Dim StaffInfoDS As New DataSet()
    Dim StaffInfoAdapter As New SqlDataAdapter()
    Dim cmdText As String = "SELECT StaffID, StaffName, tblCampuses.CampusName, Designation, 
                DOB, Gender, NIC, DateOfJoining
        FROM tblStaffInfo
        INNER JOIN tblCampuses
        ON tblStaffInfo.CampusID = tblCampuses.CampusID"
    Dim strUpdate As String = "UPDATE StaffName " &
                                 "SET StaffName = 'Anonymous' " & "WHERE StaffID = 1005"
    '                                     "SET StaffName = @StaffName " & "WHERE StaffID = @StaffID"
    Try
        Conxn.Open()
        cmd.CommandText = strUpdate
        cmd.ExecuteNonQuery()
        MsgBox("The record updated. Really?")
    Catch ex As Exception
        MsgBox("Error: " & ex.Message)
    End Try
End Sub

The above code throws an error: "Invalide object name 'StaffName'. I am very disturbed for this problem. Please help.

Chris Quinn

Your table is called tblStaffInfo, so your update statement needs to be

Raabi Anony wrote:

Dim strUpdate As String = "UPDATE tblStaffInfo " & "SET StaffName = 'Anonymous' " & "WHERE StaffID = 1005" ' "SET StaffName = @StaffName " & "WHERE StaffID = @StaffID" Try

========================================================= I'm an optoholic - my glass is always half full of vodka. =========================================================

Raabi Anony

Wow, such a major mistake, I had been overlooking. Thanks Chris Quinn and keep your glass always FULL :)