How can I update my access database via VB.net using this code

[Mr Brownstone]

Hi everyone, I nearly finished my project but have come across an error. I can't update the database, it's the only error that comes up. I tried a few things but couldn't get my head around it. Any Help will be greatly appreciated.

Here is the main snippet of code with the error.

Imports System.Data.OleDb

Public Class Form2

    Dim column1string As String
    Dim column2string As String
    Dim column3string As String
    Dim column4string As String
    Dim Count As Integer
    Dim Count2 As Integer
    Dim Count3 As Integer
    Dim Count4 As Integer
    Dim playerquerysql As String


    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        'create constant and things
        Dim con As New OleDb.OleDbConnection
        Dim cnn As New OleDb.OleDbConnection
        Dim ds_G As New DataSet
        Dim ds_Y As New DataSet
        Dim ds_T As New DataSet
        Dim dt_G As New DataTable
        Dim da_y As New OleDb.OleDbDataAdapter
        Dim da_g As New OleDb.OleDbDataAdapter
        Dim da_t As New OleDb.OleDbDataAdapter
        Dim sql As String
        Dim iStGreen As Integer
        Dim iStYellow As Integer
        Dim vGames(4) As String
        vGames(0) = ""
        vGames(1) = "Snap"
        vGames(2) = "Cribbage"
        vGames(3) = "Spilikins"
        vGames(4) = "Scrabble"
        Dim iGames As Integer
        Dim i As Integer
        Dim j As Integer
        Dim g As Integer


        iStYellow = 18
        iStGreen = 18
        iGames = 4


        Dim aGID As String = String.Concat(iStGreen, iGames)
        Dim aYId As String = String.Concat(iStYellow, iGames)


        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = db.mdb"

        con.Open()

        sql = "SELECT * FROM Pupils"
        da_y = New OleDb.OleDbDataAdapter(sql, con)
        da_y.Fill(ds_Y, "Pupils")

        sql = "SELECT * FROM Pupils_1"
        da_g = New OleDb.OleDbDataAdapter(sql, con)
        da_g.Fill(ds_G, "Pupils_1")

        sql = "SELECT * FROM tmpFixtures"
        da_t = New OleDb.OleDbDataAdapter(sql, con)
        da_t.Fill(ds_T, "tmpFixtures")

        For g = 5 To 8
            For i = 0 To 17
                If ds_Y.Tables("Pupils").Rows(i).Item(g) <> True Then
                    For j = 0 To 17

                        If ds_G.Tables("Pupils_1").Rows(j).Item(g) <> True Then
                            If ds_Y.Tables("Pupils").Rows(i).Item("Class_Y") <> ds_G.Tables("Pupils_1").Rows(j).Item("Class_G") Then
                                If ds_Y.Tables("Pupils").Rows(i).Item("Oponent_Y").ToString <> ds_G.Tables("Pupils_1").Rows(j).Item("Forename_G") Then

                                    ds_G.Tables("Pupils_1").Rows(j).Item(g) = True
                                    ds_Y.Tables("Pupils").Rows(i).Item(g) = True
                                    ds_Y.Tables("Pupils").Rows(i).Item("Oponent_Y") = ds_G.Tables("Pupils_1").Rows(j).Item("Forename_G")
                                    ds_G.Tables("Pupils_1").Rows(j).Item("Oponent_G") = ds_Y.Tables("Pupils").Rows(i).Item("Forename_Y")


                                    Dim cb_G As New OleDb.OleDbCommandBuilder(da_g)
                                    Dim updateCommand As New OleDbCommand("UPDATE abc SET Forename_G = ?, Class_G = ?", con)

                                    updateCommand.Parameters.Add("Forename_G", OleDbType.VarChar, 0, "Forename_G")
                                    updateCommand.Parameters.Add("Class_G", OleDbType.VarChar, 0, "Forename_G")


                                    da_g.UpdateCommand = updateCommand


                                    da_t.Update(dt_G)
                                    da_t.Update(dt_T, "tmpFixtures") 'error

                                    updateCommand.Parameters.Add("c", OleDbType.Integer, 0, "c")
                                    updateCommand.Parameters.Add("c", OleDbType.VarChar, 0, "c")

                                    da_g.Update(ds_Y, "pupils")


                                    Exit For
                                End If
                            End If
                        End If

                    Next j
                    Exit For
                End If

            Next i
            Exit For
        Next g

        con.Close()
    End Sub

Thanks

Ryan

[ptaylor965]

Hi Try Using

Insert & Update command

Dim OLECon As New OleDb.OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = db.mdb")
Dim OLECmd As New OleDb.OleDbCommand
Dim OLEStr as String

OLEStr ="UPDATE abc SET" 
OLEStr += " Forname_G = '" & ds_G.Tables("Pupils_1").Rows(j).Item("Forename_G") & "',"
OLEStr += " Class_G = '" & ds_G.Tables("Pupils_1").Rows(j).Item("Class_G") & "'"

OLECmd = New OleDb.OleDbCommand(OLEStr, OLEConn)

OLECon.Open()
OLECmd.ExecuteNonQuery()
OLECon.Close()

It works for SQL and i believe it also works for access databases

[Mr Brownstone]

Thanks alot it Worked :)