0

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@# ?

Bon
  • 309
  • 1
  • 5
  • 16

1 Answers1

1

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);
Caius Jard
  • 72,509
  • 5
  • 49
  • 80
  • What if user wants to change the query? Even a small change ? Then again code change. Again deployment of application. Too much headache for a small change. Let’s assume if the query is big? Then too much string operations? Again performance issue – Vivek Nuna Mar 04 '21 at 05:24
  • 1
    What are you talking about? If the user wants to change the query, and the query is code, then it's a code change.. *It doesn't matter where it is stored*. If you want the change to go live, *it has to be deployed*. Opening a db in access, changing code, and clicking save *is changing code and making a deployment*. You're arguing for some massive difference between "open visual studio, change code, click publish" versus "open access, change code, click save" - absolute inanity – Caius Jard Mar 04 '21 at 05:32
  • you are also doing the same thing boss. Just think about it is there a need to redploy the whole app if it can be done by just altering one SP – Vivek Nuna Mar 04 '21 at 05:34
  • you can test the SO separately, you can check the syntax error also. While running with the code, you need to complete the whole flow to test and debug the SP – Vivek Nuna Mar 04 '21 at 05:41
  • 1
    So what you're saying is you like to play fast and loose with things like change management, end to end testing, and you like to scatter your code all over the place.. OK. I understand your points, but I will never agree that they're mandatory for solving this problem as presented. I have no more input to this discussion – Caius Jard Mar 04 '21 at 05:46
  • it is not play fast and loose. it is a practical example. you don't want to agree, its a different thing. and my question was not at all eligible for negative voting – Vivek Nuna Mar 04 '21 at 05:48