Help in writing SQL using request.form.item(i)

[Michael Kujawa]

I am using the following to create an SQL statement
using the names and values from request.form.
The loop goes through each item in request.form

The issue comes in having an additional "and" at the end
of the loop and the value of x3 as not all options may be selected
from the form yet the loop goes through the entire request.form list

I have to add addtional code to strip off the last "and"
and was wondering if there is a way to eliminate the additional
code needed for stripping the "and".

Is there a way to determine which is the very last request.form.item(i)
that has a .key value and use that as x3? I would imagine I would not
need the additional code if there is a way to determine this.

Any help is appreciated


for j = 2 to Request.Form.count()
x2= x2 & request.form.item(j)
next
if len(rtrim(x2)) = 0 then
response.write "no options chosen"
else
whereby= ""
x3=Request.Form.count
for i = 2 to Request.Form.count()
if len(trim(request.form(i))) > 0 then
if request.form.key(i) = "MFR" then
whereby = whereby & " (Mfg1='" & request.form.item(i) & "' or mfg2='" &
request.form.item(i) & "' or mfg3='" & request.form.item(i) & "') AND "
elseif request.form.key(i) <> "MFR" then whereby = whereby &
trim(request.form.key(i)) & "='" & Request.Form.item(i) & "' AND "
elseif i=x3 then whereby = whereby & trim(request.form.key(i)) & "='" &
Request.Form.item(i) & "' "
end if
end if
next

' Needed to strip last AND from statement
x4=mid(rtrim(whereby),len(rtrim(whereby))-3,4)
if trim(x4) = "AND" then
whereby = mid(rtrim(whereby),1,len(rtrim(whereby))-3) & "order by rec_no "
& Updown
else
whereby=whereby & "order by rec_no " & Updown
end if
' End additional Code

sql = sql & whereby

 

[Michael Kujawa]

Hi Jon,

That will not work. I would get the same results as
the request.form.count() returns the entire set.

I need to know how to find the last .item with a value
and use that for the x3 value, instead of request.form.count().
Otherwise the additional AND is still there.

Not sure how to do that.

for example, if I have 22 elements in the form and the last
element chosen is 20 then I need to know that for the value
of for i = 2 to request.form.(?)

count gives the entry set and that is where the issue lies.
The form is a series of checkboxes and dropdown lists
and requires only one item to be chosen, but can have up
to 22 selections.

 

[Michael Kujawa]

">

Sorry My Bad... I forgot "No top posting"

Hi Jon,

That will not work. I would get the same results as
the request.form.count() returns the entire set.

I need to know how to find the last .item with a value
and use that for the x3 value, instead of request.form.count().
Otherwise the additional AND is still there.

Not sure how to do that.

for example, if I have 22 elements in the form and the last
element chosen is 20 then I need to know that for the value
of for i = 2 to request.form.(?)

count gives the entry set and that is where the issue lies.
The form is a series of checkboxes and dropdown lists
and requires only one item to be chosen, but can have up
to 22 selections.

 

[Michael Kujawa]

Sorry My Bad... I forgot "No top posting"

Hi Jon,

That will not work. I would get the same results as
the request.form.count() returns the entire set.

I need to know how to find the last .item with a value
and use that for the x3 value, instead of request.form.count().
Otherwise the additional AND is still there.

Not sure how to do that.

for example, if I have 22 elements in the form and the last
element chosen is 20 then I need to know that for the value
of for i = 2 to request.form.(?)

count gives the entry set and that is where the issue lies.
The form is a series of checkboxes and dropdown lists
and requires only one item to be chosen, but can have up
to 22 selections.

Thanks for the help, finally figured it out
I added another loop to get the last number and used that


for j = 2 to Request.Form.count()
if len(trim(request.form(j))) > 0 then
x3=j
end if
next

if len(rtrim(x2)) = 0 then
response.write "no options chosen"
else
whereby= ""
for i = 2 to x3
if len(trim(request.form(i))) > 0 then
if i = x3 then
whereby = whereby & trim(request.form.key(i)) & "='" &
Request.Form.item(i) & "' "
exit for
else
if request.form.key(i) = "MFR" then
whereby = whereby & " (Mfg1='" & request.form.item(i) & "' or mfg2='"
& request.form.item(i) & "' or mfg3='" & request.form.item(i) & "') AND "
elseif request.form.key(i) <> "MFR" then whereby = whereby &
trim(request.form.key(i)) & "='" & Request.Form.item(i) & "' AND "
end if
end if
end if
next