Executing Stored Procedure in loop duplicates row content

[sean]

Hi,

I am executing a stored procedure from a form post, when I execute the
stored procedure it inserts a row for every checkbox checked, however it is
populating the rows with the same content. It will only give the values from
the first form object and insert the same to content to the value of "i".
How can I make this insert the correct content into my DB?

Thanks in advance for your answer

Sean



Set adoCmd = Server.CreateObject("ADODB.Command")
adoCmd.ActiveConnection = db_conn
adoCmd.CommandType = adCmdStoredProc
adoCmd.CommandText = "sp_InsertOptions"

for i = 1 to Request("chkOptions").count
strOption = Request("chkOptions")(i)
adoCmd.Parameters.Append
adoCmd.CreateParameter("pProductID",adInteger)
adoCmd.Parameters("pProductID") = ProductID
adoCmd.Parameters.Append
adoCmd.CreateParameter("pOptionValue",adVarChar ,adParamInputOutput,255)
adoCmd.Parameters("pOptionValue") = Request("chkOptions")(i)
adoCmd.Execute()
next

[Bob Barrows]

It's time for some debugging. See below:
sean wrote:

> Hi,
>
> I am executing a stored procedure from a form post, when I execute the
> stored procedure it inserts a row for every checkbox checked, however
> it is populating the rows with the same content. It will only give
> the values from the first form object and insert the same to content
> to the value of "i". How can I make this insert the correct content
> into my DB?
>
> Thanks in advance for your answer
>
> Sean
>
>
>
> Set adoCmd = Server.CreateObject("ADODB.Command")
> adoCmd.ActiveConnection = db_conn
> adoCmd.CommandType = adCmdStoredProc
> adoCmd.CommandText = "sp_InsertOptions"

You should avoid using "sp_" to prefix your stored procedure names. "sp_"
should be reserved for system stored procedures. You pay a performance
penalty by using this prefix for you user-defined procedures since SQL will
try to execute it as if it's a system procedure.

>
> for i = 1 to Request("chkOptions").count

This should be:
for i = 1 to Request.Form("chkOptions").count

> strOption = Request("chkOptions")(i)

Response.write "Request.Form(""chkOptions"")(" & i & ")"
Response.write " contains " & strOption & "<BR>"

> adoCmd.Parameters.Append
> adoCmd.CreateParameter("pProductID",adInteger)
> adoCmd.Parameters("pProductID") = ProductID
> adoCmd.Parameters.Append
> adoCmd.CreateParameter("pOptionValue",adVarChar
> ,adParamInputOutput,255)
> adoCmd.Parameters("pOptionValue") =Request.Form("chkOptions")(i)

Why not use strOption?
adoCmd.Parameters("pOptionValue") =strOption

> adoCmd.Execute()
> next

If your debugging indicates that the loop is correctly setting the value of
the strOption variable, then you may need to set the Command object's
Prepared property to True.

HTH,
Bob Barrows

--
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"

[Keith]

Not sure if this will help, but try deleting your existing
parameters after you execute.

>-----Original Message-----
>Hi,
>
>I am executing a stored procedure from a form post, when

I execute the

>stored procedure it inserts a row for every checkbox

checked, however it is

>populating the rows with the same content. It will only

give the values from

>the first form object and insert the same to content to

the value of "i".

>How can I make this insert the correct content into my DB?
>
>Thanks in advance for your answer
>
>Sean
>
>
>
> Set adoCmd = Server.CreateObject("ADODB.Command")
>adoCmd.ActiveConnection = db_conn
>adoCmd.CommandType = adCmdStoredProc
>adoCmd.CommandText = "sp_InsertOptions"
>
>for i = 1 to Request("chkOptions").count
> strOption = Request("chkOptions")(i)
> adoCmd.Parameters.Append
>adoCmd.CreateParameter("pProductID",adInteger)
> adoCmd.Parameters("pProductID") = ProductID
> adoCmd.Parameters.Append
>adoCmd.CreateParameter

("pOptionValue",adVarChar ,adParamInputOutput,255)

> adoCmd.Parameters("pOptionValue") = Request

("chkOptions")(i)

> adoCmd.Execute()

'' verify syntax for this
for each p in adoCmd.Parameters
adoCmd.Parameters(p).delete
next

>next
>
>
>.
>