SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Thread: asp search form

  1. #1
    SitePoint Member
    Join Date
    Mar 2005
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    asp search form

    Hi all,

    I have attached my search form which is working fine except for the last sql string which works on its own but not with the other sql statements. Could anyone give me some advice on how i could link it to the other sqlstrings?

    thanx in advance
    Attached Files Attached Files

  2. #2
    SitePoint Zealot csi95's Avatar
    Join Date
    Jan 2005
    Location
    Albany, NY
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm guessing that the query you're having problems with is this one. If not, you can dissregard the rest!

    Code:
    If Request.form("spec") <> "" Then
    sqlstring = " SELECT Personnel.*, specialists.spu_id " & _
    " FROM Personnel, specialists " & _
    " WHERE Personnel.p_id = specialists.p_id " & _
    " AND specialists.spu_id = " & CInt(request.form("spec"))
    End If
    First, my guess it that Request.Form("spec") is empty. Since you never check it before the query, you don't know what's in there. If it's empty, or something other than a number, you're in trouble.

    Second, you should avoid joins like you have them written. This approach is usally more acceptable, and faster:

    Code:
    SELECT p.*, s.spu_id
    FROM Personnel p INNER JOIN specialists s
    ON p.p_id = s.p_id
    WHERE s.spu_id = 8
    It also might be helpful if you posted the error you're getting.
    Join the EasyImage Affiliate Program!
    30% commission on all sales
    Conversion rates as high as 20%
    Dedicated Affiliate Manager to help you succeed!

  3. #3
    SitePoint Member
    Join Date
    Mar 2005
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry, i think i need to explain myself better.

    I have attached the code for both the search form and the results form. So far i can search on all of the fields individually or several together. Except for the 'specialism' drop down menu which works fine on its own but when you search on it with a persones first name, for example, it doesnt work as i need to add the 'sqlstring' to the sqlstring that has already been built up. I hope this makes sense.
    Attached Files Attached Files

  4. #4
    Guru Bullschmidt's Avatar
    Join Date
    Apr 2002
    Location
    USA
    Posts
    524
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Welcome to the board and maybe something like this might work:


    If Request.form("spec") <> "" Then
    sqlstring = " SELECT p.*, s.spu_id " & _
    " FROM Personnel p INNER JOIN specialists s " & _
    " ON p.p_id = s.p_id "
    Else
    sqlstring = " SELECT * FROM Personnel WHERE 1=1"
    End If

    ...

    If Request.form("spec") <> "" Then
    sqlstring = sqlstring & " AND s.spu_id = " & CInt(request.form("spec"))
    End If
    J. Paul Schmidt
    www.Bullschmidt.com - Freelance Web and Database Developer
    www.Bullschmidt.com/DevTip.asp - Classic ASP Design Tips


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •