CODEWITHSUNDEEP
c#sqlms-access
Bon
Bon

Reputation: 309

C# and MS Access SQL with Date Delimiter

I have encounter an Error with this Query

"SELECT COUNT(*) AS x FROM accounts_info ";
        query += "INNER JOIN customers_info ON accounts_info.cust_code = customers_info.cust_code ";
        query += "WHERE accounts_info.date_due >= #@a# AND accounts_info.is_paid = 0";

OleDbCommand cmd = new OleDbCommand(query, con);
String aaa = DateTime.Now.ToString("MM/dd/yyyy");
cmd.Parameters.AddWithValue("@a", aaa);

which an error tells me: Additional information: Syntax error in date in query expression 'accounts_info.date_due >= #@a# AND accounts_info.is_paid = 0'.

Is the a way I can insert a parameter within the date delimiter #1/13/2021# like #@param@# ?

Upvotes: 0

Views: 78

Answers (1)

Caius Jard
Caius Jard

Reputation: 74710

Two things

  • Remove the #
  • Make the parameter value a date not a string

Like...

"SELECT COUNT(*) AS x FROM accounts_info ";
    query += "INNER JOIN customers_info ON accounts_info.cust_code = customers_info.cust_code ";
    query += "WHERE accounts_info.date_due >= @a AND accounts_info.is_paid = 0";

OleDbCommand cmd = new OleDbCommand(query, con);
cmd.Parameters.AddWithValue("@a", DateTime.Now.Date); //.Date will remove time portion from date

Side note..

OLEDB doesn't do named parameters. You can put them in with names but he name you give is irrelevant, it is the order that is important.. the parameters collection must contain the same number of parameters, and in the same order as they appear on the SQL. For this reason, and so as not to be lulled into thinking a parameter name can be reused several times in the query, some people use ? marks for the parameter placeholders

"SELECT COUNT(*) AS x FROM accounts_info ";
    query += "INNER JOIN customers_info ON accounts_info.cust_code = customers_info.cust_code ";
    query += "WHERE accounts_info.date_due >= ? AND accounts_info.is_paid = 0";

OleDbCommand cmd = new OleDbCommand(query, con);
cmd.Parameters.AddWithValue("p1", DateTime.Now.Date);

Upvotes: 1

Related Questions