"Mike" <ablyp***@yahoo.com> wrote in message
news:e6eok3t5f9iic54vd1lpa11gl407hb425t@4ax.com...
> 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.

"Mike" <ablyp***@yahoo.com> wrote in message
news:u3jok3ll0g7qi6uhhv9dgvjr3lnu9r94dc@4ax.com...
> 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]"
> <mvp@spam.guard.caspershouse.com> 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).