?
Solved

calendar selection use as filter for gridview

Posted on 2008-02-03
20
Medium Priority
?
461 Views
Last Modified: 2008-03-06
I have 2 dropdownlist and a gridview.  The dropdownlists filter the gridview, but now I have added two calendars and two textboxes.  After user makes selections from the calendar the text boxes are populated with the dates.  How do I add this to the following select parameter so that the filtering/querying works properly? (what I have here does not work in terms for the dates, but dropdownlists filter when I remove the dates section):

<asp:SqlDataSource ID="DATA" runat="server" ConnectionString="<%$ ConnectionStrings:DATA_Connection %>"
           SelectCommand="SELECT * FROM [atable] WHERE (afield=@param1 or @Param1='Select something' or ((CONVERT(char(10), adate, 101)>=@param3 AND CONVERT(char(10), bdate, 101)<=@param4)) AND (bfield=@Param2 OR @Param2='Select something' or ((CONVERT(char(10), adate, 101)>=@param3 AND CONVERT(char(10), bdate, 101)<=@param4))"
             UpdateCommand="Update atable set afield=@afield, bfield=@bfield where id=@ID" >   
             
             <selectParameters>
     <asp:controlparameter name="param1" controlid="DropDownList1" PropertyName="SelectedValue" type="String" />
     <asp:controlparameter name="param2" controlid="DropDownList2" PropertyName="SelectedValue" type="String" />
     <asp:ControlParameter Name="param3" ControlID="Textbox1" PropertyName="Text" Type="string" />
     <asp:ControlParameter Name="param4" ControlID="Textbox2" PropertyName="Text" Type="string" />
     
</selectParameters>
0
Comment
Question by:newbieal
20 Comments
 
LVL 29

Expert Comment

by:QPR
ID: 20809954
How do you mean does not work?
Errors or filter ignored?

Have you tried
<asp:ControlParameter Name="param4" ControlID="Calendar1" PropertyName="SelectedDate" Type="Datetime" />
and then leaving out the converts in your sql
0
 
LVL 15

Expert Comment

by:NazoUK
ID: 20810079
Your converts won't work because you are converting the date to a string then doing a text comparison which will compare them alphabetically rather than by date.

If you want to use the dates in your text boxes then use:
<asp:ControlParameter Name="param3" ControlID="Textbox1" PropertyName="Text" Type="DateTime" />
0
 
LVL 4

Author Comment

by:newbieal
ID: 20810097
It ignores the calendar selection, and dropdown selections - no grid info shown.

Can I do that although I'm populating text boxes after user makes calendar selection and then use the values (strings) in these textboxes in the selectcommand?

If I can skip this part, meaning textboxes/string values, then better.  I've never built anything like it, what is the std procedure on this?
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 2

Expert Comment

by:fredmastro
ID: 20810229
I was going to say same thing, Type="DateTime" for your TextBox contol there.

0
 
LVL 29

Expert Comment

by:QPR
ID: 20810231
I always (try to) split my logic from layout so mostly do my sql in code behind.
Here is some from a recent page....

Dim objCmd As SqlCommand = New SqlCommand()
            objCmd.Connection = ObjConn
            objCmd.CommandType = System.Data.CommandType.StoredProcedure
            objCmd.CommandText = "dbo.InsertAbsentee"
            objCmd.Parameters.Add("@Name", Data.SqlDbType.VarChar).Value = DropDownList1.SelectedValue
            objCmd.Parameters.Add("@reason", Data.SqlDbType.VarChar).Value = TextBox1.Text
            objCmd.Parameters.Add("@StartDate", Data.SqlDbType.SmallDateTime).Value = TextBox2.Text
            objCmd.Parameters.Add("@ReturnDay", Data.SqlDbType.SmallDateTime).Value = TextBox3.Text
            ObjConn.Open()
            ObjCmd.ExecuteNonQuery()

yes you can use the text box values so long as you specify them as date value types not strings

0
 
LVL 4

Author Comment

by:newbieal
ID: 20810318
fredmastro,

'type' is not valid for the textbox control.
0
 
LVL 4

Author Comment

by:newbieal
ID: 20810328
QPR,

I'd like to stick with what I have.  I like that the code is short and simple and runs quickly.  I just need to figure out how to add the date as part of the query.  
Any help on that would be greatly appreciated.
0
 
LVL 29

Expert Comment

by:QPR
ID: 20810377
What happens if you leave out type and just use
<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
0
 
LVL 4

Author Comment

by:newbieal
ID: 20810420
that's what I had...I just responded to Fredmastro, as his suggestion would not work with a textbox
0
 
LVL 29

Expert Comment

by:QPR
ID: 20810439
yes I meant leave out the "type" attribute and just go with what you have.
And without the convert functions in your sql
0
 
LVL 4

Author Comment

by:newbieal
ID: 20810479
You mean the type attribute in the control parameter, right?  As textboxes do not hold 'type'.

I've left it out in the control parameter and left out conversion, but the page loads without the gridview displayed and none of the filtering works.
0
 
LVL 29

Expert Comment

by:QPR
ID: 20810506
Yes.
can you paste your sql into query analyser and replace the params with real values and see if it runs ok?
0
 
LVL 4

Author Comment

by:newbieal
ID: 20810550
I don't have query analyzer.  I've hardcoded the values but can't tell if it is filtering it, since at page load the gridview no longer shows.
0
 
LVL 29

Expert Comment

by:QPR
ID: 20811210
do you have excel or MS Query can you retrieve the data through there?
Create a DSN that points to the server/db which you can then use to retrieve data.

or
create a variable (string) and populate it with the sql string you are creating. Then response.write this value to the page and see what it looks like.
0
 
LVL 4

Author Comment

by:newbieal
ID: 20814264
I ran the sql query in sql mgmt studion as follows, and it worked. It provided the desired results:  Now I don't know what to change in my code to make it work though.


declare @param1 varchar(100),
        @param2 varchar(250),
        @param3 varchar(10),
        @param4 varchar(10)
 
set @param1 = 'A'
set @param2 = 'Open'
set @param3 = '02/02/2008'
set @param4 = '02/03/2008'
 
 
SelectCommand="SELECT * FROM [atable] WHERE (afield=@param1 or @Param1='Select something' or ((CONVERT(char(10), adate, 101)>=@param3 AND CONVERT(char(10), bdate, 101)<=@param4)) AND (bfield=@Param2 OR @Param2='Select something' or ((CONVERT(char(10), adate, 101)>=@param3 AND CONVERT(char(10), bdate, 101)<=@param4))"
             

Open in new window

0
 
LVL 4

Author Comment

by:newbieal
ID: 20814275
Ooops, I did use the Between statement for the date range and not <= or >=
0
 
LVL 29

Expert Comment

by:QPR
ID: 20817207
maybe if we stepped back. Your select looks quite complicated.
Can you explain what it is you need to do (how the drop downs etc affect the select) then someone could offer up another select option (syntax).
As you receive no errors then perhaps the syntax is correct but the logic isn't what you think.
0
 
LVL 29

Expert Comment

by:QPR
ID: 20817238
for example:
@Param1='Select something'  is looking to compare the variable @param1 with a hard coded string 'select something'
0
 
LVL 4

Author Comment

by:newbieal
ID: 20818510
I have two dropdown lists.  First value in each of the list is 'select an item'.  The filtering works well between these two lists and the results in the gridview are perfect.  Now I need to add the calendar/date selections to this as well.  It needs to be part of the querying/filtering.  Is this what you are looking for?
0
 
LVL 29

Accepted Solution

by:
QPR earned 2000 total points
ID: 20835643
Sort of but you seem to have a hell of a lot of boolean logic (ands/ors) in the sql
From your description it sounds like you would need something like...

select *
from myTable
where aField = @param1
     and bField = @param2
     and theDate between @param3 AND @param4


What is this bit doing "afield=@param1 or @Param1='Select something' "

0

Featured Post

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.

Question has a verified solution.

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

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
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…
Free Data Recovery software is an advanced solution from Kernel Tools to recover data and files such as documents, emails, database, media and pictures, etc. It supports recovery from physical & logical drive after a hard disk crash, accidental/inte…
Suggested Courses
Course of the Month6 days, 10 hours left to enroll

593 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