Calendar control: using selections as filter to gridview

I want to capture the selection(s) a user makes on the calendar control and use that as a filter for my gridview.  This is what I have so far, but of course it isn't filtering as I believe the formatting isn't correct?
If Label2.Text Then
            data.SelectCommand = ("SELECT * FROM [atable] where date is like '" & Label2.Text & " ' ")
        End If
    End Sub
 
    Protected Sub Calendar1_SelectionChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles Calendar1.SelectionChanged
        
        Label2.Text = Calendar1.SelectedDate.ToShortDateString

Open in new window

LVL 4
newbiealAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TSmoothCommented:
Your code is kind of cut off it seems there are 2 things I notice from this.
1.) The SQL Query text is off if you are using SQL Server and you shouldn't have to use "like" if you are comparing a date stored in the database with a date in the program. You should be able to just use "=" and pass in the date. It should also be the SelectCommand's "CommandText" property that you are setting to the string. You should also look into using parameterized queries instead of concatenating strings.

2.) The SelectCommand should be updated in the calendar's selection changed event and you need to databind the gridview after updating the select command's text. In the SelectionChanged event of the calendar, after you update your label's text you should have something like the following:
data.SelectCommand.CommandText = "SELECT * FROM [atable] where [date] = '" & Calendar1.SelectedDate.ToShortDateString & "'" 
data.DataBind()

Open in new window

0
newbiealAuthor Commented:
I'm not exactly clear what you recommend changing.  

Under the page load event I have if/else if statements that check two dropdownlists and I update the select command accordingly.  I want to add the calendar control in this filtering logic, therefore I have the following (changing what i understand you suggested):

If Label2.Text Then
            data.SelectCommand = ("SELECT * FROM [atable] where date =  '" & Calendar1.SelectedDate.ToShortDateString & " ' ")
        End If
    End Sub
 
Then in the selectionchanged event I have this:
    Protected Sub Calendar1_SelectionChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles Calendar1.SelectionChanged
       
        Label2.Text = Calendar1.SelectedDate.ToShortDateString

0
newbiealAuthor Commented:
This is the format of the date field in the db: 2008-01-21 20:10:22.310
0
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

TSmoothCommented:
The load event of the page will always occur BEFORE the SelectionChanged event fires each time. So setting label2's text to be the selected date isn't going to cause your filter to update. What you need to do is change the actual select command in the calendar's selection changed event and then force your gridview to repopulate with that data by calling data bind on it. In your code above, what is "data"? Is it a dataset, dataadapter, or what? What is the gridview's datasource or DataSourceID set to?

Your Calendar's selection changed event should look something more like this:

Label2.Text = Calendar1.SelectedDate.ToShortDateString
' Insert code to change the select command here depending on what your datasource is.
GridView1.DataBind()

Open in new window

0
newbiealAuthor Commented:
data is the sqlsource...I understand better now...for filtering do I still use the label2.text?  Also, the date format in the db, does it matter the way I'm assigning the date format in the app vs. what's in the db?
0
newbiealAuthor Commented:
I understand that the filtering now occurs in the selection changed event and I no longer need it in page load, correct?  It seems to filter now, but I think due to the difference in date format it doesn't recognize the dates and it tells me that no records were found (even though there's a match)
0
TSmoothCommented:
Yeah I don't think you will need label2 anymore unless you want to display the selected date in a separate label and you don't need the page load code unless on the FIRST visit to the page there is a specific filter or sql query you want the datasource to use. In that case you'd put code inside an "If Not Page.IsPostBack" block.

As for the date issue, that depends on a couple things. What is the data type of the field set to in the database? Is it set to varchar, nvarchar, datetime, etc.? If it is set to be datetime then the format of it shouldn't matter as long as you are using a valid date format in your query string. It's best to use parameterized queries as I mentioned and you can google on the use of those. I'm assuming that the db is set to be datetime as that is how it should be but since it has time, and you want the items that occur on a specific day at any time, you need to do some conversion. In other words, when you get the selected date and use ToShortDateString() to retrieve the date you end up with something like "1/31/08" which is typically interpreted to mean "2008-01-31 00:00:00.000" which does NOT equal "2008-01-31 14:23:01.234" for example. You need to strip off the time part for your query for the database column and make sure that the date passed in matches the same format like so..

This is assuming you use SQL Server, SQL Server Express, or something else that supports the T-SQL syntax:
"SELECT * FROM [atable] WHERE CONVERT(char(10), date, 101) =  '" & Calendar1.SelectedDate.ToString("MM/dd/yyyy") & " ' ")

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
newbiealAuthor Commented:
Yes, it is a datetime field.  When I did what you suggested above in your sample code I get an error:

Conversion from string "MM/dd/yyyy" to type 'Integer' is not valid.
0
TSmoothCommented:
Can you post your code, including the line that has the error please.
0
newbiealAuthor Commented:
The toshortdatestring function is looking for an int, I believe that's why I get the error.  I left that portion out but it still doesn't filter.
Protected Sub Calendar1_SelectionChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles Calendar1.SelectionChanged
      data.SelectCommand = ("SELECT * FROM [atable] where CONVERT(char(10), date, 101) = '" & Calendar1.SelectedDate.ToShortDateString("MM/dd/yyyy") & " ' ")
        
        GridView1.DataBind()
 
        Calendar1.Visible = "false"
 
    End Sub

Open in new window

0
TSmoothCommented:
Yeah, notice that I swtiched ToShortDateString() to ToString() in my code but you ddin't switch it in yours. You need to do that so that you can specify the formatting which is essentially the same as ToShortDateString's formatting except that it ensures that leading 0's are present for the single digits to match the database's converted date. If you change ToShortDateString("MM/dd/yyyy") to ToString("MM/dd/yyyy") you should see that error message go away.
0
newbiealAuthor Commented:
yikes - thanks!! It works like a charm now.  Can you tell me how I can handle date ranges (start/ end date)?  Does this require a 2nd calendar control?
0
TSmoothCommented:
I would recommend using two textboxes that allow date entry that have icons next to them to pop up a calendar to select the date instead of having to type it in and then a button to submit the date range and filter the gridview. Your SQL statement would then switch to having a where clause that looks for a date that is greater than or equal to the start date and less than or equal to the end date. Look into using the AJAX Control Toolkit's calendar extender control for the date selection (see the ajax stuff on www.asp.net).
0
newbiealAuthor Commented:
ok, will do. thanks again!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.