OleDb Parameters BUG or BAD DESIGN

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.

[Miha Markic]

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?

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

<di********@dis cussion.microso ft.com> wrote in message
news:u8******** ******@TK2MSFTN GP09.phx.gbl...

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.

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.
"Miha Markic" <miha at rthand com> wrote in message
news:%2******** ********@TK2MSF TNGP12.phx.gbl. ..

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?

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

<di********@dis cussion.microso ft.com> wrote in message
news:u8******** ******@TK2MSFTN GP09.phx.gbl...

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.

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


"Miha Markic" <miha at rthand com> wrote in message
news:%2******** ********@TK2MSF TNGP12.phx.gbl. ..

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?

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

<di********@dis cussion.microso ft.com> wrote in message
news:u8******** ******@TK2MSFTN GP09.phx.gbl...

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.

[Ron Allen]

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.2003 FEB.1033/cpref/html/frlrfsystemdata ole
dboledbparamete rclasstopic.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
<di********@dis cussion.microso ft.com> wrote in message
news:eB******** ******@TK2MSFTN GP11.phx.gbl...

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


"Miha Markic" <miha at rthand com> wrote in message
news:%2******** ********@TK2MSF TNGP12.phx.gbl. ..

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?

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

<di********@dis cussion.microso ft.com> wrote in message
news:u8******** ******@TK2MSFTN GP09.phx.gbl...

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.

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?


"Ron Allen" <rallen@_nospam _src-us.com> wrote in message
news:uI******** *****@TK2MSFTNG P12.phx.gbl...

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.2003 FEB.1033/cpref/html/frlrfsystemdata ole dboledbparamete rclasstopic.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
<di********@dis cussion.microso ft.com> wrote in message
news:eB******** ******@TK2MSFTN GP11.phx.gbl...

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


"Miha Markic" <miha at rthand com> wrote in message
news:%2******** ********@TK2MSF TNGP12.phx.gbl. ..

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?

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

<di********@dis cussion.microso ft.com> wrote in message
news:u8******** ******@TK2MSFTN GP09.phx.gbl...
> 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.
>
>
>

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.

"Ron Allen" <rallen@_nospam _src-us.com> wrote in message
news:uI******** *****@TK2MSFTNG P12.phx.gbl...

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.2003 FEB.1033/cpref/html/frlrfsystemdata ole dboledbparamete rclasstopic.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
<di********@dis cussion.microso ft.com> wrote in message
news:eB******** ******@TK2MSFTN GP11.phx.gbl...

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


"Miha Markic" <miha at rthand com> wrote in message
news:%2******** ********@TK2MSF TNGP12.phx.gbl. ..

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?

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

<di********@dis cussion.microso ft.com> wrote in message
news:u8******** ******@TK2MSFTN GP09.phx.gbl...
> 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.
>
>
>

[Miha Markic]

Hey discussion man,

SQL *named* parameter markers are *not* supported via OleDb managed
provider.
Read this:
http://support.microsoft.com/default...b;en-us;316744

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

<di********@dis cussion.microso ft.com> wrote in message
news:uo******** ******@TK2MSFTN GP09.phx.gbl...

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?


"Ron Allen" <rallen@_nospam _src-us.com> wrote in message
news:uI******** *****@TK2MSFTNG P12.phx.gbl...

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.2003 FEB.1033/cpref/html/frlrfsystemdata ole

dboledbparamete rclasstopic.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
<di********@dis cussion.microso ft.com> wrote in message
news:eB******** ******@TK2MSFTN GP11.phx.gbl...

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


"Miha Markic" <miha at rthand com> wrote in message
news:%2******** ********@TK2MSF TNGP12.phx.gbl. ..
> 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?
>
> --
> Miha Markic - RightHand .NET consulting & software development
> miha at rthand com
> www.rthand.com
>
> <di********@dis cussion.microso ft.com> wrote in message
> news:u8******** ******@TK2MSFTN GP09.phx.gbl...
> > 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.
> >
> >
> >
>
>

Ok, make it a FEATURE REQEST then :D

The collection can be accessed by parameter name so why cant we have named
parameters?


"Miha Markic" <miha at rthand com> wrote in message
news:uY******** ******@TK2MSFTN GP09.phx.gbl...

Hey discussion man,

SQL *named* parameter markers are *not* supported via OleDb managed
provider.
Read this:
http://support.microsoft.com/default...b;en-us;316744

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

<di********@dis cussion.microso ft.com> wrote in message
news:uo******** ******@TK2MSFTN GP09.phx.gbl...

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?


"Ron Allen" <rallen@_nospam _src-us.com> wrote in message
news:uI******** *****@TK2MSFTNG P12.phx.gbl...

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.2003 FEB.1033/cpref/html/frlrfsystemdata ole

dboledbparamete rclasstopic.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
<di********@dis cussion.microso ft.com> wrote in message
news:eB******** ******@TK2MSFTN GP11.phx.gbl...
> 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
>
>
>
>
>
>
>
>
> "Miha Markic" <miha at rthand com> wrote in message
> news:%2******** ********@TK2MSF TNGP12.phx.gbl. ..
> > 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?
> >
> > --
> > Miha Markic - RightHand .NET consulting & software development
> > miha at rthand com
> > www.rthand.com
> >
> > <di********@dis cussion.microso ft.com> wrote in message
> > news:u8******** ******@TK2MSFTN GP09.phx.gbl...
> > > 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.
> > >
> > >
> > >
> >
> >
>
>

[Miha Markic]

Hi,

<di********@dis cussion.microso ft.com> wrote in message
news:uB******** ********@TK2MSF TNGP09.phx.gbl. ..

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.

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.

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com