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

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.


--
- Nicholas Paldino [.NET/C# MVP]
- (E-Mail Removed)


"Mike" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.

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
On Tue, 27 Nov 2007 11:00:03 -0500, "Nicholas Paldino [.NET/C# MVP]"
<(E-Mail Removed)> wrote:

>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,

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


--
- Nicholas Paldino [.NET/C# MVP]
- (E-Mail Removed)

"Mike" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
> On Tue, 27 Nov 2007 11:00:03 -0500, "Nicholas Paldino [.NET/C# MVP]"
> <(E-Mail Removed)> wrote:
>
>>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.

Thanks Nicholas, you help me a lot.

Mike

On Tue, 27 Nov 2007 12:14:02 -0500, "Nicholas Paldino [.NET/C# MVP]"
<(E-Mail Removed)> wrote:

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

Hi,


--
Ignacio Machin
http://www.laceupsolutions.com
Mobile & warehouse Solutions.
"Mike" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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