locked
Throwing two values at a SelectCommand RRS feed

  • Question

  • User1357167467 posted

    Hi,

    I want to make a lookup take either a (serial) number or a (string) postcode / zipcode and lookup from a database, but I'm getting error message

    Conversion failed when converting the nvarchar value 'ab12 3cd' to data type int.

    when I put a value in which is not just a number (numeric lookup works fine)

    My query is written as:

    <asp:SqlDataSource ID="SqlDataSourceA1" runat="server"
                ConnectionString="<%$ ConnectionStrings:SQLConnString %>" 
                SelectCommand="SELECT * FROM [Data] WHERE ([Postcode]=@DataEntry) or ([URN] = @DataEntry) ">
                    <SelectParameters>
                        <asp:ControlParameter ControlID="Selection" Name="DataEntry" PropertyName="Text" />
                    </SelectParameters>
            </asp:SqlDataSource>

    Running this query in SQLMS works fine

    SELECT * FROM [Data] WHERE ([URN] = '100055') or ([Postcode]='ab12 3cd')

    and returns the values I expect, but when I try to add that process in an .aspx page it fails.
    I can't work out why the form is trying to convert the @DataEntry value to int.

    Any help much appreciated, thanks.

    Phil

    Friday, November 1, 2019 7:47 PM

Answers

  • User409696431 posted
    SELECT * FROM [Data] WHERE ([URN] = '100055') or ([Postcode]='ab12 3cd')

    works because you are passing two values, an int to URN and a string to Postcode.

    SELECT * FROM [Data] WHERE ([Postcode]=@DataEntry) or ([URN] = @DataEntry)

    will not work because you are passing the same parameter value to both an int and a string datatype, and if it's a string that is not simply numbers, it will fail.  You can't do that. 

    The best way to do this is to parse the "Selection" text during the event that triggers the lookup.  In that event, based on the value you find, assign the correct Select command to the DataSource in code behind, then DataBind() the control you are displaying the results in.

    A simple version without any error checking on the inputs:

    In the .aspx page:
    
            <asp:TextBox ID="Selection" runat="server"></asp:TextBox>
            <asp:Button ID="Lookup" runat="server" Text="Search" OnClick="Lookup_Click" />
            <br />
            <asp:GridView ID="GridView2" runat="server" DataSourceID="SqlDataSourceA1">
            </asp:GridView>
            <br />
            <asp:SqlDataSource ID="SqlDataSourceA1" runat="server" ConnectionString="<%$ ConnectionStrings:DefaultConnection %>" >
            </asp:SqlDataSource>
    
    
    In the .aspx.cs page:
    
    protected void Lookup_Click(object sender, EventArgs e)
            {
                int urn;
                string postcode;
                if(int.TryParse(Selection.Text, out urn))
                { SqlDataSourceA1.SelectCommand = "SELECT * FROM [Data] WHERE ([URN]=" + urn + ")";}
                else
                { postcode = Selection.Text;
                  SqlDataSourceA1.SelectCommand = "SELECT * FROM [Data] WHERE ([Postcode]='" + postcode + "')";
                }
                GridView2.DataBind();
            }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, November 2, 2019 11:33 PM

All replies

  • User409696431 posted
    SELECT * FROM [Data] WHERE ([URN] = '100055') or ([Postcode]='ab12 3cd')

    works because you are passing two values, an int to URN and a string to Postcode.

    SELECT * FROM [Data] WHERE ([Postcode]=@DataEntry) or ([URN] = @DataEntry)

    will not work because you are passing the same parameter value to both an int and a string datatype, and if it's a string that is not simply numbers, it will fail.  You can't do that. 

    The best way to do this is to parse the "Selection" text during the event that triggers the lookup.  In that event, based on the value you find, assign the correct Select command to the DataSource in code behind, then DataBind() the control you are displaying the results in.

    A simple version without any error checking on the inputs:

    In the .aspx page:
    
            <asp:TextBox ID="Selection" runat="server"></asp:TextBox>
            <asp:Button ID="Lookup" runat="server" Text="Search" OnClick="Lookup_Click" />
            <br />
            <asp:GridView ID="GridView2" runat="server" DataSourceID="SqlDataSourceA1">
            </asp:GridView>
            <br />
            <asp:SqlDataSource ID="SqlDataSourceA1" runat="server" ConnectionString="<%$ ConnectionStrings:DefaultConnection %>" >
            </asp:SqlDataSource>
    
    
    In the .aspx.cs page:
    
    protected void Lookup_Click(object sender, EventArgs e)
            {
                int urn;
                string postcode;
                if(int.TryParse(Selection.Text, out urn))
                { SqlDataSourceA1.SelectCommand = "SELECT * FROM [Data] WHERE ([URN]=" + urn + ")";}
                else
                { postcode = Selection.Text;
                  SqlDataSourceA1.SelectCommand = "SELECT * FROM [Data] WHERE ([Postcode]='" + postcode + "')";
                }
                GridView2.DataBind();
            }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, November 2, 2019 11:33 PM
  • User1357167467 posted

    Putting the If / else in the .cs was the obvious answer which I was never going to find. Thank you!!

    Sunday, November 3, 2019 4:49 PM