How to solve "Syntax error in INSERT INTO statement"

Question

The connection works fine.I am just facing problem in this portion. My design view of RECORD table is as follows:

Name Type Size

     RecordID                Long Integer                             4
     BatchID                 Long Integer                             4

     ProcessID               Long Integer                             4
     EmployeeID              Long Integer                             4
     Start                   Date With Time                           8

     End                     Date With Time                           8
     Status                  Yes/No                                   1
     Hour                    Long Integer

Here is my code:

protected void btnSave_Click(object sender, EventArgs e)
    {

        DateTime a = new DateTime(cldStart.SelectedDate.Year,cldStart.SelectedDate.Month,cldStart.SelectedDate.Day,Convert.ToInt32(ddlStartHour.SelectedValue),Convert.ToInt32(ddlStartMinute.SelectedValue),00);
        DateTime b = new DateTime(cldEnd.SelectedDate.Year, cldEnd.SelectedDate.Month, cldEnd.SelectedDate.Day, Convert.ToInt32(ddlEndHour.SelectedValue), Convert.ToInt32(ddlEndMinute.SelectedValue), 00);
        TimeSpan c = b-a;

        //insert into record table
        using (OleDbConnection con = new OleDbConnection(conString))
        {
            try
            {
                OleDbCommand cmd = new OleDbCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "INSERT INTO RECORD (BatchID,ProcessID,EmployeeID,Start,End,Status,Hour) VALUES (@batchid,@processid,@employeeid,@start,@end,@status,@hours)";
                cmd.Parameters.AddWithValue("@batchid", Convert.ToInt32(ddlBatchNo.SelectedValue));
                cmd.Parameters.AddWithValue("@processid", Convert.ToInt32(ddlSubCategory.SelectedValue));
                cmd.Parameters.AddWithValue("@employeeid", Convert.ToInt32(ddlEmployeeId.SelectedValue));
                   cmd.Parameters.AddWithValue("@start",a.ToString() );
                   cmd.Parameters.AddWithValue("@end", b.ToString());
                   cmd.Parameters.AddWithValue("@hours", c.ToString());
                   cmd.Parameters.AddWithValue("@status",chboxStatus.Checked);
                cmd.Connection = con;
                con.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                txtRemark.Text = "wrong";
            }
        }

    }

I am trying to insert into record table but it shows me

"syntax error in InSERT into command".

Don't know what is wrong.

mybirthname · Accepted Answer · 2016-12-09 10:16:12Z

4

OleDbCommand doesn't have the concept of names parameters so you must define the parameters in the same order as in your query.

   cmd.Parameters.AddWithValue("@hours", c.ToString());
   cmd.Parameters.AddWithValue("@status",chboxStatus.Checked);

So you should have

   cmd.Parameters.AddWithValue("@status",chboxStatus.Checked);
   cmd.Parameters.AddWithValue("@hours", c.ToString());

But in your query the status is before hours. Also fix your reserved words like others mention it.

answered Dec 9, 2016 at 10:16

mybirthname

18.2k3 gold badges35 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mairaj Ahmad · Accepted Answer · 2016-12-09 10:17:27Z

3

Your column names are keyword like end,hour so you need to surround it in braces

cmd.CommandText = "INSERT INTO RECORD (BatchID,ProcessID,EmployeeID,Start,[End],Status,[Hour]) VALUES (@batchid,@processid,@employeeid,@start,@end,@status,@hours)";

answered Dec 9, 2016 at 10:17

Mairaj Ahmad

14.6k2 gold badges29 silver badges43 bronze badges

1 Comment

user3431431 Over a year ago

Thank you so much !!! I made a silly mistake of using column names as keywords!!Now it works completely fine

esiprogrammer · Accepted Answer · 2016-12-09 10:18:58Z

2

Start,End,Status,Hour field names are keywords. change them to [Start],[End],[Status],[Hour] or either change field names in Database

answered Dec 9, 2016 at 10:18

esiprogrammer

1,4381 gold badge18 silver badges22 bronze badges

Comments

Alec. · Accepted Answer · 2016-12-09 10:15:49Z

You're table name is the same as the record keyword

Try wrapping it in brackets

protected void btnSave_Click(object sender, EventArgs e) {

    DateTime a = new DateTime(cldStart.SelectedDate.Year,cldStart.SelectedDate.Month,cldStart.SelectedDate.Day,Convert.ToInt32(ddlStartHour.SelectedValue),Convert.ToInt32(ddlStartMinute.SelectedValue),00);
    DateTime b = new DateTime(cldEnd.SelectedDate.Year, cldEnd.SelectedDate.Month, cldEnd.SelectedDate.Day, Convert.ToInt32(ddlEndHour.SelectedValue), Convert.ToInt32(ddlEndMinute.SelectedValue), 00);
    TimeSpan c = b-a;

    //insert into record table
    using (OleDbConnection con = new OleDbConnection(conString))
    {
        try
        {
            OleDbCommand cmd = new OleDbCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "INSERT INTO [RECORD] (BatchID,ProcessID,EmployeeID,Start,End,Status,Hour) VALUES (@batchid,@processid,@employeeid,@start,@end,@status,@hours)";
            cmd.Parameters.AddWithValue("@batchid", Convert.ToInt32(ddlBatchNo.SelectedValue));
            cmd.Parameters.AddWithValue("@processid", Convert.ToInt32(ddlSubCategory.SelectedValue));
            cmd.Parameters.AddWithValue("@employeeid", Convert.ToInt32(ddlEmployeeId.SelectedValue));
               cmd.Parameters.AddWithValue("@start",a.ToString() );
               cmd.Parameters.AddWithValue("@end", b.ToString());
               cmd.Parameters.AddWithValue("@hours", c.ToString());
               cmd.Parameters.AddWithValue("@status",chboxStatus.Checked);
            cmd.Connection = con;
            con.Open();
            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            txtRemark.Text = "wrong";
        }
    }

}

Collectives™ on Stack Overflow

How to solve "Syntax error in INSERT INTO statement"

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related