I am not getting filter outcome in datagridview

Question

1.00/5 (1 vote)

See more:

I have
1)combobox having different column names like ,ID,Empid,Deptid
2)Textbox
3)datetimepicker1 & datetimepicker2

4) Run fileter button using above 1,2,3
5)Department button only uses Datetimepickers

but i am not getting results...

What I have tried:

C#

private void button1_Click(object sender, EventArgs e)
        {
            
                con2.Open();
                cmd = new OleDbCommand();
                da1 = new OleDbDataAdapter("select * from Finaldetails where (" + this.comboBox1.SelectedItem + ")  Like ('" + this.textBox1.Text + "') and  Date between '" + this.dateTimePicker1.Value.ToString("yyyy/MM/dd") + "' and '" + this.dateTimePicker2.Value.ToString("yyyy/MM/dd") + "' and Status = 'Approved'", con2);
                //cmd.Parameters.Add("@v1", OleDbType.VarChar , 100).Value = this.textBox1.Text;
                //cmd.Parameters.Add("@v2", OleDbType.DBDate).Value = this.dateTimePicker1.Value.ToString("yyyy/MM/dd");
                //cmd.Parameters.Add("@v3", OleDbType.DBDate).Value = this.dateTimePicker2.Value.ToString("yyyy/MM/dd");
                DataSet ds = new DataSet();
                da1.Fill(ds, "Finaldetails");
                dataGridView1.DataSource = ds.Tables["Finaldetails"];
                con2.Close();
}

private void button4_Click(object sender, EventArgs e)
        {
            //OleDbConnection con2 = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=F:\Consoldetails.accdb");
           
            OleDbDataAdapter da1 = new OleDbDataAdapter("select * from Finaldetails where Date between '"+dateTimePicker1.Value.ToString("yyyy/MM/dd")+"' and '"+dateTimePicker2.Value.ToString("yyyy/MM/dd")+"' and Status = 'Approved'",con2);
            DataSet ds = new DataSet();
            da1.Fill(ds, "Finaldetails");
            dataGridView1.DataSource = ds.Tables["Finaldetails"];
            con2.Close();
        
}

Posted 6-Apr-18 8:25am

Prateek gsharma

Updated 6-Apr-18 19:33pm

Bryian Tan

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RickZeeland · Answer 1 · 2018-04-06T09:52:00

Solution 1

First you could try using the % wildcard in your where clause:

Like ('%" + this.textBox1.Text + "%')

Otherwise you could try DataTable.Select() see example here: c# - Filtering DataSet - Stack Overflow[^]Instead of using a where clause in your:

da1 = new OleDbDataAdapter()

You could use something like:

DataSet ds = new DataSet();
da1.Fill(ds, "Finaldetails");
var strExpr = "MyField LIKE '%" + this.textBox1.Text + "%' AND Date > #6/1/2001#";
var foundRows = ds.Table[0].Select(strExpr);
dataGridView1.DataSource = foundRows;

Posted 6-Apr-18 9:52am

RickZeeland

Updated 9-Apr-18 0:39am

v4

Comments

Prateek gsharma 7-Apr-18 15:30pm

didn't get you sir.plz explain how to use that in my code?

Prateek gsharma 9-Apr-18 6:02am

var strExpr = "Select * from Finaldetails where ('"+this.combobox.selecteditem+"' and Like '%" + this.textBox1.Text + "%' AND Date between '"+this.Datetimepicker1.values.ToString("yyyy/MM/dd")+"' and '"+this.Datetimepicker2.values.ToString("yyyy/MM/dd")+"'";

RickZeeland 9-Apr-18 6:33am

That will not work, it's not full SQL syntax, see examples here: https://www.dotnetperls.com/datatable-select

Wendelius · Answer 2 · 2018-04-06T19:35:00

If I understand your question correctly, the query doesn't return rows.

If this is the case, place a breakpoint on

da1.Fill(ds, "Finaldetails");

Using debugger, investigate:
- Does the query look correct in da1.SelectCommand, see OleDbDataAdapter.SelectCommand Property (System.Data.OleDb)[^]
- When you execute the fill investigate, does the datatable in the dataset contain any rows.

If needed you can go through these tutorial:
- Debugging in Visual Studio[^]
- Navigating through Code with the Debugger[^]

Also you have tried the parameters and once you get the query working correctly, I suggest utilizing the parameters again. At the moment you're vulnerable to SQL injections and you're making assumptions about the server side date format.