OleDbCommand Parameters.Add Error

  • Thread starter Thread starter Patrick Jox
  • Start date Start date
P

Patrick Jox

Hello,
can anybody explain me, why I find anywhere in the documentation this
example

Sql = "SELECT tblPerson.Person_GUID FROM tblPerson " +
"WHERE tblPerson.Username = @User AND tblPerson.Password = @Pwd";

cmd = new OleDbCommand(Sql, m_conn);
cmd.Parameters.Add("@User", Username);
cmd.Parameters.Add("@Pwd", Password);
try
{
return (int)cmd.ExecuteScalar();
}
catch
{
return -1;
}

Executing this code snippet I get an error message saying that the variable
@user is not declared. When I modify the select statement as follows
everything works fine!

Sql = "SELECT tblPerson.Person_GUID FROM tblPerson " +
"WHERE tblPerson.Username = ? AND tblPerson.Password = ?";

WHY?????

thanks - Patrick
 
Patrick:

OleDb has some quirky behavior and while AFAIK, named params are supposed to
work, I've seen too many posts indicating the otherwise (where people were
using them correctly) to believe that. This post reinforces it. Was the
error message literal too btw? Lowercase u in user whereas you used
Uppercase and it was consistent?

This is NOT the problem, I only ask b/c if it is, it'd be even quirkier.
 
Hi Wiliam,
thanks for this immediate reply. I just tried around and found out the
following
- This Problem occurs even, if the parameter names are literal and in the
correct case
- To get rid of the problem that using the ? the parameters.add must be in
the correct order I tryed the following overload to connect the parameters
with the columns

Sql = "SELECT tblPerson.Person_GUID FROM tblPerson " +
"WHERE Username = ? AND Password = ?";

cmd = new OleDbCommand(Sql, m_conn);
OleDbParameter p1 = cmd.Parameters.Add("@User", OleDbType.VarChar, 50,
"Username");
p1.Value = Username;
OleDbParameter p2 = cmd.Parameters.Add("@Pwd", OleDbType.VarChar, 50,
"Password");
p2.Value = Password;
try
{
return (int)cmd.ExecuteScalar();
}
catch(Exception e)
{
return -1;
}
this worked. Now I turned the order as follows
OleDbParameter p2 = cmd.Parameters.Add("@Pwd", OleDbType.VarChar, 50,
"Password");
p2.Value = Password;
OleDbParameter p1 = cmd.Parameters.Add("@User", OleDbType.VarChar, 50,
"Username");
p1.Value = Username;
and did not find the correct result. I think, that the mapping between
Parameter and Column did not work.

cu - Patrick
 
Hi Patrick:

Glad to hear you got it working. It is true that if you use the ? then the
order has to be the same b/c there's no identifier to do any
differentiation.

Glad you got it working

Cheers,

Bill
 
Back
Top