Re: Parameters Question

Kerry,
You are correct. I've worked with both in .NET and with C++ in Visual Studio
6.0
Here's a link to prove that.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconusingstoredprocedureswithcommand.asp
One thing to note as well, if you create a Visual 6.0 C++ program to access
OleDB the syntax for calling a stored procedure is the same as the ODBC
format:
{ CALL SampleProc(?, ?) }
But in .NET it's just the stored procedure name. Go figure. :)

Using Parameters with a SqlCommand
When using parameters with a SqlCommand, the names of the parameters added
to the Parameters collection must match the names of the parameter markers
in your stored procedure. The .NET Framework Data Provider for SQL Server
treats parameters in the stored procedure as named parameters and searches
for the matching parameter markers.

The .NET Framework Data Provider for SQL Server does not support the
question mark (?) placeholder for passing parameters to an SQL statement or
a stored procedure. In this case, you must use named parameters, as in the
following example.

SELECT * FROM Customers WHERE CustomerID = @CustomerIDUsing Parameters with
an OleDbCommand or OdbcCommand
When using parameters with an OleDbCommand or OdbcCommand, the order of the
parameters added to the Parameters collection must match the order of the
parameters defined in your stored procedure. The .NET Framework Data
Provider for OLE DB and .NET Framework Data Provider for ODBC treat
parameters in a stored procedure as placeholders and applies parameter
values in order. In addition, return value parameters must be the first
parameters added to the Parameters collection.

The .NET Framework Data Provider for OLE DB and .NET Framework Data Provider
for ODBC do not support named parameters for passing parameters to an SQL
statement or a stored procedure. In this case, you must use the question
mark (?) placeholder, as in the following example.

SELECT * FROM Customers WHERE CustomerID = ?As a result, the order in which
Parameter objects are added to the Parameters collection must directly
correspond to the position of the question mark placeholder for the
parameter.



"Kerry Moorman" <KerryMoorman@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:14E8E7AD-5F58-4FD5-8338-9F226ACE1857@xxxxxxxxxxxxxxxx
> Scott,
>
> But as I said in my post, I have never been able to get named parameters
> to
> work in OLEDB for SQL Server. In my SQL statements I cannot use, for
> example
> "@ID". Instead I must use "?" as a placeholder. And my parameters must be
> in
> the same order as the "?" placeholders. Again, this is not the case for
> Access.
>
> Also as I said in my post, the reason I sometimes use OLEDB for both
> Access
> and SQL Server is so I can upsize the Access database to SQL Server and
> use
> the same ADO.Net code (yes I realize that SQLClient provides better
> performance).
>
> Do you have an example of using named parameters with OLEDB for both
> Access
> and SQL Server? Perhaps you have always used the SQLClient for SQL Server
> databases and you never noticed how the OLEDB stuff works differently with
> SQL Server than with Access?
>
> Kerry
>
>
> "Scott M." wrote:
>
>> But think about this for a second...If named parameters will work in
>> OLEDB
>> for Access (in any sequence) and they will also work in OLEDB for SQL (in
>> the correct sequence only), then why not just used named parameters in
>> the
>> correct sequence all the time and both would be happy. In addition,
>> (realistically speaking) why would you be using OLEDB with SQL rather
>> than
>> the SQLClient namespace?
>>
>>
>> "Kerry Moorman" <KerryMoorman@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:75521369-4646-4914-9468-A93E6AD60678@xxxxxxxxxxxxxxxx
>> > Scott,
>> >
>> > In my experience, the OLEDB provider for Access lets you use named
>> > parameters or the question mark character (and maybe additional
>> > options).
>> >
>> > The OLEDB provider for SQL Server (Provider=SQLOLEDB) will not allow
>> > named
>> > parameters. It will work with question mark placeholders. The
>> > parameters
>> > then
>> > must be in the order of the question marks.
>> >
>> > I would love to see some code that uses OLEDB to work with both Access
>> > and
>> > SQL Server databases where you can use parameter names in any order for
>> > both
>> > Access and SQL Server. Since I have never been able to get this to
>> > work, I
>> > always use question marks for both databases (when using OLEDB) so the
>> > code
>> > will work with just connection string changes between the 2 databases.
>> >
>> > Kerry Moorman
>> >
>> >
>> > "Scott M." wrote:
>> >
>> >> In ADO.NET, you don't use the question mark to indicate a parameter
>> >> value,
>> >> you use @paramaterName as in:
>> >>
>> >> "SELECT * FROM tblUsers WHERE User=@user"
>> >>
>> >> Check out this for more info:
>> >>
>> >> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdataoledboledbparameterclasstopic.asp
>> >>
>> >>
>> >>
>> >>
>> >> "ltt19" <ltt19@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> >> news:1A0A7F63-C0DD-45BF-BB7C-11675BB479CC@xxxxxxxxxxxxxxxx
>> >> > Hi,
>> >> >
>> >> > I'm new to ADO.net, even new to Databases access, and I have a
>> >> > CommandText
>> >> > that is useful to all of my tables, however I can not find the
>> >> > approppiate
>> >> > use of paramnter to do this. Like:
>> >> >
>> >> > "SELECT * FROM ?"
>> >> >
>> >> > I'm using an Acess database and the OLEDB.
>> >> >
>> >> > Another Question, How can I use paramter to Select Fields that can
>> >> > be
>> >> > chosen by the user? Like a "SELECT ? ? FROM Music" but I don't know
>> >> > wich
>> >> > are
>> >> > these "?" and how many will have.
>> >> >
>> >> > Hope that I was clear.
>> >> >
>> >> > Any help would be very useful.
>> >> > Thanks in advance.
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>


.

Relevant Pages

  • Re: Parameters Question
    ... > According to Bill's latest post, you can get it to work in OLEDB for SQL ... >> But as I said in my post, I have never been able to get named parameters ... >> work in OLEDB for SQL Server. ... >>> correct sequence all the time and both would be happy. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Parameters Question
    ... Also as I said in my post, the reason I sometimes use OLEDB for both Access ... and SQL Server is so I can upsize the Access database to SQL Server and use ... Do you have an example of using named parameters with OLEDB for both Access ... It will work with question mark placeholders. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Parameters Question
    ... Named parameters were introduced late in the game--in ADO 2.5 or 2.6 IIRC. ... > But think about this for a second...If named parameters will work in OLEDB ... >> The OLEDB provider for SQL Server will not allow ... It will work with question mark placeholders. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: oledbconnection v sqlconnection
    ... The OleDb provider is a one-size-fits-all data access interface that uses ... No two database engines have the same SQL so you'll have to ... actual low-level data access layer and exposes a common object layer that ... Hitchhiker's Guide to Visual Studio and SQL Server ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Access linked tables to ODBC in C++/ADO application
    ... to stay inside an actual Access OLEDB file. ... the .MDB, but are run against linked tables to the SQL Server ODBC ... I know that OLEDB is just the standard protocol, ...
    (comp.databases.ms-access)
Loading