INSERT Syntax Error - Pls Help!

  • Thread starter Thread starter Duppypog
  • Start date Start date
D

Duppypog

I'm trying to insert a record into an Access database, but am getting the error "System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement." I've checked and rechecked the code. I've tried variations, like adding .toString after name.text, nothing I'm doing is working. I hope someone can see what I'm missing.

Thanks for your help!
Lynnette





Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click

Dim strSQL As String
Dim cnn As New OleDbConnection(Constants.OleDbConnectionString)


strSQL = "SELECT * FROM Radios"


Dim objCommand As OleDbCommand = New OleDbCommand(strSQL, cnn) Dim objDataAdapter As New OleDbDataAdapter()
objDataAdapter.SelectCommand = objCommand


Dim objDataSet As New DataSet()
objDataAdapter.Fill(objDataSet, "Radios")

Dim objTable As DataTable
objTable = objDataSet.Tables("Radios")


Dim chkM As CheckBox = Me.chkMike
Dim updval As String
If chkM.Checked Then
updval = "1"
Else
updval = "0"
End If

Dim chkC As CheckBox = Me.chkCharger
Dim updval2 As String
If chkC.Checked Then
updval2 = "1"
Else
updval2 = "0"
End If

Dim objDataRow As DataRow
objDataRow = objTable.NewRow()
objDataRow("intRIN6Digit") = CInt(txtRIN.Text) 'integer datatype; Primary key
objDataRow("strAssignment") = txtAssignment.Text
objDataRow("strCall") = txtCall.Text
objDataRow("strDivision") = ddlDiv.SelectedItem.Text
objDataRow("strDistrict-Section") = txtSecLoc.Text
objDataRow("strTalkGroup") = txtTG.Text
objDataRow("strSerialNumber") = txtSerial.Text
objDataRow("strModel") = txtModel.Text
objDataRow("bPSMike") = CBool(updval)
objDataRow("bIndividualCharger") = CBool(updval2)

If Len(Me.txtChargerNo.Text) > 1 Then 'integer datatype
objDataRow("intChargerNumber") = CInt(txtChargerNo.Text)
Else
objDataRow("intChargerNumber") = Convert.IsDBNull(txtChargerNo.Text)
End If

objDataRow("strOwner") = txtOwner.Text
If Len(Me.txtScan.Text) > 1 Then 'integer datatype
objDataRow("intAssetTagNo") = CInt(txtScan.Text)
Else
objDataRow("intAssetTagNo") = Convert.IsDBNull(txtScan.Text)
End If

objDataRow("dtSentForRepair") = txtRepair.Text
objDataRow("Count") = txtCount.Text

objDataRow("strRemarks") = txtRemarks.Text


objTable.Rows.Add(objDataRow)

Dim objCommandBuilder As New OleDbCommandBuilder(objDataAdapter)
objDataAdapter.InsertCommand = objCommandBuilder.GetInsertCommand()
objDataAdapter.Update(objDataSet, "Radios")

lblConfirm.Text = ("The Record has been added.")

txtRIN.Text = Nothing
txtAssignment.Text = Nothing
txtCall.Text = Nothing
txtSecLoc = Nothing
txtTG.Text = Nothing
txtSerial.Text = Nothing
txtModel.Text = Nothing
txtChargerNo.Text = Nothing
txtOwner.Text = Nothing
txtScan.Text = Nothing
txtRepair.Text = Nothing
txtCount.Text = Nothing
txtRemarks.Text = Nothing
updval = Nothing
updval2 = Nothing

strSQL = Nothing
objCommandBuilder.Dispose()
objDataSet.Dispose()
objDataAdapter.Dispose()
cnn.Close()

End Sub
 
Thanks for your quick response. I ended up changing the fieldnames in the database to strCount and strDistrictLocation and that worked.

Thanks for the time about my "clean up" code. I tend to err on doing too much.

Thanks again,
lds
Looks like you have a "count" column, which is a reserved SQL word.

Try set the QuotePrefix and QuoteSuffix of the commandbuilder object to "[" and "]" respectively.

Also, you don't need to set all the object to Nothing, nor do you need to call Dispose on any of them. This just clutters code and doesn't help. Closing any open connections is sufficient.
I'm trying to insert a record into an Access database, but am getting the error "System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement." I've checked and rechecked the code. I've tried variations, like adding .toString after name.text, nothing I'm doing is working. I hope someone can see what I'm missing.

Thanks for your help!
Lynnette





Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click

Dim strSQL As String
Dim cnn As New OleDbConnection(Constants.OleDbConnectionString)


strSQL = "SELECT * FROM Radios"


Dim objCommand As OleDbCommand = New OleDbCommand(strSQL, cnn) Dim objDataAdapter As New OleDbDataAdapter()
objDataAdapter.SelectCommand = objCommand


Dim objDataSet As New DataSet()
objDataAdapter.Fill(objDataSet, "Radios")

Dim objTable As DataTable
objTable = objDataSet.Tables("Radios")


Dim chkM As CheckBox = Me.chkMike
Dim updval As String
If chkM.Checked Then
updval = "1"
Else
updval = "0"
End If

Dim chkC As CheckBox = Me.chkCharger
Dim updval2 As String
If chkC.Checked Then
updval2 = "1"
Else
updval2 = "0"
End If

Dim objDataRow As DataRow
objDataRow = objTable.NewRow()
objDataRow("intRIN6Digit") = CInt(txtRIN.Text) 'integer datatype; Primary key
objDataRow("strAssignment") = txtAssignment.Text
objDataRow("strCall") = txtCall.Text
objDataRow("strDivision") = ddlDiv.SelectedItem.Text
objDataRow("strDistrict-Section") = txtSecLoc.Text
objDataRow("strTalkGroup") = txtTG.Text
objDataRow("strSerialNumber") = txtSerial.Text
objDataRow("strModel") = txtModel.Text
objDataRow("bPSMike") = CBool(updval)
objDataRow("bIndividualCharger") = CBool(updval2)

If Len(Me.txtChargerNo.Text) > 1 Then 'integer datatype
objDataRow("intChargerNumber") = CInt(txtChargerNo.Text)
Else
objDataRow("intChargerNumber") = Convert.IsDBNull(txtChargerNo.Text)
End If

objDataRow("strOwner") = txtOwner.Text
If Len(Me.txtScan.Text) > 1 Then 'integer datatype
objDataRow("intAssetTagNo") = CInt(txtScan.Text)
Else
objDataRow("intAssetTagNo") = Convert.IsDBNull(txtScan.Text)
End If

objDataRow("dtSentForRepair") = txtRepair.Text
objDataRow("Count") = txtCount.Text

objDataRow("strRemarks") = txtRemarks.Text


objTable.Rows.Add(objDataRow)

Dim objCommandBuilder As New OleDbCommandBuilder(objDataAdapter)
objDataAdapter.InsertCommand = objCommandBuilder.GetInsertCommand()
objDataAdapter.Update(objDataSet, "Radios")

lblConfirm.Text = ("The Record has been added.")

txtRIN.Text = Nothing
txtAssignment.Text = Nothing
txtCall.Text = Nothing
txtSecLoc = Nothing
txtTG.Text = Nothing
txtSerial.Text = Nothing
txtModel.Text = Nothing
txtChargerNo.Text = Nothing
txtOwner.Text = Nothing
txtScan.Text = Nothing
txtRepair.Text = Nothing
txtCount.Text = Nothing
txtRemarks.Text = Nothing
updval = Nothing
updval2 = Nothing

strSQL = Nothing
objCommandBuilder.Dispose()
objDataSet.Dispose()
objDataAdapter.Dispose()
cnn.Close()

End Sub
 
Back
Top