Update database from dataset

[salvelinus]

I've got code that deletes a row from a dataset (also can add a row, haven't looked at updating yet). I want the user to make all changes to the dataset, then when done save all changes back to the database, instead of saving each time a change is made.

The user can select any of about 60 db's, which have similar but not always identical tables. I've tried code like da.Update(ds), da.Update(ds, "TableName"), etc, but get errors a/o the db doesn't update. I can run straight SQL on each change, but want to minimize the connection time.

Example of the delete code (for the dataset) below. This should be simple, but every book/example I've seen assumes a connection to just one database. Thanks.

'Delete selected record
    Private Sub cmdDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
  Handles cmdDelete.Click   
        Dim dt As New DataTable()

        Try
            dt = ds.Tables("[QS]")
            dt.Rows.Remove(dt.Rows(m_intCurRow))
            txtQuNum.Clear()
            txtQuestion.Clear()
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error Deleting Record")
        End Try
    End Sub

[salvelinus]

I've modified code below for adding a record, but I get a syntax error in the INSERT statement, whether I use the GetUpdateCommand or build it myself. There are only two fields in this table, a double and a text field. I've searched all over and can't see what's going wrong. The dataset gets updated, but not the db.

'To add a record
Private Sub cmdNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdNew.Click
        Dim objRow As DataRow

        Try
            objRow = ds.Tables("[QS]").NewRow
            objRow.Item("Q#") = CDbl(txtQuNum.Text)
            objRow.Item("QText") = txtQuestion.Text
            ds.Tables("[QS]").Rows.Add(objRow)

            da.InsertCommand.Connection = cn
            da.Update(ds, "[QS]")
            ds.AcceptChanges()
        Catch ex As Exception
            MessageBox.Show(ex.Message & vbCrLf, "Error Adding Record")
        End Try
    End Sub

'To fill the dataset
Private Sub FillDs(ByVal strTable)
        'Dim cn As New OleDbConnection()
        Dim cmd As New OleDbCommand()
        Dim strSQL, strSqlInsert, strPath, strCn, strProj As String

        strSQL = "SELECT * FROM " & strTable
        strPath = strRelPath & gstrFilePath
        strCn = strProv & strPath

        Try
            If txtQuNum.Text = "" Then
                txtQuNum.Text = "0"
            End If
            strSqlInsert = "INSERT INTO QS ([Q#], QTEXT) VALUES " & CDbl(txtQuNum.Text) & ", '" & txtQuestion.Text & "'"

            cn.ConnectionString = strCn
            cmd = cn.CreateCommand
            cmd.CommandText = strSQL
            da.SelectCommand = cmd
            cb = New OleDbCommandBuilder(da)
            da.UpdateCommand = cb.GetUpdateCommand
            da.DeleteCommand = cb.GetDeleteCommand
            da.InsertCommand = cb.GetInsertCommand
            'da.InsertCommand = New OleDbCommand(strSqlInsert)

            ds.Clear()
            da.FillSchema(ds, SchemaType.Source, strTable)
            da.Fill(ds, strTable)

        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error Reading " & strProj, MessageBoxButtons.OK, MessageBoxIcon.Error)

        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try
    End Sub

[Wallabie]

I see you have a "[" and "]" around the table "QS" in the New_Click event but the brackets are around the Q# in the insert statement.

Not related to the problem but you also have GetDeleteCommand and GetUpDateCommand in the FillDs function which is unnecessary as your Inserting Into the database here at this point.

Also im not sure about the ds.FillSchema part i don't use that myself i just use the ds.Fill...... part, you seem to be filling it twice at that point, maybe someone else might help a bit more there.

EDIT:
Forgot to mention, square brackets are for used for reserve words. Is "QS" are reserved word? Not that it might make any difference anyway.
It would be better if you renamed the fields in the table and the table name to something a bit more easier to use.