Error: "Syntax error in INSERT INTO statement"
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.
精彩评论