Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Help in writing SQL using request.form.item(i)

Reply
Thread Tools

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

 
 
Michael Kujawa
Guest
Posts: n/a
 
      10-11-2006
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


 
Reply With Quote
 
 
 
 
Michael Kujawa
Guest
Posts: n/a
 
      10-11-2006
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 com> wrote in message
news:(E-Mail Removed)...
> if j= Request.Form.count() then
> whereby = whereby & ...
> else
> whereby = whereby & ... & "AND"
> end if
>
>
> "Michael Kujawa" <nof at kujawas dot net> wrote in message

news:(E-Mail Removed)...
> >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
> >
> >

>
>



 
Reply With Quote
 
 
 
 
Michael Kujawa
Guest
Posts: n/a
 
      10-11-2006


> >
> > "Michael Kujawa" <nof at kujawas dot net> wrote in message

> news:(E-Mail Removed)...


> > > 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 com> wrote in message
> news:(E-Mail Removed)...
> > 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.



 
Reply With Quote
 
Michael Kujawa
Guest
Posts: n/a
 
      10-11-2006

"Michael Kujawa" <nof at kujawas dot net> wrote in message
news:%(E-Mail Removed)...
>
>
> > >
> > > "Michael Kujawa" <nof at kujawas dot net> wrote in message

> > news:(E-Mail Removed)...

>
> > > > 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 com> wrote in message
> > news:(E-Mail Removed)...
> > > 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


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL Reference, SQL Queries, SQL help ecoolone ASP .Net 0 01-03-2008 10:58 AM
SQL: writing more concise paramaterized SQL darrel ASP .Net 13 03-30-2006 03:59 PM
PRIMARY KEY required for WRITING to SQL DATABASE using DATA GRID? =?Utf-8?B?cG11ZA==?= ASP .Net 1 12-28-2004 04:48 PM
How do you connect to SQL server using SQL users with Windows Auth? help =?Utf-8?B?UmV6YQ==?= ASP .Net 2 06-08-2004 08:14 PM
Can't connect to SQL Server, using Windows Authentication users of SQL server? help =?Utf-8?B?UmV6YQ==?= ASP .Net 3 06-07-2004 06:42 PM



Advertisments