selected value used as a variable for SQL Query_for Column and for criteria

Question

Good day.

I am working on a Database app (Windows forms, C#).

My application has an option (i wish) to search by different parameters.

First search parameter/criteria is determined by a button click. When a button (for example "button1") is clicked, "label4" is assigned a value (different value for a different button; there are 6 buttons). This value is equal to the column name in my Database Table.

Also, after button1 is clicked a combobox ("comboBox1") is populated with data. [For defining query parameters (from comboBox selection) I used: Stackoverflow_1 and Stackoverflow_2.]

So, I tried to use the same principle as I got from 1 and 2, but it doesn't seem to work.

The results should be displayed in a "Data Grid View" after "button9 is clicked". Did I go wrong with "data adapter" and "data set" for the Data Grid View? If I "hard-code" the column value, it works, so my suspect is "column variable".

Help. Thank you.

TLDR: how to set variables in SQL string search?

 public void button9_Click(object sender, EventArgs e)
        {  
            if (con.State == ConnectionState.Closed)
            {
                con.Open();

                string column = this.label4.Text;                                       //column is the variable/value which I get when selecting "sort by" button
                string XXX = this.comboBox1.GetItemText(this.comboBox1.SelectedItem);  //XXX is the variable/value which I get when comboBox item is selected
                SqlCommand cmd9 = new SqlCommand("SELECT * FROM tblTable WHERE @column=@XXX", con);

                cmd9.Parameters.Add("@column", SqlDbType.NVarChar, -1);
                cmd9.Parameters["@column"].Value = column;

                cmd9.Parameters.Add("@XXX", SqlDbType.NVarChar, -1);
                cmd9.Parameters["@XXX"].Value = XXX;


                SqlDataAdapter adapter9 = new SqlDataAdapter(cmd9);  //to view data in DataGridview I need a "data adapter" and "data set"
                DataSet ds9 = new DataSet();
                adapter9.Fill(ds9, "tblTable");

                dataGridView1.DataSource = ds9.Tables["tblTable"];

                con.Close();

             }


        }

omriman12 · Accepted Answer · 2018-07-25 15:18:03Z

@column is not a parameter....

If you want a dynamic query you should use if-else statements with concated query string.(List StringBuilder)

you need something like:

con.Open();

string column = this.label4.Text;                                       
string XXX = this.comboBox1.GetItemText(this.comboBox1.SelectedItem);  
StringBuilder sb = new StringBuilder("SELECT * FROM tblTable WHERE 1=1");
if(column == "...")
{
    sb .Append(" column=@XXX...");
}
SqlCommand cmd9 = new SqlCommand(sb.ToSTring(), con);

cmd9.Parameters.Add("@XXX", SqlDbType.NVarChar, -1);
cmd9.Parameters["@XXX"].Value = XXX;

SqlDataAdapter adapter9 = new SqlDataAdapter(cmd9);  //to view data in DataGridview I need a "data adapter" and "data set"
DataSet ds9 = new DataSet();
adapter9.Fill(ds9, "tblTable");

dataGridView1.DataSource = ds9.Tables["tblTable"];

con.Close();

Also never open and close connections like that, use Using

Parus · Accepted Answer · 2018-07-26 06:18:04Z

Omriman12 sent me in the right direction. Here is what works:

  public void button9_Click(object sender, EventArgs e)
        {  
            if (con.State == ConnectionState.Closed)
            {
                con.Open();

                string column = this.label4.Text;                                       //column is the variable/value which I get when selecting "sort by" button
                string XXX = this.comboBox1.GetItemText(this.comboBox1.SelectedItem);  //XXX is the variable/value which I get when comboBox item is selected

                StringBuilder sb = new StringBuilder();

                //1
                if (column == "AAA")
                {
                    sb.Append("SELECT * FROM tblTable_values WHERE ").Append(column).Append(" =@XXX");
                }
                //2
                if (column == "BBB")
                {
                    sb.Append("SELECT * FROM tblTable_values WHERE ").Append(column).Append(" =@XXX");
                }
                //3
                if (column == "CCC")
                {
                    sb.Append("SELECT * FROM tblTable_values WHERE ").Append(column).Append(" =@XXX");
                }
                //4
                if (column == "DDD")
                {
                    sb.Append("SELECT * FROM tblTable_values WHERE ").Append(column).Append(" =@XXX");
                }
                //5
                if (column == "EEE")
                {
                    sb.Append("SELECT * FROM tblTable_values WHERE ").Append(column).Append(" =@XXX");
                }
                //6
                if (column == "FFF")
                {
                    sb.Append("SELECT * FROM tblTable_values WHERE ").Append(column).Append(" =@XXX");
                }

                SqlCommand cmd9 = new SqlCommand(sb.ToString(), con);

                cmd9.Parameters.Add("@XXX", SqlDbType.NVarChar, -1);
                cmd9.Parameters["@XXX"].Value = XXX;


                SqlDataAdapter adapter9 = new SqlDataAdapter(cmd9);  //to view data in DataGridview I need a "data adapter" and "data set"
                DataSet ds9 = new DataSet();
                adapter9.Fill(ds9, "tblTable_values");

                dataGridView1.DataSource = ds9.Tables["tblTable_values"];

                con.Close();

             }

yaaaaay!

Collectives™ on Stack Overflow

selected value used as a variable for SQL Query_for Column and for criteria

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related