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
%>

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.

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:

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.

ohhh! Sorry. I didn't saw that. Jacob.

hi guys. thanks for the reply.

it should be when NOT EQUAL TO ZERO THEN I ADD THE SQL STATEMENT. sorry about that.

!= has to be <>

Jacob.

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.

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.

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.

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