Problem with search query. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problem with search query.

Author

Topic

Queued
Starting Member

2 Posts

Posted - 2003-04-11 : 05:12:58

Hi all,

I have a problem with a query witch should display the results from a form. I've tried a lot of thinks but it doesn't work


strid = request("strid")
strname = request("strname")
strusername = request("strusername")
strlocation = request("strlocation")
strgender = request("strgender")

  Set rs = Server.CreateObject("ADODB.Recordset")
  strSQL = "SELECT * FROM MEMBERS WHERE "

  If Request.Form("strid") <> "" Then
  strSQL = strSQL & "ID LIKE '%" & strid & "%' "
  Else
  End If

  If Request.Form("strname") <> "" Then
  strSQL = strSQL & "AND T_NAME LIKE '" & strname & "' "
  Else
  End If

  If Request.Form("strusername") <> "" Then
  strSQL = strSQL & "AND T_USERNAME LIKE '%" & strusername & "%' "
  End If

  If Request.Form("strlocation") <> "" Then
  strSQL = strSQL & "AND T_LOCATION LIKE '%" & strlocation & "%' "
  End If
  set rs = my_Conn.Execute(strSQL)

I get the next error message:


Microsoft JET Database Engine error '80040e14' 

Syntax error (missing operator) in query expression 'AND T_NAME LIKE 'Rob''. 

/gallery_search_results.asp, line 59

When I do a response.write on the query I get the next result:


SELECT * FROM MEMBERS WHERE AND T_NAME LIKE 'Rob'

Can anybody help me out?

Edited by - Queued on 04/11/2003 05:13:39

macka
Posting Yak Master

162 Posts

Posted - 2003-04-11 : 05:21:54

Queued,

I'm guessing that strid is = "" and thats causing the problem. Try changing the following lines:



  strSQL = "SELECT * FROM MEMBERS WHERE 1=1 "

...and....


  strSQL = strSQL & "AND ID LIKE '%" & strid & "%' "

Cheers,

macka.

--
There are only 10 types of people in the world - Those who understand binary, and those who don't.

Queued
Starting Member

2 Posts

Posted - 2003-04-11 : 05:39:54

It works!

Thanks a lot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources