I was recently volunteered, by my boss, to finish a front end for a SQL Server database. The database has many Stored Procedures and many views, which are no problem.

I have run into a problem though that is killing me, I can't figure it out and I am at the point where I want to quit my job because of this.

I have an asp page with a form on it. Inside of this form lye two list boxes( a lot more, but it is not relevant ). One list box contains items from a table view that gives the user selections, the other list box is used to store those collections. I have the code working with JavaScript to add and remove the items from the list box. OK, now the tricky stuff.

I then have a JS function that on the submition of the form takes the all of the items that are in that list box and converts them to a string. OK, these pass as a comma delimited string. That is working. Now though I need to divide the string into separate values that the Stored Procedure needs. I have done that. The Stored Procedure, made by an outsourcer, requires the use of ten values. The default for a value if it does not exist needs to be 0. Now that I have the string passing and separating, how do I make the values of the variables 1 - 10 if they do not exist in the string. I have heard many different ways, but I am really at my wits end and this is driving me crazy. I know asp well enough, but not damn near this good. Here is my code below:

<%
'Retrieve the values from the list box
Dim strItems
strItems = Trim(Request.Form("right")
'Split the list box values
Dim arrOfItems
arrOfItems = Split(strItems, ","
arrOfItems.Count
'Create the variables for the Stored Procedure
Dim var1
Dim var2
Dim var3
Dim var4
Dim var5
Dim var6
Dim var7
Dim var8
Dim var9
Dim var10
var1 = arrOfItems(0)
var2 = arrOfItems(1)
var3 = arrOfItems(2)
var4 = arrOfItems(3)
var5 = arrOfItems(4)
var6 = arrOfItems(5)
var7 = arrOfItems(6)
var8 = arrOfItems(7)
var9 = arrOfItems(8)
var10 = arrOfItems(9)


set cdmStored = Server.CreateObject("ADODB.Command"
@MedicineTypeCode(01) = 0
@MedicineTypeCode(02) = 0
@MedicineTypeCode(03) = 0
@MedicineTypeCode(04) = 0
@MedicineTypeCode(05) = 0
@MedicineTypeCode(06) = 0
@MedicineTypeCode(07) = 0
@MedicineTypeCode(08) = 0
@MedicineTypeCode(09) = 0
@MedicineTypeCode(10) = 0
cdmStored.ActiveConnection = MM_connPSR_STRING
cdmStored.CommandText = "dbo.spSav_FallEvent_UserMedications"
cdmStored.CommandType = 4
cdmStored.CommandTimeout = 0
cdmStored.Prepared = true
cdmStored.Parameters.Append cdmStored.CreateParameter("@RETURN_VALUE", 3, 4)
cdmStored.Parameters.Append cdmStored.CreateParameter("@UEID", 3, 1,4,UEID)
cdmStored.Parameters.Append cdmStored.CreateParameter("@MedicinetypeCode01", 3, 1,4,var1)
cdmStored.Parameters.Append cdmStored.CreateParameter("@MedicinetypeCode02", 3, 1,4,var2)
cdmStored.Parameters.Append cdmStored.CreateParameter("@MedicinetypeCode03", 3, 1,4,var3)
cdmStored.Parameters.Append cdmStored.CreateParameter("@MedicinetypeCode04", 3, 1,4,var4)
cdmStored.Parameters.Append cdmStored.CreateParameter("@MedicinetypeCode05", 3, 1,4,var5)
cdmStored.Parameters.Append cdmStored.CreateParameter("@MedicinetypeCode06", 3, 1,4,var6)
cdmStored.Parameters.Append cdmStored.CreateParameter("@MedicinetypeCode07", 3, 1,4,var7)
cdmStored.Parameters.Append cdmStored.CreateParameter("@MedicinetypeCode08", 3, 1,4,var8)
cdmStored.Parameters.Append cdmStored.CreateParameter("@MedicinetypeCode09", 3, 1,4,var9)
cdmStored.Parameters.Append cdmStored.CreateParameter("@MedicinetypeCode10", 3, 1,4,var10)
cdmStored.Execute()
%>

try this
<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
&lt;%
'Retrieve the values from the list box
Dim strItems
strItems = Trim(Request.Form("right")
'Split the list box values
Dim arrOfItems
arrOfItems = Split(strItems, ","
arrOfItems.Count
'Create the variables for the Stored Procedure
Dim var1
Dim var2
Dim var3
Dim var4
Dim var5
Dim var6
Dim var7
Dim var8
Dim var9
Dim var10
var1 = parse( arrOfItems(0) )
var2 = parse( arrOfItems(1) )
var3 = parse( arrOfItems(2) )
var4 = parse( arrOfItems(3) )
var5 = parse( arrOfItems(4) )
var6 = parse( arrOfItems(5) )
var7 = parse( arrOfItems(6) )
var8 = parse( arrOfItems(7) )
var9 = parse( arrOfItems(8) )
var10 = parse( arrOfItems(9) )


set cdmStored = Server.CreateObject("ADODB.Command"

cdmStored.ActiveConnection = MM_connPSR_STRING
cdmStored.CommandText = "dbo.spSav_FallEvent_UserMedications"
cdmStored.CommandType = 4
cdmStored.CommandTimeout = 0
cdmStored.Prepared = true
cdmStored.Parameters.Append cdmStored.CreateParameter("@RETURN_VALUE", 3, 4)
cdmStored.Parameters.Append cdmStored.CreateParameter("@UEID", 3, 1,4,UEID)
cdmStored.Parameters.Append cdmStored.CreateParameter("@MedicinetypeCode01", 3, 1,4,var1)
cdmStored.Parameters.Append cdmStored.CreateParameter("@MedicinetypeCode02", 3, 1,4,var2)
cdmStored.Parameters.Append cdmStored.CreateParameter("@MedicinetypeCode03", 3, 1,4,var3)
cdmStored.Parameters.Append cdmStored.CreateParameter("@MedicinetypeCode04", 3, 1,4,var4)
cdmStored.Parameters.Append cdmStored.CreateParameter("@MedicinetypeCode05", 3, 1,4,var5)
cdmStored.Parameters.Append cdmStored.CreateParameter("@MedicinetypeCode06", 3, 1,4,var6)
cdmStored.Parameters.Append cdmStored.CreateParameter("@MedicinetypeCode07", 3, 1,4,var7)
cdmStored.Parameters.Append cdmStored.CreateParameter("@MedicinetypeCode08", 3, 1,4,var8)
cdmStored.Parameters.Append cdmStored.CreateParameter("@MedicinetypeCode09", 3, 1,4,var9)
cdmStored.Parameters.Append cdmStored.CreateParameter("@MedicinetypeCode10", 3, 1,4,var10)
cdmStored.Execute()

<b>function parse(val)
if trim(val) = "" then
parse = 0
else
parse = val
end if
end function</b>
%&gt;<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>Notice the new function... if the value is empty, it will return a zero... I think that should work, unless the array isnt actually 10 long, this might give you an index out of bounds error... in that case, I would make sure on the javascript side that you <b>will</b> have 10 spaces available.

Hope that helps,

Joel Martinez [ ]
----------
E-Commerce Concepts with Ultradev...pre-order yours at
www.basic-ultradev.com/ecomm_concepts/

I haven't checked this out but I think you could make things easier for yourself.

The string produced by the list menu will be something is like this: 2,4,7,8,9,10

So why not:

strSearch = Request("ListMenuName"

Then:

cdmStored.Parameters.Append cdmStored.CreateParameter("@strSearch", 200, 1,200,strSearch)


Then set up your stored procedure like this:


CREATE PROCEDURE spMedicineView
@strSearch varchar(200)
AS
If @strSearch = -1
SELECT * FROM YourMedicinesTable
Else
DECLARE @strSQL varchar(500)
SELECT @strSQL = 'SELECT * FROM YourMedicinesTable WHERE (MedicineID IN ('+@strSearch+'))'
execute( @strSQL )
GO

This would be a far better method, because the stored procedure will cope with any number of selections from the list menu. In addition if you make one of the options VIEW ALL and set its value to -1 the procedure will return all records.

Regards

Owen.

Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo

Edited by - oeastwick on 07 Dec 2001 22:35:46