[RESOLVED] Updating access table from a dataset

[paulorton]

The table in question has 2 rows and I am making changes to just one of them (via the dataset). The dataset (which contains both rows) is altered as follows:

For Each row As DataRow In ds_Centres.Tables(0).Rows
                If row("Centre_Name").ToString = lstCentres.SelectedItem.ToString Then
                    row("Centre_Name") = txtCentreName.Text
                    row("Monday_Class") = chkClass_Days0.Checked
                    row("Tuesday_Class") = chkClass_Days1.Checked
                    row("Wednesday_Class") = chkClass_Days2.Checked
                    row("Thursday_Class") = chkClass_Days3.Checked
                    row("Friday_Class") = chkClass_Days4.Checked
                    row("Saturday_Class") = chkClass_Days5.Checked
                    row("Sunday_Class") = chkClass_Days6.Checked
                    row("Correspondence") = chkClass_Days7.Checked
                End If

"If row("Centre_Name").ToString = lstCentres.SelectedItem.ToString" determines which row has been edited.

Now this code attempts to update the table:

Dim Conn As OleDbConnection        
        Dim SQL As String
        Dim ConnectionString As String

        SQL = "SELECT * FROM tblCentre_Details Order by ID"
        ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & My.Settings.AppPath & "\" & ManagerName & "_FCLManager2011_Centres.mdb"
        Conn = New OleDbConnection(ConnectionString)
        Dim da = New OleDbDataAdapter(SQL, Conn)        

        da.UpdateCommand = New OleDbCommand("UPDATE tblCentre_Details SET Centre_Name = @Centre_Name, Monday_Class = @Monday_Class, Tuesday_Class = @Tuesday_Class, Wednesday_Class = @Wednesday_Class, Thursday_Class = @Thursday_Class, Friday_Class = @Friday_Class, Saturday_Class = @Saturday_Class, Sunday_Class = @Sunday_Class, Correspondence = @Correspondence")
        da.UpdateCommand.Connection = Conn
        da.UpdateCommand.Parameters.Add("@Centre_Name", OleDbType.VarChar, 50, "Centre_Name")
        da.UpdateCommand.Parameters.Add("@Monday_Class", OleDbType.Boolean, 1, "Monday_Class")
        da.UpdateCommand.Parameters.Add("@Tuesday_Class", OleDbType.Boolean, 1, "Tuesday_Class")
        da.UpdateCommand.Parameters.Add("@Wednesday_Class", OleDbType.Boolean, 1, "Wednesday_Class")
        da.UpdateCommand.Parameters.Add("@Thursday_Class", OleDbType.Boolean, 1, "Thursday_Class")
        da.UpdateCommand.Parameters.Add("@Friday_Class", OleDbType.Boolean, 1, "Friday_Class")
        da.UpdateCommand.Parameters.Add("@Saturday_Class", OleDbType.Boolean, 1, "Saturday_Class")
        da.UpdateCommand.Parameters.Add("@Sunday_Class", OleDbType.Boolean, 1, "Sunday_Class")
        da.UpdateCommand.Parameters.Add("@Correspondence", OleDbType.Boolean, 1, "Correspondence")

        Try
            Conn.Open()
            da.Update(ds_Centres)
            ds_Centres.AcceptChanges()
            da.Dispose()
            Conn.Close()

        Catch ex As Exception
            MsgBox("Cannot open connection ! ")
        End Try

What is going wrong is that the 2 rows in the table become 2 copies of the row which had changes made to it. The row which was unchanged simply disappears!

What am I doing wrong, please?

[jmcilhinney]

You have no WHERE clause in your UPDATE statement, so you are telling the database to update every row. Generally speaking, you specify the primary key in the WHERE clause to identify the row you want to update, e.g.

UPDATE MyTable
SET Name = @Name
WHERE ID = @ID

Does your table have a primary key? It's not the Centre_Name column is it?

While it's legal and possible to change the value of the primary key column(s), it's a bad idea. If you feel that you need to do that then you should generally add an extra column that contains auto-incrementing numeric IDs and use that as the primary key, then add a unique index on your other column. You can then change the value of the other column while maintaining a constant primary key value with which to identify the row.

[paulorton]

Does your table have a primary key? It's not the Centre_Name column is it?

Yes, I do - it's an auto-incrementing ID column.

Re the need for a WHERE clause: I must confess to not getting this - I thought the dataset (which in this case is a complete copy of the table) was all that was required - why would it require me to identify which rows had changed when it contains that information itself?

And whilst I can see that passing the ID of the row that had changed would stop *every* row from being updated, how would I go about passing the IDs of multiple rows that had been altered?

[paulorton]

Sorry about bumping but I really need to get a handle on this stuff.

[jmcilhinney]

The UpdateCommand contains SQL code to update a single record. After you edit the data in your DataTable and call Update on the adapter, basically what happens is that it loops through the DataRows in the DataTable and checks the RowState of each one. For those that have a RowState of Modified, it executes the UpdateCommand, using the field values from the row to set the parameter values of the command. That's why your SQL code needs the ID to identify the row being updated. The SQL code will be executed once for each row that you edited and it must be able to identify that row in order to update the corresponding record in the database. For an example, you might like to follow the CodeBank link in my signature and check out my thread on Retrieving & Saving Data.

[paulorton]

I usually get there in the end - thanks for your time!

[paulorton]

Hmm, I spoke too soon! I'm now getting "Concurrency violation: the UpdateCommand affected 0 of the expected 1 records." when the line "da.Update(ds_Centres)" is executed.

Here is the relevant code:

Public Sub db_EditCentre(ByVal ManagerName As String, ByVal CentreName As String, ByVal ds_Centres As DataSet)

        Dim Conn As OleDbConnection        
        Dim SQL As String
        Dim ConnectionString As String

        SQL = "SELECT * FROM tblCentre_Details Order by ID"
        ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & My.Settings.AppPath & "\" & ManagerName & "_FCLManager2011_Centres.mdb"
        Conn = New OleDbConnection(ConnectionString)
        Dim da = New OleDbDataAdapter(SQL, Conn)
       
        da.UpdateCommand = New OleDbCommand("UPDATE tblCentre_Details SET Centre_Name = @Centre_Name, Monday_Class = @Monday_Class, Tuesday_Class = @Tuesday_Class, Wednesday_Class = @Wednesday_Class, Thursday_Class = @Thursday_Class, Friday_Class = @Friday_Class, Saturday_Class = @Saturday_Class, Sunday_Class = @Sunday_Class, Correspondence = @Correspondence WHERE ID = @ID")
        da.UpdateCommand.Connection = Conn
        da.UpdateCommand.Parameters.Add("@ID", OleDbType.Integer, 5, "ID")
        da.UpdateCommand.Parameters.Add("@Centre_Name", OleDbType.VarChar, 50, "Centre_Name")
        da.UpdateCommand.Parameters.Add("@Monday_Class", OleDbType.Boolean, 1, "Monday_Class")
        da.UpdateCommand.Parameters.Add("@Tuesday_Class", OleDbType.Boolean, 1, "Tuesday_Class")
        da.UpdateCommand.Parameters.Add("@Wednesday_Class", OleDbType.Boolean, 1, "Wednesday_Class")
        da.UpdateCommand.Parameters.Add("@Thursday_Class", OleDbType.Boolean, 1, "Thursday_Class")
        da.UpdateCommand.Parameters.Add("@Friday_Class", OleDbType.Boolean, 1, "Friday_Class")
        da.UpdateCommand.Parameters.Add("@Saturday_Class", OleDbType.Boolean, 1, "Saturday_Class")
        da.UpdateCommand.Parameters.Add("@Sunday_Class", OleDbType.Boolean, 1, "Sunday_Class")
        da.UpdateCommand.Parameters.Add("@Correspondence", OleDbType.Boolean, 1, "Correspondence")

        Try
            Conn.Open()
            da.Update(ds_Centres)
            ds_Centres.AcceptChanges()
            da.Dispose()
            Conn.Close()

        Catch ex As Exception
            MsgBox("Cannot open connection ! ")
        End Try

    End Sub

Private Sub cmdEditCentre_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdEditCentre.Click
        
        If cmdEditCentre.Text = "Edit" Then
            ActionMode = "Edit"
            Enable_chkClass_Days()
            txtCentreName.Enabled = True
            cmdEditCentre.Text = "Save"
            cmdNewCentre.Enabled = False
            cmdDeleteCentre.Enabled = False
            cmdCancel.Enabled = True
            ValidCentreName = True
        Else 'cmdEditCentre.Text = "Save"
           
            For Each row As DataRow In ds_Centres.Tables(0).Rows
                If row("Centre_Name").ToString = lstCentres.SelectedItem.ToString Then
                    row("Centre_Name") = txtCentreName.Text
                    row("Monday_Class") = chkClass_Days0.Checked
                    row("Tuesday_Class") = chkClass_Days1.Checked
                    row("Wednesday_Class") = chkClass_Days2.Checked
                    row("Thursday_Class") = chkClass_Days3.Checked
                    row("Friday_Class") = chkClass_Days4.Checked
                    row("Saturday_Class") = chkClass_Days5.Checked
                    row("Sunday_Class") = chkClass_Days6.Checked
                    row("Correspondence") = chkClass_Days7.Checked
                End If
            Next
            db_EditCentre(gManagerName, gWorkingCentre, ds_Centres)
        End If

    End Sub

Any thoughts?

[jmcilhinney]

The Jet OLE DB provider, which you use to connect to Access, doesn't actually support named parameters. Even though you can give your parameters names to make the code clearer to you, those names are ignored by the system. The system only cares about the position of the parameters, i.e. you need to add the parameters to your command in the same order that they appear in the SQL code. Yours are not in the same order, so that is almost certainly the issue. You have @ID last in the SQL code but you add it first to the command.

[paulorton]

Exactly right - thank you!