How to implement a search method in c# and SQL Server?

Question

I want to implement a search method in which the user can select the search type from a combobox and enter search value in textbox.

The search button code is here but when I click on the search button, result datagridview is empty.

What is the problem?

 private void button1_Click(object sender, EventArgs e)
 {
     SqlCommand cmd = new SqlCommand();

     SqlConnection con = new SqlConnection();
     con.ConnectionString = "Data Source=.;Initial Catalog=MyDatabase;Integrated Security=True";

     con.Open();

     cmd.Connection = con;

     DataSet ds = new DataSet();

     SqlDataAdapter da = new SqlDataAdapter();

     cmd.CommandText = "select * from person where @parameter1=@parameter";

     if (comboBox1.SelectedIndex == 0)
     {
        cmd.Parameters.AddWithValue("@parameter1", "name");
     }
     else
     {
        cmd.Parameters.AddWithValue("@parameter1", "code");
     }

     cmd.Parameters.AddWithValue("@parameter",textBox1.Text);

     da.SelectCommand = cmd;
     da.Fill(ds);

     dataGridView1.DataSource = ds.Tables[0];
     con.Close();
}

Why don't you use EF? Just wondering, would be a lot easier. — Jamie Rees
– Jamie Rees, Commented Jul 20, 2015 at 14:50

Thomas Stringer · Accepted Answer · 2015-07-20 14:51:12Z

1

You can't parameterize column names unless you take extra consideration like with dynamic SQL (not recommended in this situation).

What you should do is change your logic and query to handle NULL for name or text, or just have conditional logic to construct your query.

string colSearchName;

if (comboBox1.SelectedIndex == 0)
    colSearchName = "name";
else
    colSearchName = "code";

cmd.CommandText = string.Format("select * from person where {0}=@parameter", colSearchName);

// ... so on and so forth

answered Jul 20, 2015 at 14:51

Thomas Stringer

5,9123 gold badges28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to implement a search method in c# and SQL Server?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related