Need help urgently.... pls???

[madg3rr]

i m doing a search engine whereby i haf to haf 2 dropdown menu. Its the category & the dept menu.

The category menu allows user to choose "all category, cat1, cat2". The dept allows user choose the dept they wanna search within my site...

Then wen the user selects the 2 choices. My system will search fer the whole database & show me all the data according to the criteria they haf selected. I m running out of ideas as to how to do dis. can sumone pls help me??? by givin me examples or even a webbie tad will my life easier... pls???

[Bullschmidt]

Dynamically build the WHERE clause of your SQL statement based on what was in the list boxes.

If Request.Form("Category") <> "All Categories" Then
strSQL = strSQL & "Category = '" & Request.Form("Category") & "' "
End If

[madg3rr]

but den i haf 2 menus. The other menu is department. How do i do the sql? How do i do a 2criteria. Wad symbol do i haf 2 use? & one more how do i display all the results? izit possible if u would gif me an example? thanx...

[Bullschmidt]

strSQL = "SELECT * FROM MyTable WHERE (1 = 1)"
If Request.Form("Category") <> "All" Then
strSQL = strSQL & "And (Category = '" & Request.Form("Category") & "') "
End If
If Request.Form("Department") <> "All" Then
strSQL = strSQL & "And (Department = '" & Request.Form("Department") & "') "
End If

[madg3rr]

wads wrong wif the code? why does it gives me dis error?
[Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause

Dim strSQL

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "CPAR"

strSQL="Select * FROM Search"

If Request.Form("Category") <> "All Categories" Then
strSQL = strSQL & "Category = '" & Request.Form("Category") & "' "
End If

If Request.Form("Division") <> "All Divisions" Then
strSQL = strSQL & "And (Division = '" & Request.Form("Division") & "') "
End If

set rsDisplay = Server.CreateObject("ADODB.Recordset")
set rsSearch=Conn.Execute(strSQL) <-- frm dis line...

[jsawkang]

hi...

u forgot to Put "WHERE" after selecting from the table.

try this:

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "CPAR"

strSQL="Select * FROM Search"

If Request.Form("Category") <> "All Categories" Then
strSQL = strSQL & " WHERE Category = '" & Request.Form("Category") & "' "
End If

If Request.Form("Division") <> "All Divisions" Then
strSQL = strSQL & " And (Division = '" & Request.Form("Division") & "') "
End If

[madg3rr]

I've added the WHERE but It still cant be done... is der sumthin wrong wif the defination of my connection?
dis is my whole code... can help me find wads wrong wif it???

Dim strSQL

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "CPAR"

strSQL="Select * FROM Search"

If Request.Form("Category") <> "All Categories" Then
strSQL = strSQL & "WHERE Category = '" & Request.Form("Category") & "' "
End If

If Request.Form("Division") <> "All Divisions" Then
strSQL = strSQL & "And (Division = '" & Request.Form("Division") & "') "
End If

set rsSearch=Conn.Execute(strSQL)

[Angelika]

try this:

<%
Dim strSQL,Category,Division

Category=Request.Form("Category")
Division=Request.Form("Division")

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "CPAR"

strSQL="Select * FROM Search "

If not Category = "All Categories" Then
strSQL = strSQL + "WHERE Category = '"&Category&"' "
End If

If Not Division = "All Divisions" Then
strSQL = strSQL + "And Division = '"&Division&"' "
End If

set rsSearch=Conn.Execute(strSQL)
%>

[madg3rr]

hei thanx thanx realli thanx... u saved my life... i can do it oredi... real thanx...

[jsawkang]

Hi why don't u do like this.

strSQL="Select * FROM Search"

If Request.Form("Category") <> "All Categories" Then
strSQL = strSQL & " WHERE Category = '" & Request.Form("Category") & "'"
end if
If Request.Form("Division") <> "All Divisions" Then
strSQL = strSQL & " And Division = '" & Request.Form("Division") & "'"
End If

what sort of error u got anyway? And what is "CPAR"?

[madg3rr]

i not so sure wad the error is... it onli says dis...

[Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause

but then thanx agn. i m able to do it. i changed the code according to wad the previous sender has adviced.
CPAR means corrective & preventive action report.

[Bullschmidt]

Yes, my example of this:
<<
strSQL = "SELECT * FROM MyTable WHERE (1 = 1)"
If Request.Form("Category") <> "All" Then
strSQL = strSQL & "And (Category = '" & Request.Form("Category") & "') "
End If
If Request.Form("Department") <> "All" Then
strSQL = strSQL & "And (Department = '" & Request.Form("Department") & "') "
End If
>>

Really needed an extra space after (1 = 1) to be like this instead:
<<
strSQL = "SELECT * FROM MyTable WHERE (1 = 1) "
If Request.Form("Category") <> "All" Then
strSQL = strSQL & "And (Category = '" & Request.Form("Category") & "') "
End If
If Request.Form("Department") <> "All" Then
strSQL = strSQL & "And (Department = '" & Request.Form("Department") & "') "
End If
>>