update a database using a dataadapter and a dataset/datagrid

pfpc · Post by **pfpc** » Tue 14 Mar 2006 12:57

I am trying to update a database table with changes made to it in code
I have a datset to which I have made changes to some or all rows and I now want to write it back to the database.

But the database just isn't updating.

I have the following code to update on a button click.

Code: Select all

Try
            '' update the database with the dataset
            'Dim ModifiedRecords As DataTable = DataSetLAUpdateCOver.Tables(0).GetChanges(System.Data.DataRowState.Modified)
            ''' If Not ModifiedRecords Is Nothing Then
            DataSetLAUpdateCOver.AcceptChanges()
  
            'To submit the data from the DataSet into the database.
            Dim i As Integer = MySqlDataAdapterLAUpdateCOver.Update(DataSetLAUpdateCOver)

            Label6.Text = "Changed Records: " & i.ToString

            MySqlDataAdapterLAUpdateCOver.Update(DataSetLAUpdateCOver.Tables(0))

            ' bind to the drop down list
            DatagridUpdateCOver.DataSource = "DataSetLAUpdateCOver"
            DatagridUpdateCOver.DataBind()

        Catch ex As Exception
        End Try

I make changes to the datase as below

Code: Select all

' now update the dataset
                    DataSetLAUpdateCOver.Tables(0).Rows(RowLoopIndex).Item(1) = strCarryOver
                    DataSetLAUpdateCOver.Tables(0).Rows(RowLoopIndex).Item(2) = dblCalcBasic
                    DataSetLAUpdateCOver.Tables(0).Rows(RowLoopIndex).Item(3) = dblService

This is the problem
The database is not updated using the dataadapter and dataset
Also
The line Label6.Text = "Changed Records: " & i.ToString
show the records (i) as being = 0
Even when I make a change to the database manually then run the code i still = 0.

Code: Select all

 'MySqlDataAdapterLAUpdateCOver
        '
        Me.MySqlDataAdapterLAUpdateCOver.AcceptChangesDuringFill = False
        Me.MySqlDataAdapterLAUpdateCOver.SelectCommand = Me.mySqlSelectCommandLAUpdateCOver
        Me.MySqlDataAdapterLAUpdateCOver.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "ATTENDANCE_LeaveAllowances", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("FFNumber", "FFNumber"), New System.Data.Common.DataColumnMapping("CarryOver", "CarryOver"), New System.Data.Common.DataColumnMapping("Basic", "Basic"), New System.Data.Common.DataColumnMapping("service", "service"), New System.Data.Common.DataColumnMapping("location", "location"), New System.Data.Common.DataColumnMapping("SDATE", "SDATE")})})
        Me.MySqlDataAdapterLAUpdateCOver.UpdateCommand = Me.mySqlUpdateCommandLAUpdateCOver
        '

'mySqlUpdateCommandLAUpdateCOver

Code: Select all

        '
        Me.mySqlUpdateCommandLAUpdateCOver.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"

And the parameters are as follows

Code: Select all


        Me.mySqlUpdateCommandLAUpdateCOver.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.mySqlUpdateCommandLAUpdateCOver.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.mySqlUpdateCommandLAUpdateCOver.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.mySqlUpdateCommandLAUpdateCOver.UpdatedRowSource = System.Data.UpdateRowSource.None
        '

Any direction on this would be a great help
Thanks