Not getting results while filtering datagridview using datetimepicker, combobox & textbox

Question

1.00/5 (1 vote)

See more:

Not getting results While filtering datagridview using Datetimepicker,Combobox & textbox

Using Access databse.

My date format is : MM/dd/yyyy

moreover i want to get this result using "Run Filter" button.

What I have tried:

Error Message : System.Data.OleDb.OleDbException: 'No value given for one or more required parameters.

'

My Code :

C#

private void textBox1_TextChanged(object sender, EventArgs e)
        {
            //Format(dateTimePicker1.Value, "mm/dd/yyyy");
            if(comboBox1.Text == "ID")
            {
                OleDbDataAdapter da = new OleDbDataAdapter("select * from Spldetails where ID like '%" + textBox1.Text + "%' and Date >= ('"+(dateTimePicker1.Value.ToString("mm/dd/yyyy"))+ "') and Date <= ('" + (dateTimePicker2.Value.ToString("mm/dd/yyyy")) + "')", con);
                OleDbCommandBuilder build = new OleDbCommandBuilder(da);
                DataSet ds = new DataSet();
                da.Fill(ds, "Spldetails");
                dataGridView1.DataSource = ds.Tables["Spldetails"];
            }
            if (comboBox1.Text == "Empid")
            {
                OleDbDataAdapter da = new OleDbDataAdapter("select * from Spldetails where EmpId like '%" + textBox1.Text + "%' and Date >= ('" + (dateTimePicker1.Value.ToString("mm/dd/yyyy")) + "') and Date <= ('" + (dateTimePicker2.Value.ToString("mm/dd/yyyy")) + "'))", con);
                OleDbCommandBuilder build = new OleDbCommandBuilder(da);
                DataSet ds = new DataSet();
                da.Fill(ds, "Spldetails");
                dataGridView1.DataSource = ds.Tables["Spldetails"];
            }
        }

Posted 11-Apr-18 9:10am

Prateek gsharma

Updated 11-Apr-18 9:20am

Wendelius

v2

Add a Solution

1 solution

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)

Wendelius · Answer 1 · 2018-04-11T09:20:00

Solution 1

As explained earlier, you should use parameters. This will allow you to pass the values to the query safely and without conversion problems.

The obvious question with the code you provided is: Which one of the statements fails. And the second question is: Using debugger what does the whole SQL statement look like when executed? In other words what is the result of the string concatenation?

When you post the SQL statement used, we have something concrete to investigate. Unfortunately, without seeing the actual statement we can only guess what has been concatenated into the SQL string.

So as explained earlier, use the debugger to investigate the running code and the variable values and check what really gets executed when Fill is called.

Posted 11-Apr-18 9:20am

Wendelius

Comments

Prateek gsharma 12-Apr-18 6:25am

i will try sir

Prateek gsharma 12-Apr-18 14:41pm

Still i am getting same error while debugging

private void textBox1_TextChanged(object sender, EventArgs e)
{
//Format(dateTimePicker1.Value, "mm/dd/yyyy");
if(comboBox1.Text == "ID")
{
OleDbDataAdapter da = new OleDbDataAdapter("select * from Spldetails where ID like '% @v1 %' and DDate between @d1 and @d2", con);
cmd.Parameters.Add("@v1", OleDbType.VarChar, 30).Value = this.textBox1.Text;
cmd.Parameters.Add("@d1", OleDbType.Date).Value = this.dateTimePicker1.Text;
cmd.Parameters.Add("@d2", OleDbType.Date).Value = this.dateTimePicker2.Text;
OleDbCommandBuilder build = new OleDbCommandBuilder(da);
DataTable dt = new DataTable();
da.Fill(dt);
dataGridView1.DataSource = dt;
//DataSet ds = new DataSet();
//da.Fill(ds, "Spldetails");
//dataGridView1.DataSource = ds.Tables["Spldetails"];
}
if (comboBox1.Text == "Empid")
{
OleDbDataAdapter da = new OleDbDataAdapter("select * from Spldetails where EmpId like '%@v1%' and DDate between @d1 and @d2", con);
cmd.Parameters.Add("@v1", OleDbType.VarChar, 30).Value = this.textBox1.Text;
cmd.Parameters.Add("@d1", OleDbType.Date).Value = this.dateTimePicker1.Text;
cmd.Parameters.Add("@d2", OleDbType.Date).Value = this.dateTimePicker2.Text;
OleDbCommandBuilder build = new OleDbCommandBuilder(da);
DataTable dt = new DataTable();
//DataSet ds = new DataSet();
da.Fill(dt);
dataGridView1.DataSource = dt;
//da.Fill(ds, "Spldetails");
//dataGridView1.DataSource = ds.Tables["Spldetails"];
}
}

Wendelius 12-Apr-18 23:02pm

What error do you get?
On which line the error occurs?

Prateek gsharma 13-Apr-18 13:53pm

Error Message : System.Data.OleDb.OleDbException: 'No value given for one or more required parameters

Wendelius 16-Apr-18 23:09pm

Based on the error message, you haven't provided values for all of the parameters. It seems that you use a different command object than the data adapters command. Instead of

cmd.Parameters.Add("@v1", OleDbType.VarChar, 30).Value = this.textBox1.Text;

try using

da.SelectCommand.Parameters.Add("@v1", OleDbType.VarChar, 30).Value = this.textBox1.Text;

Also do not concatenate % characters withe the parameter. Set them inside the parameter. In other words

OleDbDataAdapter da = new OleDbDataAdapter("select * from Spldetails where EmpId like '@v1' and DDate between @d1 and @d2", con);
da.SelectCommand.Parameters.Add("@v1", OleDbType.VarChar, 30).Value = "%" + this.textBox1.Text + "%";

Prateek gsharma 19-Apr-18 13:06pm

Now i am getting like this

System.Data.OleDb.OleDbException: 'Data type mismatch in criteria expression.'

Wendelius 19-Apr-18 13:15pm

Check that the data types of the parameters correspond the values you put into them. Also check that the data you use in the parameters matches with the type the corresponding column is defined in the database.

Prateek gsharma 20-Apr-18 1:34am

ok

Prateek gsharma 16-Apr-18 0:15am

No one able to solve my Issue?