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

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
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" />
newbiealAuthor Commented:
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?
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

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

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

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

newbiealAuthor Commented:

'type' is not valid for the textbox control.
newbiealAuthor Commented:

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.
What happens if you leave out type and just use
<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
newbiealAuthor Commented:
that's what I had...I just responded to Fredmastro, as his suggestion would not work with a textbox
yes I meant leave out the "type" attribute and just go with what you have.
And without the convert functions in your sql
newbiealAuthor Commented:
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.
can you paste your sql into query analyser and replace the params with real values and see if it runs ok?
newbiealAuthor Commented:
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.
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.

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.
newbiealAuthor Commented:
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

newbiealAuthor Commented:
Ooops, I did use the Between statement for the date range and not <= or >=
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.
for example:
@Param1='Select something'  is looking to compare the variable @param1 with a hard coded string 'select something'
newbiealAuthor Commented:
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?
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' "


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

From novice to tech pro — start learning today.