[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

calendar in gridview

Posted on 2008-01-30
13
Medium Priority
?
1,517 Views
Last Modified: 2011-10-19
How can I add a calendar in a gridview to be used as filtering (start and end date)?
0
Comment
Question by:newbieal
  • 7
  • 6
13 Comments
 
LVL 1

Expert Comment

by:culprit
ID: 20782338
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
 
LVL 4

Author Comment

by:newbieal
ID: 20782512
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
 
LVL 1

Expert Comment

by:culprit
ID: 20784193
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 4

Author Comment

by:newbieal
ID: 20788852
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
 
LVL 1

Expert Comment

by:culprit
ID: 20789530
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
 
LVL 4

Author Comment

by:newbieal
ID: 20789586
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
 
LVL 1

Accepted Solution

by:
culprit earned 1500 total points
ID: 20790343
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
 
LVL 4

Author Comment

by:newbieal
ID: 20790920
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
 
LVL 1

Expert Comment

by:culprit
ID: 20791048
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
 
LVL 4

Author Comment

by:newbieal
ID: 20792140
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
 
LVL 4

Author Comment

by:newbieal
ID: 20792348
I continued with what I had and added the logic you provided.  Thank you so much for helping me!!
0
 
LVL 4

Author Closing Comment

by:newbieal
ID: 31426558
Thank you for your great patience!!
0
 
LVL 1

Expert Comment

by:culprit
ID: 20798299
its no problem . Good luck on the rest of your project !
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In an ASP.NET application, I faced some technical problems. In this article, I list them out and show the solutions that I found.  I hope it will be useful. Problem: After closing a pop-up window, the parent page should be refreshed automaticall…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
How to fix display issue, screen flickering issue when I plug in power cord to the machine. Before I start explaining the solution lets check out once the issue how it looks like after I connect the power cord. most of you also have faced this…
Suggested Courses

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question