Adding filters in C#

[TheDocterd]

Hi there

I'm looking for a way to add filters in my windows form application. I have a menu strip with item called 'Filters'. Under that items there are items called 'Jobs Pending, Jobs Outstanding, Etc.....'

Take for example 'Jobs Pending'. How do I filter from my database in sql server 2005? My form has a datagridview if that helps.

Here is a sample code I tried, but unsuccessful :(

private void jobsPendingToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                //Opening the sql connection and using custom sql statements
                SqlConnection conn = new SqlConnection("Data Source=DEWALD-PC;Initial Catalog=LogbookDatabase;Persist Security Info=True;User ID=sa;Password=123");
                String query = "SELECT * FROM " + currentYearLbl.Text + " WHERE " + jobsPendingToolStripMenuItem + " = Pending";
                SqlDataAdapter dap = new SqlDataAdapter(query, conn);
                DataTable dt = new DataTable();
                dap.Fill(dt);
                dataGridView1.DataSource = dt;
            }
            catch (Exception err)
            {
                MessageBox.Show("Unable update datagridview" + err);
            }
        }

[kvprajapati]

Pending is a column name then,

String query = "SELECT * FROM " + currentYearLbl.Text + " WHERE Pending='" + jobsPendingToolStripMenuItem + "'";

[Mitja Bonca]

As said....
one more thing, the parameter you want to pass to the sql query its not good to use in the query it self. Its better to "attach" it to the sqlCommand,m like this:

try
            {
                //Opening the sql connection and using custom sql statements
                SqlConnection conn = new SqlConnection("Data Source=DEWALD-PC;Initial Catalog=LogbookDatabase;Persist Security Info=True;User ID=sa;Password=123");
                String query = String.Format("SELECT * FROM " + currentYearLbl.Text + " WHERE Pending = @myPending");
                SqlCommand cmd = new SqlCommand(query, conn);
                cmd.Parameters.Add("@myPendings", SqlDbType.VarChar, 50).Value = jobsPendingToolStripMenuItem;
                SqlDataAdapter dap = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                dap.Fill(dt);
                dataGridView1.DataSource = dt;
            }
            catch (Exception err)
            {
                MessageBox.Show("Unable update datagridview" + err);
            }

[TheDocterd]

That makes sense :) but now a new problem arises...

At this part of the code:

cmd.Parameters.Add("@myPendings", SqlDbType.VarChar, 50).Value = jobsPendingToolStripMenuItem;

The last piece of the code is the problem. How do I set the filter to fill the datagridview with only entries that are checked true? My pending column in the database is type bit. I tried setting this piece of code equal to true, but then it said I must declare the scalar of @myPending.

[Mitja Bonca]

What did you have before in the "jobsPendingToolStripMenuItem" value? I was even surprised to see this value (actually is not a value, but some control) in your query string.

So, this quiery compares to a boolean value from your dgv`s selected row or what? If so, you can get the boolean value from selected row and pass it in the sqlCommand parameter to the query, like:

foreach    (DataGridViewRow row in dataGridView1.Rows)
            {
                object value = row.Cells["checkBox"].Value;
                if (value != null && (Boolean)value == true)
                {
                    //this row has a tick, so you can count it in!
                }
            }

But I am not sure what you intend to do. You want for all "checked" rows to do what?

[TheDocterd]

That's correct. It has to display all the entries that have a true value in the pending column.

This is the code I have so far:

private void jobsPendingToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                //Opening the sql connection and using custom sql statements
                SqlConnection conn = new SqlConnection("Data Source=DEWALD-PC;Initial Catalog=SurveyDataDatabase;Persist Security Info=True;User ID=sa;Password=123");
                String query = String.Format("SELECT * FROM " + currentYearLbl.Text + " WHERE Pending = @myPending");
                SqlCommand cmd = new SqlCommand(query, conn);
                cmd.Parameters.Add("@myPendings", SqlDbType.Bit).Value = true;
                SqlDataAdapter dap = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                dap.Fill(dt);
                dataGridView1.DataSource = dt;
            }
            catch (Exception err)
            {
                MessageBox.Show("Unable update datagridview" + err);
            }
        }

[Mitja Bonca]

And, is it working, or not? Sorry, but you didnt say anything about it. Just wondering if it does. If so, please mark the thread as salved, otherwise, ask me for some more (but only about this topic, if there is something else, please start a new thread).

bye, bye

Mitja

[TheDocterd]

I still get an error... :(

cmd.Parameters.Add("@myPendings", SqlDbType.Bit).Value = true;

[Mitja Bonca]

Which error?

[TheDocterd]

'Must declare the scalar variable @myPending' ??

[Mitja Bonca]

you said that "Panding" column name is a type of "bit". That means only true, or false value are required for insertion and selection. I did that in the upper example:
... Value = true; //this is it!

If this is not working for you, I dont know what can be wrong.. but I guess your Pending column is not a type of bit. Please take a look and check the code and database ones again.

Mitja

[TheDocterd]

Me again

I managed to get the problem solved with your code. It was my mistake so sorry for the trouble ey..

Here is the code that is working for me now:

private void jobsPendingToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                foreach (DataGridViewRow row in dataGridView1.Rows)
                {
                    object value = row.Cells["Pending"].Value;
                    if (value != null && (Boolean)value == true)
                    {
                        SqlConnection conn = new SqlConnection("Data Source=DEWALD-PC;Initial Catalog=SurveyDataDatabase;Persist Security Info=True;User ID=sa;Password=123");
                        String query = String.Format("SELECT * FROM " + currentYearLbl.Text + " WHERE Pending = '" + value + "' AND Office = '" + displayOfficeLbl.Text + "'");
                        SqlCommand cmd = new SqlCommand(query, conn);
                        SqlDataAdapter dap = new SqlDataAdapter(cmd);
                        DataTable dt = new DataTable();
                        dap.Fill(dt);
                        dataGridView1.DataSource = dt;
                    }
                }
            }
            catch (Exception er)
            {
                MessageBox.Show("Error: " + er);
            }
        }

Thank you so much for the help :)