473,320 Members | 2,029 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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.

Jul 21 '05 #1
14 3369
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********@discussion.microsoft.com> wrote in message
news:u8**************@TK2MSFTNGP09.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.

Jul 21 '05 #2
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****************@TK2MSFTNGP12.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********@discussion.microsoft.com> wrote in message
news:u8**************@TK2MSFTNGP09.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.


Jul 21 '05 #3
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****************@TK2MSFTNGP12.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********@discussion.microsoft.com> wrote in message
news:u8**************@TK2MSFTNGP09.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.


Jul 21 '05 #4
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
<di********@discussion.microsoft.com> wrote in message
news:eB**************@TK2MSFTNGP11.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****************@TK2MSFTNGP12.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********@discussion.microsoft.com> wrote in message
news:u8**************@TK2MSFTNGP09.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.



Jul 21 '05 #5
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*************@TK2MSFTNGP12.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.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
<di********@discussion.microsoft.com> wrote in message
news:eB**************@TK2MSFTNGP11.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****************@TK2MSFTNGP12.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********@discussion.microsoft.com> wrote in message
news:u8**************@TK2MSFTNGP09.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.
>
>
>



Jul 21 '05 #6
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*************@TK2MSFTNGP12.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.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
<di********@discussion.microsoft.com> wrote in message
news:eB**************@TK2MSFTNGP11.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****************@TK2MSFTNGP12.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********@discussion.microsoft.com> wrote in message
news:u8**************@TK2MSFTNGP09.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.
>
>
>



Jul 21 '05 #7
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********@discussion.microsoft.com> wrote in message
news:uo**************@TK2MSFTNGP09.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*************@TK2MSFTNGP12.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.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
<di********@discussion.microsoft.com> wrote in message
news:eB**************@TK2MSFTNGP11.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****************@TK2MSFTNGP12.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********@discussion.microsoft.com> wrote in message
> news:u8**************@TK2MSFTNGP09.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.
> >
> >
> >
>
>



Jul 21 '05 #8
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**************@TK2MSFTNGP09.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********@discussion.microsoft.com> wrote in message
news:uo**************@TK2MSFTNGP09.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*************@TK2MSFTNGP12.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.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
<di********@discussion.microsoft.com> wrote in message
news:eB**************@TK2MSFTNGP11.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****************@TK2MSFTNGP12.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********@discussion.microsoft.com> wrote in message
> > news:u8**************@TK2MSFTNGP09.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.
> > >
> > >
> > >
> >
> >
>
>



Jul 21 '05 #9
Hi,

<di********@discussion.microsoft.com> wrote in message
news:uB****************@TK2MSFTNGP09.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
Jul 21 '05 #10
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
<di********@discussion.microsoft.com> wrote in message
news:uo**************@TK2MSFTNGP09.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*************@TK2MSFTNGP12.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.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
<di********@discussion.microsoft.com> wrote in message
news:eB**************@TK2MSFTNGP11.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****************@TK2MSFTNGP12.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********@discussion.microsoft.com> wrote in message
> news:u8**************@TK2MSFTNGP09.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.
> >
> >
> >
>
>



Jul 21 '05 #11
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" <rallen@_nospam_src-us.com> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...
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
<di********@discussion.microsoft.com> wrote in message
news:uo**************@TK2MSFTNGP09.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*************@TK2MSFTNGP12.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.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
<di********@discussion.microsoft.com> wrote in message
news:eB**************@TK2MSFTNGP11.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****************@TK2MSFTNGP12.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********@discussion.microsoft.com> wrote in message
> > news:u8**************@TK2MSFTNGP09.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.
> > >
> > >
> > >
> >
> >
>
>



Jul 21 '05 #12
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

<di********@discussion.microsoft.com> wrote in message
news:%2******************@TK2MSFTNGP11.phx.gbl...
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" <rallen@_nospam_src-us.com> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...
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
<di********@discussion.microsoft.com> wrote in message
news:uo**************@TK2MSFTNGP09.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*************@TK2MSFTNGP12.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.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
> <di********@discussion.microsoft.com> wrote in message
> news:eB**************@TK2MSFTNGP11.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****************@TK2MSFTNGP12.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********@discussion.microsoft.com> wrote in message
> > > news:u8**************@TK2MSFTNGP09.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.
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>



Jul 21 '05 #13
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" <rallen@_nospam_src-us.com> wrote in message
news:OO**************@TK2MSFTNGP10.phx.gbl...
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

<di********@discussion.microsoft.com> wrote in message
news:%2******************@TK2MSFTNGP11.phx.gbl...
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" <rallen@_nospam_src-us.com> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...
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
<di********@discussion.microsoft.com> wrote in message
news:uo**************@TK2MSFTNGP09.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*************@TK2MSFTNGP12.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.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
> > <di********@discussion.microsoft.com> wrote in message
> > news:eB**************@TK2MSFTNGP11.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****************@TK2MSFTNGP12.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********@discussion.microsoft.com> wrote in message
> > > > news:u8**************@TK2MSFTNGP09.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.
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>



Jul 21 '05 #14

<di********@discussion.microsoft.com> wrote in message
news:eo**************@TK2MSFTNGP09.phx.gbl...
Ok, make it a FEATURE REQEST then :D

The collection can be accessed by parameter name so why cant we have named
parameters?
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.

"Miha Markic" <miha at rthand com> wrote in message
news:uY**************@TK2MSFTNGP09.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********@discussion.microsoft.com> wrote in message
news:uo**************@TK2MSFTNGP09.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*************@TK2MSFTNGP12.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.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
> <di********@discussion.microsoft.com> wrote in message
> news:eB**************@TK2MSFTNGP11.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****************@TK2MSFTNGP12.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********@discussion.microsoft.com> wrote in message
> > > news:u8**************@TK2MSFTNGP09.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.
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>



Jul 21 '05 #15

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

14
by: | last post by:
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...
9
by: Pam Ammond | last post by:
I need the code to update the database when Save is clicked and a text field has changed. This should be very easy since I used Microsoft's wizards for the OleDBAdapter and OleDBConnection, and...
0
by: NicK chlam via DotNetMonster.com | last post by:
this is the error i get System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement. at System.Data.Common.DbDataAdapter.Update(DataRow dataRows, DataTableMapping tableMapping) at...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.