CODEWITHSUNDEEP
c#sql-server
daneshjoo
daneshjoo

Reputation: 27

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

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();
}

Upvotes: 1

Views: 309

Answers (1)

Thomas Stringer
Thomas Stringer

Reputation: 5862

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

Upvotes: 1

Related Questions