asp search form

[tidalwave]

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

[csi95]

I'm guessing that the query you're having problems with is this one. If not, you can dissregard the rest!

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:

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.

[tidalwave]

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.

[Bullschmidt]

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