asked on

calendar selection use as filter for gridview

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>

QPR

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

NazoUK

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" />

newbieal

ASKER

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?

fredmastro

I was going to say same thing, Type="DateTime" for your TextBox contol there.

QPR

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

newbieal

ASKER

fredmastro,

'type' is not valid for the textbox control.

newbieal

ASKER

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.

QPR

What happens if you leave out type and just use
<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>

newbieal

ASKER

that's what I had...I just responded to Fredmastro, as his suggestion would not work with a textbox

QPR

yes I meant leave out the "type" attribute and just go with what you have.
And without the convert functions in your sql

newbieal

ASKER

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.

QPR

Yes.
can you paste your sql into query analyser and replace the params with real values and see if it runs ok?

newbieal

ASKER

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.

QPR

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.

newbieal

ASKER

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

newbieal

ASKER

Ooops, I did use the Between statement for the date range and not <= or >=

QPR

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.

QPR

for example:
@Param1='Select something' is looking to compare the variable @param1 with a hard coded string 'select something'

newbieal

ASKER

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?

ASKER CERTIFIED SOLUTION

QPR

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial