By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,290 Members | 1,384 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,290 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Oct 11 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
if j= Request.Form.count() then
whereby = whereby & ...
else
whereby = whereby & ... & "AND"
end if
"Michael Kujawa" <nof at kujawas dot netwrote in message news:uu**************@TK2MSFTNGP04.phx.gbl...
>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


Oct 11 '06 #2

P: n/a
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.


"Jon Paal" <Jon nospam Paal @ everywhere dot comwrote in message
news:12*************@corp.supernews.com...
if j= Request.Form.count() then
whereby = whereby & ...
else
whereby = whereby & ... & "AND"
end if
"Michael Kujawa" <nof at kujawas dot netwrote in message
news:uu**************@TK2MSFTNGP04.phx.gbl...
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


Oct 11 '06 #3

P: n/a


"Michael Kujawa" <nof at kujawas dot netwrote in message
news:uu**************@TK2MSFTNGP04.phx.gbl...
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
>
">
"Jon Paal" <Jon nospam Paal @ everywhere dot comwrote in message
news:12*************@corp.supernews.com...
if j= Request.Form.count() then
whereby = whereby & ...
else
whereby = whereby & ... & "AND"
end if
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.

Oct 11 '06 #4

P: n/a

"Michael Kujawa" <nof at kujawas dot netwrote in message
news:%2****************@TK2MSFTNGP02.phx.gbl...
>
>
"Michael Kujawa" <nof at kujawas dot netwrote in message
news:uu**************@TK2MSFTNGP04.phx.gbl...
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

">
"Jon Paal" <Jon nospam Paal @ everywhere dot comwrote in message
news:12*************@corp.supernews.com...
if j= Request.Form.count() then
whereby = whereby & ...
else
whereby = whereby & ... & "AND"
end if
>

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
Oct 11 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.