VB-Access: Syntax error in INSERT INTO command
-
I am using following code to populate dataTable and then updating Access Table. I am getting error "OleDbException was unhandled" "Syntax error in INSERT INTO statement. Any help would be appreciated. Thanks, Aman
Private Sub dataGridAccess() Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & AccessFile Dim myConnection As OleDbConnection = New OleDbConnection myConnection.ConnectionString = connString myConnection.Open() Dim myTable As String = "RealTimeWQActual" Dim myCmd As String = "Select * from " & myTable & " ORDER BY sdate" ' create a data adapter Dim da As OleDbDataAdapter Try da = New OleDbDataAdapter(myCmd, myConnection) Catch ex As Exception ' Catch the error. MsgBox(ex.ToString) ' Show friendly error message. End Try MsgBox(myTable & " " & myCmd) ''Dim dr As New OleDbDataReader ' Create command builder Dim CB As OleDbCommandBuilder = New OleDbCommandBuilder(da) ' create a new dataset Dim ds As DataSet = New DataSet ' fill dataset da.Fill(ds, myTable) ' Insert code to populate the DataTable with rows. ' Set the DataSource and DataMember of the DataGrid control. DataGrid1.SetDataBinding(ds, myTable) Dim dt As DataTable = ds.Tables(myTable) Dim rrows = dt.Rows.Count MsgBox("rrows= " & rrows) ' NewRow to create a DataRow. Dim row As DataRow row = dt.NewRow() ' Then add the new row to the collection. row("SDATE") = "1" row("STIME") = "1" row("Parameter") = "1" row("Value") = "1" row("d1") = "1" row("d2") = "1" dt.Rows.Add(row) '**** error comes at the next step *** da.Update(ds, myTable) '**** Program runs without the previous step and dataTable shows the added row myConnection.Close() End Sub -
I am using following code to populate dataTable and then updating Access Table. I am getting error "OleDbException was unhandled" "Syntax error in INSERT INTO statement. Any help would be appreciated. Thanks, Aman
Private Sub dataGridAccess() Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & AccessFile Dim myConnection As OleDbConnection = New OleDbConnection myConnection.ConnectionString = connString myConnection.Open() Dim myTable As String = "RealTimeWQActual" Dim myCmd As String = "Select * from " & myTable & " ORDER BY sdate" ' create a data adapter Dim da As OleDbDataAdapter Try da = New OleDbDataAdapter(myCmd, myConnection) Catch ex As Exception ' Catch the error. MsgBox(ex.ToString) ' Show friendly error message. End Try MsgBox(myTable & " " & myCmd) ''Dim dr As New OleDbDataReader ' Create command builder Dim CB As OleDbCommandBuilder = New OleDbCommandBuilder(da) ' create a new dataset Dim ds As DataSet = New DataSet ' fill dataset da.Fill(ds, myTable) ' Insert code to populate the DataTable with rows. ' Set the DataSource and DataMember of the DataGrid control. DataGrid1.SetDataBinding(ds, myTable) Dim dt As DataTable = ds.Tables(myTable) Dim rrows = dt.Rows.Count MsgBox("rrows= " & rrows) ' NewRow to create a DataRow. Dim row As DataRow row = dt.NewRow() ' Then add the new row to the collection. row("SDATE") = "1" row("STIME") = "1" row("Parameter") = "1" row("Value") = "1" row("d1") = "1" row("d2") = "1" dt.Rows.Add(row) '**** error comes at the next step *** da.Update(ds, myTable) '**** Program runs without the previous step and dataTable shows the added row myConnection.Close() End SubI am guessing you need to provide the adapter with an insert statement ?
Christian Graus Driven to the arms of OSX by Vista. Read my blog to find out how I've worked around bugs in Microsoft tools and frameworks.
-
I am using following code to populate dataTable and then updating Access Table. I am getting error "OleDbException was unhandled" "Syntax error in INSERT INTO statement. Any help would be appreciated. Thanks, Aman
Private Sub dataGridAccess() Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & AccessFile Dim myConnection As OleDbConnection = New OleDbConnection myConnection.ConnectionString = connString myConnection.Open() Dim myTable As String = "RealTimeWQActual" Dim myCmd As String = "Select * from " & myTable & " ORDER BY sdate" ' create a data adapter Dim da As OleDbDataAdapter Try da = New OleDbDataAdapter(myCmd, myConnection) Catch ex As Exception ' Catch the error. MsgBox(ex.ToString) ' Show friendly error message. End Try MsgBox(myTable & " " & myCmd) ''Dim dr As New OleDbDataReader ' Create command builder Dim CB As OleDbCommandBuilder = New OleDbCommandBuilder(da) ' create a new dataset Dim ds As DataSet = New DataSet ' fill dataset da.Fill(ds, myTable) ' Insert code to populate the DataTable with rows. ' Set the DataSource and DataMember of the DataGrid control. DataGrid1.SetDataBinding(ds, myTable) Dim dt As DataTable = ds.Tables(myTable) Dim rrows = dt.Rows.Count MsgBox("rrows= " & rrows) ' NewRow to create a DataRow. Dim row As DataRow row = dt.NewRow() ' Then add the new row to the collection. row("SDATE") = "1" row("STIME") = "1" row("Parameter") = "1" row("Value") = "1" row("d1") = "1" row("d2") = "1" dt.Rows.Add(row) '**** error comes at the next step *** da.Update(ds, myTable) '**** Program runs without the previous step and dataTable shows the added row myConnection.Close() End SubHere I used ACE.OLEDB.12.0. for office 2007-Access [accdb] But I want you to use Jet.Oledb for office 2003-Access [mdb] Dim Ithuva As String = "Samples To Aman" con = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Temp\sample1.accdb;Persist Security Info=False") con.Open() Dim CmdTxt As String = "Insert into address (city) values ('" & Ithuva & "')" Dim cmd As OleDbCommand = New OleDbCommand(CmdTxt, con) cmd.ExecuteNonQuery() con.Close()
-
I am using following code to populate dataTable and then updating Access Table. I am getting error "OleDbException was unhandled" "Syntax error in INSERT INTO statement. Any help would be appreciated. Thanks, Aman
Private Sub dataGridAccess() Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & AccessFile Dim myConnection As OleDbConnection = New OleDbConnection myConnection.ConnectionString = connString myConnection.Open() Dim myTable As String = "RealTimeWQActual" Dim myCmd As String = "Select * from " & myTable & " ORDER BY sdate" ' create a data adapter Dim da As OleDbDataAdapter Try da = New OleDbDataAdapter(myCmd, myConnection) Catch ex As Exception ' Catch the error. MsgBox(ex.ToString) ' Show friendly error message. End Try MsgBox(myTable & " " & myCmd) ''Dim dr As New OleDbDataReader ' Create command builder Dim CB As OleDbCommandBuilder = New OleDbCommandBuilder(da) ' create a new dataset Dim ds As DataSet = New DataSet ' fill dataset da.Fill(ds, myTable) ' Insert code to populate the DataTable with rows. ' Set the DataSource and DataMember of the DataGrid control. DataGrid1.SetDataBinding(ds, myTable) Dim dt As DataTable = ds.Tables(myTable) Dim rrows = dt.Rows.Count MsgBox("rrows= " & rrows) ' NewRow to create a DataRow. Dim row As DataRow row = dt.NewRow() ' Then add the new row to the collection. row("SDATE") = "1" row("STIME") = "1" row("Parameter") = "1" row("Value") = "1" row("d1") = "1" row("d2") = "1" dt.Rows.Add(row) '**** error comes at the next step *** da.Update(ds, myTable) '**** Program runs without the previous step and dataTable shows the added row myConnection.Close() End Sub