Syntax error(missing operation) in query expression

[Rimmi90]

    I have databse in access 2007. my date datatype is Date/Time. I have made function to insert values to database. 
    But it is showingd syntax error(missing operation)..." for insering date. 
    Please help me how should i do i have tried hash in front of date and tried to save with
    parameters.

    My function is 
     public void purchaseProduct(string productname, string challan_no, DateTime challan_date, string bill_no, DateTime bill_date, string Customer)
            {
                try
                {
                    if (con.State != ConnectionState.Open)
                    {
                        con.Open();
                        OleDbCommand insert = new OleDbCommand("INSERT INTO [PRODUCT_DETAILS]([PRODUCT_NAME],[CHALLAN_NO],[CHALLAN_DATE],[BILL_NO],[BILL_DATE],[CUST_NAME])VALUES('"+productname+"','"+challan_no+"','#'+challan_date+'#','"+bill_no+"','#'+bill_date+'#','"+Customer+"')", con);
                        insert.ExecuteNonQuery();
                        MessageBox.Show("Your data has been saved.");
                    }
                }
                catch(Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }

                finally
                {
                    con.Close();
                }
            }
             here how I am calling this function and I have used datetime picker.

             add.purchaseProduct(txtproductname.Text.ToUpper(), txtchallan_no.Text.ToUpper(), txtchallan_date.Value.Date, txtbill_no.Text.ToUpper(), txtbill_date.Value.Date, txtCustomer.Text.ToUpper());

[ChrisHunter]

The Access DateTime format might be different to the C#.NET DateTime format, this is the case with SQL Server. try to research both C#.NET and Access DateTime format and try to figure out how to convert the C# DateTime so that it can be stored in the DB.

[Momerath]

Or use parameterized queries and you don't have to worry about formats.

[Rimmi90]

I have used

parameterized queries

but than i was getting error datatype mismatch in criteria expresson :(

[kvprajapati]

Put at least one space/whitespace between keywords/identifier in SELECT statement and never use hardcoded string.

Have a look at : [CUST_NAME])VALUES('"+productname and INTO [PRODUCT_DETAILS]([PRODUCT_NAME]

Always use parameters.

using(OleDbConnection con=new OleDbConnection(cnStr))
 {
   string sql="INSERT INTO [PRODUCT_DETAILS] ([PRODUCT_NAME],[CHALLAN_NO],[CHALLAN_DATE],[BILL_NO],[BILL_DATE],[CUST_NAME]) VALUES  (@productName],@challanNo,@challanDate,@billNo,@billDate,@custName)";
    using(OleDbCommand insert = new OleDbCommand(sql, con))
     {
      insert.Parameters.AddWithValue("@productName",productname);
      //or insert.Parameters.Add("@productName",OleDbType.VarChar,30).Value=productname;
      insert.Parameters.Add("@challanNo",OleDbType.Integer).Value=challan_no;
      insert.Parameters.Add("@challanDate",OleDbType.DateTime).Value=challan_date;
      ......
      con.Open();
      insert.ExecuteNonQuery();
      con.Close();
      MessageBox.Show("Your data has been saved.");
     }
  }

[Rimmi90]

    using(OleDbCommand insert = new OleDbCommand(query, con))
                        {
                            insert.Parameters.AddWithValue("@productname",productname);
                            insert.Parameters.AddWithValue("@challanno",challan_no);
                            insert.Parameters.AddWithValue("@challandate",challan_date);
                            insert.Parameters.AddWithValue("@billno",bill_no);
                            insert.Parameters.AddWithValue("@billdate",bill_date);
                            insert.Parameters.AddWithValue("@Customer",Customer);
                            insert.ExecuteNonQuery();

          MessageBox.Show("Your data has been saved.");
         }

         Now it is showing Datatype mismatch criteria in expression.
         I have even tried with datatype.
         insert.Parameters.Add("@productname", OleDbType.VarChar).Value = productname;
                            insert.Parameters.Add("@challanno", OleDbType.VarChar).Value = challan_no;
                            insert.Parameters.Add("@challandate", OleDbType.DBDate).Value = challan_date;
                            insert.Parameters.Add("@billno", OleDbType.VarChar).Value = bill_no;
                            insert.Parameters.Add("@billdate", OleDbType.DBDate).Value = bill_date;
                            insert.Parameters.Add("@Customer", OleDbType.VarChar).Value = Customer;
                            But same error.