OleDb Parameters BUG or BAD DESIGN

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I was performing SQL UPDATE queries and I notice that they SUCCEED on the
ExecuteNonQuery() call with NO exceptions raised BUT they fail at the
Database. They say they succeed in the code but they fail at the database.

To fix this they Parameters.Add must be called in the ORDER they are in
the SQL STATEMENT.

This is confusing and bad.

How to repro.
Construct a SQL query with the @ParamName .... out of order from the
Parameter.Add("@ParamName", var); calls.



Thanks.
 
Hi,

If you are using OleDb than markers for parameters are probably question
marks (?).
So, adding the parameters in the same order is the only way for OleDb to
recognize them.
Or is your scenario different?
 
I use DatabaseCommand.Parameters.Add("@ParamName", paramVar);

This is how it is documented and how it works today, the problem is the
ORDER oft he Parameter.Add( method calls MUST match the order in the SQL
statement.

This is NOT documented nor the logical design if we are using NAMED
PARAMATERS.

If it is dependant on the order we do NOT need named parameters then. That
is the entire concept of named parameters.

Its a bug or bad design in my book.
 
There are 2 issues here.

1. It programatically succeeds on the ExecuteNonQuery() call when in fact
it does NOT succeed

and

2. Named parameters are not processed correctly if they are not in order of
the sql statement placement
 
I don't know why this call succeeds but the OleDb database interface
doesn't support named parameters. See the remarks section of
OleDbParameter Class (
ms-help://MS.VSCC.2003/MS.MSDNQTR.2003FEB.1033/cpref/html/frlrfsystemdataole
dboledbparameterclasstopic.htm)
on my machine.
You can name them any way you like but they are position dependent and
only require a name to access them in the collection.

Ron Allen
 
You say on one hand it doesnt support named parameters then on the other you
say "only require a name to access them in the collection." so which is it?
Yes or no?
 
Hi,

There is still the issue of it reporting a SUCCESS when it obviously fails
on the UPDATE at the database.

With the above out of order parameters its still returning success (no
exceptions) on the update when infact its NOT updating, zero rows
Click to expand...
affected.

Since when should SQL server (or any other database server) complain when
there are no rows updated?
If you take a look at ExecuteNonQuery() you'll see that it returns the
number of rows affected.
 
There is still the issue of it reporting a SUCCESS when it obviously fails
on the UPDATE at the database.

With the above out of order parameters its still returning success (no
exceptions) on the update when infact its NOT updating, zero rows affected.
 
Ok, make it a FEATURE REQEST then :D

The collection can be accessed by parameter name so why cant we have named
parameters?
 
For access them in the collection I mean in the Parameters collection of the
command i.e.,
myCommand.Parameters["paramName"].Value = xx
to access the parameter for modification. The names don't mean anything to
the positioning of the parameter in the CommandText of the query. These
type of queries are normally written as
Select myField from myTable Where (myField2 = ?) AND (myfield3 = ?)
or
Insert into myTable (myField1, myField2) VALUES (?, ?)
where the parameter positions are indicated by the ? symbols.

Ron Allen
 
Ok, fine but then why does it allow me to use names? I think it confuses
the issue.

I can use these named parameters in the database itself. So this is a SQL
standard with named parameters? IF so, will this be in the longhorn API set
to support named parameters?


Ron Allen said:
For access them in the collection I mean in the Parameters collection of the
command i.e.,
myCommand.Parameters["paramName"].Value = xx
to access the parameter for modification. The names don't mean anything to
the positioning of the parameter in the CommandText of the query. These
type of queries are normally written as
Select myField from myTable Where (myField2 = ?) AND (myfield3 = ?)
or
Insert into myTable (myField1, myField2) VALUES (?, ?)
where the parameter positions are indicated by the ? symbols.

Ron Allen
You say on one hand it doesnt support named parameters then on the other you
say "only require a name to access them in the collection." so which is it?
Yes or no?
Click to expand...
ms-help://MS.VSCC.2003/MS.MSDNQTR.2003FEB.1033/cpref/html/frlrfsystemdataole
in
fact OleDb at
the
Click to expand...
Click to expand...
 
Well, I think that OleDb isn't going to support them ever but I haven't
looked thouroughly at the Whidbey documents yet and I know that Database
access is supposed to be improved. MS seems to be pushing people to go to
MSDE instead of Access for local databases and I'd imagine that this will
intensify when the 'Yukon' version of SQL server is released. FWIW I've
just finished migrating our large in-house billing system to MSDE/SQL server
from Access and found about double the performance and much lower memory
requirements on the client. Doing everything through StoredProcedures is
much easier and the nested select logic is much cleaner.
The names appear to be there just to satisfy the IDBParameter interface
and, of course, to make them easy to access through the Parameters
collection for the programmer. It would certainly make things easier if
names were supported though.

Ron Allen

Ok, fine but then why does it allow me to use names? I think it confuses
the issue.

I can use these named parameters in the database itself. So this is a SQL
standard with named parameters? IF so, will this be in the longhorn API set
to support named parameters?


Ron Allen said:
For access them in the collection I mean in the Parameters collection of the
command i.e.,
myCommand.Parameters["paramName"].Value = xx
to access the parameter for modification. The names don't mean
Click to expand...
anything
to
the positioning of the parameter in the CommandText of the query. These
type of queries are normally written as
Select myField from myTable Where (myField2 = ?) AND (myfield3 = ?)
or
Insert into myTable (myField1, myField2) VALUES (?, ?)
where the parameter positions are indicated by the ? symbols.

Ron Allen
other
you is
it?
Click to expand...
ms-help://MS.VSCC.2003/MS.MSDNQTR.2003FEB.1033/cpref/html/frlrfsystemdataole
order
from
Click to expand...
Click to expand...
 
They can push all they want but until the support officially ends its being
used.

I would love to use sql server and stored procedures but these dudes run
Access so theyre getting simple SQL queries as punishment :D


Ron Allen said:
Well, I think that OleDb isn't going to support them ever but I haven't
looked thouroughly at the Whidbey documents yet and I know that Database
access is supposed to be improved. MS seems to be pushing people to go to
MSDE instead of Access for local databases and I'd imagine that this will
intensify when the 'Yukon' version of SQL server is released. FWIW I've
just finished migrating our large in-house billing system to MSDE/SQL server
from Access and found about double the performance and much lower memory
requirements on the client. Doing everything through StoredProcedures is
much easier and the nested select logic is much cleaner.
The names appear to be there just to satisfy the IDBParameter interface
and, of course, to make them easy to access through the Parameters
collection for the programmer. It would certainly make things easier if
names were supported though.

Ron Allen

Ok, fine but then why does it allow me to use names? I think it confuses
the issue.

I can use these named parameters in the database itself. So this is a SQL
standard with named parameters? IF so, will this be in the longhorn API set
to support named parameters?


Ron Allen said:
For access them in the collection I mean in the Parameters collection
Click to expand...
of
the
command i.e.,
myCommand.Parameters["paramName"].Value = xx
to access the parameter for modification. The names don't mean
Click to expand...
anything
to
the positioning of the parameter in the CommandText of the query. These
type of queries are normally written as
Select myField from myTable Where (myField2 = ?) AND (myfield3 = ?)
or
Insert into myTable (myField1, myField2) VALUES (?, ?)
where the parameter positions are indicated by the ? symbols.

Ron Allen
You say on one hand it doesnt support named parameters then on the other
you
say "only require a name to access them in the collection." so
Click to expand...
Click to expand...
which
is
Click to expand...
ms-help://MS.VSCC.2003/MS.MSDNQTR.2003FEB.1033/cpref/html/frlrfsystemdataole
dependent
and when
in fail
at
Click to expand...
Click to expand...
 
Ok, make it a FEATURE REQEST then :D

The collection can be accessed by parameter name so why cant we have named
parameters?
Click to expand...
Mainly because, if memory serves, OleDb itself doesn't support it, its not
an issue with the .NET implementation but more with the native OleDb system.
To update OleDb natively would require a considerable amount of modification
in a fair number of drivers(and standards), not all of which are in MS's
control.

However, Ideally the .NET implementation should be capable of parsing the
sql and reordering the parameters collection to match the command passed in.
An enterprising developer could probably easily write a replacement that
does just that.
 
Back
Top