Keep getting ORA-0096 missing expression for oracle db in c# application

Question

Question In my select variable I cant get it to work correctly each time I run it get

ORA-0096 missing expression

I'm thinking it has something to do with the quotes. What I want to do do is search all of my records where the date is between my start_dp(datetimepicker) and my end_dp(datetimepicker) Any help would be greatly appreciated.

 string cr; 
 cr = "SELECT * FROM con_dates WHERE DATE BETWEEN " + start_dp.Value.Date + " and " + end_dp.Value.Date + "";

I tried this expression with params but still the same exact error for oracle Date is a datetime data type and the value.date of both pickers should just be the date so I am not sure what is going on here...

OracleCommand cmd = new OracleCommand("SELECT * FROM con_dates WHERE ""date"" between :start and :end", dvconn);

cmd.Parameters.Add(new OracleParameter("start", start_dp.Value.Date));
cmd.Parameters.Add(new OracleParameter("end", end_dp.Value.date));

What is the value of cr after the concatenation? Are you sure that start_dp.Value.Date return the date string in the right format. Try to use parameters and not concatenate strings. — Evgeni Dimitrov
– Evgeni Dimitrov, Commented Jan 20, 2016 at 12:53

Soner Gönül · Accepted Answer · 2016-01-20 12:54:52Z

2

I think since DATE is a reserved keyword in Oracle, it does not sees your DATE as a column name but sees it as a function as DATE(expr). That's why it expects an expression after it.

You can escape it with using " as "DATE" but as a better way, change it to non-reserved word.

But more important, you should always use parameterized queries. This kind of string concatenations are open for SQL Injection attacks.

edited Jan 20, 2016 at 12:54

answered Jan 20, 2016 at 12:50

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

James Brown Over a year ago

Thanks for your answer but when I tried this and still the same oracle error. cr = "SELECT * FROM con_dates WHERE ""DATE"" BETWEEN " + start_dp.Value.Date + " and " + end_dp.Value.Date + ""; @Soner Gönül

Soner Gönül Over a year ago

@JamesBrown Do not use this way. Use parameterized queries.

James Brown Over a year ago

Updated my question where I used params instead but still the same oracle error. @Soner Gönül

Hambone · Accepted Answer · 2016-01-20 15:42:44Z

Here is an example of how you would use the parameters on your example:

OracleCommand cmd = new OracleCommand(
    "SELECT * FROM con_dates WHERE \"date\" between :start and :end", dvconn);
cmd.Parameters.Add(new OracleParameter("start", OracleDbType.Date));
cmd.Parameters.Add(new OracleParameter("end", OracleDbType.Date));

cmd.Parameters[0].Value = start_dp.Value;
cmd.Parameters[1].Value = end_dp.Value;

OracleDataReader reader = cmd.ExecuteReader();

while (reader.Read())
{

}

reader.Close();

Some notes:

It looks like you were escaping your quotes VBA style... to escape them in C# use the \ backslash
I am assuming start_dp and end_dp are date time edits or something where the Value property is an actual DateTime. If not, then these need to be converted to actual date times. ODP handles the dirty work, but the datatypes are important when you pass them to the parameters.
If you can avoid it, I would seriously not name columns after datatypes, keywords, reserved words, etc...

Ridikk12 · Accepted Answer · 2016-01-20 12:59:28Z

0

Try to add alias to your table name like con_dates c and then use c.DATE.

answered Jan 20, 2016 at 12:59

Ridikk12

4161 gold badge4 silver badges13 bronze badges

Collectives™ on Stack Overflow

Keep getting ORA-0096 missing expression for oracle db in c# application

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related