Datagridview c# Where query

Question

I am attempting to make my datagridview3 populate with the results of the following code and query but when I click the button to run the code the datagrid view stays blank. Is there anything I am missing?

                private void filter()
    {
        string Query2 = "Select * " +
            "From Child " +
            "WHERE @Value like '@Textbox'";

        SqlConnection con2 = new SqlConnection("Data Source=LT-SDGFLD-1803;Initial Catalog=Test;Persist Security Info=True;User ID=sa;Password=");
        SqlCommand cmd2 = new SqlCommand(Query2 , con2);
        try
        {
            cmd2.Parameters.Add("@Value", System.Data.SqlDbType.VarChar);
            cmd2.Parameters.Add("@Textbox", System.Data.SqlDbType.VarChar);
            cmd2.Parameters["@Value"].Value = Properties.Settings.Default.ComboBox;
            cmd2.Parameters["@Textbox"].Value = Properties.Settings.Default.TextBox;
            con2.Open();
            SqlDataAdapter da2 = new SqlDataAdapter();
            da2.SelectCommand = cmd2;
            DataTable dt2 = new DataTable();
            da2.Fill(dt2);
            dataGridView3.DataSource = dt2;
            dataGridView3.Update();
            dataGridView3.Refresh();
            con2.Close();
        }
        catch (Exception ec)
        {
            MessageBox.Show(ec.Message);
        }
    }

    private void button1_Click(object sender, EventArgs e)
    {
        dataGridView3.DataSource = "LT-SDGFLD-1803";
        dataGridView3.Refresh();
        filter();
    }

    private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
    {
        Properties.Settings.Default.ComboBox = comboBox1.SelectedText.ToString();
        Properties.Settings.Default.Save();
    }

    private void textBox1_TextChanged(object sender, EventArgs e)
    {
        Properties.Settings.Default.TextBox = textBox1.Text.ToString();
        Properties.Settings.Default.Save();
    }

Possible duplicate of [How can I refresh c# dataGridView after update ?](https://stackoverflow.com/questions/7008361/how-can-i-refresh-c-sharp-datagridview-after-update) — Mong Zhu, Sep 19 '17 at 09:26
@MihaiOvidiuDrăgoi Didn't work, still nothing populating in Datagridview3 — imamage597, Sep 19 '17 at 09:30
did you check with the debugger, whether you `dt2` has any entries at all ?=!. Are the columns with their corresponding headers at least visible in the datagridview? or is it completely empty? — Mong Zhu, Sep 19 '17 at 09:31
@MongZhu I know for definite that what I was inputting exists within my SQL database. I don't know how to view the debugger and see what it pulls back? — imamage597, Sep 19 '17 at 09:32
set a breakpoint at this line: `dataGridView3.DataSource = dt2;`. Run the program in Debug modus. -> it will stop at this line and let you examine the variables. Then hover with the mouse over the variable `dt2` and check the `Rows.Count` property — Mong Zhu, Sep 19 '17 at 09:35
@MongZhu The rows count is 0 which I am guessing means that my values aren't being saved and set? I have updated the OP with my latest code. — imamage597, Sep 19 '17 at 09:49
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/154781/discussion-between-mong-zhu-and-imamage597). — Mong Zhu, Sep 19 '17 at 10:51
I am not sure if you can pass the field name `@Value` as a parameter. Try the query without parameters. — Slai, Sep 19 '17 at 11:02
@Slai The query works perfectly if I manually input the data I want back. — imamage597, Sep 19 '17 at 11:21
one of the errors is that you need to use `comboBox1.SelectedItem.ToString()` instead of `omboBox1.SelectedText.ToString();`. otherwise the value of `Properties.Settings.Default.ComboBox` will be empty. — Mong Zhu, Sep 19 '17 at 11:55
@MongZhu I have changed this to no avail. Still an empty Datagridview upon button press — imamage597, Sep 19 '17 at 12:00
@imamage597 I know. There is more, but I haven't figured it out yet — Mong Zhu, Sep 19 '17 at 12:17
actually the next point is that you should use the value from combobox directly, since it is safe for sql injection. Parameters are used only for values that you want to compare, not for placeholders for column names — Mong Zhu, Sep 19 '17 at 12:56

score 0 · Answer 1 · answered Sep 19 '17 at 10:55

0

I think you should use dataGridView3.DataBind(); after adding datasource to the DataGridview.

answered Sep 19 '17 at 10:55

Nagib Mahfuz

833
10
19

I have another datagridview which uses the same code as this but without the textbox and combobox and that works perfectly. The databind doesn't make any difference. – imamage597 Sep 19 '17 at 11:25
`DataBind()` on a `DataGridView` in `Winforms`? `DataBind()` applies to `GridView` in `asp.net WebForms` – blaze_125 Sep 19 '17 at 18:21

score 0 · Answer 2 · answered Sep 19 '17 at 17:21

Field name can't be passed as parameter, but something like this should work:

string query = "SELECT * FROM Child WHERE " + Properties.Settings.Default.ComboBox + " LIKE '@Textbox'";
string cnStr = "Data Source=LT-SDGFLD-1803;Initial Catalog=Test;Persist Security Info=True;User ID=sa;Password=";
DataTable dt = new DataTable();

using (SqlDataAdapter da = new SqlDataAdapter(query, cnStr)) {
    da.SelectCommand.Parameters.Add("@Textbox", System.Data.SqlDbType.VarChar).Value = Properties.Settings.Default.TextBox;
    da.Fill(dt);
}
dataGridView3.DataSource = dt;

Datagridview c# Where query

2 Answers2