Multiples filters: should be wront codes. Which solution?

Mfwamba Tshimanga

Hi!

I would like to filter dates with two values once (dates + days + name).

But I receive this error message: "not sufficient parameters..."

conn.Open();

OleDbCommand cmd = conn.CreateCommand();

cmd.CommandType = CommandType.Text;

cmd.CommandText = "select * from personal where (date between ? and ?) and days = ? and (name = ?)";

var param1 = new OleDbParameter("@StartDate", OleDbType.Date);

var param2 = new OleDbParameter("@EndDate", OleDbType.Date);

var param3 = new OleDbParameter("@name", OleDbType.VarChar);

var param4 = new OleDbParameter("@days", OleDbType.VarChar);

param1.Value = dataStart.Value.Date;

param2.Value = dataFinal.Value.Date;

param3.Value = days.Text;

param4.Value = name.Text;

if (dataStart.Value <= dataFinal.Value)

{

cmd.Parameters.Add(param1);

cmd.Parameters.Add(param2);

}

else

{

cmd.Parameters.Add(param2);

cmd.Parameters.Add(param1);

}

cmd.Parameters.Add(param3);

cmd.ExecuteNonQuery();

DataTable dt = new DataTable();

OleDbDataAdapter da = new OleDbDataAdapter(cmd);

da.Fill(dt);

conn.Close();

dgvRpt2.DataSource = dt;

}

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes11y ago
Accepted answer
The expression you'll need to use to test for nullness is:

ISNULL(?)

However, if you're referring to the third parameter both as it stands and within this expression then you'll need to add it to the parameter collection again.

Remember that OleDb associates the parameters in the collection with the '?'s in the query in exactly the same order.
Mfwamba Tshimanga11y ago
Perfect. Its works.
But tell me Vulpes. The textbox "days" I put a number (for example 1). Then if I need to say "when is null". What can I write as code. But its bugging.
For instance her: param3 param3.IsNullable();
Doesnt works!
Mfwamba Tshimanga11y ago
wait little bit Vulpes. I didnt realize that you do respond. Let me test it please
Mfwamba Tshimanga11y ago
The error message say that:
the parameters are wrong or not enough.
Vulpes11y ago
You haven't added the fourth parameter to the collection:

cmd.Parameters.Add(param4);

Also you need to get the third and fourth parameters in the right order - days should come before name as the query stands:

var param3 = new OleDbParameter("@days", OleDbType.VarChar);
var param4 = new OleDbParameter("@name", OleDbType.VarChar);