-Dman100-
asked on
pass comma delimited list parameters into stored procedure
I have several parameters that I need to pass into a stored procedure. Each parameter holds a comma delimited list. I think I need to split each parameter and use a loop to cycle through each parameter's value. If anyone can provide some insight on how to accomplish this, it would be helpful.
Here is my code for the stored procedure without splitting any of the parameters:
Dim cmd_SpUpdateComments__cmti d
cmd_SpUpdateComments__cmti d = -1
if(Request("commentsID") <> "") then cmd_SpUpdateComments__cmti d = Request("commentsID")
Dim cmd_SpUpdateComments__stat usrevw
cmd_SpUpdateComments__stat usrevw = ""
if(Request("revw_status") <> "") then cmd_SpUpdateComments__stat usrevw = Request("revw_status")
Dim cmd_SpUpdateComments__stat usabr
cmd_SpUpdateComments__stat usabr = ""
if(Request("abr_status") <> "") then cmd_SpUpdateComments__stat usabr = Request("abr_status")
Dim cmd_SpUpdateComments__comm ents
cmd_SpUpdateComments__comm ents = ""
if(Request("comments") <> "") then cmd_SpUpdateComments__comm ents = Request("comments")
set cmd_SpUpdateComments = Server.CreateObject("ADODB .Command")
cmd_SpUpdateComments.Activ eConnectio n = MM_DBConn_STRING
cmd_SpUpdateComments.Comma ndText = "dbo.ABR_REVWR_CMTS_UPDATE "
cmd_SpUpdateComments.Comma ndType = 4
cmd_SpUpdateComments.Comma ndTimeout = 0
cmd_SpUpdateComments.Prepa red = true
cmd_SpUpdateComments.Param eters.Appe nd cmd_SpUpdateComments.Creat eParameter ("@RETURN_ VALUE", 3, 4)
cmd_SpUpdateComments.Param eters.Appe nd cmd_SpUpdateComments.Creat eParameter ("@cmtid", 3, 1,4,cmd_SpUpdateComments__ cmtid)
cmd_SpUpdateComments.Param eters.Appe nd cmd_SpUpdateComments.Creat eParameter ("@statusr evw", 200, 1,50,cmd_SpUpdateComments_ _statusrev w)
cmd_SpUpdateComments.Param eters.Appe nd cmd_SpUpdateComments.Creat eParameter ("@statusa br", 200, 1,50,cmd_SpUpdateComments_ _statusabr )
cmd_SpUpdateComments.Param eters.Appe nd cmd_SpUpdateComments.Creat eParameter ("@comment s", 200, 1,8000,cmd_SpUpdateComment s__comment s)
cmd_SpUpdateComments.Execu te()
How do I setup the loop, split the parameters, and alter the code for the parameters in the stored procedure?
Dim IDLoop
IDLoop = Split(Request.Form("commen tsID"),", ")
For a = 0 to UBound(IDLoop)
z = IDLoop(a)
Any help is appreciated. Thanks in advance.
-D-
Here is my code for the stored procedure without splitting any of the parameters:
Dim cmd_SpUpdateComments__cmti
cmd_SpUpdateComments__cmti
if(Request("commentsID") <> "") then cmd_SpUpdateComments__cmti
Dim cmd_SpUpdateComments__stat
cmd_SpUpdateComments__stat
if(Request("revw_status") <> "") then cmd_SpUpdateComments__stat
Dim cmd_SpUpdateComments__stat
cmd_SpUpdateComments__stat
if(Request("abr_status") <> "") then cmd_SpUpdateComments__stat
Dim cmd_SpUpdateComments__comm
cmd_SpUpdateComments__comm
if(Request("comments") <> "") then cmd_SpUpdateComments__comm
set cmd_SpUpdateComments = Server.CreateObject("ADODB
cmd_SpUpdateComments.Activ
cmd_SpUpdateComments.Comma
cmd_SpUpdateComments.Comma
cmd_SpUpdateComments.Comma
cmd_SpUpdateComments.Prepa
cmd_SpUpdateComments.Param
cmd_SpUpdateComments.Param
cmd_SpUpdateComments.Param
cmd_SpUpdateComments.Param
cmd_SpUpdateComments.Param
cmd_SpUpdateComments.Execu
How do I setup the loop, split the parameters, and alter the code for the parameters in the stored procedure?
Dim IDLoop
IDLoop = Split(Request.Form("commen
For a = 0 to UBound(IDLoop)
z = IDLoop(a)
Any help is appreciated. Thanks in advance.
-D-
ASKER
Anyone have any suggestions or ideas? I'm lost on this? I'm not getting an error, but nothing is getting written into the database?
Here is the code to execute the procedure:
Dim CmtIdLoop
CmtIdLoop = Split(Request.Form("commen tsID"),", ")
revwStatLoop = Split(Request.Form("review _status"), ", ")
abrstatLoop = Split(Request.Form("abr_st atus"),", ")
commentsLoop = Split(Request.Form("commen ts"),", ")
For l = 0 to Ubound(CmtIdLoop)
s = (CmtIdLoop(l))
t = (revwStatLoop(l))
u = (abrstatLoop(l))
v = (commentsLoop(l))
Dim cmd_SpUpdateComments__cmti d
cmd_SpUpdateComments__cmti d = s
Dim cmd_SpUpdateComments__stat usrevw
cmd_SpUpdateComments__stat usrevw = t
Dim cmd_SpUpdateComments__stat usabr
cmd_SpUpdateComments__stat usabr = u
Dim cmd_SpUpdateComments__comm ents
cmd_SpUpdateComments__comm ents = v
set cmd_SpUpdateComments = Server.CreateObject("ADODB .Command")
cmd_SpUpdateComments.Activ eConnectio n = MM_DBConn_STRING
cmd_SpUpdateComments.Comma ndText = "dbo.ABR_REVWR_CMTS_UPDATE "
cmd_SpUpdateComments.Comma ndType = 4
cmd_SpUpdateComments.Comma ndTimeout = 0
cmd_SpUpdateComments.Prepa red = true
cmd_SpUpdateComments.Param eters.Appe nd cmd_SpUpdateComments.Creat eParameter ("@RETURN_ VALUE", 3, 4)
cmd_SpUpdateComments.Param eters.Appe nd cmd_SpUpdateComments.Creat eParameter ("@cmtid", 3, 1,4,cmd_SpUpdateComments__ cmtid)
cmd_SpUpdateComments.Param eters.Appe nd cmd_SpUpdateComments.Creat eParameter ("@statusr evw", 200, 1,50,cmd_SpUpdateComments_ _statusrev w)
cmd_SpUpdateComments.Param eters.Appe nd cmd_SpUpdateComments.Creat eParameter ("@statusa br", 200, 1,50,cmd_SpUpdateComments_ _statusabr )
cmd_SpUpdateComments.Param eters.Appe nd cmd_SpUpdateComments.Creat eParameter ("@comment s", 200, 1,8000,cmd_SpUpdateComment s__comment s)
cmd_SpUpdateComments.Execu te()
Next
Thanks,
-D-
Here is the code to execute the procedure:
Dim CmtIdLoop
CmtIdLoop = Split(Request.Form("commen
revwStatLoop = Split(Request.Form("review
abrstatLoop = Split(Request.Form("abr_st
commentsLoop = Split(Request.Form("commen
For l = 0 to Ubound(CmtIdLoop)
s = (CmtIdLoop(l))
t = (revwStatLoop(l))
u = (abrstatLoop(l))
v = (commentsLoop(l))
Dim cmd_SpUpdateComments__cmti
cmd_SpUpdateComments__cmti
Dim cmd_SpUpdateComments__stat
cmd_SpUpdateComments__stat
Dim cmd_SpUpdateComments__stat
cmd_SpUpdateComments__stat
Dim cmd_SpUpdateComments__comm
cmd_SpUpdateComments__comm
set cmd_SpUpdateComments = Server.CreateObject("ADODB
cmd_SpUpdateComments.Activ
cmd_SpUpdateComments.Comma
cmd_SpUpdateComments.Comma
cmd_SpUpdateComments.Comma
cmd_SpUpdateComments.Prepa
cmd_SpUpdateComments.Param
cmd_SpUpdateComments.Param
cmd_SpUpdateComments.Param
cmd_SpUpdateComments.Param
cmd_SpUpdateComments.Param
cmd_SpUpdateComments.Execu
Next
Thanks,
-D-
Sorry you've not gotten attention prior.
There is no reason I am aware of that would prevent you from sending a comma delimited list as a parameter.
Do you have the stored procedure's code? (Not the ASP)
I'll try to help,
Rod
There is no reason I am aware of that would prevent you from sending a comma delimited list as a parameter.
Do you have the stored procedure's code? (Not the ASP)
I'll try to help,
Rod
ASKER
Hi Rod,
Thanks for replying to my post. I found the problem and the error was caused by the stored procedure. I had the paramethers in the procedure out of order. I kept debugging the ASP code, looking at the values passed into the procedure and couldn't find anything wrong, I finally hardcoded the values into the SP, which helped me identify the problem. I need to get in the habit of hardcoding values as a first test to spot errors like this on the database side.
Thanks again for replying to my post. You've helped me with several other questions I've posted here, and I really appreciate it.
Regards,
-D-
Thanks for replying to my post. I found the problem and the error was caused by the stored procedure. I had the paramethers in the procedure out of order. I kept debugging the ASP code, looking at the values passed into the procedure and couldn't find anything wrong, I finally hardcoded the values into the SP, which helped me identify the problem. I need to get in the habit of hardcoding values as a first test to spot errors like this on the database side.
Thanks again for replying to my post. You've helped me with several other questions I've posted here, and I really appreciate it.
Regards,
-D-
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Dim CmtIdLoop
CmtIdLoop = Split(Request.Form("commen
revwStatLoop = Split(Request.Form("review
abrstatLoop = Split(Request.Form("abr_st
commentsLoop = Split(Request.Form("commen
For l = 0 to Ubound(CmtIdLoop)
s = CmtIdLoop(l)
t = revwStatLoop
u = abrstatLoop
v = commentsLoop
Dim cmd_SpUpdateComments__cmti
cmd_SpUpdateComments__cmti
if(Request("commentsID") <> "") then cmd_SpUpdateComments__cmti
Dim cmd_SpUpdateComments__stat
cmd_SpUpdateComments__stat
if(Request("revw_status") <> "") then cmd_SpUpdateComments__stat
Dim cmd_SpUpdateComments__stat
cmd_SpUpdateComments__stat
if(Request("abr_status") <> "") then cmd_SpUpdateComments__stat
Dim cmd_SpUpdateComments__comm
cmd_SpUpdateComments__comm
if(Request("comments") <> "") then cmd_SpUpdateComments__comm
set cmd_SpUpdateComments = Server.CreateObject("ADODB
cmd_SpUpdateComments.Activ
cmd_SpUpdateComments.Comma
cmd_SpUpdateComments.Comma
cmd_SpUpdateComments.Comma
cmd_SpUpdateComments.Prepa
cmd_SpUpdateComments.Param
cmd_SpUpdateComments.Param
cmd_SpUpdateComments.Param
cmd_SpUpdateComments.Param
cmd_SpUpdateComments.Param
cmd_SpUpdateComments.Execu
Next
The parameters I passed in are the following:
abr_status = ONHOLD, INPROCESS
commentsID = 97, 101
review_status = WAITING, INREVIEW
comments = This is a test, This is another comments test
I recieve the following error:
"Application uses a value of the wrong type for the current operation"
The error is thrown on the following line:
cmd_SpUpdateComments.Param
Not sure what is causing the error?
Any help is appreciated. Thanks.
-D-