Compare datetime value from datetimepicker with value within db

[Mike]

Hi,
I use MS SQL Express and VS 2005 c#, win application.

I would like to select value rom DateTimePicker and list all values
for selected date within GridView.

I have method as follows:

public DataTable GetOffersForDate_A(DateTime OfferDate)
{
DataTable dt = new DataTable();
if (aConnection.State == ConnectionState.Closed)
aConnection.Open();
SqlDataAdapter da = new SqlDataAdapter();

da.SelectCommand = new SqlCommand(@"SELECT * FROM tblOffer
WHERE OfferDate = " + OfferDate, aConnection);

da.Fill(dt);
return dt;
}

Problem is, it is my conclusion, may be is wrong, in following:

one value looks like:
26.11.2007 00:00:00

and another one is something like this:
26.11.2007 07:35:23

As we can see this is not the same time value. For me time is not
important, I want to find only rows based on date selected in
DateTimePicker.

Furthermore, I am not sure that this is the best apporach.

How I can do this?

Thanks

 

[Nicholas Paldino [.NET/C# MVP]]

Mike,

Well, first, you want to use a parameterized query. Once you have that,
you want to have your query look like this:

select
*
from
tblOffer
where
cast(floor(cast(OfferDate as float)) as datetime) =
cast(floor(cast(@date as float)) as datetime)

The above query will take the date passed in (in the form of the @date
parameter) and the OfferDate date time and trim the time from them. This
way, all the items which have the same date portion of the date time will be
the same and the comparison will work.

In on the off chance you are using the beta of SQL Server 2008, there is
a separate date data type which if you cast to it, I am pretty sure will
give you just the date parts to compare.

On a side note, I notice you are holding onto a connection
(aConnection). Is there are reason you don't just open a new one and then
close it when you are done? Connection pooling should eliminate the need
for something like this.

Additionally, even if you wanted to hold onto the connection, you are
not closing it after this method. Holding onto the connection while it is
open is a pretty bad idea, IMO.

 

[Mike]

Nicholas.

This works, thank you very much.

I admit that I do not understand everything... These days I have a lot
of trouble with DateTime handling and conversion. As it seems, the
only solution (I hope is not) is to use parametrized queries on this
or on that way. I am confused because I do not like that approach.

Especially thanks for advice about closing connection, it was my
mistake nothing else. What you exactly mean when you say "Connection
pooling should eliminate the need for something like this"? Is there
some example which will guide me to learn better solution?

Thanks again.

Mike

 

[Nicholas Paldino [.NET/C# MVP]]

Mike,

The parameterized query really had nothing to do with the solution. You
could have inserted the string representation of the date in place of @date
and it would have worked.

The reason you want to use a parameterized query is for security.
Google "injection attack" and it will show you why you should use them
instead of placing parameters in query strings yourself.

As for closing the connection, I'm assuming that aConnection is held on
the class level. You are keeping this open, which is generally a bad idea,
as you are wasting that database handle when you aren't using it. However,
it can be costly at times to create a new database handle each time you need
one. This is where connection pooling comes in. You enable it in your
connection string. Once you do that, just create and open a new connection
anywhere you need it, but make sure to call Close/Dispose on it when done
(use the using statement to ensure this happens).

 

[Mike]

Thanks Nicholas, you help me a lot.

Mike

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,


--
Ignacio Machin
http://www.laceupsolutions.com
Mobile & warehouse Solutions.

Nicholas.

This works, thank you very much.

I admit that I do not understand everything... These days I have a lot
of trouble with DateTime handling and conversion. As it seems, the
only solution (I hope is not) is to use parametrized queries on this
or on that way. I am confused because I do not like that approach.

Why you do not like it? IMO it's much clearer code than a bunch of
concatenations.

The solution has nothing to do with it though. The magic was in the cast to
float and back to datetime:
cast(floor(cast(OfferDate as float)) as datetime)

That is a very clever solution, I did not know it