Start New Discussion within our Software Development Community

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
    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

    'Calling the list of tire's brands
    For i As Integer = 0 To ds.Tables("Inventory").Rows.Count - 1
    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?

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:


Update a DataAdaptor Microsoft

 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
            cmd.Connection = con
            cmd.CommandText = "UPDATE TODO SET TDDateCompleted='" & Now & "', TDDone = '1' WHERE TDID='" & TDID & "'"
        Catch ex As Exception
            MessageBox.Show("Error while updating record on table..." & ex.Message, " Update Records")
            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.

This article has been dead for over six months. Start a new discussion instead.