SELECT statement doesn't return the expected results

Question

Hello there good people of Stackoverflow.

I have a issue with my Sql, since I'm new to it I'm quite sure that its a matter of Syntax or bad/misunderstod logic.

here is the relevant frondend:

<asp:DropDownList ID="DropDownList_Brand" runat="server">
  <asp:ListItem>Brændeovn</asp:ListItem>
  <asp:ListItem>Brændespande</asp:ListItem>
  <asp:ListItem>Pejsesæt</asp:ListItem>
  <asp:ListItem>Optænding</asp:ListItem>
  <asp:ListItem>Vedligeholdelse</asp:ListItem>
</asp:DropDownList>
<asp:TextBox ID="TextBox_Sog_Adv" runat="server"></asp:TextBox>
<asp:Button ID="Button_Sog_Adv" runat="server" Text="SØG" OnClick="Button_Sog_Click_Adv" />

And here is the backend:

protected void Button_Sog_Click_Adv(object sender, EventArgs e)
{
    SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = conn;
    var Pr_Navn = TextBox_Sog_Adv.Text;
    var Pr_Type = DropDownList_Brand.Text;
    cmd.CommandText = "SELECT * FROM Table_Products WHERE products_name LIKE '%" + Pr_Navn + "%' OR products_brand LIKE '%" + Pr_Navn + "%' AND products_type='" + Pr_Type + "'";


    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    adapter.Fill(dt);
    Repeater_Sog_Adv.DataSource = dt;
    Repeater_Sog_Adv.DataBind();
}

Now I'm quite sure its the AND thats the problem, I'm lead to belive that AND is like OR but it must be True. (Might be Wrong)

Yet the main focus here is this line:

cmd.CommandText = "SELECT * FROM Table_Products WHERE products_name LIKE '%" + Pr_Navn + "%' OR products_brand LIKE '%" + Pr_Navn + "%' AND products_type='" + Pr_Type + "'";

Any help is much appreciated.

We don't know what results you want to get,so we can't help. Are the results you are getting the wrong ones? Are you getting any error? Edit your question and add this info. Anyway, I think you probably are missing parenthesis in your SELECT clause — Pikoh, Sep 30 '16 at 07:30
Please edit the title and question text. Ask what you actually want. `SQL Adv. Search` is meaningless. Especially when the only statement is a pretty basic `SELECT`. You don't even say *what* is your problem. — Panagiotis Kanavos, Sep 30 '16 at 07:38
@Pikoh Sorry for leaving the error out, well it works but its like it returns the search query ignoring everything after the AND. — Loke Lindhardt, Sep 30 '16 at 07:42

score 4 · Accepted Answer · edited May 23 '17 at 10:34

4

You probably miss some parenthesis

WHERE (products_name LIKE '%" + Pr_Navn + "%' OR products_brand LIKE '%" + Pr_Navn + "%') AND products_type='" + Pr_Type + "'";

You may take a look at operator precedence in sql (sql server here) to understand how mixed OR/AND are treated, and forget it immediatly to remember you should rather use parenthesis.

Now, your should take care of sql injection, and use parameterized queries.

edited May 23 '17 at 10:34

Community

1
1

answered Sep 30 '16 at 07:31

Raphaël Althaus

59,727
6
96
122

I tried to understand what the query was meant to do, and yeah, now it makes more sense. – quetzalcoatl Sep 30 '16 at 07:35
You mean like this? cmd.Parameters.Add("@Pr_image", System.Data.SqlDbType.VarChar).Value = TextBox_Billede.Text; The reason i diden't use it here was just to make it easyer to read. – Loke Lindhardt Sep 30 '16 at 07:44
somethink like that, you may also use `cmd.Parameter.AddWithValue("@Pr_image", TextBox_Billede.Text)`. And of course you'll have to change your query. – Raphaël Althaus Sep 30 '16 at 07:47
I forgot to say your absolutely right it was the () missing,.. When learning syntax can be your enemy. Thanks @RaphaëlAlthaus – Loke Lindhardt Sep 30 '16 at 08:01

SELECT statement doesn't return the expected results

1 Answers1