Variable number of variables 1

[jon24422531]

Hi

I am currently trying to develop a little web app on our intranet where we are trying to limit use of SQL QA. So a user wants a query writing (say SELECT UserID, UserFullName, JobTitle FROM USERS WHERE JobTitle LIKE '%'+@JobTitleContains+'%' AND UserID LIKE @UserIDLike) This is stored in a table with an ID number and Title.
I then have a StoreProc that pulls all the @XXXX's out and displays them on a web page with an Input box for the variable.
Another StoredProc then reassembles the query with the variables in.

The problem I can't work out is how to pass what could be a differing number of variables back to the StoredProc. Ideally I just want it to look like:
EXEC JSP_QueryExecute 4,@JobTitleContains=Sales@UserIDLike=Jonathan Where 4 is the queryID

So in a nutshell, I am trying to get all my Request.Form data in a single string.

Please try to avoid JavaScript, I am not clever enough.....

Jonathan

http://www.telecom-stuff.com

 

[vicvirk]

This little snippit will take ALL the values sent over by a form and create a string separating each field with an ampersand.

str = ""
for each f in request.form
	execute "str = str & ""&" & f & "=" & request.form(f) & """"
next

'remove the starting ampersand
str = right(str,len(str) - 1)

response.write str

So if you have

<input type="text" name="color" value="blue" />
<input type="text" name="number" value="one" />
<input type="text" name="age" value="32" />

submitting those values to the script will return a string with a value of:

color=blue&number=one&age=32

doesn't matter how many fields there are, it will take each one and build the string...

--------
GOOGLE is a great resource to find answers to questions like "how do i..."

If you don't know exaclty what you want to do or what to search on, try Google Suggest:

http://www.google.com/webhp?complete=1&hl=en

--------
I have recently been semi-converted to ensuring all my code (well most of it) works in both javascript and non-javasc

 

[jon24422531]

Vicverk

That was so elegant, many thanks. Have a little star!



Jonathan

http://www.telecom-stuff.com

 

[jon24422531]

Vicverk

I have another question, how can I exclude one form element from being included in the "str" string value. In your example above you have a text input called 'age', could that be not included in the final string? I ask because one element of my form I wish to use elsewhere.

Thanks

Jonathan

http://www.telecom-stuff.com

 

[jon24422531]

Vicverk

I've worked it out....

str = ""
for each f in request.form
	[COLOR=blue]if f <> ("QueryID") Then[/color]
    execute "str = str & ""&" & f & "=" & request.form(f) & """"
	[COLOR=blue]End IF[/color]
next

Thanks again...

Jonathan

http://www.telecom-stuff.com

 

[vicvirk]

you could also use this little snippit (which is always in my global.asp file)

function isInFilter(a,b)
blnReturn = false ' by default it's not there
ra = split(b,",")
for i = 0 to ubound(ra)
if a = ra(i) then
blnReturn = true ' it found
exit for ' don't check any futher
end if
next
isInFilter = blnReturn
end function

So:

isInFilter("red","blue,red,green") would return true
isInFilter("black","blue,red,green") would return false

In you example you could have called it as follows:

str = ""for each f in request.form    
if not isInFilter(f,"QueryID") then
execute "str = str & ""&" & f & "=" & request.form(f) & """"    
End IF
next

If you want to leave out more items, just separate each by a comma.

--------
GOOGLE is a great resource to find answers to questions like "how do i..."

If you don't know exaclty what you want to do or what to search on, try Google Suggest:

http://www.google.com/webhp?complete=1&hl=en

--------
I have recently been semi-converted to ensuring all my code (well most of it) works in both javascript and non-javasc