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.
Nov 15 '05
14  1248 
For access them in the collection I mean in the Parameters collection of the
command i.e.,
myCommand.Param eters["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********@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, 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******** ********@TK2MSF TNGP11.phx.gbl. .. For access them in the collection I mean in the Parameters collection of
the command i.e.,
myCommand.Param eters["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********@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.
> > >
> > >
> > >
> >
> >
>
>
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 StoredProcedure s 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********@dis cussion.microso ft.com> wrote in message
news:%2******** **********@TK2M SFTNGP11.phx.gb l... 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******** ********@TK2MSF TNGP11.phx.gbl. .. For access them in the collection I mean in the Parameters collection of the command i.e.,
myCommand.Param eters["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********@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.
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
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******** ******@TK2MSFTN GP10.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 StoredProcedure s 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********@dis cussion.microso ft.com> wrote in message
news:%2******** **********@TK2M SFTNGP11.phx.gb l... 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******** ********@TK2MSF TNGP11.phx.gbl. .. For access them in the collection I mean in the Parameters collection
of the command i.e.,
myCommand.Param eters["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********@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.
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
<di********@dis cussion.microso ft.com> wrote in message
news:eo******** ******@TK2MSFTN GP09.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******** ******@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.
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
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 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.
|
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 DataSet; and all I'm doing is
showing one record in text fields, allowing the user to modify the text
fields, and then updating the database again when the user clicks the Save
button. The fields already show the correct data record since I have...
|
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 System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String
srcTable)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet)
at TryThis.Form1.save() in C:\Documents and Settings\Nick\My Documents\...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
