OleDbException was unhandled - Syntax error in INSERT INTO

Joe Gambler

17y14.4k

I am attempting to use an Access .mdb file as my database on a stand alone PC that has neither SQL or Access installed.
The program will read the records, but when I attempt to add a record I receive the syntax error.
As I have only been learning C# for 8 lessons, this is a little beyond my level of knowledge.

This is being coded & debugged on Visual Studio 2008 Pro. In the school.mdb file. the ID & Phone _Number columns are "numbers" & all other columns are "text".
This code works and "loads" the form with the 1st record in the school.mdb file

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

namespace UsingAccess

{

public partial class namesForm : Form

{

System.Data.OleDb.OleDbConnection con;

DataSet ds1;

System.Data.OleDb.OleDbDataAdapter da;

public namesForm()

{

InitializeComponent();

}

private void Form1_Load(object sender, EventArgs e) /* loads the 1st record in school.mdb to the form */

{

con = new System.Data.OleDb.OleDbConnection();

ds1 = new DataSet();

con.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/school.mdb";

string sql = "SELECT * From [Names]";

da = new System.Data.OleDb.OleDbDataAdapter(sql, con);

con.Open();

da.Fill(ds1, "Names");

NavigateRecords();

con.Close();

con.Dispose();

}

private void NavigateRecords()

{

DataRow dRow = ds1.Tables["Names"].Rows[0];

firstNameTextBox.Text = dRow.ItemArray.GetValue(1).ToString();

lastNameTextBox.Text = dRow.ItemArray.GetValue(2).ToString();

address1TextBox.Text = dRow.ItemArray.GetValue(3).ToString();

address2TextBox.Text = dRow.ItemArray.GetValue(4).ToString();

phoneNoTextBox.Text = dRow.ItemArray.GetValue(5).ToString();

}

However when I attempt to add a record from the form, I receive the error at the code line

da.Update(ds1, "Names");

private void btnSave_Click(object sender, EventArgs e)

{

con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/school.mdb";

con.Open();

System.Data.OleDb.OleDbCommandBuilder cb;

cb = new System.Data.OleDb.OleDbCommandBuilder(da);

DataRow dRow = ds1.Tables["Names"].NewRow();

dRow[0] = IDNoTextBox.Text;

dRow[1] = firstNameTextBox.Text;

dRow[2] = lastNameTextBox.Text;

dRow[3] = address1TextBox.Text;

dRow[4] = address2TextBox.Text;

dRow[5] = phoneNoTextBox.Text;

dRow[6] = yearNoTextBox.Text;

ds1.Tables["Names"].Rows.Add(dRow);

MaxRows = MaxRows + 1;

inc = MaxRows - 1;

da.Update(ds1, "Names");

MessageBox.Show("Entry Added");

}

Thanks for taking the time to read this message and any suggestions or help would be appreciated.

10 Replies

Know the answer? Post it — somebody with the same question will find it here.

Kirtan Patel17y ago
Accepted answer
Code Like Below Use Command Builder GetUpdateCommand() method

            con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/school.mdb";
            con.Open();

            DataRow dRow = ds1.Tables["Names"].NewRow();
            dRow[0] = IDNoTextBox.Text;
            dRow[1] = firstNameTextBox.Text;
            dRow[2] = lastNameTextBox.Text;
            dRow[3] = address1TextBox.Text;
            dRow[4] = address2TextBox.Text;
            dRow[5] = phoneNoTextBox.Text;
            dRow[6] = yearNoTextBox.Text;
            ds1.Tables["Names"].Rows.Add(dRow);
            MaxRows = MaxRows + 1;
            inc = MaxRows - 1;

            //Use Command Builder to Get Update Command First
            OleDbCommandBuilder combuild = new OleDbCommandBuilder(da);
            combuild.GetUpdateCommand();
            da.Update(ds1, "Names");
            MessageBox.Show("Entry Added");

if still it not solved error then
upload your project zip file at
http://mediafire.com

and provide me download link

[email protected]

thank you :)
Joe Gambler15y ago
My apologies, but this was more than 2 years ago and I no longer have the information.
Kemal Umut15y ago
hi i have the same problem. do you remember how did you solved the problem
system17y ago
i am using Adodb connection in vb6
i am using insert into query
i initialize string variable with query and that's value is
insert into tbltemp(emailid,password) values(aa,aa)

i am initializing like this
con="insert into tbltemp(emailid,password) values('aa','aa')"
when i run this query in ms access, it's working properly
but when i run this in vb6 , it's giving error
syntax error in insert into
please help me
Kirtan Patel17y ago
Hi Friend,

your Problem Solved check your mail

Good Luck :)

Happy Coding :)
Joe Gambler17y ago

Hi Sriram,

Thanks for your suggestion. I have a vague understanding of what you are proposing, but, given that I have only had the 7 classes and this is far above my level of knowledge, so perhaps you would be kind enough to possibly give me some code (with explanations/comments) to assist me with my learning the language.

Thanks for taking the time to help me with this problem.

Joe Gambler17y ago

Hi Kirtan

I made the changes you suggested, however the code line:

//Use Command Builder to Get Update Command First

OleDbCommandBuilder combuild = new OleDbCommandBuilder(da);

combuild.GetUpdateCommand();

has produced a different error when I rebuilt the solution This may mean that the Syntax error is still within the code, but I cannot tell , as yet, as it will not rebuild the solution:

OleDbCommandBuilder combuild = new OleDbCommandBuilder(da);

Error:

The type or namespace name 'OleDbCommandBuilder' could not be found (are you missing a using directive or an assembly reference?)

I have zipped the project files(UsingAccess - 211009.zip) and the school..mdb (as schoolMDB.zip).

The download links will be sent to the email address you provided..

Thank you for your kind assistance with this.

Sriram Ramani17y ago
Hi, What i feel is that instead of using datarow , better u can use oledbCommand to directly insert . like this oledbcommand cmd=new oledbcommand(cn,"insert into table_name(fieldnames . . . ) values(text1,text2,text3 . . . .)") cmd.executenonquery() Sure this will help u out.! Any hepl let me know!

Joe Gambler17y ago

Thanks for the quick response.
When I saw your response, I immediately thought that was the obvious answer.
But, unfortunately even with the changes you suggested; the error is still there.

I have attempted changing the 2 columns to text,, removed the Primary Key from the ID column(dRow[0]) of the school.mdb file... nothing seems to change the error.

I have included the exception details below... I'm still perplexed by what is causing the Syntax error.

System.Data.OleDb.OleDbException was unhandled

Message="Syntax error in INSERT INTO statement."

Source="Microsoft JET Database Engine"

ErrorCode=-2147217900

StackTrace:

at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)

at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)

at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)

at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)

at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)

at UsingAccess.namesForm.btnSave_Click(Object sender, EventArgs e) in K:\Tech - Matt\UsingAccess - reads- will not insert\UsingAccess\Form1.cs:line 103

at System.Windows.Forms.Control.OnClick(EventArgs e)

at System.Windows.Forms.Button.OnClick(EventArgs e)

at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)

at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)

at System.Windows.Forms.Control.WndProc(Message& m)

at System.Windows.Forms.ButtonBase.WndProc(Message& m)

at System.Windows.Forms.Button.WndProc(Message& m)

at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)

at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)

at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)

at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)

at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)

at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)

at System.Windows.Forms.Application.Run(Form mainForm)

at UsingAccess.Program.Main() in K:\Tech - Matt\UsingAccess - reads- will not insert\UsingAccess\Program.cs:line 18

at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)

at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)

at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()

at System.Threading.ThreadHelper.ThreadStart_Context(Object state)

at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)

at System.Threading.ThreadHelper.ThreadStart()

InnerException:

Kirtan Patel17y ago
Problem is Here
DataRow dRow = ds1.Tables["Names"].NewRow();
//Here dRow[0] is Numeric Value and You are Passing String

dRow[0] = Convert.ToInt32(IDNoTextBox.Text);

dRow[1] = firstNameTextBox.Text;

dRow[2] = lastNameTextBox.Text;

dRow[3] = address1TextBox.Text;

dRow[4] = address2TextBox.Text;
//Here dRow[5] is Numeric Value and You are Passing String

dRow[5] = Convert.ToInt32(phoneNoTextBox.Text);

dRow[6] = yearNoTextBox.Text;
Try it if it solve your problem then
mark "do you like this answer" please :)
else
let me know what problem you are facing :) i will help you :)