Searching a GridView bound to SQLDataSource using Textboxes

Question

My scenario is a Gridview which is bound to a stored procedure using asp:SQLDataSource. This was quickly done using the wizard where i selected the SP and all data was dispalyed. The SP was created in such a way that it contains 4 parameters. When such parameters are null, they will return all values in the query.

I have created 3 textboxes and a dropdown list to populate these parameters, binding them to a button in an attempt to create a search functionality. I have tried many ways and methods, especially by research on this site but none seem to work. I cant quite find my issue so I appreciate any help on the matter. Below is my code.

<asp:SqlDataSource ID="sqlSourceID" runat="server" ConnectionString="<%$ ConnectionStrings:DBCONNSTRING%>"
            SelectCommand="storedProcedureName" SelectCommandType="StoredProcedure">                
        </asp:SqlDataSource>

The above is the SQL stored procedure bind.

I have attemtped to bind the controls as parameters for the SQL server. Prior to this code, the page used to load all records fine without search parameters as the SP was assuming a null value. Now nothing loads.

            <SelectParameters>
                <asp:ControlParameter ConvertEmptyStringToNull="true" ControlID="textBox1" PropertyName="Text" Name="1" Type="DateTime" />
                <asp:ControlParameter ConvertEmptyStringToNull="true" ControlID="textBox2" PropertyName="Text" Name="2" Type="DateTime" />
                <asp:ControlParameter ConvertEmptyStringToNull="true" ControlID="textBox3" PropertyName="Text" Name="3" Type="String" />
                <asp:ControlParameter ConvertEmptyStringToNull="true" ControlID="ddl1" PropertyName="SelectedText" Name="4" Type="String" />
            </SelectParameters>

Where the Control ID represents the textbox ID

Below is my code behind.

        sqlSourceID.SelectParameters["textbox1"].DefaultValue = this.textbox.Text;
        sqlSourceID.SelectParameters["textbox2"].DefaultValue = this.textbox1.Text;
        sqlSourceID.SelectParameters["textbox3"].DefaultValue = this.textbox2.Text;
        sqlSourceID.SelectParameters["ddl1"].DefaultValue =    Convert.ToString(this.ddl.SelectedText);

Well that is my problem. I need to find a way to pass parameters from the textboxes when the Search Button is clicked and assume null on page load or if nothing is written.

Thanks to all those who contribute in advance.

When you type in actual values for your parameters, does it work? — Ann L.
– Ann L., Commented Nov 2, 2012 at 16:44
I notice that in your SelectParameters section, the various control parameters are named "1", "2", "3", etc., but in your code-behind you're using the control names "textbox1", "textbox2", etc., as indexers to the parameters of sqlSourceID. Is that correct? — Ann L.
– Ann L., Commented Nov 2, 2012 at 16:47
@Filip , Can you tell me what changes you made to my post? Did you modify the code?? — DottoreM
– DottoreM, Commented Nov 5, 2012 at 8:06
@AnnL. I am a bit confused now sorry. Basically I put the name of the textbox control ID where there is ControlID on the control parameter. And I gave it a Name for it to be identified. In my code behind, the select Parameters I wrote the name of the actual parameter rather than the control. I have tried typing actual values but it did not work so far. I seem to be confusing how i should match the controls with parameters. — DottoreM
– DottoreM, Commented Nov 5, 2012 at 8:09
@DottoreM I've put my comments in an answer, since it will let me use examples and links. — Ann L.
– Ann L., Commented Nov 6, 2012 at 20:10

Ann L. · Accepted Answer · 2012-11-06 20:10:20Z

1

I don't guarantee that this will solve the problem, but there are three things I see that look problematic:

You gave the values 1, 2, and 3 to the Name property of your asp:ControlParameter tags. Unless the stored procedure you're calling actually has parameters named @1, @2, and @3, you need to change those names to those of the parameters of the stored procedure you want to map to.
Similarly, in your code-behind, you use the control names to look up the parameters of your SqlDataSource.SelectCommand, like so:
```
sqlSourceID.SelectParameters["textbox1"].DefaultValue = this.textbox.Text;
```
But "textbox1" is not the name of your stored procedure parameter. As you have written it, @1 is the name of that parameter. If you keep the names 1, 2, and 3, you should do your parameter setting like this:
```
sqlSourceID.SelectParameters["1"].DefaultValue = this.textbox.Text;
```
But I don't think you're supposed to need to set the values of the parameters in code-behind at all. That's the point of the ControlParameter: it takes the value of a control property and assigns that to the command parameter. Here are some links that might help: Selecting Data Using the SqlDataSource Control and ASP.NET DetailsView and ControlParameter Example.

I hope this helps.

answered Nov 6, 2012 at 20:10

Ann L.

14k5 gold badges39 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DottoreM Over a year ago

Thank you, this answer contributed to the solution of my problem. I also had other problems which I managed to solve on my own aswell.

Collectives™ on Stack Overflow

Searching a GridView bound to SQLDataSource using Textboxes

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related