calendar in gridview

How can I add a calendar in a gridview to be used as filtering (start and end date)?
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.

culpritCommented:
I would suggest adding the calendar outside of gridview then bind the gridview to a dataset that results from your search. Just change the Connection String for the SqlConnection and change the date field to match yours and the table name in the query and everything should work just fine

 I have given the button click event code below and also attached both the codebehind and html source files as txt files. i hope this helps

 Dim sqlConnection As New SqlConnection("Your connection string here")
 
        Dim cmdSql As SqlCommand = New SqlCommand(sqlConnection.ConnectionString)
 
        cmdSql.CommandType = CommandType.text
        cmdSql.CommandText = "SELECT * FROM YOURTABLE WHERE date >= " _
        & Me.calBegin.SelectedDate & "and date <= " & Me.calEnd.SelectedDate
 
 
        Dim myNewDA As New SqlDataAdapter(cmdSql)
 
        '4. Populate the DataSet
        myNewDA.Fill(myDataset, "YOURTABLE")
        Dim drow As DataRowView
        Dim tbl As DataTable
        Dim vw As DataView
 
        tbl = myDataset.Tables("YOURTABLE")
 
        vw = tbl.DefaultView
 
        If vw.Count > 0 Then
            drow = vw(0)
 
 
            Me.GridView1.DataSource = tbl
            GridView1.DataBind()
 
        End If
        myNewDA.Dispose()

Open in new window

CodeBehind.txt
HtmlSource.txt
0
newbiealAuthor Commented:
I guess I could hide the calendar and only show it if the user clicks on a button?  

Also, what if I then need to add additional filtering capabilities, such as dropdown lists?

0
culpritCommented:
Its sounds like you are wanting the functionality of AJAX. There is a Calendar control extender that the AJAX control tool kit offers that i believe will give you this functionality. (only showing the calender when the user clicks in a textbox)
 Please check out
http://www.asp.net 
look at the ajax control toolkit examples for the calender control extender. This site has great tutorial videos that will greatly enhance your site.

if you need additional filtering capabilities on the button click  , have the first value in the dropdown list be "ANY" with a value of 0 and if the selected index of the dropdown isn't 0 then concatenate the additonal query to the main query. Check out the example below.(this would be in the button click event
if i wanted to find a certain state and name for a Customer ) Let me know if this is what you're looking for.


Dim strQuery as String  = "Select * from Customer where "
 
If ddlState.SelectedIndex <> 0 then
strQuery += " State = '" & ddlState.SelectedItem.ToString & "' "
End If
 
If ddlCustomerNames.SelectedIndex <> 0 then
strQuery += " And CustomerName = '" & ddlCustomerName.SelectedItem.ToString & "' "
End If
 
 
Dim sqlConnection As New SqlConnection("Your connection string here")
 
Dim cmdSql As SqlCommand = New SqlCommand(sqlConnection.ConnectionString)
 
        cmdSql.CommandType = CommandType.text 
 
       'This will set the command text to proper query 
        cmdSql.CommandText =  strQuery 

Open in new window

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

newbiealAuthor Commented:
i wish I had access to AJAx but I don't so I'm left with asp and vb, unfortuanatly.  I'm looking more for something I can mostly code in asp .net via filter or select parameters.  

I just want the user to be able to select values from the 2 dropdowns and the calendar and the gridview filter accordingly.  Meaning if dropdownlist1 has been selected the gridview should filter accordingly if then dropdownlist2 has been changed then it should filter further and so on with the date range/ calendar as well.  But if let's say dropdownlist1 has been changed back to index of 0 (meaning 'select a value from the list') then the gridview should at least still be filtering from the current settings for dropdownlist2, etc.  but it doesn't do that right now.

Thanks for all of your help and patience!
0
culpritCommented:
its no problem.

it just sounds like you need to have some logic to give you the results that you wish to have. (Similair to last code example i gave )  you'll have be testing both values together and both values apart each time in your IF statements.

if this doesn't help just tell me exactly what you're doing and i can assist with a sample project.


If ddlState.SelectedIndex <> 0 then
strQuery += " State = '" & ddlState.SelectedItem.ToString & "' "
End If
 
If ddlCustomerNames.SelectedIndex <> 0 then
strQuery += " And CustomerName = '" & ddlCustomerName.SelectedItem.ToString & "' "
End If
 

Open in new window

0
newbiealAuthor Commented:
Here is as far as I've gotten.  I haven't even yet attempted to add the calendar control yet.  I just want to get the filtering down properly.  The issue is that when the first dropdownlist is selected, index 1 is selected, it does not filter properly for that value, but when the second value, index 2 is selected, filtering is fine.  There  is also some discrepency between dropdownlist1.index1.selected and dropdownlist2.index1orindex2.selected.  Sometimes the filtering seems to be working and accurate and sometimes it's not.  What can the issue be?
 SelectCommand="SELECT * FROM [atable] WHERE (([afield] = @afield) OR (@afield IS NULL)) OR (([bfield] = @bfield) OR (@bfield IS NULL))"
            CancelSelectOnNullParameter="False" >
            <SelectParameters>
                <asp:ControlParameter ControlID="DropDownList1" Name="afield" PropertyName="SelectedValue"
                    Type="String" ConvertEmptyStringToNull="True" />
                <asp:ControlParameter ControlID="DropDownList2" Name="bfield" PropertyName="SelectedValue"
                    Type="String" ConvertEmptyStringToNull="True" />
                </SelectParameters>
 
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If DropDownList1.SelectedIndex.Equals(0) And DropDownList2.SelectedIndex.Equals(0) Then
            data.SelectCommand = ("SELECT * FROM [atable]")
            GridView1.DataBind()
        End If

Open in new window

0
culpritCommented:
the code you gave for the page load says that only change the select command and databind with both values selected are 0 . You need to add additional if statements  (MORE LOGIC)

YOU MAY WANT TO TO NEST THESE IF STATEMENTS AS I AM NOT TESTING THESE BUT THEY SHOULD WORK. I WOULD SUGGEST USING A SIMILIAR STYLE TO THE ONE I SHOWED YOU AT FIRST AND JUST MANUALLY BIND YOUR GRIDVIEW TO YOUR DATASET THAT WAY YOU CAN CUSTOMIZE YOUR SQL TO BE EXACTLY WHAT YOU NEED. I THINK THIS MAY BY EASIER FOR YOUR TASK.

THIS SHOULD BE IN THE PAGE LOAD

just add your customized SQL in the if statements
Dim sqlConnection As New SqlConnection("Your connection string here")
 
        Dim cmdSql As SqlCommand = New SqlCommand(sqlConnection.ConnectionString)
 
        cmdSql.CommandType = CommandType.text
        
      
If NOT DropDownList1.SelectedIndex.Equals(0) And DropDownList2.SelectedIndex.Equals(0) Then
          'FILTER ONLY FOR DROPDOWNLIST1
 cmdSql.CommandText = "select * from mytable where afield = '" & DropDownList1.SelectedItem.tostring & " ' "
 
 
        End If
 
If DropDownList1.SelectedIndex.Equals(0) And NOT DropDownList2.SelectedIndex.Equals(0) Then
          'FILTER FOR ONLY DROPDOWNLIST2
cmdSql.CommandText = "select * from mytable where bfield = '" & DropDownList2.SelectedItem.tostring & " ' "
        End If
 
 
If NOT DropDownList1.SelectedIndex.Equals(0) And NOT DropDownList2.SelectedIndex.Equals(0) Then
          'FILTER FOR BOTH DROPDOWNLIST1 AND DROPDOWNLIST2
cmdSql.CommandText = "select * from mytable where afield = '" & DropDownList1.SelectedItem.tostring & " ' " and bfield = '" &
DropDownList2.SelectedItem.tostring & " ' "
        End If
'Load all table data because no filter is set
 If DropDownList1.SelectedIndex.Equals(0) And DropDownList2.SelectedIndex.Equals(0) Then
           cmdSql.CommandText = "SELECT * FROM [atable]"
        End If
 
 
 
 
 Dim myNewDA As New SqlDataAdapter(cmdSql)
 
        '4. Populate the DataSet
        myNewDA.Fill(myDataset, "YOURTABLE")
        Dim drow As DataRowView
        Dim tbl As DataTable
        Dim vw As DataView
 
        tbl = myDataset.Tables("YOURTABLE")
 
        vw = tbl.DefaultView
 
        If vw.Count > 0 Then
            drow = vw(0)
 
 
            Me.GridView1.DataSource = tbl
            GridView1.DataBind()
 
 
 
 
 

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:
Ok, I'll give this a try.  I just assumed that the rest of the logic occurred in the aspx page under the select command...will I have to delete this if I implement the above code?
0
culpritCommented:
I would just add a new page and put a new gridview on the page and a couple of new dropdownlists and then add the code above to the page load

and Test it that way you will still have the original one to refer to if you need it.

oh and you will need to declare

Dim myDataset as new Dataset

then you should be able to just copy and paste the code
0
newbiealAuthor Commented:
Ok, I did that but ran into several errors, the latest being: Fill: SelectCommand.Connection property has not been initialized.

for this line of code: myNewDA.Fill(myDataset, "YOURTABLE")
0
newbiealAuthor Commented:
I continued with what I had and added the logic you provided.  Thank you so much for helping me!!
0
newbiealAuthor Commented:
Thank you for your great patience!!
0
culpritCommented:
its no problem . Good luck on the rest of your project !
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.