开发者

Error: "Syntax error in INSERT INTO statement"

开发者 https://www.devze.com 2023-03-25 02:59 出处:网络
I\'m using vb.net 2010, my provider is OLEDB. I\'m making a database program using vb.net which goes through the database through the navigation buttons I have created, however, I get the following er

I'm using vb.net 2010, my provider is OLEDB. I'm making a database program using vb.net which goes through the database through the navigation buttons I have created, however, I get the following error after I press the the Commit or Update button. ERROR is {Syntax error in INSERT INTO statement.} It occurs at two places within the 2 buttons at {dataadapter.Update(dataset, "GRE Library")}

Everything else works fine as far as I know. I do have other forms, but they are not really important or linked.

HERE IS MY CODE:

Public Class EditLibraryForm

Dim MaxRows As Integer
Dim inc As Integer
Dim connection As New OleDb.OleDbConnection
Dim databaseProvider As String
Dim databaseSource As String

Dim dataset As New DataSet
Dim dataadapter As OleDb.OleDbDataAdapter
Dim sql As String

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

    databaseProvider = "Provider=Microsoft.ACE.OLEDB.12.0;"
    databaseSource = "Data Source = H:\Game Revolutionaries Entertainment TEST2\Game Revolutionaries Entertainment\Resource\GRE Library.accdb"
    connection.ConnectionString = databaseProvider & databaseSource

    connection.Open()
    sql = "SELECT * FROM [GRE Library Logistics]"
    dataadapter = New OleDb.OleDbDataAdapter(sql, connection)
    dataadapter.Fill(dataset, "GRE Library")

    connection.Close()

    MaxRows = dataset.Tables("GRE Library").Rows.Count
    inc = -1

End Sub

Private Sub NavigateRecords()

    txtname.Text = dataset.Tables("GRE Library").Rows(inc).Item(0).ToString
    txtrating.Text = dataset.Tables("GRE Library").Rows(inc).Item(2).ToString
    txtgenre.Text = dataset.Tables("GRE Library").Rows(inc).Item(3).ToString
    txtplatform.Text = dataset.Tables("GRE Library").Rows(inc).Item(4).ToString
    txtrd.Text = dataset.Tables("GRE Library").Rows(inc).Item(5).ToString
    txtdc.Text = dataset.Tables("GRE Library").Rows(inc).Item(6).ToString
    txtinfo.Text = dataset.Tables("GRE Library").Rows(inc).Item(7).ToString
    txtpn.Text = dataset.Tables("GRE Library").Rows(inc).Item(8).ToString
    txtsupport.Text = dataset.Tables("GRE Library").Rows(inc).Item(9).ToString
    txtnews.Text = dataset.Tables("GRE Library").Rows(inc).Item(10).ToString


End Sub
Private Sub btnnext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnnext.Click

    If inc <> MaxRows - 1 Then
        inc = inc + 1
        NavigateRecords()
    Else
        MsgBox("No More items in Library")
    End If

End Sub

Private Sub btnprevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnprevious.Click

    If inc > 0 Then
        inc = inc - 1
        NavigateRecords()
    Else
        MsgBox("This is First item")
    End If

End Sub

Private Sub btnlast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnlast.Click

    If inc <> MaxRows - 1 Then
        inc = MaxRows - 1
        NavigateRecords()
    Else
        MsgBox("Already on last item")
    End If

End Sub

Private Sub btnfirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnfirst.Click

    If inc <> 0 Then
        inc = 0
        NavigateRecords()
    Else
        MsgBox("Already on first item")
    End If

End Sub

Private Sub btnupdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnupdate.Click

    Dim cb As New OleDb.OleDbCommandBuilder(dataadapter)

    txtname.Text = dataset.Tables("GRE Library").Rows(inc).Item(0).ToString
    txtrating.Text = dataset.Tables("GRE Library").Rows(inc).Item(2).ToString
    txtgenre.Text = dataset.Tables("GRE Library").Rows(inc).Item(3).ToString
    txtplatform.Text = dataset.Tables("GRE Library").Rows(inc).Item(4).ToString
    txtrd.Text = dataset.Tables("GRE Library").Rows(inc).Item(5).ToString
    txtdc.Text = dataset.Tables("GRE Library").Rows(inc).Item(6).ToString
    txtinfo.Text = dataset.Tables("GRE Library").Rows(inc).Item(7).ToString
    txtpn.Text = dataset.Tables("GRE Library").Rows(inc).Item(8).ToString
    txtsupport.Text = dataset.Tables("GRE Library").Rows(inc).Item(9).ToString
    txtnews.Text = dataset.Tables("GRE Library").Rows(inc).Item(10).ToString



    dataset.Tables("GRE Library").Rows(inc).Item(0) = txtname.Text
    dataset.Tables("GRE Library").Rows(inc).Item(2) = txtrating.Text
    dataset.Tables("GRE Library").Rows(inc).Item(3) = txtgenre.Text
    dataset.Tables("GRE Library").Rows(inc).Item(4) = txtplatform.Text
    dataset.Tables("GRE Library").Rows(inc).Item(5) = txtrd.Text
    dataset.Tables("GRE Library").Rows(inc).Item(6) = txtdc.Text
    dataset.Tables("GRE Library").Rows(inc).Item(7) = txtinfo.Text
    dataset.Tables("GRE Library").Rows(inc).Item(8) = txtpn.Text
    dataset.Tables("GRE Library").Rows(inc).Item(9) = txtsupport.Text
    dataset.Tables("GRE Library").Rows(inc).Item(10) = txtnews.Text


    dataadapter.Update(dataset, "GRE Library")
    MsgBox("Library updated")

End Sub

Private Sub btnaddnew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnaddnew.Click

    btncommit.Enabled = True
    btnaddnew.Enabled = False
    btnupdate.Enabled = False
    btndelete.Enabled = False

    txtname.Clear()
    txtrating.Clear()
    txtgenre.Clear()
    txtplatform.Clear()
    txtrd.Clear()
    txtdc.Clear()
    txtinfo.Clear()
    txtpn.Clear()
    txtsupport.Clear()
    txtnews.Clear()

End Sub

Private Sub btnclear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnclear.Click

    btncommit.Enabled = False
    btnaddnew.Enabled = True
    btnupdate.Enabled = True
    btndelete.Enabled = True

    inc = 0
    NavigateRecords()

End Sub

Private Sub btncommit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btncommit.Click

    If inc <> -1 Then

        Dim cb As New OleDb.OleDbCommandBuilder(dataadapter)
        Dim datasetNewRow As DataRow

        datasetNewRow = dataset.Tables("GRE Library").NewRow()

        datasetNewRow.Item("Name") = txtname.Text
        datasetNewRow.Item("PersonelRating") = txtrating.Text
        datasetNewRow.Item("Genre") = txtgenre.Text
        datasetNewRow.Item("Platform") = txtplatform.Text
        datasetNewRow.开发者_JAVA技巧Item("ReleaseDate") = txtrd.Text
        datasetNewRow.Item("DevelopingCompany") = txtdc.Text
        datasetNewRow.Item("GameInformation") = txtinfo.Text
        datasetNewRow.Item("PersonalNotes") = txtpn.Text
        datasetNewRow.Item("Support") = txtsupport.Text
        datasetNewRow.Item("News") = txtnews.Text


        dataset.Tables("GRE Library").Rows.Add(datasetNewRow)

        dataadapter.Update(dataset, "GRE Library")

        MsgBox("New item has been added to the library")

        btncommit.Enabled = False
        btnaddnew.Enabled = True
        btnupdate.Enabled = True
        btndelete.Enabled = True

    End If

End Sub

Private Sub btndelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndelete.Click


    Dim cb As New OleDb.OleDbCommandBuilder(dataadapter)
    dataset.Tables("GRE Library").Rows(inc).Delete()
    MaxRows = MaxRows - 1

    inc = 0
    NavigateRecords()
    dataadapter.Update(dataset, "GRE Library")

End Sub


You may want to use the .GetInsertCommand().CommandText method & property to retrieve what the builder is creating for you and display it on the screen so you can determine what issue there may be with your method.

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号