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 database, wasting time and creating extra network traffic) every time
the code runs. I strongly discourage this practice, but many programmers do
take this lazy route because it entails adding only a single line to their
code:

oCmd.CommandType = nCommandType
oCmd.CommandText = sCommandText
oCmd.Parameters.Refresh
Set oRetPm = oCmd.Parameters(0)
etc.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


.