Need help for update to database

[mmayputt]

Friend Sub Inventory_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

    'Connect to database
    dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
    dbSource = "Data Source= C:/Inventory.mdb"
    con.ConnectionString = dbProvider & dbSource
    'Open database connection
    con.Open()
    MsgBox("Inventory is opened now")

    'Import the data on tables
    sql = "SELECT * FROM Brands"
    sql1 = "SELECT * FROM Model"
    da = New OleDb.OleDbDataAdapter(sql, con)
    da1 = New OleDb.OleDbDataAdapter(sql1, con)
    'Build up database Adapters
    da.Fill(ds, "Inventory")
    da1.Fill(ds, "Inventory2")

    maxrows = ds.Tables("Inventory").Rows.Count
    maxrows1 = ds.Tables("Inventory2").Rows.Count

    inc = -1
    inc1 = 0

    con.Close()
    'Calling the list of tire's brands
    For i As Integer = 0 To ds.Tables("Inventory").Rows.Count - 1
        Me.BrandsComboBox.Items.Add(ds.Tables("Inventory").Rows(i).Item(0))
    Next i


End Sub


 Friend Sub SaveButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SaveButton.Click


    Dim ch As New OleDb.OleDbCommandBuilder(da1)
    Dim i As Integer
    For i = 0 To ds.Tables("Inventory2").Rows.Count - 1
        If ds.Tables("Inventory2").Rows(i).Item(1) = ModelsComboBox.Text Then
            ds.Tables("Inventory2").Rows(i).Item(2) = PriceTextBox.Text
            ds.Tables("Inventory2").Rows(i).Item(3) = InStockTextBox.Text
        End If
    Next i
   ** da1.Update(ds, "Inventory2")** I got an error with this sentence
     End Sub

     an error said that 
     OLEDB.Exception was unhandled 
     Syntax error in UPDATE statement.

     I have changed from Inventory2 to Model which is the column name in the database
     but it updated to dataset, not to my database. any suggestion?

[G_Waddell]

Hi,
You've defined ch as an oledbcommandbuilder on da1 but you have not given it a SQL Update statement or procedure to run on the update. You also haven't specified that it is the Update command for da1.

I think what you are looking for is this:

ch.GetUpdateCommand()
da1.Update(ds)

Update a DataAdaptor Microsoft

[TheQuestor]

 Private Sub Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Dim TDID = CType(CType(sender, System.Windows.Forms.Button).Tag, String)
        Dim con As SqlConnection = New SqlConnection("Data Source=" & ServerName & ";Initial Catalog=" & CatName & ";Persist Security Info=True;User ID=" & DBUserName & ";Password=" & Password & "")
        Dim cmd As New SqlCommand
        Try
            con.Open()
            cmd.Connection = con
            cmd.CommandText = "UPDATE TODO SET TDDateCompleted='" & Now & "', TDDone = '1' WHERE TDID='" & TDID & "'"
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            MessageBox.Show("Error while updating record on table..." & ex.Message, " Update Records")
        Finally
            con.Close()
            con.Dispose()
            con = Nothing
            cmd = Nothing
            TDID = Nothing
            End Try
    End Sub

Using something like above is the easiest way to update SQL. Also you really should wrap your code in a "try / end try" statement as it helps track down what is not working.