Loke Lindhardt Loke Lindhardt - 2 months ago 7
ASP.NET (C#) Question

SELECT statement doesn't return the expected results

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.

Answer

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.