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

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

Postby 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
pfpc
 
Posts: 6
Joined: Tue 23 Aug 2005 09:32

Postby 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).
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby 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))
pfpc
 
Posts: 6
Joined: Tue 23 Aug 2005 09:32


Return to dotConnect for MySQL