current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

I have been trying to add two SelectParameters to my SqlDataSource with no avail.

Below is my code in the aspx page

<table>
        <tr>
            <td colspan="2"><strong>Search By child</strong>
            </td>
        </tr>
        <tr>
            <td>Case Number:
            </td>
            <td>
                <asp:TextBox ID="txtCaseNumber" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td>Last Name:
            </td>
            <td>
                <asp:TextBox ID="txtLastName" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td>First Name:
            </td>
            <td>
                <asp:TextBox ID="txtFirstName" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td colspan="2">
                <asp:LinkButton ID="lnkSearchChild" runat="server">Search</asp:LinkButton>
            </td>
        </tr>
    </table>

<asp:GridView ID="childListGrid" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1" AllowPaging="True" AllowSorting="True" PageSize="20" BackColor="White" BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px" CellPadding="4" ForeColor="Black" GridLines="Horizontal">
        <Columns>
            <asp:BoundField DataField="child_recordId" HeaderText="Child ID" InsertVisible="False" ReadOnly="True" SortExpression="child_recordId" />
            <asp:BoundField DataField="child_caseNumber" HeaderText="Case Number" SortExpression="child_caseNumber" />
            <asp:BoundField DataField="child_LastName" HeaderText="Last Name" SortExpression="child_LastName" />
            <asp:BoundField DataField="child_FirstName" HeaderText="First Name" SortExpression="child_FirstName" />
            <asp:TemplateField HeaderText=" " InsertVisible="False"
                SortExpression="child_recordId">
                <ItemTemplate>
                    &nbsp;&nbsp;
                        <asp:LinkButton ID="lnkViewForms" runat="server" CommandName="ViewForms" CommandArgument='<%# Bind("child_recordId") %>' OnClick="lnkViewForms_Click">View forms</asp:LinkButton>
                    &nbsp;&nbsp;
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
        <FooterStyle BackColor="#CCCC99" ForeColor="Black" />
        <HeaderStyle BackColor="#333333" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="White" ForeColor="Black" HorizontalAlign="Right" />
        <SelectedRowStyle BackColor="#CC3333" Font-Bold="True" ForeColor="White" />
        <SortedAscendingCellStyle BackColor="#F7F7F7" />
        <SortedAscendingHeaderStyle BackColor="#4B4B4B" />
        <SortedDescendingCellStyle BackColor="#E5E5E5" />
        <SortedDescendingHeaderStyle BackColor="#242121" />
    </asp:GridView>

<asp:SqlDataSource ID="SqlDataSource5" runat="server" ConnectionString="<%$ ConnectionStrings:eci_conn %>" SelectCommand="SELECT ... FROM ... WHERE [child_LastName] LIKE @lname + '%' AND [child_FirstName] LIKE @fname + '%' ORDER BY [child_LastName]">
        <SelectParameters>
            <asp:ControlParameter Name="lname" DbType="String" />
            <asp:ControlParameter Name="fname" DbType="String" />
        </SelectParameters>
    </asp:SqlDataSource>

And in my code behind I tried the following

    Dim lNameParam As New Parameter("@lname", DbType.String)
    Dim fNameParam As New Parameter("@fname", DbType.String)
    lNameParam.DefaultValue = txtLastName.Text.Trim()
    fNameParam.DefaultValue = txtFirstName.Text.Trim()
    SqlDataSource5.SelectParameters.Add(lNameParam)
    SqlDataSource5.SelectParameters.Add(fNameParam)
    childListGrid.DataSourceID = "SqlDataSource5"

and also this

SqlDataSource5.SelectParameters.Add("@lname", txtLastName.Text.Trim())
SqlDataSource5.SelectParameters.Add("@fname", txtFirstName.Text.Trim())
childListGrid.DataSourceID = "SqlDataSource5"

and this

SqlDataSource5.SelectParameters("lname").DefaultValue = txtLastName.Text.Trim()
SqlDataSource5.SelectParameters("fname").DefaultValue = txtFirstName.Text.Trim()
childListGrid.DataSourceID = "SqlDataSource5"

Nothing seems to be working, what else can I try?

thanks

share|improve this question
    
What exactly do you mean when you say it's not working? –  jadarnel27 Feb 24 at 16:39
    
Nothing gets returned. It works just fine with one parameter, but it is just not working with two. When I step thru the code I see that the count is 4 instead of two dl.dropboxusercontent.com/u/2956296/parameters.png –  JGeZau Feb 24 at 16:45

2 Answers 2

up vote 0 down vote

Try this

SqlDataSource5.SelectParameters("lname").DefaultValue = txtLastName.Text.Tostring
SqlDataSource5.SelectParameters("fname").DefaultValue = txtFirstName.Text.Tostring
childListGrid.DataSource = "SqlDataSource5"
childListGrid.Databind
share|improve this answer
    
Not working with this either –  JGeZau Feb 24 at 16:48
    
where are you updating this code? is it in your page load? are you using an ajax call to update the reuslts if its not in your page load? –  Ishey4 Feb 24 at 16:51
    
not on page load, when I click my search link –  JGeZau Feb 24 at 17:29
    
try adding a databind to your page load. also did you test your query to make sure you are getting results back? –  Ishey4 Feb 24 at 17:31
    
the query is working and also is works fine with one parameter, but stops working when I add two –  JGeZau Feb 24 at 17:32
up vote 0 down vote

You only need to do one or the other (markup or codebehind) when adding parameters to your SqlDataSource. Take a look at this:

<SelectParameters>
    <asp:ControlParameter Name="lname" ControlID="txtLastName" PropertyName="Text" />
    <asp:ControlParameter Name="fname" ControlID="txtFirstName" PropertyName="Text" />
</SelectParameters>

At this point, both of your ControlParameters have been added to your SqlDataSource. There is no need to add them again in codebehind.

Notice that I added the "ControlID" and "PropertyName" properties to the markup. I'm not sure how you were getting by without them, but they are necessary for the parameter to work correctly.

Since you are changing datasources during your postback, you need to make sure and call databind after making that change:

childListGrid.DataSourceID = "SqlDataSource5";
childListGrid.DataBind();
share|improve this answer
    
I'm creating the GridView in the markup. I changed the markup to reflect the one you provided. If I do not need to add the parameters in the code behind, how do I perform the search? If I just leave childListGrid.DataSourceID = "SqlDataSource5" in the code behind and then click in search it brings back everything. I added my GridView code to the question –  JGeZau Feb 24 at 17:26
    
@JGeZau Your DataSourceID in the markup is set to DataSourceID="SqlDataSource1". Shouldn't it be DataSourceID="SqlDataSource5"? I think maybe you have not explained your scenario fully, or I'm just not understanding something. –  jadarnel27 Feb 24 at 18:11
    
SqlDataSource1 is the "default" datasource. I have other ones for the search functionality. Once you first load the page, it loads with DS1; DS2 is to search by CaseNumber (as you can see I have a text box for this) and this is working perfect. I have DS3 and DS4 that search by Last Name and First Name respectively, this also works perfect because is using two DSs for first and last name. What I want to do is get rid of DS3 and DS4 and combine them to be able to search for fname and lname together, this is where I am having trouble –  JGeZau Feb 24 at 18:54
    
@JGeZau Ah, thank you for the additional information! I think I see what your problem is. In your first comment here, you said "If I just leave childListGrid.DataSourceID = "SqlDataSource5" in the code behind and then click in search it brings back everything" - you need to call databind after you change the datasource ID, and then you should be good to go. I updated my answer. –  jadarnel27 Feb 24 at 20:03
    
Hey, @JGeZau. Did this answer solve your problem, or help you solve your problem? –  jadarnel27 Mar 25 at 16:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.