locked
SqlDataSource returns data for only parameter in vs 2010 RRS feed

  • Question

  • User-1997257017 posted

    In a vB.NET 2010 web form application, I am changing an existing web page. I want the user to be able to select various customers based upon if the first name, last name, or middle names are selected. The idea is for the user to select one, two, and/or three of the parameters and hit the search key.

    The problem is the results only return data based upon only one parameter. If data for more than one paramter is selected, only data from one parameter is selected. When I run the sql in sql server management studio, I get the results I want.

    Thus can you tell me what I can do to solve the problem?

    The code that is having the error is listed below;

    <%@ Page Title="" Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="Support.aspx.vb" Inherits="letters_Support" %>
    <asp:Content ID="Content1" ContentPlaceHolderID="MPHead" Runat="Server">   
    </asp:Content>
    <asp:Content ID="Content2" ContentPlaceHolderID="MPContent" Runat="Server">
    <asp:Panel runat="server" ID="pnlSupport">    
       <h3 style="text-align: center; border-style:none; ">Support Page</h3> 
       
          <table style="width: 100%;">          
              <tr>
                <td>
                   Last Name: 
                </td>
                <td>
                   <asp:TextBox ID="lname" runat="server"></asp:TextBox>
                </td>
                <td>
                    First Name: 
                </td>
                <td>
                   <asp:TextBox ID="fname" runat="server"></asp:TextBox>
                </td>
                <td>
                     Middle Name: 
                </td>
                <td>
                   <asp:TextBox ID="mname" runat="server"></asp:TextBox>
                </td>
                 <td>
                    Search by Name:
                </td>
                <td>
                   <asp:Button ID="btnSearch" runat="server" Text="Search" />
                </td>            
            </tr>
        </table>
        <br /> 
          <asp:GridView ID="GridViewSupport" runat="server" AutoGenerateColumns="False"
            PageSize="25"
            GridLines="Both"
            EnableSortinAndPagingCallbacks="true"
            HeaderStyle-CssClass="ui-widget-header"
            PagerStyle-CssClass="ui-widget ui-widget-content"
            Width="100%" 
            EmptyDataText="No records were selected." 
            DataSourceID="sqlTEST" AllowPaging="True" AllowSorting="True" >
            <Columns>
                <asp:BoundField DataField="lastName" HeaderText="Last Name" SortExpression="lastName" ItemStyle-HorizontalAlign="Center" ItemStyle-CssClass="ui-widget-content" ControlStyle-CssClass="text required ui-widget-content ui-corner-all"/>
                <asp:BoundField DataField="firstName" HeaderText="First Name" SortExpression="firstName" ItemStyle-HorizontalAlign="Center" ItemStyle-CssClass="ui-widget-content" ControlStyle-CssClass="text required ui-widget-content ui-corner-all"/>
                <asp:BoundField DataField="middleName" HeaderText="Middle Name" SortExpression="middleName" ItemStyle-HorizontalAlign="Center" ItemStyle-CssClass="ui-widget-content" ControlStyle-CssClass="text required ui-widget-content ui-corner-all"/>
            </Columns>
        </asp:GridView>
        <asp:SqlDataSource ID="sqlTEST" runat="server" 
        ConnectionString="<%$ ConnectionStrings:sqlTEST %>" SelectCommand="SELECT [lastName] 
                ,[firstName]  
                ,[middleName] 
                ,[suffix]  
                ,a.[userid]
                from [TEST].[dbo].[Identity] 
                  where ([lastName] like  '%' + @lname  + '%' and
                       [firstName] like'%' + @fname + '%' and
    		   [middleName]  like'%' + @mname + '%') 
    	       or ([lastName] like  '%' + @lname  + '%' and
                       [firstName] like'%' + @fname + '%'  and
    	               @mname is null)				    
    	       or ([lastName] like  '%' + @lname  + '%' and
                       [middleName] like'%' + @mname + '%'  and
    	               @fname is null) 
                   or ([firstName] like  '%' + @fname  + '%' and
                       [middleName] like'%' +  @mname + '%' and
    	               @lname is null) 
                   or ([lastName] like  '%' + @lname  + '%' and
                       @fname is null and
    	               @mname is null) 
    		or (@lname is null and
                       [firstName] like'%' +  @fname + '%' and
    	               @mname is null) 
                    or (@lname is null and
                       @fname is null and
    	               [middleName] like'%' +  @mname + '%') 	
                   or (@fname is null and @lname is null and @mname is null)     
          	  order by  [lastName], [firstName], [middleName]" 
            CancelSelectOnNullParameter="false"  >
        <SelectParameters>
            <asp:ControlParameter ControlID="lname" Name="lname" PropertyName="Text" ConvertEmptyStringToNull="true" />
            <asp:ControlParameter ControlID="fname" Name="fname" PropertyName="Text" ConvertEmptyStringToNull="true" />
            <asp:ControlParameter ControlID="mname" Name="mname" PropertyName="Text" ConvertEmptyStringToNull="true" />
        </SelectParameters>
    </asp:SqlDataSource>
      </asp:Panel>
    </asp:Content>
    
    
    

    Thursday, December 4, 2014 4:00 PM

Answers

  • User-821857111 posted

    Try changing the selectcommand as follows:

    SELECT [lastName], [firstName],[middleName] ,[suffix],a.[userid]
    from [TEST].[dbo].[Identity] where 
    ([@fname is null or firstname like '%' + @fname + '%') AND 
    [@@mname is null or middleName like '%@ + @@mname + '%') AND
    [@lname is null or lastName like '%' + @lname + '%'))
    order by  [lastName], [firstName], [middleName]

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 4, 2014 5:05 PM

All replies

  • User-821857111 posted

    Try changing the selectcommand as follows:

    SELECT [lastName], [firstName],[middleName] ,[suffix],a.[userid]
    from [TEST].[dbo].[Identity] where 
    ([@fname is null or firstname like '%' + @fname + '%') AND 
    [@@mname is null or middleName like '%@ + @@mname + '%') AND
    [@lname is null or lastName like '%' + @lname + '%'))
    order by  [lastName], [firstName], [middleName]

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 4, 2014 5:05 PM
  • User-1997257017 posted

    I tried changing the select statement and I get results that look like:

    when I select firstname= 'Diane' and lastname= 'smith'

    firstname      lastname

    diane            smith

    diane            jones

    barb            smith

    Friday, December 5, 2014 1:18 PM
  • User-821857111 posted

    What result would you like?

    Friday, December 5, 2014 1:41 PM
  • User-1997257017 posted

    Your query works fine. I was incorrectly looking at the wrong web page.

    Friday, December 5, 2014 4:29 PM