locked
Sql - This doesn't make any senseSqlDataSource1.SelectCommand = "select * from perfumes where marca=? and genero='Masculino' order by preco ASC"; SqlDataSource1.SelectParameters.Add("@ff", Request.QueryString["marca"]); RRS feed

  • Question

  • User-909867351 posted

    Hi 

    This sql works for me:

    SqlDataSource1.SelectCommand = "select * from perfumes where marca=? and genero='Masculino' order by preco ASC";
    SqlDataSource1.SelectParameters.Add("@ff", Request.QueryString["marca"]);

    This sql doesn't work. It returns no rows, why?

    string sexo = "Masculino";
            SqlDataSource1.SelectCommand = "select * from perfumes where marca=? and genero=? order by preco ASC";
            SqlDataSource1.SelectParameters.Add("@ff", Request.QueryString["marca"]);
            SqlDataSource1.SelectParameters.Add("@genero", sexo);

    Sunday, July 5, 2015 7:34 PM

All replies

  • User2024324573 posted

    Try to assign default value as:


    SqlDataSource1.SelectParameters["genero"].DefaultValue = sexo.ToString();

    see the trick here

    Hope this will help.

    Sunday, July 5, 2015 7:52 PM
  • User-1716253493 posted

    remove "@" character

    string sexo = "Masculino";
    SqlDataSource1.SelectCommand = "select * from perfumes where marca=@ff and genero=@genero order by preco ASC";
    SqlDataSource1.SelectParameters.Add("ff", Request.QueryString["marca"]);
    SqlDataSource1.SelectParameters.Add("genero", sexo);

    Sunday, July 5, 2015 7:58 PM
  • User-909867351 posted

    No luck

     string sexo = "Masculino";
            SqlDataSource1.SelectCommand = "select * from perfumes where marca=@ff and genero=@sexo order by preco ASC";
            SqlDataSource1.SelectParameters.Add("ff", Request.QueryString["marca"]);
            SqlDataSource1.SelectParameters.Add("sexo", sexo);

    Returns no records

    This not work too

    SqlDataSource1.SelectCommand = "select * from perfumes where marca=@ff and genero='Masculino' order by preco ASC";
            SqlDataSource1.SelectParameters.Add("ff", Request.QueryString["marca"]);

    Monday, July 6, 2015 5:55 AM
  • User-909867351 posted

    Hi

    This not work in my case:

    string sexo = "Masculino";
            SqlDataSource1.SelectCommand = "select * from perfumes where marca=@marca and genero=@genero order by preco ASC";
            SqlDataSource1.SelectParameters.Add("@marca", Request.QueryString["marca"]);
            SqlDataSource1.SelectParameters.Add("@genero", sexo);
            SqlDataSource1.SelectParameters["@genero"].DefaultValue = sexo.ToString();

    This not work either

     SqlDataSource1.SelectCommand = "select * from perfumes where marca=@marca and genero='Masculino' order by preco ASC";
            SqlDataSource1.SelectParameters.Add("@marca", Request.QueryString["marca"]);

    This works

    SqlDataSource1.SelectCommand = "select * from perfumes where marca=? and genero='Masculino' order by preco ASC";
            SqlDataSource1.SelectParameters.Add("@marca", Request.QueryString["marca"]);

    Monday, July 6, 2015 6:03 AM
  • User-271186128 posted

    Hi Mario Lopes,

    According to your code and description, I create a sample using the following code, it worked well on my side.

        <div>
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="UserID">
                <Columns>
                    <asp:BoundField DataField="UserID" HeaderText="UserID" InsertVisible="False" ReadOnly="True" SortExpression="UserID" />
                    <asp:BoundField DataField="UserName" HeaderText="UserName" SortExpression="UserName" />
                </Columns>
            </asp:GridView>
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MyTestDBConnStr %>" ></asp:SqlDataSource>
        </div>

    Code Behind:

            protected void Page_Load(object sender, EventArgs e)
            {
                GridView1.DataSourceID = "SqlDataSource1";
                SqlDataSource1.SelectCommand = "SELECT [UserID], [UserName] FROM [UserTest] WHERE UserID=@userid and UserName= @username";
                SqlDataSource1.SelectParameters.Add("userid", "1001");
                SqlDataSource1.SelectParameters.Add("username", "AA1");
                GridView1.DataBind();
            }

    Note: I use SQL server database.

    Since you are using question mark in the select query statement, I suppose perhaps you are connecting to an OLE DB or ODBC data source. If that is the case, I suggest you could try to use the following code:

    string sexo = "Masculino";
            SqlDataSource1.SelectCommand = "select * from perfumes where marca=? and genero=? order by preco ASC";
            SqlDataSource1.SelectParameters.Add("marca", Request.QueryString["marca"]);
            SqlDataSource1.SelectParameters.Add("genero", sexo);

    Here is a article about using Parameters with the SqlDataSource Control, please refer to it: https://msdn.microsoft.com/en-us/library/z72eefad(v=vs.140).aspx

    Best Regards,
    Dillion

    Tuesday, July 7, 2015 1:58 AM
  • User-909867351 posted

    Many thanks for your time and support

    Unfotunately I don't have good news. I work with one Mysql table and odbc connection

    Please if you want to see my table here is it:

    http://cheirabem.com/perfumes.zip

    The code

    string sexo = "Masculino";
            SqlDataSource1.SelectCommand = "select * from perfumes where marca=? and genero=? order by preco ASC";
            SqlDataSource1.SelectParameters.Add("marca", Request.QueryString["marca"]);
            SqlDataSource1.SelectParameters.Add("genero", sexo);

    doesn't work for me but the code:

    SqlDataSource1.SelectCommand = "select * from perfumes where marca=? and genero='Masculino' order by preco ASC";
            SqlDataSource1.SelectParameters.Add("marca", Request.QueryString["marca"]);
    

    works fine

    Why?

    Tuesday, July 7, 2015 2:10 PM