Updating a mysql database using a dataset - parameter use query.

pfpc · Post by **pfpc** » Fri 24 Mar 2006 16:41

I am using mysql database with vb.net 2003
I have read in a dataset and made changes to it
My problem is reloading the data from the database and none of the changes have been made to the actual database itself.

Code: Select all

 ' set the loop variables
            Dim RowLoopIndex As Integer
            For RowLoopIndex = 0 To (DataSetLAUpdateCOver.Tables(0).Rows.Count - 1)

Try


                    ' Read in the variables for each row
                    strFFNumber = ds.Tables(0).Rows(RowLoopIndex).Item(0).ToString

           
 ' now update the dataset
' First, this is how to start the edit process on a record, in this case record 0.  
DataSetLAUpdateCOver.Tables(0).Rows(RowLoopIndex).BeginEdit()

DataSetLAUpdateCOver.Tables(0).Rows(RowLoopIndex).Item(2) = 100
DataSetLAUpdateCOver.Tables(0).Rows(RowLoopIndex).Item(1) = "IamChanged"
DataSetLAUpdateCOver.Tables(0).Rows(RowLoopIndex).Item(3) = 2


' row no longer in edit status, not saved yet either
DataSetLAUpdateCOver.Tables(0).Rows(RowLoopIndex).EndEdit()

Catch ex As Exception
 End Try
Next

now commit the changes to the dataset and send the changes in to a datagrid

Code: Select all

try

                ' Changes are committed on the dataset
                DataSetLAUpdateCOver.AcceptChanges()

                ' update the database
                MySqlDataAdapterLAUpdateCOver.Update(DataSetLAUpdateCOver.Tables(0))

                ' bind to the datagrid
                DatagridUpdateCOver1.DataSource = "DataSetLAUpdateCOver"
                DatagridUpdateCOver1.DataBind()


            Catch ex As Exception
                ' Error during Update, add code to locate error, reconcile 
                ' and try to update again.
            End Try

The dataset shows the changes in the DatagridUpdateCOver.
But if I refill the dataset from the database there are no changes

... fill the dataset here and

' bind to the datagrid
DatagridUpdateCOver1.DataSource = "DataSetLAUpdateCOver"
DatagridUpdateCOver1.DataBind()
but the changes are missing so the database hasn't updated

This is the line that doesn't work
' update the database
MySqlDataAdapterLAUpdateCOver.Update(DataSetLAUpdateCOver.Tables(0))

Can any body see why this code isn't working as the try catch do not bring up any error

This is the dataadapter.

'mySqlUpdateCommand1
'
Me.mySqlUpdateCommand1.CommandText = "UPDATE ATTENDANCE_LeaveAllowances " & Microsoft.VisualBasic.ChrW(10) & "SET Basic = :Basic, " & Microsoft.VisualBasic.ChrW(10) & "Service = :Service " & Microsoft.VisualBasic.ChrW(10) & "WHER" & _
"E FFNumber = :FFNumber"
Me.mySqlUpdateCommand1.Connection = Me.MySqlConnection
Me.mySqlUpdateCommand1.Name = "mySqlUpdateCommand1"
Me.mySqlUpdateCommand1.Parameters.Add(New CoreLab.MySql.MySqlParameter("Basic", CoreLab.MySql.MySqlType.VarChar, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Basic", System.Data.DataRowVersion.Current, Nothing))
Me.mySqlUpdateCommand1.Parameters.Add(New CoreLab.MySql.MySqlParameter("Service", CoreLab.MySql.MySqlType.VarChar, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "Service", System.Data.DataRowVersion.Current, Nothing))
Me.mySqlUpdateCommand1.Parameters.Add(New CoreLab.MySql.MySqlParameter("FFNumber", CoreLab.MySql.MySqlType.VarChar, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "FFNumber", System.Data.DataRowVersion.Original, Nothing))
Me.mySqlUpdateCommand1.UpdatedRowSource = System.Data.UpdateRowSource.None

Alexey · Post by **Alexey** » Mon 27 Mar 2006 09:24

When you use AcceptChanges() method before MySqlDataAdapter.Update() all changed rows obtain RowState = Unchanged. That's why Update() method doesn't update database (because nothing is to be updated).

pfpc · Post by **pfpc** » Thu 30 Mar 2006 16:42

Thanks

That solved my problem

This now works as follows

Code: Select all

For loop

  
                ' Start the edit process on a row 
                DataSet1.Tables(0).Rows(RowLoopIndex).BeginEdit()
                ' Set the variables
                DataSet1Tables(0).Rows(RowLoopIndex).Item(1) = CInt(strCarryOver)
                DataSet1.Tables(0).Rows(RowLoopIndex).Item(2) = CInt(dblCalcBasic.ToString)
                DataSet1.Tables(0).Rows(RowLoopIndex).Item(3) = CInt(dblService.ToString)
                ' The row is no longer editable but not saved yet either
                DataSet1.Tables(0).Rows(RowLoopIndex).EndEdit()
            Next

            ' Update the database
            MySqlDataAdapter1.Update(DataSet1.Tables(0))