Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old September 10th, 2003, 10:19 PM
Authorized User
 
Join Date: Sep 2003
Location: , , .
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
Default document form options value sql

how do i put an sql statement inside if-else statement?? or is that possible?
my aim is to check if the listbox is not selected(options=0) then I will add sql statement ("AND table.field=value")

my code:
<%
sql = "SELECT * FROM ..WHERE table.field ='"& value & "'"

if (document.formname.listboxname.options[0].value==0)
    sqlsort = "AND table1.field1 ='"& value1 & "'"
end if
%>

Reply With Quote
  #2 (permalink)  
Old September 11th, 2003, 04:05 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Copenhagen, , Denmark.
Posts: 440
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You want to generate an SQL statements, but if some value is 0 you want to append something more!? (you could probably structure it better than me)...

<%
...
sql = "SELECT * FROM table"
if (document.formname.listboxname.options[0].value == 0)
   sql = sql & ", table1 WHERE table.field='" & value & "' AND table1.field1='" & value1 & "'"
else
   sql = sql & " WHERE table.field='" & value & "'"
end if
...
%>

Hope it helps!

Jacob.
Reply With Quote
  #3 (permalink)  
Old September 11th, 2003, 04:14 AM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

The problem with this code is that document.formname.listboxname.options refers to the client, while the ASP will execute at the server. You'll need Request.QueryString (when your form uses GET) or Request.Form (when your form uses POST) to retrieve the data from the form.

The general syntax is like this:

Request.Form(FieldName)

This will return the value of the HTML field FieldName. In your code, something like this will work:

Code:
<%
  If Request.Form("ListBoxName") = "" Then
    ' Do something with your SQL statement
  End If
%>
Request.Form("ListBoxName") will be "" when no selection is made, or when the user has chosen an item with a value of "" (<option value="">Option without a value</option>)

HtH,

Imar




---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Reply With Quote
  #4 (permalink)  
Old September 11th, 2003, 04:24 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Copenhagen, , Denmark.
Posts: 440
Thanks: 0
Thanked 0 Times in 0 Posts
Default

ohhh! Sorry. I didn't saw that. Jacob.
Reply With Quote
  #5 (permalink)  
Old September 11th, 2003, 04:25 AM
Authorized User
 
Join Date: Sep 2003
Location: , , .
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
Default

hi guys. thanks for the reply.

it should be when NOT EQUAL TO ZERO THEN I ADD THE SQL STATEMENT. sorry about that.
Reply With Quote
  #6 (permalink)  
Old September 11th, 2003, 04:28 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Copenhagen, , Denmark.
Posts: 440
Thanks: 0
Thanked 0 Times in 0 Posts
Default

!= has to be <>

Jacob.
Reply With Quote
  #7 (permalink)  
Old September 11th, 2003, 04:33 AM
Authorized User
 
Join Date: Sep 2003
Location: , , .
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
Default

actualy my aim is to 'search' the database. i have 5 listboxes: user, task, project etc. when i choose user X, all of X's data will be shown. if i choose task Y, all records with task Y will show. If they're not chosen, it will show all. Or if i choose X and Y, only records with X and Y will show. .... there are many variations.
Reply With Quote
  #8 (permalink)  
Old September 11th, 2003, 10:18 PM
Authorized User
 
Join Date: Sep 2003
Location: , , .
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
Default

anyway, the <'"&value1&"'> part in <sqlsort = "AND table1.field1 ='"& value1 & "'"> part doesn't work. it shows no data. only when i put it equal to some actual value then it works.
Reply With Quote
  #9 (permalink)  
Old September 12th, 2003, 01:42 AM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

Not sure what you're saying here. It makes sense you need to set the value equal yo some value in the database, otherwise you won't get a record, no?

Can you post a bit more of your code and explain what you're trying to do?


Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Reply With Quote
  #10 (permalink)  
Old September 12th, 2003, 02:37 AM
Authorized User
 
Join Date: Sep 2003
Location: , , .
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
Default

i have 5 dynamic listboxes- user,task,country etc. i also have a submit button.
eg of user: John, sally
eg of tasks: Admin, R&D
eg of country: SG, US

i meant that <sqlsort = "AND table1.field1 ='"& value1 & "'"> doesn't work.
but sqlsort = "AND table1.field1 ='John'"> does.
it not dynamic.

code eg.
showUser= Request.Form("user")
...

//listbox code
<select name="user" >
<option value="1" Selected> Choose User..</option>
...

<option value="<%=userRetrieved%>"> <%=userRetrieved%> </option>
...

<%
//tbl_Team is the table with user,task,project etc..tbl_user just lists all the usernames

sql="SELECT * FROM tbl WHERE tbl_Team.ID = tbl_users.ID"


If showUser="John" Then
    sql = sql & " AND tbl_TeamEffort.User ='John'"
end if


%>

the IF-ELSE part is the part which I do not know how to write.
i want to write SQL/IF-ELSE statements where

IF user listbox's option value <> 1(or if listbox selected)

THEN sql=sql&"AND (tbl_Team.ID = (SELECT ID FROM tbl_users WHERE tbl_users.name ='"& showUser & "')"

END IF

IF task listbox's option value <>1
....
>>>>>>>

This is so that I can have any combination of listboxes. for example i can list [John and R&D]. Then ALL my records with user=john and task=R&D will show.
OR if i want to show [R&D and US], all records with task=r&D and country=US will show.
So, if the listboxes are not chosen, they show all the values.

THANKS in advance
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

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 Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
IE 7 form select options harpua HTML Code Clinic 0 May 9th, 2007 11:59 PM
Document.form can't find Label bekim Javascript How-To 1 February 13th, 2006 07:39 PM
Print Word document from Access Form ppenn Access VBA 2 December 14th, 2005 07:51 AM
display word document in form collie VB.NET 2002/2003 Basics 1 September 12th, 2004 09:53 PM



All times are GMT -4. The time now is 01:57 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.