Compare date from database using parameters

Question

string queryString = "SELECT SUM(skupaj_kalorij)as Skupaj_Kalorij  "
                + "FROM (obroki_save LEFT JOIN users ON obroki_save.ID_uporabnika=users.ID)"
                + "WHERE (users.ID= " + a.ToString() + ") AND (obroki_save.datum= @datum)";

            using (OleDbCommand cmd = new OleDbCommand(queryString,database))                                    
                {
                    DateTime datum = DateTime.Today;
                    cmd.Parameters.AddWithValue("@datum", datum);
                }
            loadDataGrid2(queryString);

I tried now with parameters. But i don't really know how to do it correctly. I tried like this, but the parameter datum doesn't get any value(according to c#).

what error? also where are you comparing a date, I can see you create date but not use it? — Pharabus
– Pharabus, Commented May 20, 2010 at 13:08
The error may be that ToShortDateString returns the date in a format that SQL does not recognizes. (It is generally better to use parameters to pass parameters in a query.) But without indication of what error actually occurred, it's hard to tell if the cause of the error is this or something entirely different. — user192472
– user192472, Commented May 20, 2010 at 13:10
numbers syntax error in query command in the whole where statement(users.id=1 AND obroki_save.datum=20.5.2010) — Simon
– Simon, Commented May 20, 2010 at 13:15

masoud ramezani · Accepted Answer · 2010-05-20 13:27:25Z

3

please try this :

database = new OleDbConnection(connectionString);
                database.Open();
                date = DateTime.Now.ToShortDateString();
                string queryString = "SELECT SUM(skupaj_kalorij)as Skupaj_Kalorij  "
                    + "FROM (obroki_save LEFT JOIN users ON obroki_save.ID_uporabnika=users.ID)" 
                    + "WHERE users.ID= " + a.ToString()+" AND obroki_save.datum= '" +DateTime.Today.ToShortDateString() + "'";
                loadDataGrid2(queryString);

when you use with Date, you must write like this

select * from table where date = '@date'

not like

select * from table where date = @date

edited May 20, 2010 at 13:27

answered May 20, 2010 at 13:08

masoud ramezani

23k32 gold badges104 silver badges153 bronze badges

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

3 Comments

Simon Over a year ago

but man. I don't get no results now.... Couse i changed the datatype from date to text in the database. Now i changed it back to date/time and i get an error that theres a wrong data type in the Conditional statement.

masoud ramezani Over a year ago

use DateTime.Now.ToString(); instead of DateTime.Now.ToShortDateString();

Simon Over a year ago

I changed the commas on the top (doesn't matter how they're called right now :D), so i get an messege that there's an syntax error in the number somewhere in the where statement

Paddy · Accepted Answer · 2010-05-20 13:09:58Z

1

While it's usually useful to post the error, I'd hazard a guess and say that you're getting a conversion error with your date.

You should really look at parameterising your queries...

You should read this: http://www.aspnet101.com/2007/03/parameterized-queries-in-asp-net/

And if you can't be bothered reading that, then try changing your 'a' variable to '1; DROP TABLE obroki; --' (but only after you back up your database).

answered May 20, 2010 at 13:09

Paddy

34k15 gold badges82 silver badges121 bronze badges

2 Comments

David-W-Fenton Over a year ago

Did you try the # delimiter for the date?

David-W-Fenton Over a year ago

BTW, Jet/ACE is not vulnerable to that particular SQL injection vulnerability, because it doesn't execute but one SQL statement at a time.

David-W-Fenton · Accepted Answer · 2010-05-20 23:41:45Z

0

Perhaps you need to write your SQL string in the SQL dialect of the database you're using. In Jet/ACE SQL (what's used by Access), the delimiter for date values is #, so you'd need this:

  obroki_save.datum= #" +DateTime.Today.ToShortDateString() + "#"

Of course, some data interface libraries translate these things for you, so that may not be the problem here.

answered May 20, 2010 at 23:41

David-W-Fenton

23.2k4 gold badges48 silver badges59 bronze badges

Collectives™ on Stack Overflow

Compare date from database using parameters

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related