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

2 Answers2

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
  • 69,583
  • 5
  • 45
  • 72
  • 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
-1

Your query is giving your the query below.

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

so please remove # from the query.

Also please create a Stored procedure and pass the parameter instead of writing the query in the code.

vivek nuna
  • 16,885
  • 12
  • 74
  • 152
  • Really? A stored procedure? Why, when the query can be/already is parameterized, the end? – Caius Jard Mar 04 '21 at 05:08
  • because it makes loosely coupled. let's say you need to modify the query then no need to make the code change and redeploy the application. – vivek nuna Mar 04 '21 at 05:10
  • Keeping your application apsara access logic in a different place to the rest of the application code isn't what i call "loose coupling", it's just an alternative code management strategy, or possibly a change management headache but each to their own. My main issue with your recommendation to do so is that it reads like it is required to solve the problem, when it is not the case – Caius Jard Mar 04 '21 at 05:20
  • @CaiusJard it’s just a suggestion for good practice – vivek nuna Mar 04 '21 at 05:22
  • I'm pointing out that it doesn't read like a suggestion, it reads like an instruction. Add some words like "alternatively, you could create a stored procedure.." if that's your intent.. I don't see it as particularly more "good practice" than eg keeping the query text in a text file that is deployed along with the app. All you're doing with a stored procedure is changing the place a query text is stored; changes still have to be "compiled" and "deployed". Taking a parameterized query and making a parameterized stored procedure that you have to call with a parameterized query,is pointless here – Caius Jard Mar 04 '21 at 05:27
  • https://stackoverflow.com/questions/59880/are-stored-procedures-more-efficient-in-general-than-inline-statements-on-mode have a look on this @CaiusJard – vivek nuna Mar 04 '21 at 05:29