We help IT Professionals succeed at work.

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

jcook32
jcook32 asked
on
361 Views
Last Modified: 2010-04-17
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
Comment
Watch Question

Commented:
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).
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

Commented:
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

Author

Commented:
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:)))

Commented:
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

Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Commented:
.. 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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.