CODEWITHSUNDEEP
c#sqlsql-server
Kuriyama Mirai
Kuriyama Mirai

Reputation: 937

Correct Query wrong output

I have this query in my project

u_name = Application.Current.Properties["username"].ToString();
cmd = new SqlCommand("Select 'User ID' = u_id, 'USERNAME' = u_name, 'ROLE' = r_name from [User] as u, [Role] as r Where u_name != '@u_name' and u.r_id = r.r_id and r_name like '%" + txtBoxSearch.Text.Replace("'", "''").Trim() + "%' or u_name like '%" + txtBoxSearch.Text.Replace("'", "''").Trim() + "%'", conn);
cmd.Parameters.AddWithValue("@u_name", u_name);

da = new SqlDataAdapter(cmd);

dt = new DataTable("User");
da.Fill(dt);
dataGridUser.ItemsSource = dt.DefaultView;
dataGridUser.Columns[0].Visibility = System.Windows
                                  .Visibility
                                  .Hidden;

When I try to use the query in SQL Server, It shows the correct output but when I use it in my project and show it in a datagrid using search button. It still shows the username that should not be shown because of u_name != @u_name statement I use in my query.Whats wrong with that?

Upvotes: 0

Views: 84

Answers (2)

user3624833
user3624833

Reputation:

Its because of your final OR clause. You have this at the end of your query:

    or u_name like '%" + txtBoxSearch.Text.Replace("'", "''").Trim() + "%'

Use ( and ) to enclose different groups of your WHERE clause for the correct result. You are perhaps getting the right output in the query analyzer because you are replacing the reference to the txtBoxSearch control with a literal value.

Upvotes: 0

Cyassin
Cyassin

Reputation: 1490

Your username is not wrapped in single quotes and is therefore being displayed as if:

u_name != Administrator

instead of 

u_name != 'Administrator'

To resolve this, I suggest that you change:

u_name != @u_name

into

u_name != '@u_name'

Upvotes: 1

Related Questions