Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 828
  • Last Modified:

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__cmtid
cmd_SpUpdateComments__cmtid = -1
if(Request("commentsID") <> "") then cmd_SpUpdateComments__cmtid = Request("commentsID")

Dim cmd_SpUpdateComments__statusrevw
cmd_SpUpdateComments__statusrevw = ""
if(Request("revw_status") <> "") then cmd_SpUpdateComments__statusrevw = Request("revw_status")

Dim cmd_SpUpdateComments__statusabr
cmd_SpUpdateComments__statusabr = ""
if(Request("abr_status") <> "") then cmd_SpUpdateComments__statusabr = Request("abr_status")

Dim cmd_SpUpdateComments__comments
cmd_SpUpdateComments__comments = ""
if(Request("comments") <> "") then cmd_SpUpdateComments__comments = Request("comments")

set cmd_SpUpdateComments = Server.CreateObject("ADODB.Command")
cmd_SpUpdateComments.ActiveConnection = MM_DBConn_STRING
cmd_SpUpdateComments.CommandText = "dbo.ABR_REVWR_CMTS_UPDATE"
cmd_SpUpdateComments.CommandType = 4
cmd_SpUpdateComments.CommandTimeout = 0
cmd_SpUpdateComments.Prepared = true
cmd_SpUpdateComments.Parameters.Append cmd_SpUpdateComments.CreateParameter("@RETURN_VALUE", 3, 4)
cmd_SpUpdateComments.Parameters.Append cmd_SpUpdateComments.CreateParameter("@cmtid", 3, 1,4,cmd_SpUpdateComments__cmtid)
cmd_SpUpdateComments.Parameters.Append cmd_SpUpdateComments.CreateParameter("@statusrevw", 200, 1,50,cmd_SpUpdateComments__statusrevw)
cmd_SpUpdateComments.Parameters.Append cmd_SpUpdateComments.CreateParameter("@statusabr", 200, 1,50,cmd_SpUpdateComments__statusabr)
cmd_SpUpdateComments.Parameters.Append cmd_SpUpdateComments.CreateParameter("@comments", 200, 1,8000,cmd_SpUpdateComments__comments)
cmd_SpUpdateComments.Execute()


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("commentsID"),", ")
For a = 0 to UBound(IDLoop)
z = IDLoop(a)


Any help is appreciated.  Thanks in advance.
-D-
0
-Dman100-
Asked:
-Dman100-
  • 3
  • 2
1 Solution
 
-Dman100-Author Commented:
I tried the following:

Dim CmtIdLoop
CmtIdLoop = Split(Request.Form("commentsID"),", ")
revwStatLoop = Split(Request.Form("review_status"),", ")
abrstatLoop = Split(Request.Form("abr_status"),", ")
commentsLoop = Split(Request.Form("comments"),", ")
For l = 0 to Ubound(CmtIdLoop)
s = CmtIdLoop(l)
t = revwStatLoop
u = abrstatLoop
v = commentsLoop

Dim cmd_SpUpdateComments__cmtid
cmd_SpUpdateComments__cmtid = -1
if(Request("commentsID") <> "") then cmd_SpUpdateComments__cmtid = s

Dim cmd_SpUpdateComments__statusrevw
cmd_SpUpdateComments__statusrevw = ""
if(Request("revw_status") <> "") then cmd_SpUpdateComments__statusrevw = t

Dim cmd_SpUpdateComments__statusabr
cmd_SpUpdateComments__statusabr = ""
if(Request("abr_status") <> "") then cmd_SpUpdateComments__statusabr = u

Dim cmd_SpUpdateComments__comments
cmd_SpUpdateComments__comments = ""
if(Request("comments") <> "") then cmd_SpUpdateComments__comments = v

set cmd_SpUpdateComments = Server.CreateObject("ADODB.Command")
cmd_SpUpdateComments.ActiveConnection = MM_DBConn_STRING
cmd_SpUpdateComments.CommandText = "dbo.ABR_REVWR_CMTS_UPDATE"
cmd_SpUpdateComments.CommandType = 4
cmd_SpUpdateComments.CommandTimeout = 0
cmd_SpUpdateComments.Prepared = true
cmd_SpUpdateComments.Parameters.Append cmd_SpUpdateComments.CreateParameter("@RETURN_VALUE", 3, 4)
cmd_SpUpdateComments.Parameters.Append cmd_SpUpdateComments.CreateParameter("@cmtid", 3, 1,4,cmd_SpUpdateComments__cmtid)
cmd_SpUpdateComments.Parameters.Append cmd_SpUpdateComments.CreateParameter("@statusrevw", 200, 1,50,cmd_SpUpdateComments__statusrevw)
cmd_SpUpdateComments.Parameters.Append cmd_SpUpdateComments.CreateParameter("@statusabr", 200, 1,50,cmd_SpUpdateComments__statusabr)
cmd_SpUpdateComments.Parameters.Append cmd_SpUpdateComments.CreateParameter("@comments", 200, 1,8000,cmd_SpUpdateComments__comments)
cmd_SpUpdateComments.Execute()

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.Parameters.Append cmd_SpUpdateComments.CreateParameter("@statusabr", 200, 1,50,cmd_SpUpdateComments__statusabr)

Not sure what is causing the error?

Any help is appreciated.  Thanks.
-D-
0
 
-Dman100-Author Commented:
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("commentsID"),", ")
revwStatLoop = Split(Request.Form("review_status"),", ")
abrstatLoop = Split(Request.Form("abr_status"),", ")
commentsLoop = Split(Request.Form("comments"),", ")
For l = 0 to Ubound(CmtIdLoop)
s = (CmtIdLoop(l))
t = (revwStatLoop(l))
u = (abrstatLoop(l))
v = (commentsLoop(l))

Dim cmd_SpUpdateComments__cmtid
cmd_SpUpdateComments__cmtid = s

Dim cmd_SpUpdateComments__statusrevw
cmd_SpUpdateComments__statusrevw = t

Dim cmd_SpUpdateComments__statusabr
cmd_SpUpdateComments__statusabr = u

Dim cmd_SpUpdateComments__comments
cmd_SpUpdateComments__comments = v

set cmd_SpUpdateComments = Server.CreateObject("ADODB.Command")
cmd_SpUpdateComments.ActiveConnection = MM_DBConn_STRING
cmd_SpUpdateComments.CommandText = "dbo.ABR_REVWR_CMTS_UPDATE"
cmd_SpUpdateComments.CommandType = 4
cmd_SpUpdateComments.CommandTimeout = 0
cmd_SpUpdateComments.Prepared = true
cmd_SpUpdateComments.Parameters.Append cmd_SpUpdateComments.CreateParameter("@RETURN_VALUE", 3, 4)
cmd_SpUpdateComments.Parameters.Append cmd_SpUpdateComments.CreateParameter("@cmtid", 3, 1,4,cmd_SpUpdateComments__cmtid)
cmd_SpUpdateComments.Parameters.Append cmd_SpUpdateComments.CreateParameter("@statusrevw", 200, 1,50,cmd_SpUpdateComments__statusrevw)
cmd_SpUpdateComments.Parameters.Append cmd_SpUpdateComments.CreateParameter("@statusabr", 200, 1,50,cmd_SpUpdateComments__statusabr)
cmd_SpUpdateComments.Parameters.Append cmd_SpUpdateComments.CreateParameter("@comments", 200, 1,8000,cmd_SpUpdateComments__comments)
cmd_SpUpdateComments.Execute()

Next

Thanks,
-D-
0
 
rdivilbissCommented:
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
0
 
-Dman100-Author Commented:
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-
0
 
rdivilbissCommented:
No problem.  Sometimes it just helps to say the problem out loud.
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now