SQL statement with datetimepicker

Question

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!

Solution 1

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

Solution 2

Try dtpJobDate.Value.

Solution 3

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") + " + "'";

Comments

[Jhonny D. Cano -Leftware-]

I guess DateTimePicker's Text Property is not associated with the current date of the control

[Jhonny D. Cano -Leftware-]

I guess DateTimePicker's Text Property is not associated with the current date of the control

[David Archer]

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

[MatBailie]

+1: Parameterised Queries exist for a reason. And it's not so they can be ignored :)

[David Archer]

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?

[David Archer]

Seems like it should work, but nothing really happening with this one. Trying to get parameters working above.

[Frederik Gheysels]

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;

[David Archer]

Hmm... something seems to be missing. Please check my edit above, am I missing something?

[Frederik Gheysels]

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.

[David Archer]

Sorry, just noticed that and changed it. The error though still states "Fill: SelectCommand.Connection property has not been initialized." Any ideas? Sorry to keep asking!

[Frederik Gheysels]

Just read the error message :) The SqlCommand that you've assigned to your data-adapter, also has a connection property. You must offcourse assign the SqlConnection to this property, so that the DataAdapter knows to which database he has to connect in order to retrieve the information that you want. :) Btw: how are you retrieving data from the DB right now ? You're using a connection as well ... :)

[Frederik Gheysels]

Take a look at MSDN. there exists an SqlConnection class. Instantiate it, and pass the correct connection-string to it via the constructor. SqlConnection conn = new SqlConnection ("..."); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn;

[David Archer]

Cool. I've made some changed, but an unexpected error has now appeared!

[Jhonny D. Cano -Leftware-]

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