ADO Command object ingores parameter names when invoking stored procedure

[R Baker]

When I invoke a SQL Server stored procedure using named parameters, it is
clear that the parameter names are being ignored and that only the order of
the parameters is being considerd. Furthermore, I don't get errors about the
parameter names being wrong, only that the types don't match. If I specify
all the parameters (don't omit any optional ones) and specify them the
correct order, it works.

Is there a way to use the parameter names (other than calling Refresh) when
invoking a stored procedure using and ADO Command object?

Thanks,

Randy

[Chris Petchey]

If you read the ADO documentation it does say that the "name" property of
the parameter is only for use within the vb program so that the vb program
can tell the parameter objects within the parameters collection from one
another, and the "name" property has NO relation to the stored procedure
paramters other than by ordinal position in the sequence

R Baker <nos...@nospamx.com> wrote in message
news:eDwlQkacAHA.1752@tkmsftngp03...

[Doo]

If you use the recordset object to create the connection object you must
specify the parameters ordianlly.

If you use the connection object to execute the SP you use the parameters by
name and their order is of no importance.

--
Doo
Senior Data Architect / DBA
PlanetJam Media Group

"R Baker" <nos...@nospamx.com> wrote in message
news:eDwlQkacAHA.1752@tkmsftngp03...

[R Baker]

Can you be more specific? I'm using code I borrowed from the Fitch & Mather
sample:

' Create the ADO objects
Dim cmd As ADODB.Command
Set cmd = CreateObject("ADODB.Command")

' Init the ADO objects & the stored proc parameters
cmd.ActiveConnection = ConnectionString
cmd.CommandText = strSP
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh

collectParams cmd, params 'This builds the parameter collection by name

' Execute without a resulting recordset
cmd.Execute , , ADODB.adExecuteNoRecords'

Thanks,

Randy

"Doo" <D...@PlanetJam.com> wrote in message
news:e3b8GyacAHA.1748@tkmsftngp05...

[R Baker]

OK. So does this mean that one cannot invoke a SQL stored procedure from ADO
using named parameters, short of explicitly building the SQL string and
executing it directly?

"Chris Petchey" <ch...@2manykooks.co.uk> wrote in message
news:OHEg1xacAHA.1924@tkmsftngp05...

[Bob (Max) Lambert]

Not at all, it is possible to use the parameter's name to specify
which parameter you want to set the value of or read the value of.
(I assume this is a VB app, if you're doing it in C++ let me know and
I'll send you a sample of how it's done there).

(assume we've already set up the connection in m_objAdoConnect)

m_objAdoCommand.ActiveConnection = m_objAdoConnect
m_objAdoCommand.CommandTimeout = 2400
m_objAdoCommand.CommandType = adCmdStoredProc
m_objAdoCommand.CommandText = "spTestProc"

' you need this to build the command objects parameter collection
m_objAdoCommand.Parameters.Refresh

' the spTestProc has two input parameters defined in the stored
' proc as @fQty money, and @fDivisor money, and one output
' parameter called @fQuotient money
m_objAdoCommand.Parameters.Item("@fQty") = 100
m_objAdoCommand.Parameters.Item("@fDivisor") = 20
m_objAdoCommand.Execute
msgBox "the result of 100 / 20 = ' & _
m_objAdoCommand.Parameters.Item("@fQuotient")

(the source for the above stored proc)
CREATE PROCEDURE spTestProc
@fQty money,
@fDivisor money,
@fQuotient money output
AS
select @fQuotient = @fQty / @fDivisor
return @@error

On Fri, 29 Dec 2000 10:53:20 -0500, "R Baker" <nos...@nospamx.com>
wrote:

[Carl Prothman]

"R Baker" <nos...@nospamx.com> wrote

> When I invoke a SQL Server stored procedure using named parameters, it is
> clear that the parameter names are being ignored and that only the order
of
> the parameters is being considerd. Furthermore, I don't get errors about
the
> parameter names being wrong, only that the types don't match. If I specify
> all the parameters (don't omit any optional ones) and specify them the
> correct order, it works.
>

Randy
Chris's comment was right on the mark for this one.

> Is there a way to use the parameter names (other than calling Refresh)
when
> invoking a stored procedure using and ADO Command object?
>

Yes, create the parameters by-hand. Note, Refresh is slow because the ADO
has to query the database twice, once to get the meta data for each
parameter, and once to get the data.

Here is an example of using an ADO Command / Parameter to call a Stored
Procedures which performs an insert and returns the correct Identity.
Note, the parameter names are not important (I use the column name just to
make the code readable and to get the Identity value). Also, the order is
imporant. And you also need to include the RETURN_VALUE parameter (always
first) when calling a SQL Server Stored Procedure.

'********
' Create and open a new Connection object
Set oConn = New ADODB.Connection
oConn.Open "Provider=SQLOLEDB;" & _
"Data Source=(local);" & _
"Initial Catalog=NorthWind;" & _
"User ID=sa;" & _
"Password=;"

' Create a new Command object
Set oCmd = New ADODB.Command
With oCmd

' Set Command properties
.ActiveConnection = oConn
.CommandType = adCmdStoredProc
.CommandText = "sp_Employees_Insert"

' Create Command Parameter(s)
.Parameters.Append .CreateParameter("RETURN_VALUE", adInteger,
adParamReturnValue, 4)
.Parameters.Append .CreateParameter("EmployeeID", adInteger,
adParamOutput, 4)
.Parameters.Append .CreateParameter("LastName", adVarWChar,
adParamInput, 20, sLastName)
.Parameters.Append .CreateParameter("FirstName", adVarWChar,
adParamInput, 10, sFirstName)
.Parameters.Append .CreateParameter("Title", adVarWChar,
adParamInput, 30, vTitle)
.Parameters.Append .CreateParameter("TitleOfCourtesy", adVarWChar,
adParamInput, 25, vTitleOfCourtesy)
.Parameters.Append .CreateParameter("BirthDate", adDBTimeStamp,
adParamInput, 8, vBirthDate)
.Parameters.Append .CreateParameter("HireDate", adDBTimeStamp,
adParamInput, 8, vHireDate)
.Parameters.Append .CreateParameter("Address", adVarWChar,
adParamInput, 60, vAddress)
.Parameters.Append .CreateParameter("City", adVarWChar,
adParamInput, 15, vCity)
.Parameters.Append .CreateParameter("Region", adVarWChar,
adParamInput, 15, vRegion)
.Parameters.Append .CreateParameter("PostalCode", adVarWChar,
adParamInput, 10, vPostalCode)
.Parameters.Append .CreateParameter("Country", adVarWChar,
adParamInput, 15, vCountry)
.Parameters.Append .CreateParameter("HomePhone", adVarWChar,
adParamInput, 24, vHomePhone)
.Parameters.Append .CreateParameter("Extension", adVarWChar,
adParamInput, 4, vExtension)
.Parameters.Append .CreateParameter("Photo", adLongVarBinary,
adParamInput, 16, vPhoto)
.Parameters.Append .CreateParameter("Notes", adLongVarWChar,
adParamInput, 16, vNotes)
.Parameters.Append .CreateParameter("ReportsTo", adInteger,
adParamInput, 4, vReportsTo)
.Parameters.Append .CreateParameter("PhotoPath", adVarWChar,
adParamInput, 255, vPhotoPath)

' Run the command (perform the Insert)
.Execute , , adExecuteNoRecords

' Get the new Identity value
lEmployeeID = .Parameters("EmployeeID").Value
End With
'********

To help with the ADO data type mapping, check out:
http://www.able-consulting.com/ADODataTypeEnum.htm

Note, the above code (and the related Stored Procedure) was generated from
eCodeGen
http://www.able-consulting.com/ecodegen/index.htm

--

Thanks,
Carl Prothman
Microsoft Visual Basic MVP
eCodeGen - An on-line Code Generator for VB Developers
http://www.able-consulting.com/ecodegen

[Don Arsenault]

I also didn't like this "feature" when I discovered it.

Actually, the only time it becomes a problem is with optional parameters
(i.e. parameters with a default value). By creating a parameter and
appending it to a command object, a value will always be passed to the
stored procedure for that parameter's ordinal position. If you don't assign
a value, NULL will be passed, and the parameter's default value will not be
used. There's no way (that I know of) to just exclude the parameter because
ADO doesn't use the named parameter syntax (EXEC spx @param=value).

When dealing with optional parameters, we have to revert to generating the
SQL by concatenating the procedure name and parameters.

Don.

"R Baker" <nos...@nospamx.com> wrote in message
news:eDwlQkacAHA.1752@tkmsftngp03...