Database search SQL problems!

Results 1 to 2 of 2

Thread: Database search SQL problems!

  1. #1
    Join Date
    Dec 1969

    Default Database search SQL problems!

    I am trying to search an Access database, you can search by either text or number. But I keep getting this error:<BR><BR>Microsoft JET Database Engine error &#039;80040e14&#039; <BR>Syntax error (missing operator) in query expression &#039;* like &#039;%%search%%&#039;&#039;. <BR>/ecase/lessonplans.asp, line 206 <BR><BR>Some of the fields are text and some of them are number. Could searching number fields for text or vice versa be the problem? I have several text fields and several number fields I would like to search. Here is my code:<BR><BR>&#060;%<BR>u_search=request.querystri ng("u_search")<BR>u_exp=request.querystring("u_exp ")<BR>set my_conn = server.createobject ("adodb.connection")<BR> strDBPath = "D:Webswebsitedatadatabase.mdb"<BR> "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";"<BR>&#039;Determines if the user submitted text or number<BR>if u_exp="Text" then<BR>sql = "select * from LessonPlans where * like &#039;%%" &u_search &"%%&#039;" <BR>else<BR>sql = "select * from LessonPlans where * like %%" &u_search &"%%" <BR>end if<BR>set rs = my_conn.execute(sql)<BR>%&#062;<BR>

  2. #2
    Join Date
    Dec 1969

    Default Basic WHERE clause...

    You can&#039;t do that.<BR><BR>You can&#039;t ask to match EVERY field in the table by using * in the WHERE clause in SQL.<BR><BR>You *MUST* specify an actual field name (or an expression, though that doesn&#039;t work well with a LIKE operator).<BR><BR>If you want to search multiple fields, then you must put each one in separately in the WHERE clause.<BR><BR>Oh...and you can NOT use LIKE with ANY fields except textual ones. No numeric, data, boolean, bit, byte fields.<BR><BR>Finally, I dunno where the use of a pair of % signs comes from. It doesn&#039;t hurt, but it&#039;s meaningless. One % sign *already* means "match any characters". So two mean "match any characters and then match any characters". Pretty silly, no?<BR><BR> an example, you need something like:<BR><BR>match = "&#039;%" & u_search & "%&#039;" <BR>sql = "SELECT * FROM LessonPlans WHERE planName LIKE " & match & " AND planDescription LIKE " & match<BR><BR>Time to go get a good basic reference guide to SQL. The "HELP" that is built in to Access does a pretty good job at this. It wouldn&#039;t have misled you into believing that * LIKE xxx would work, if you&#039;d used it to guide you.<BR><BR>

Posting Permissions

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