Selecting Dates for database connection

11 Jul 2006 3:01 PM

MasterChief

I have a calendar control that I am using to have a person select a
date. Then I connect to a database and I want to display anything that
is equal to that date or where the date selected is atleast between the
start date and end date. I just don't know how I am suppose to get this
to work in SQL. Here is my code
        Dim selectedDate As String =
cldrEvents.SelectedDate.ToString("d")
        Dim sqlConnectionString As String
        sqlConnectionString =
ConfigurationManager.ConnectionStrings("eventsConnectionString").ConnectionString.ToString
        Dim sqlConnection As New SqlConnection(sqlConnectionString)

This below is the part I don't understand. I want selectedDate to be
compared between events_startdate and events_enddate. If the
selectedDate is between or equal to the events_starttime and
events_endtime then display the event. How do I get this to happen?
Should I be converting selectedDate to a DateTime object before putting
it into the the sql command?

        Dim sqlCommand As New SqlCommand("", sqlConnection)

        sqlConnection.Open()
        Dim sqlReader As SqlDataReader = SqlCommand.ExecuteReader()
        gdvEvents.DataSource = sqlReader
        gdvEvents.DataBind()

11 Jul 2006 5:02 PM

William (Bill) Vaughn

One would code a SELECT statement that has a WHERE clause like this:

WHERE SelectedDate BETWEEN @LowDate AND @HighDate

You would then setup the SqlCommand.Parameters collection to contain two
parameters cast as DateTime and set the Value with the date values you
wanted to use as criteria.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Show quote

"MasterChief" <consta***@mix-net.net> wrote in message
news:1152630073.739097.233310@m73g2000cwd.googlegroups.com...
>I have a calendar control that I am using to have a person select a
> date. Then I connect to a database and I want to display anything that
> is equal to that date or where the date selected is atleast between the
> start date and end date. I just don't know how I am suppose to get this
> to work in SQL. Here is my code
>        Dim selectedDate As String =
> cldrEvents.SelectedDate.ToString("d")
>        Dim sqlConnectionString As String
>        sqlConnectionString =
> ConfigurationManager.ConnectionStrings("eventsConnectionString").ConnectionString.ToString
>        Dim sqlConnection As New SqlConnection(sqlConnectionString)
>
> This below is the part I don't understand. I want selectedDate to be
> compared between events_startdate and events_enddate. If the
> selectedDate is between or equal to the events_starttime and
> events_endtime then display the event. How do I get this to happen?
> Should I be converting selectedDate to a DateTime object before putting
> it into the the sql command?
>
>        Dim sqlCommand As New SqlCommand("", sqlConnection)
>
>        sqlConnection.Open()
>        Dim sqlReader As SqlDataReader = SqlCommand.ExecuteReader()
>        gdvEvents.DataSource = sqlReader
>        gdvEvents.DataBind()
>

11 Jul 2006 6:56 PM

MasterChief

Thanks for the quick response. My only question is this

WHERE SelectedDate BETWEEN @LowDate AND @HighDate

In this statement SelectedDate seems to be a database field and LowDate
and HighDate are fields that are inputed from the user.

What I am trying to do is have SelectedDate be the date selected by a
person in a calendar and then do a calculation to see if it is in the
date range between the database field event_startdate and
event_enddate.

So it is like it is the reverse of what you are doing where
SelectedDate is the variable and LowDate and Highdate are the database
fields. The problem I am having is the Between statement wants the
selectedDate to be a database instead of a variable like you have it.