PLEASE HELP!!! Problem with extended SELECT statement and END IF's.

Hello.

I'm trying to search on 3 database fields, but am trying to cater for every
logical occurence of people leaving one or more search fields blank.

This is the only way (very long winded) that I can see of doing this, and it still doesn't work.

Could someone please tell me a) how I can get this script working
b) if there is an easier and shorter way to do this.

Regards Damiano3


sqlString = "SELECT image_id, image_thumbnail, image_name, image_keywords "
sqlString = sqlString & "FROM images WHERE ("

IF searchFor <> "" AND iref <> "" AND itype <> "" THEN
sqlString = sqlString & "image_name LIKE '%" & searchFor & "%' OR "
sqlString = sqlString & "image_keywords LIKE '%" & searchFor & "%' AND "
sqlString = sqlString & "image_id = " & iref & " AND "
sqlString = sqlString & "image_type LIKE '%" & itype & "%' "

ELSEIF searchFor <> "" AND iref <> "" THEN
sqlString = sqlString & "image_name LIKE '%" & searchFor & "%' OR "
sqlString = sqlString & "image_keywords LIKE '%" & searchFor & "%' AND "
sqlString = sqlString & "image_id = " & iref & " "

ELSEIF searchFor <> "" AND itype <> "" THEN
sqlString = sqlString & "image_name LIKE '%" & searchFor & "%' OR "
sqlString = sqlString & "image_keywords LIKE '%" & searchFor & "%' AND "
sqlString = sqlString & "image_type LIKE '%" & itype & "%' "

ELSEIF searchFor <> "" THEN
sqlString = sqlString & "image_name LIKE '%" & searchFor & "%' OR "
sqlString = sqlString & "image_keywords LIKE '%" & searchFor & "%' "

ELSEIF iref <> "" AND itype <> "" THEN
sqlString = sqlString & "image_id = " & iref & " AND "
sqlString = sqlString & "image_type LIKE '%" & itype & "%' "

ELSEIF iref <> "" THEN
sqlString = sqlString & "image_id = " & iref & " "

ELSEIF itype <> "" THEN
sqlString = sqlString & "image_type LIKE '%" & itype & "%' "

ELSE
%>

YOU MUST ENTER SOMETHING

<%

END IF

sqlString = sqlString & ") ORDER BY image_id;"

Example:

cheers for the quick response, checked over your code and updated mine.

Its much better thanks, but only works if at least the searchFor field is full.

What next ????


Here's my updated code.

Thanks again Damiano3



strSql = "SELECT image_id, image_thumbnail, image_name, image_keywords FROM images WHERE "

If Len(searchFor) <> 0 Then
strWhere = "image_name LIKE '%" & searchFor & "%'"
blnFirstParam = True
End If

If Len(iref) <> 0 Then
If blnFirstParam = True Then
strWhere = strWhere & " AND image_id = " & iref & ""
Else
strWhere = "image_id = " & iref & ""
blnFirstParam = True
End If
End If

If Len(itype) <> 0 Then
If blnFirstParam = True Then
strWhere = strWhere & " AND image_type LIKE '%" & itype & "%'"
Else
strWhere = "image_type LIKE '%" & itype & "%'"
blnFirstParam = True
End If
End If

SqlString = strSql & strWhere

Update:

Changed the AND's to OR's. but still get the same problem.

Any more help would be greatly appreciated.

Damiano3