Join the Stack Overflow Community
Stack Overflow is a community of 6.8 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 0 down vote favorite

I have a very simple ASP.NET page that has two textboxes, a button, a SqlDataSource and a GridView. The SqlDataSource gets it's two parameters from the two textboxes, which it then passes on to a stored procedure. I want to have the ability for a user to fill out either field or both fields and have the results display in the GridView. Here is the relevant page code:

<table>
    <tr>
        <td>
            <asp:Label ID="Label4" runat="server" Text="Sales Order * Line Number:"></asp:Label>
        </td>
        <td>
            <asp:TextBox ID="txtSalesOrderLineNumber" runat="server"></asp:TextBox>
        </td>
    </tr>
    <tr>
        <td class="auto-style1">
            <asp:Label ID="Label2" runat="server" Text="Shipping box number:"></asp:Label>
        </td>
        <td class="auto-style1">
            <asp:TextBox ID="txtShipBoxNumber" runat="server"></asp:TextBox>
        </td>
    </tr>
    <tr>
        <td class="auto-style1">
            &nbsp;</td>
        <td class="auto-style1">
            <asp:Button ID="cmdSearch" runat="server" Text="Search" />
        </td>
    </tr>
</table>

<asp:SqlDataSource ID="SqlDataSource_GetShipBoxesBySearch" runat="server" ConnectionString="<%$ ConnectionStrings:MyApp.My.MySettings.ConnStr %>" SelectCommand="usp_PerformSearch" SelectCommandType="StoredProcedure">
    <SelectParameters>
        <asp:ControlParameter ControlID="txtSalesOrderLineNumber" Name="pSalesOrderLineNumber" PropertyName="Text" Type="String" />
        <asp:ControlParameter ControlID="txtShipBoxNumber" Name="pShipBoxNumber" PropertyName="Text" Type="String" />
    </SelectParameters>
</asp:SqlDataSource>

To simplify things, the cmdSearch button just calls the databind for the GridView:

   Protected Sub cmdSearch_Click(sender As Object, e As EventArgs) Handles cmdSearch.Click
            gvShipBoxes.DataBind()
    End Sub

GridView code:

<asp:GridView ID="gvShipBoxes" runat="server" AllowPaging="True" 
AllowSorting="True" AutoGenerateColumns="False" CellPadding="4" DataKeyNames="ShipBoxID" 
DataSourceID="SqlDataSource_GetShipBoxesBySearch" ForeColor="#333333" GridLines="None">
        <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
        <Columns>
            ...columns in here...
        </Columns>
        <EditRowStyle BackColor="#999999" />
        <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
        <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
        <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
        <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
        <SortedAscendingCellStyle BackColor="#E9E7E2" />
        <SortedAscendingHeaderStyle BackColor="#506C8C" />
        <SortedDescendingCellStyle BackColor="#FFFDF8" />
        <SortedDescendingHeaderStyle BackColor="#6F8DAE" />
</asp:GridView>

My problem is that if both fields are filled out, everything works, but if only one is filled out, nothing happens. I have used SQL Server Profiler to verify this. If both fields are filled out, I get an event in the profiler that shows my stored procedure being called with those two parameters. However, if only one is filled out, nothing is shown in profiler. Therefore, it is not even calling the stored procedure when only one textbox is filled out. I have experimented with setting the "DefaultValue" and "ConvertEmptyStringToNull" properties on both of the control parameters, but that made no difference.

I am probably missing something simple, and I would appreciate any suggestions. Thank you!

share|improve this question
    
Can you also post definition of your stored proc? – Andrei Jun 1 '16 at 13:52
    
And one more question - did you make sure DataBind is actually called at all if only one textbox is filled out? – Andrei Jun 1 '16 at 13:58
    
Andrei - Thanks for your quick reply. I have tested my stored proc in SSMS with only single parameters (both NULLs and empty strings) and it works well. I can post the code, but it's kinda not really the point. If SQL profiler doesn't even see the procedure being called, it doesn't matter what the results look like. – Loki70 Jun 1 '16 at 14:03
    
Andrei - And yes, I set a breakpoint on my DataBind line in code, and it does get executed when there is only one textbox filled out. For some reason, it just doesn't call SQL. Thanks again for your replies! – Loki70 Jun 1 '16 at 14:04
    
Ok, as long as you verified SP works with nulls/empty strings there is no need to see it indeed. How about grid view itself? Does it just have DataSourceId declaratively in the markup? Also I do not see subscription of cmdSearch_Click to events of cmdSearch – Andrei Jun 1 '16 at 14:10
up vote 0 down vote accepted

Have you tried this 

SqlDataSource_GetShipBoxesBySearch.SelectParmeter['pSalesOrderLineNumber'].DefaultValue = 'X';
SqlDataSource_GetShipBoxesBySearch.SelectParmeter['pShipBoxNumber'].DefaultValue = 'Y';
gvShipBoxes.DataBind();
share|improve this answer
    
Umer - Great suggestion. This put me on the track to something that works. Since I can't put all the code in this comment, I will add it as an answer (but mark your post as the accepted answer). Thank you! – Loki70 Jun 1 '16 at 14:45
    
glad i could i help.thank you too – umer Jun 2 '16 at 15:09
up vote 0 down vote

Per the suggestion by Umer, I removed the parameter declarations from my web page code. It just looks like this now:

<asp:SqlDataSource ID="SqlDataSource_GetShipBoxesBySearch" runat="server" ConnectionString="<%$ ConnectionStrings:MyApp.My.MySettings.ConnStr %>" SelectCommand="usp_PerformSearch" SelectCommandType="StoredProcedure">
        <SelectParameters>
        </SelectParameters>
    </asp:SqlDataSource>

Then I changed by button code to this:

Protected Sub cmdSearch_Click(sender As Object, e As EventArgs) Handles cmdSearch.Click

Try
    'Create the parameters for the SqlDataSource
    Dim p1 As Parameter = SqlDataSource_GetShipBoxesBySearch.SelectParameters("pSalesOrderLineNumber")
    Dim p2 As Parameter = SqlDataSource_GetShipBoxesBySearch.SelectParameters("pShipBoxNumber")

    'In any case, remove the existing parameters (on the 2nd search, don't want to keep previous values)
    SqlDataSource_GetShipBoxesBySearch.SelectParameters.Remove(p1)
    SqlDataSource_GetShipBoxesBySearch.SelectParameters.Remove(p2)

    'If there is a value in the textbox, add it as a parameter value...
    If txtSalesOrderLineNumber.Text <> "" Then
        SqlDataSource_GetShipBoxesBySearch.SelectParameters.Add("pSalesOrderLineNumber", txtSalesOrderLineNumber.Text.ToString)
    End If

    If txtShipBoxNumber.Text <> "" Then
        SqlDataSource_GetShipBoxesBySearch.SelectParameters.Add("pShipBoxNumber", txtShipBoxNumber.Text.ToString)
    End If

    gvShipBoxes.DataBind()

Catch ex As Exception
    lblError.Text = "Error in [cmdSearch_Click]: " & ex.Message.ToString
End Try

End Sub

Once I did this, I verified what was being passed in Sql Profiler. As I hoped, when only one textbox was filled out, only one parameter was passed. When both were filled out, both parameters were passed. In both scenarios, the GridView reflected the correct results of the stored proc.

Thank you for your help!

share|improve this answer

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.