olmie75
New member
I am having an issue where I keep getting a runtime error for "syntax error in INSERT INTO statement" on the Update method of a oledb data adapter. I can't seem to get a string that shows me the exact text of the insert statement. Also, I've written code to create & execute the insert statement myself, which works fine, but I would obviously prefer to use the built in data objects to save time.
Can anyone explain why the "da.Update(ds, "Observations")" command gives me this error, and what I can do to fix it?
Also, how can I see the exact text of the insert statement generated by this command? (cb.GetInsertCommand.CommandText just returns a generic template with ? for all values)
Thanks in advance
Here is the code that doesn't work:
This code (where I create the INSERT text myself, works)
Here is the form initialization & declarations:
Can anyone explain why the "da.Update(ds, "Observations")" command gives me this error, and what I can do to fix it?
Also, how can I see the exact text of the insert statement generated by this command? (cb.GetInsertCommand.CommandText just returns a generic template with ? for all values)
Thanks in advance
Here is the code that doesn't work:
VB.NET:
Private Sub AddRecord()
Dim cb As New OleDb.OleDbCommandBuilder(da)
Dim dsNewRow As DataRow
dsNewRow = ds.Tables("Observations").NewRow()
'dsNewRow.Item("Timestamp") = Now
dsNewRow.Item("Project_FK") = cboProject.SelectedValue
dsNewRow.Item("ProjPlant_FK") = cboPlant.SelectedValue
dsNewRow.Item("MfgArea_FK") = cboMfgArea.SelectedValue
dsNewRow.Item("ProjTeam_FK") = cboTeamMember.SelectedValue
dsNewRow.Item("Observation") = txtObservation.Text
dsNewRow.Item("ImpactFactor") = txtImpactFactor.Text
dsNewRow.Item("Recommendation") = txtRecommendation.Text
dsNewRow.Item("POC") = cboPOC.SelectedItem
dsNewRow.Item("SW") = cboSWOT.SelectedItem
dsNewRow.Item("SQDCM") = cboSQDCM.SelectedItem
dsNewRow.Item("BestPractice") = chkBestPractice.Checked
dsNewRow.Item("Use") = chkUseObservation.Checked
ds.Tables("Observations").Rows.Add(dsNewRow)
'cb.GetInsertCommand.CommandText
da.Update(ds, "Observations")
intMaxRows = intMaxRows + 1
End Sub
This code (where I create the INSERT text myself, works)
VB.NET:
Private Sub AddRecord()
Dim strInsert As String
Dim cmdInsert As OleDb.OleDbCommand
strInsert = "INSERT INTO tblObservations (Project_FK, ProjPlant_FK, MfgArea_FK, " & _
"ProjTeam_FK, Observation, ImpactFactor, Recommendation, POC, SW, SQDCM, BestPractice, Use) " & _
"VALUES (" & cboProject.SelectedValue & ", " & _
cboPlant.SelectedValue & ", " & _
cboMfgArea.SelectedValue & ", " & _
cboTeamMember.SelectedValue & ", '" & _
txtObservation.Text & "', '" & _
txtImpactFactor.Text & "', '" & _
txtRecommendation.Text & "', '" & _
cboPOC.SelectedItem & "', '" & _
cboSWOT.SelectedItem & "', '" & _
cboSQDCM.SelectedItem & "', " & _
chkBestPractice.Checked & ", " & _
chkUseObservation.Checked & ")"
cmdInsert = New OleDb.OleDbCommand(strInsert, con)
con.Open()
cmdInsert.ExecuteNonQuery()
con.Close()
intMaxRows = intMaxRows + 1
MsgBox("New Record added to the Database")
End Sub
Here is the form initialization & declarations:
VB.NET:
Public Class frmObservationDetail
Dim intCurrentRow As Integer
Dim intMaxRows As Integer
Dim con As New OleDb.OleDbConnection
Dim ds As New DataSet
Dim da As OleDb.OleDbDataAdapter
Dim strSql As String
Dim bolIsNewRecord As Boolean
Private Sub frmObservationDetail_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'Vb_assessment_toolDataSet.tblMfgAreaList' table. You can move, or remove it, as needed.
Me.TblMfgAreaListTableAdapter.Fill(Me.Vb_assessment_toolDataSet.tblMfgAreaList)
'TODO: This line of code loads data into the 'Vb_assessment_toolDataSet.tblProjTeam' table. You can move, or remove it, as needed.
Me.TblProjTeamTableAdapter.Fill(Me.Vb_assessment_toolDataSet.tblProjTeam)
'TODO: This line of code loads data into the 'Vb_assessment_toolDataSet.tblProjPlant' table. You can move, or remove it, as needed.
Me.TblProjPlantTableAdapter.Fill(Me.Vb_assessment_toolDataSet.tblProjPlant)
'TODO: This line of code loads data into the 'Vb_assessment_toolDataSet.tblProjects' table. You can move, or remove it, as needed.
Me.TblProjectsTableAdapter.Fill(Me.Vb_assessment_toolDataSet.tblProjects)
con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\vb_assessment_tool.mdb"
con.Open()
strSql = "SELECT * FROM tblObservations"
da = New OleDb.OleDbDataAdapter(strSql, con)
da.Fill(ds, "Observations")
con.Close()
intMaxRows = ds.Tables("Observations").Rows.Count
intCurrentRow = 0
NavigateRecords()
End Sub
End Class