Question

Locked

SQL in ASP page, database search

By kmosli ·
Hi to all,

I have an ASP page that has 10 fields (drop-down menus) it is a sort of a form. Depending on what you choose, the information will extracted from the DB. Note that you have the option either choosing all the ten fields or even one, or two , or three.

My page now contains only 4 fields and it is working perfect. My code has about 16 If statements in order to handle all scenarios and possibilities. In other words, some users will fill up one field and some will fill 9 or 10. My question is there any other ways to write the queries so that I do not have to write a lot of If statements. I am talking about more than 800 If statements since there are 10 fields to fill so the number of the scenarios and possibilities are very very very large. Please advise if you have any suggestions. I appreciate your help and assistance.

any further information needed will be provided...
Thanks and regards...
Khaled

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

More Info Needed...

by jgarcia102066 In reply to SQL in ASP page, database ...

If you post your current IF statement with the 4 fields, I might have enough information to give you a more efficient approach.

Collapse -

Thanks a lot...

by kmosli In reply to More Info Needed...

The four fields with the IF statements:


set conn=Server.CreateObject("ADODB.Connection")

conn.Provider="Microsoft.Jet.OLEDB.4.0"

conn.Open "C:/Inetpub/wwwroot/Database/scholarship_db.mdb"

set rs =Server.CreateObject("ADODB.recordset")

rs.Open "SELECT majors FROM Major", conn


student_major=request.form("majors")

%>

<form method="post" action="scholarship_multiple.asp">
Please Select your Major from the list <select name="majors">
<% do until rs.EOF
response.write("<option")
if rs.fields("majors")=student_major then
response.write(" selected")
end if
response.write(">")
response.write(rs.fields("majors"))
rs.MoveNext
loop
rs.Close
set rs=Nothing %>
</select>


<%

set rsclass =Server.CreateObject("ADODB.recordset")

rsclass.Open "SELECT student_class FROM Class", conn

student_classs=request.form("student_class")

%>

<p />
Please Select your Class from the list <select name="student_class">
<% do until rsclass.EOF
response.write("<option")
if rsclass.fields("student_class")=student_classs then
response.write(" selected")
end if
response.write(">")
response.write(rsclass.fields("student_class"))
rsclass.MoveNext
loop
rsclass.Close
set rsclass=Nothing %>
</select>


<%

set rs3 =Server.CreateObject("ADODB.recordset")

rs3.Open "SELECT ethnicity FROM Ethnicity", conn

student_ethnicity=request.form("ethnicity")

%>
<p />
Please select your Ethnicity from the list <select name="ethnicity">
<% do until rs3.EOF
response.write("<option")
if rs3.fields("ethnicity")=student_ethnicity then
response.write(" selected")
end if
response.write(">")
response.write(rs3.fields("ethnicity"))
rs3.MoveNext
loop
rs3.Close
set rs3=Nothing %>
</select>

<%

set rs4 =Server.CreateObject("ADODB.recordset")

rs4.Open "SELECT student_gender FROM Gender", conn

student_gender=request.form("student_gender")

%>
<p />
Please select your Gender from the list <select name="student_gender">
<% do until rs4.EOF
response.write("<option")
if rs4.fields("student_gender")=student_gender then
response.write(" selected")
end if
response.write(">")
response.write(rs4.fields("student_gender"))
rs4.MoveNext
loop
rs4.Close
set rs4=Nothing %>
</select>

<p />
<input type="submit" value="Click for Scholarship Information"> <input type="button" class="black10px" onClick="document.location.href='scholarship_multiple.asp'" value=" Clear " /> <input type="button" class="black10px" onClick="document.location.href='scholarship_all.asp'" value=" Show All Scholarships " />

<p />

<%
if student_major<> "" AND student_ethnicity <>"" AND student_gender<> "" AND student_classs<>"" then
sql="SELECT * FROM scholarships WHERE (Major_lookup2='" & student_major & "' OR Major_lookup2='Any') AND (Ethnicity_lookup='" & student_ethnicity & "' OR Ethnicity_lookup='Any') AND (Gender_lookup='" & student_gender & "' OR Gender_lookup='Any') AND (Class_lookup='" & student_classs & "' OR Class_lookup='Any')"

elseif student_major<>"" AND student_ethnicity="" AND student_gender="" AND student_classs="" then
sql="SELECT * FROM scholarships WHERE Major_lookup2='" & student_major & "' OR Major_lookup2='Any'"

elseif student_major="" AND student_ethnicity<>"" AND student_gender="" AND student_classs="" then
sql="SELECT * FROM scholarships WHERE Ethnicity_lookup='" & student_ethnicity & "' OR Ethnicity_lookup='Any'"

elseif student_major="" AND student_ethnicity="" AND student_gender<>"" AND student_classs="" then
sql="SELECT * FROM scholarships WHERE Gender_lookup='" & student_gender & "' OR Gender_lookup='Any'"

elseif student_major="" AND student_ethnicity="" AND student_gender="" AND student_classs<>"" then
sql="SELECT * FROM scholarships WHERE Class_lookup='" & student_classs & "' OR Class_lookup='Any'"

elseif student_major<>"" AND student_ethnicity<>"" AND student_gender="" AND student_classs="" then
sql="SELECT * FROM scholarships WHERE (Ethnicity_lookup='" & student_ethnicity & "' OR Ethnicity_lookup='Any') AND (Major_lookup2='" & student_major & "' OR Major_lookup2='Any')"

elseif student_major="" AND student_ethnicity="" AND student_gender<>"" AND student_classs<>"" then
sql="SELECT * FROM scholarships WHERE (Class_lookup='" & student_classs & "' OR Class_lookup='Any') AND (Gender_lookup='" & student_gender & "' OR Gender_lookup='Any')"

elseif student_major="" AND student_ethnicity<>"" AND student_gender<>"" AND student_classs="" then
sql="SELECT * FROM scholarships WHERE (Ethnicity_lookup='" & student_ethnicity & "' OR Ethnicity_lookup='Any') AND (Gender_lookup='" & student_gender & "' OR Gender_lookup='Any')"

elseif student_major<>"" AND student_ethnicity="" AND student_gender="" AND student_classs<>"" then
sql="SELECT * FROM scholarships WHERE (Major_lookup2='" & student_major & "' OR Major_lookup2='Any') AND (Class_lookup='" & student_classs & "' OR Class_lookup='Any')"

elseif student_major<>"" AND student_ethnicity="" AND student_gender<>"" AND student_classs="" then
sql="SELECT * FROM scholarships WHERE (Major_lookup2='" & student_major & "' OR Major_lookup2='Any') AND (Gender_lookup='" & student_gender & "' OR Gender_lookup='Any')"

elseif student_major="" AND student_ethnicity<>"" AND student_gender="" AND student_classs<>"" then
sql="SELECT * FROM scholarships WHERE (Ethnicity_lookup='" & student_ethnicity & "' OR Ethnicity_lookup='Any') AND (Class_lookup='" & student_classs & "' OR Class_lookup='Any')"

elseif student_major<>"" AND student_ethnicity<>"" AND student_gender<>"" AND student_classs="" then
sql="SELECT * FROM scholarships WHERE (Major_lookup2='" & student_major & "' OR Major_lookup2='Any') AND (Ethnicity_lookup='" & student_ethnicity & "' OR Ethnicity_lookup='Any') AND (Gender_lookup='" & student_gender & "' OR Gender_lookup='Any')"

elseif student_major<>"" AND student_ethnicity<>"" AND student_gender="" AND student_classs<>"" then
sql="SELECT * FROM scholarships WHERE (Major_lookup2='" & student_major & "' OR Major_lookup2='Any') AND (Ethnicity_lookup='" & student_ethnicity & "' OR Ethnicity_lookup='Any') AND (Class_lookup='" & student_classs & "' OR Class_lookup='Any')"

elseif student_major<>"" AND student_ethnicity="" AND student_gender<>"" AND student_classs<>"" then
sql="SELECT * FROM scholarships WHERE (Major_lookup2='" & student_major & "' OR Major_lookup2='Any') AND (Gender_lookup='" & student_gender & "' OR Gender_lookup='Any') AND (Class_lookup='" & student_classs & "' OR Class_lookup='Any')"

elseif student_major="" AND student_ethnicity<>"" AND student_gender<>"" AND student_classs<>"" then
sql="SELECT * FROM scholarships WHERE (Ethnicity_lookup='" & student_ethnicity & "' OR Ethnicity_lookup='Any') AND (Gender_lookup='" & student_gender & "' OR Gender_lookup='Any') AND (Class_lookup='" & student_classs & "' OR Class_lookup='Any')"

else
sql="SELECT * from scholarships WHERE Major_lookup2='" & student_major & "' AND Ethnicity_lookup='" & student_ethnicity & "' AND Gender_lookup='" & student_gender & "' AND Class_lookup='" & student_classs & "'"

end if

and after this is the printing and displaying part...I appreciate your assistance and your concerns...
Thanks Khaled

Collapse -

Yes more info

by LarryD4 In reply to SQL in ASP page, database ...

I can't see why you would need an if statement, with the info you provided.

Such as if I had 4 dropdowns/lists etc. that allowed you to choose 10 options each, that will be used to query.

My sql would be something like
objRS.Filter = "field1 LIKE" & listbox1 & "field2 LIKE" * listbox2 & "field3 LIKE" & listbox3 & "field4 LIKE" & listbox4

Collapse -

Thanks

by kmosli In reply to Yes more info

Hi,

Thanks. I do not know if I am explaining myself correctly. I have 10 fields (drop down lists) on the web page. The user has the option to select either one field (drop menu list) in order to receive information from the database. OR you can choose 5 fields out of ten and then select in order to get to the database. So that's way I figured out that 4 fields need 16 scenarios, then 10 fields will need 1000 or so scenarios. Thanks again and I appreciate your help.

Collapse -

Want to Search with ASP and SQL?

This might help with the coding.
http://authors.aspalliance.com/glenncook/searchcode.asp

Please post back if you have any more problems or questions.
If this info is useful, please give a thumbs up. Thanks

Back to Web Development Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums