2

This should hopefully be a simple one. When using a date time picker in a windows form, I want an SQL statement to be carried out, like so:

string sql = "SELECT * FROM Jobs WHERE JobDate = '" + dtpJobDate.Text + "'";

Unfortunately, this doesn't actually provide any results because the JobDate field is stored as a DateTime value. I'd like to be able to search for all records that are on this date, no matter what the time stored may be, any help?

New query:

        SqlDataAdapter da2 = new SqlDataAdapter();
        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = "SELECT * FROM Jobs WHERE JobDate >= @p_StartDate AND JobDate < @p_EndDate";
        cmd.Parameters.Add ("@p_StartDate", SqlDbType.DateTime).Value = dtpJobDate.Value.Date;
        cmd.Parameters.Add ("@p_EndDate", SqlDbType.DateTime).Value = dtpJobDate.Value.Date.AddDays(1);
        cmd.Connection = conn;
        da2.SelectCommand = cmd;
        da2.Fill(dt);
        dgvJobDiary.DataSource = dt;

Huge thanks for all the help!

Improve this question
1
  • I guess DateTimePicker's Text Property is not associated with the current date of the control Commented Sep 9, 2009 at 21:33

4 Answers 4

Reset to default
10

Just one answer: use parametrized queries.

This is for different reasons:

  • security (no risk of SQL Injection
  • no longer those problems for which you're opening a topic
  • performance.

Thus, write your statement like this:

SqlCommand cmd = new SqlCommand();
cmd.CommandText = "SELECT * FROM Jobs WHERE JobDate = @p_Date"
cmd.Parameters.Add ("@p_Date", SqlDbType.DateTime).Value = dtpJobDate.Value;

If you want to ignore the time, then I think the best bet is to do a range search, if the time is stored in the DB, that is. Something like this (just the SQL query):

SELECT * FROM Jobs WHERE JobDate >= @p_StartDate AND JobDate < @p_EndDate

StartDate would then be dtpJobDate.Value.Date, and EndDate would be dtpJobDate.Value.Date.AddDays(1)

If the Time is not stored in the DB, then you can do this:

SELECT * FROM Jobs WHERE JobDate = @p_Date

where the search argument should be dtpJobDate.Value.Date

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

9 Comments

+1: Parameterised Queries exist for a reason. And it's not so they can be ignored :)
This looks really good and I want to use it, but I'm not sure where to take it from this point. Usually I would fill a new data adapter with the sql query, how would I do that with this?
A DataAdapter has a 'SelectCommand' property, to which you can assign an IDbCommand. (If you use Sql Server: a SqlCommand). SqlDataAdapter adapter = new SqlDataAdapter(); SqlCommand cmd = new SqlCommand (); cmd.CommandText = "SELECT ..."; cmd.Parameters. .... adapter.SelectCommand = cmd;
Hmm... something seems to be missing. Please check my edit above, am I missing something?
yes: the name of your parameters is not good. The parameters in you query are called p_StartDate and p_EndDate. But, you're adding Parameters to the Parameters collection which are called 'p_Date' . The parameter-names must match, so you've to add a p_StartDate parameter and a p_EndDate parameter.
|
1

Try dtpJobDate.Value.

Improve this answer

Comments

1

Other than the SQL injection stuff in other answers, you can use something like this:

dtpJobDate.Value.ToString("yyyyMMdd HH:mm:ss");

But probably you won't find anything with exact time match, so you can change your query for something like

string sql = "SELECT * FROM Jobs WHERE JobDate BETWEEN '" + dtpJobDateStart.Value.ToString("yyyyMMdd HH:mm:ss") + "' AND '" + + dtpJobDateEnd.Value.ToString("yyyyMMdd HH:mm:ss") + " + "'";
Improve this answer

2 Comments

Seems like it should work, but nothing really happening with this one. Trying to get parameters working above.
It's your best choice... Anyways take into account the time values of the DateTimePickers are being included into the sql query with this one, so you would want to put the format of the DateTimePickers to Long or Custom
0

First of all - you have left a door open for SQL injection in your example.

Other than that - to answer your question, you'll have to drop the times off of the JobDate column to get the match done. Try something like this (SQL Injection code left in example for comparison)...

string sql = "SELECT * FROM Jobs WHERE CAST(CONVERT(CHAR(8), JobDate, 112) AS DATETIME) = '" + dtpJobDate.Text + "'";

If you were to parameterize your query - you could do it something like this...

using (var conn = new SqlConnection(myConnectionString))
using (var cmd = new SqlCommand("SELECT * FROM Jobs WHERE JobDate = @JobDate", conn))
{
    cmd.Parameters.Add(new SqlParameter("@JobDate", dtpJobDate.Value));

    conn.Open();
    using (var reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            // your code here to deal with the records...
        }
    }
}
Improve this answer

3 Comments

I guess DateTimePicker's Text Property is not associated with the current date of the control
This hasn't worked either - it's also produced an error: "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value." Sorry I didn't mention this earlier, but I'm using a British date system
David, can u tell me if my solution did helped u out?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.