Syntax error in INSERT INTO statement

[beta911]

I really need help from you guys... my Insert statement doesn't work in C# but the Update statement was just fine.

Possible to guide me on the delete statement as well?

Here is my code:

string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\\Inetpub\\wwwroot\\H1N1\\TTH H1N1\\TTH H1N1\\bin\\Debug\\H1N1.mdb";
            OleDbConnection conn = new OleDbConnection(ConnectionString);
            OleDbCommand cmd = new OleDbCommand("INSERT INTO H1N1_info (Visitor ID, Visitor Name, Visitor NRIC, Gender, Temperature, PatientName, Patient Wald, Patient Bed, Relationship, Time In) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",conn);
            cmd.Parameters.Add("@Visitor Name", OleDbType.VarChar).Value = txtname.Text;
            cmd.Parameters.Add("@Visitor NRIC", OleDbType.VarChar).Value = txtic.Text;
            cmd.Parameters.Add("@Gender", OleDbType.VarChar).Value = txtgender.Text;
            cmd.Parameters.Add("@Temperature", OleDbType.VarChar).Value = txttemp.Text;
            cmd.Parameters.Add("@Patient Name", OleDbType.VarChar).Value = txtpname.Text;
            cmd.Parameters.Add("@Patient Wald", OleDbType.VarChar).Value = txtwallno.Text;
            cmd.Parameters.Add("@Patient Bed", OleDbType.VarChar).Value = txtbedno.Text;
            cmd.Parameters.Add("@Relationship", OleDbType.VarChar).Value = txtrelationship.Text;
            cmd.Parameters.Add("@Time In", OleDbType.VarChar).Value = txttimein.Text;
            cmd.Parameters.Add("@Visitor ID", OleDbType.VarChar).Value = txtid.Text;
           

            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();

            ClearForm(this);
            MessageBox.Show("Record Added!");

[ddanbe]

Hi beta911, welcome here at DANIWEB.
Could you be more specific please? The statement "It does not work" is a little vague...

[beta911]

Hi ddanbe,

The error i received was "Syntax error in INSERT INTO statement." and pointed to "cmd.ExecuteNonQuery();"

Below is the image:
[img]http://www.jrcamedia.org/Insert%20Error.jpg[/img]

below is the error detail:

System.Data.OleDb.OleDbException was unhandled
  Message="Syntax error in INSERT INTO statement."
  Source="Microsoft JET Database Engine"
  ErrorCode=-2147217900
  StackTrace:
       at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
       at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
       at TTH_H1N1.reg.btnsave_Click(Object sender, EventArgs e) in C:\Inetpub\wwwroot\H1N1\TTH H1N1\TTH H1N1\reg.cs:line 142
       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 TTH_H1N1.Program.Main() in C:\Inetpub\wwwroot\H1N1\TTH H1N1\TTH H1N1\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:

[beta911]

Hi ddanbe,

Tks for helping, i was already solved the problem. :)

[sknake]

Could you post your solution back to this thread in case someone else happens along to read this in the future?

Also don't forget to mark this thread as solved -- and good luck!

[thines01]

Was the problem the SPACES in the column names that needed to be wapped in tick-marks or single quotes?

[beta911]

I was missed type the database columns name and change the cmd.Parameters.Add to cmd.Parameters.AddWithValue

before:

1.
      string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\\Inetpub\\wwwroot\\H1N1\\TTH H1N1\\TTH H1N1\\bin\\Debug\\H1N1.mdb";
   2.
      OleDbConnection conn = new OleDbConnection(ConnectionString);
   3.
      OleDbCommand cmd = new OleDbCommand("INSERT INTO H1N1_info (Visitor ID, Visitor Name, Visitor NRIC, Gender, Temperature, PatientName, Patient Wald, Patient Bed, Relationship, Time In) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",conn);
   4.
      cmd.Parameters.Add("@Visitor Name", OleDbType.VarChar).Value = txtname.Text;
   5.
      cmd.Parameters.Add("@Visitor NRIC", OleDbType.VarChar).Value = txtic.Text;
   6.
      cmd.Parameters.Add("@Gender", OleDbType.VarChar).Value = txtgender.Text;
   7.
      cmd.Parameters.Add("@Temperature", OleDbType.VarChar).Value = txttemp.Text;
   8.
      cmd.Parameters.Add("@Patient Name", OleDbType.VarChar).Value = txtpname.Text;
   9.
      cmd.Parameters.Add("@Patient Wald", OleDbType.VarChar).Value = txtwallno.Text;
  10.
      cmd.Parameters.Add("@Patient Bed", OleDbType.VarChar).Value = txtbedno.Text;
  11.
      cmd.Parameters.Add("@Relationship", OleDbType.VarChar).Value = txtrelationship.Text;
  12.
      cmd.Parameters.Add("@Time In", OleDbType.VarChar).Value = txttimein.Text;
  13.
      cmd.Parameters.Add("@Visitor ID", OleDbType.VarChar).Value = txtid.Text;
  14.
       
  15.
       
  16.
      conn.Open();
  17.
      cmd.ExecuteNonQuery();
  18.
      conn.Close();
  19.
       
  20.
      ClearForm(this);
  21.
      MessageBox.Show("Record Added!");

After: refer to the RED words

string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\\Inetpub\\wwwroot\\H1N1\\TTH H1N1\\TTH H1N1\\bin\\Debug\\H1N1.mdb";
            OleDbConnection conn = new OleDbConnection(ConnectionString);
            OleDbCommand cmd = new OleDbCommand("INSERT INTO H1N1_info (visitor_id, visitor_name, visitor_ic, gender, temperature, patient_name, patient_wall, patient_bed, relationship, time_in) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",conn);
            cmd.Parameters.AddWithValue("@Visitor ID", OleDbType.VarChar).Value = txtid.Text;
            cmd.Parameters.AddWithValue("@Visitor Name", OleDbType.VarChar).Value = txtname.Text;
            cmd.Parameters.AddWithValue("@Visitor NRIC", OleDbType.VarChar).Value = txtic.Text;
            cmd.Parameters.AddWithValue("@Gender", OleDbType.VarChar).Value = txtgender.Text;
            cmd.Parameters.AddWithValue("@Temperature", OleDbType.VarChar).Value = txttemp.Text;
            cmd.Parameters.AddWithValue("@Patient Name", OleDbType.VarChar).Value = txtpname.Text;
            cmd.Parameters.AddWithValue("@Patient Wald", OleDbType.VarChar).Value = txtwallno.Text;
            cmd.Parameters.AddWithValue("@Patient Bed", OleDbType.VarChar).Value = txtbedno.Text;
            cmd.Parameters.AddWithValue("@Relationship", OleDbType.VarChar).Value = txtrelationship.Text;
            cmd.Parameters.AddWithValue("@Time In", OleDbType.VarChar).Value = txttimein.Text;
            
           

            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();

            ClearForm(this);
            MessageBox.Show("Record Added!");

[beta911]

This is a great forum! :)