Re: Non initialized Cmd Parameter Collection via ASP vs SQL Server 200



Steven Cheng [MSFT] wrote:
Set oCmd = Server.CreateObject("ADODB.Command")
if isObject(vConn) then
Set oCmd.ActiveConnection = vConn
else
oCmd.ActiveConnection = vConn

Bad idea. You should explicitly create a Connection object rather than
having ADO create one in the background

set oConn = CreateObject("adodb.connection")
oConn.Open vConn
Set oCmd.ActiveConnection = oConn

That leaves you in control over when the connection is closed and released.

end if

oCmd.CommandType = nCommandType
oCmd.CommandText = sCommandText

Set oRetPm = oCmd.Parameters(0)
<snip>
oCmd.Parameters(i + 1).Value = aParameters(i)

You say this works on your server?? I'm amazed.

I'm with Steven. You need to create and append your parameter objects to the
Parameters collection. At this point, there should be no parameter objects
in the Parameters collection and this line should always return an error
(given that aParameters contains elements, that is).

Of course, that means you are not going to be able to be as generic as you
are trying to be here. One of the gains from using parameters rather than
dynamic sql is type safety. In order to create parameter objects, you are
going to need to know the data types of those parameters, based on the data
types assigned to them in the stored procedure, not on the data type of the
data being passed.

Of course, you could cause your code to be very inefficient by taking the
lazy route of refreshing the Parameters collection (causing an extra trip to
the dat