asked on

SQL Query for a search on a ASP web page using SQL SERVER

I have a web page that has 6 fields that can be either filled in or left blank. Based on what fields are fields are selected or filled in a query will be built. Is there a way to build the query in ASP so I would not have to account for all the fields in an IF statement?
example:
if fld1 is null or fld2 is null or fld3 is not null.
elseif....
end if
doing it this way is drawn out and requires a lot of coding. if there are six search fields I have to cover all possible combinations.

I need a way to only build the statement based on something is not null.
Please help

jcook32

abymats

I haven't worked on plain ASP, only on ASP.NET with C#. However, I guess I can attempt to answer your question.

In situation like yours, what I do is construct the query into a string first and then execute it. It is the natural way to do it in ASP.NET, and I guess you should be able to do the same in plain ASP as well.

The approach would be like this:
Let's say you want to update the 'employee' table based on the form fields:
string setText = "";
if (address != null) {
setText += " , address='" +address+"'"; // a conditional expression needed to make sure
//there is no comma the first time around
}
if (base_salaray != null) {
setText += ", base_salary="+base_salary;
}
...
string updateText = "UPDATE Employee SET "+setText+"Where ...";
Now, execute updateText.
This way you will have only 6 'if' statements, not 64! (2 to the power of 6).

Sandeepk1999

Dim sSQL

sSQL = "Select FieldX"
If fld1 is Not Null Then
sSQL = sSQL & ", Feild1"
End If
If fld2 is Not Null Then
sSQL = sSQL & ", Field2"
End If
.
.
.
If fld6 is Not Null Then
sSQL = sSQL & ", Field6"
End If

sSQL = sSQL & " from Table1"

If you can post couple of scenarios it would be much easier to figure out..

Sandeep

Kavar

instead .... (this is much easier)
name all of your queryfields starting with query and ending with the SQL value name

ie queryAddress,queryName

then on the post
set controls=document.all
for each control in controls
if left(control.name,5)="query" then
if control.value<>"" then
SqlQuery=SqlQuery & right(control.name,len(control.name)-5) & "='" & control.value & "' "
end if
end if
next

jcook32

ASKER

OK, this is what I do not want to do:

      lsSQL ="select jd.*,tb.City_Long, md.GrpDesc"
lsSQL =lsSQL & " FROM jobdescdocind as jd,lookups_tblCity as tb,Master_Group as md"
lsSQL =lsSQL & " WHERE jd.locID = tb.CityID"
       lsSQL =lsSQL & " AND jd.GroupID = md.GrpID"
       if selLocation = "All" and pubdoctitle = "" and selGroup = "All" then
       'when all the variables are = then not and clause is used and all records are returned
       elseif selLocation <> "All" and pubdoctitle <> "" and selGroup <> "All" then
            lsSQL = lsSQL & " AND jd.LocID=" & selLocation
            lsSQL = lsSQL & " AND jd.GroupID=" & selGroup
            lsSQL = lsSQL & " AND (jd.PublicTitle LIKE '%" & pubdoctitle & "%'"
            lsSQL = lsSQL & " OR jd.DocTitle LIKE'%" & pubdoctitle & "%')"
       elseif selLocation <> "All" and pubdoctitle = "" and selGroup = "All" then
            lsSQL = lsSQL & " AND jd.LocID=" & selLocation
       elseif selLocation = "All" and pubdoctitle <> "" and selGroup = "All" then
            lsSQL = lsSQL & " AND (jd.PublicTitle LIKE '%" & pubdoctitle & "%'"
            lsSQL = lsSQL & " OR jd.DocTitle LIKE'%" & pubdoctitle & "%')"
       elseif selLocation = "All" and pubdoctitle = "" and selGroup <> "All" then
            lsSQL = lsSQL & " AND jd.GroupID=" & selGroup
       elseif selLocation <> "All" and pubdoctitle <> "" and selGroup = "All" then
            lsSQL = lsSQL & " AND jd.LocID=" & selLocation
            lsSQL = lsSQL & " AND (jd.PublicTitle LIKE '%" & pubdoctitle & "%'"
            lsSQL = lsSQL & " OR jd.DocTitle LIKE'%" & pubdoctitle & "%')"
       elseif selLocation <> "All" and pubdoctitle = "" and selGroup <> "All" then
            lsSQL = lsSQL & " AND jd.LocID=" & selLocation
            lsSQL = lsSQL & " AND jd.GroupID=" & selGroup
       elseif selLocation = "All" and pubdoctitle <> "" and selGroup <> "All" then
            lsSQL = lsSQL & " AND jd.GroupID=" & selGroup
            lsSQL = lsSQL & " AND (jd.PublicTitle LIKE '%" & pubdoctitle & "%'"
            lsSQL = lsSQL & " OR jd.DocTitle LIKE'%" & pubdoctitle & "%')"
       end if
       if chkFirmwide = "on" then
       lsSQL = lsSQL & " AND jd.FirmwidePos = 1"
       end if
       lsSQL =lsSQL & " ORDER BY " & strSort & " " & strType
       'response.Write lsSQL
            Set rs = dbConn.execute(lsSQL)
because instead of just having three fields I have 6 or 8 whatever it may be.
The one good this is that I have a contant statement for the WHERE meaning it will be the same everytime so all I have to build are all the AND parts fo the statement.
let me know if the code above helps.
Thanks so much:)))

Kavar

sorry about my earlier reply forgetting the "where" and the " and "

at the beggining you need
SqlQuery=SqlQuery & " where "

the long line should have read
SqlQuery=SqlQuery & right(control.name,len(control.name)-5) & "='" & control.value & "' and "

at the end of the loop you need to remove the last " and "
if right(sqlQuery,5)=" and " then
SqlQuery=Left(SqlQuery,len(SqlQuery)-5)
end if

Finnally adjust for the possibility that they entered nothing
if right(sqlQuery,7)= " where " then
SqlQuery=Left(SQlQuery,len(sqlQuery)-7)
end if

ASKER CERTIFIED SOLUTION

abymats

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

abymats

.. Forgot to emphasize that you replace the if .. elsif structure with independant if..endif structures. Each one will do its job and append whatever is relevant to the query string