syntax error in query

Question

I am new to programming and is developing a new desktop database applcation in Access, I am trying to insert data into a table. I had two datetime picker and I read the value from it as

jobcodedatabean.PaperRecievedate1 = dtpjobcodedate.Value.Date;
jobcodedatabean.Shipmenentdate = dtpshipmentdate.Value.Date;

and I had passed the databean to a function

 public void addaction(JobCodeDataBean jobcodedatabean)
    {
        MessageBox.Show(jobcodedatabean.Shipmenentdate.ToString());

        try
        {

            OleDbConnection oleDbConnection1 = new System.Data.OleDb.OleDbConnection(connString);
            oleDbConnection1.Open();
            OleDbCommand oleDbCommand1 = new System.Data.OleDb.OleDbCommand("INSERT INTO jobcodemastertable (jobcode ,customercode,totaltrip,shipmentdate,fromPlace, destination,description ,packagetype ,noofpackage ,contactperson ,jobecodedate ) Values ('" + jobcodedatabean.Jobcode + "', '" + jobcodedatabean.Customercode + "' ," + jobcodedatabean.Totaltrip + "," + jobcodedatabean.Shipmenentdate + " ,'" + jobcodedatabean.Fromplace + "','" + jobcodedatabean.Destination + "','" + jobcodedatabean.Description + "','" + jobcodedatabean.Typeofpackage + "','" + jobcodedatabean.Noofpackages + "','" + jobcodedatabean.Contactperson + "'," + jobcodedatabean.PaperRecievedate1 + ") ", oleDbConnection1);
            oleDbCommand1.CommandType = CommandType.Text;
            oleDbCommand1.ExecuteNonQuery();

            oleDbConnection1.Close();

       }
       catch (Exception)
        {
            MessageBox.Show(e);

        }

but i am getting the exception at the query

Syntax error (missing operator) in query expression '2/16/2012 12:00:00 AM'.

In access the date fields are in short date format

Please somebody help to sort out my mistake

why dont you use string.format for this, this is so hard to read. — DotNetUser
– DotNetUser, Commented Feb 16, 2012 at 22:15

Rob Bell · Accepted Answer · 2012-02-16 22:44:06Z

3

Incorrect quotations. To avoid these kinds of mistakes, use ordered parameters:

var myCommand = new OleDbCommand(
    "INSERT INTO MyTable(someDateField, someTextField, someNumberField) VALUES (?, ?, ?)"
);

myCommand.Parameters.Add(DateTime.Now);
myCommand.Parameters.Add("Some text");
myCommand.Parameters.Add(123);

Using parameters also helps protect against SQL injection attacks. In your example, if one of the strings contained an apostrophe, it would fail unless you correctly converted it to two apostrophes. With parameters these are escaped correctly automatically.

edited Feb 16, 2012 at 22:44

answered Feb 16, 2012 at 22:28

Rob Bell

3,7245 gold badges31 silver badges49 bronze badges

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

Comments

Andrei · Accepted Answer · 2012-02-16 22:12:26Z

2

You forgot to enclose dates in quotes:

... ",'" + jobcodedatabean.Shipmenentdate + "' ,'" ...
... "','" + jobcodedatabean.PaperRecievedate1 + "') " ...

Note single quotes around both dates.

answered Feb 16, 2012 at 22:12

Andrei

56.8k10 gold badges92 silver badges114 bronze badges

Collectives™ on Stack Overflow

syntax error in query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related