Calendar control: using selections as filter to gridview

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

Select allOpen in new window

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

This is the format of the date field in the db: 2008-01-21 20:10:22.310

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

Select allOpen in new window

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?

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)

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.

Can you post your code, including the line that has the error please.

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

Select allOpen in new window

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.

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?

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

ok, will do. thanks again!