How to pass the data to OracleCommand Class c#

Question

I am trying to pass a variable to a sql script and run in using c#.

I get an error saying

"missing expression".

fromDate and toDate are passed as 'dd//mm//yy format. Am i not passing the parametres coorectly using OracleCommand ?

public void TransactionReport(string fromDate, string toDate)
        {
            string query = "select s.store_code,count(i.invc_sid) as count from invoice_v i Left join store_v s ON i.sbs_no = s.sbs_no and i.store_no = s.store_no Where(i.created_date between to_date(@fromDate,'MM//DD//YY') and to_date(@toDate,'MM//DD//YY'))  and i.proc_status not in ('131072','65536','147456', '81920') and i.invc_type = 0 AND i.sbs_no = 6  GROUP BY  s.store_code";
            conn.Open();
            using (OracleCommand cmd = new OracleCommand(query, conn))
            {
                cmd.Parameters.Add(new OracleParameter("fromDate", fromDate));
                cmd.Parameters.Add(new OracleParameter("toDate", toDate));
                OracleDataReader dr = cmd.ExecuteReader();

Have you execute the query in database directly? EDIT: I think you have forgotten @ in the query. — kostas ch., Mar 24 '14 at 13:37
Oracle parameters are usually marked with ':', e.g. :fromDate — Dmitry Bychenko, Mar 24 '14 at 13:42
http://stackoverflow.com/questions/11048910/oraclecommand-sql-parameters-binding this might help. — Mo Patel, Mar 24 '14 at 13:48

score 0 · Accepted Answer · answered Mar 24 '14 at 13:43

Try : instead of @

string query = "select s.store_code,count(i.invc_sid) as count from invoice_v i Left join store_v s ON i.sbs_no = s.sbs_no and i.store_no = s.store_no Where(i.created_date between to_date(:fromDate,'MM//DD//YY') and to_date(:toDate,'MM//DD//YY'))  and i.proc_status not in ('131072','65536','147456', '81920') and i.invc_type = 0 AND i.sbs_no = 6  GROUP BY  s.store_code";

score 0 · Answer 2 · answered Mar 24 '14 at 13:46

0

In the Oracel you should not use the sign @, this is only in SQL Server, remove the @ only and it will work...

answered Mar 24 '14 at 13:46

Adel

1,468
15
18

What should you put instead @ ? thats a parametre i am passing. – user2539119 Mar 24 '14 at 13:50
Nothing, just the parameter name – Adel Mar 24 '14 at 13:52
See my answer Try `:` instead of `@` – Microsoft DN Mar 24 '14 at 13:54

How to pass the data to OracleCommand Class c#

2 Answers2