Syntax error in INSERT INTO statement. Can anyone help me with this?

Question

Here is the exception:

            System.Data.OleDb.OleDbException was unhandled
              Message=Syntax error in INSERT INTO statement.
              Source=Microsoft Office Access 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 SyndicateII.Form1.NavRec() in C:\Documents and Settings\David\Desktop\SyndicateII\SyndicateII\Form1.cs:line 787
                   at SyndicateII.Form1.button48_Click(Object sender, EventArgs e) in C:\Documents and Settings\David\Desktop\SyndicateII\SyndicateII\Form1.cs:line 1298
                   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(IntPtr 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 SyndicateII.Program.Main() in C:\Documents and Settings\David\Desktop\SyndicateII\SyndicateII\Program.cs:line 18
                   at System.AppDomain._nExecuteAssembly(RuntimeAssembly 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, Boolean ignoreSyncCtx)
                   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
                   at System.Threading.ThreadHelper.ThreadStart()
              InnerException:

Here is the chunk of code that I am using to add a new row to my Access 2007 database. This chunk of code is outside of my db.open and db.close code. I was getting a connection error before but i commented out the db.dispose line and now its giving me a syntax error. I tried doing an rDS.InsertCommand. That didn't return any errors, but it didn't insert the new row into the database. I've never had to do this before in my code so im not possitvie if i'm going about this the right way. I was wondering if the issue was when the database connection is made in my Form1_Load. I used Microsoft.ACE.OLEDB.12.0, but while searching for help with my error it seems that most people have been using Microsoft.JET.OLEDB.4.0. I tried switching to that but that also gives me an error. I'm really not sure what the issue is and i hope i can get some help. I've been staring at my pc for the past few days tring to figure it out on my own. But i've had no luck.

            string path = "c:\\" + textBox269.Text + "_" + id + ".wav";

            //Add recording to the listview
            item = new ListViewItem(textBox269.Text);
            item.SubItems.Add(richTextBox11.Text);
            item.SubItems.Add(DateTime.Now.ToString());
            item.SubItems.Add(path);
            listView1.Items.Add(item);

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

            DataRow rRow2 = rDS.Tables["Recordings"].NewRow();

            rRow2[1] = id;
            rRow2[2] = textBox269.Text;
            rRow2[3] = richTextBox11.Text;
            rRow2[4] = DateTime.Now.ToString();
            rRow2[5] = path;

            //rRow2["RefID"] = id;
            //rRow2["RecName"] = textBox269.Text;
            //rRow2["Desc"] = richTextBox11.Text;
            //rRow2["DateAdded"] = DateTime.Now.ToString();
            //rRow2["FileLocation"] = path;

            rDS.Tables["Recordings"].Rows.Add(rRow2);

            rMaxRows = rMaxRows + 1;
            rInc = rInc + 1;

            rAD.Update(rDS, "Recordings"); <--this line is where the error occurs

What is the schema? How many columns are in "Recordings"? Also, rRow should use 0-based indexing, 1-5 should maybe be 0-4? — Joachim Isaksson, Jan 20 '12 at 17:27
There are 6 columns so i'm using the 0-5 index. But I don't want to put anything in the 0 index cause its an auto number column in the db. But should i still include the 0 index? I don't have to have it as auto number. I don't actually need that column to be honest. its the Id column that is automatically made as the primary key. — user1161086, Jan 20 '12 at 17:45
I'd check that all the types are correct and - if the DateAdded column is not a string - remove the ToString(). Without seeing the schema any further analysis is kind of hard :) — Joachim Isaksson, Jan 20 '12 at 17:58

score 1 · Answer 1 · edited Jun 25 '12 at 19:19

1

Replace

rAD.Update(rDS, "Recordings");

with this:

rAD.Update(rDS.Tables["Recordings"]);

edited Jun 25 '12 at 19:19

Thiem Nguyen

6,345
7
30
50

answered Jun 25 '12 at 19:05

Shashi Pandey

11
1

ron tornambe · Answer 2 · 2012-01-20T18:50:06.447

0

To insert a row into ms-access, try adapting the following code to use your needs:

public void InsertRow()
{
  try
  {
    using (OleDbConnection con = new OleDbConnection())
    {
      con.ConnectionString =  Users.GetConnectionString(); // your ms-access connection string
      con.Open();
      OleDbCommand cmd = new OleDbCommand();
      cmd.Connection = con;
      cmd.CommandType = CommandType.Text;
      cmd.CommandText = "INSERT INTO Recordings(RecName,Desc,DateAdded, FileLocation) VALUES(id,textBox269.Text,richTextBox11.Text,DateTime.Now,path)";
      cmd.ExecuteNonQuery();
    }
  }
  catch (Exception ex)
  {
    throw new Exception(ex.Message);
  }
}

edited Jan 20 '12 at 18:50

answered Jan 20 '12 at 17:50

ron tornambe

10,452
7
33
60

What does your connection string look like? Your table definition would also be helpful. – ron tornambe Jan 20 '12 at 18:28
con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source = U:/Syndicate II/Syndicate II.accdb;Persist Security Info = False"; – user1161086 Jan 20 '12 at 18:31
Sorry, i'm not sure what you mean by table definition. – user1161086 Jan 20 '12 at 18:32
The table definition refers to the Access Table Recordings and what data types and other properties you set. – ron tornambe Jan 20 '12 at 18:41
My recordings table has 6 columns. ID which is an auto number data type. RefID is a number data type. RecName and Desc are text data types. DateAdded is a date/time data type. And FileLocation is a hyperlink data type. – user1161086 Jan 20 '12 at 18:45
I edited the code above - in the INSERT I removed the reference to the auto-number and removed the ToString() from the date/time. Please try it again. – ron tornambe Jan 20 '12 at 18:51
ID is the auto number not RefID. RefID is the column used to link the recordings table to another table. – user1161086 Jan 20 '12 at 18:59
Thank you very much for the help you've given me so far. But I've got to leave now and wont be able to continue this anymore today. But if you do think of anything else that may help or would be good to try. Please post it. I'll be working on this over the weekend if I have time. Thanks again. – user1161086 Jan 20 '12 at 19:13

Syntax error in INSERT INTO statement. Can anyone help me with this?

2 Answers2