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#).

Answer 1

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

Answer 2

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).

Answer 3

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.