Need help trying to build a conditional SQL statem

Results 1 to 3 of 3

Thread: Need help trying to build a conditional SQL statem

  1. #1
    Rob Guest

    Default Need help trying to build a conditional SQL statem

    I am trying to build up an SQL statement based on conditional input from a user. I am using a few if statements to compose my SQL as follows<BR> property = TRIM(Request.form("prop"))<BR> yr = TRIM(Request.form("yr"))<BR> surv = TRIM(Request.form("surv_type"))<BR> report = TRIM(Request.form("rep_num"))<BR><BR> DIM SQLQRY<BR><BR> If property &#060;&#062; "" Then<BR> SQLQRY="SELECT * FROM gisdb_info_table WHERE property <BR> LIKE &#039;%"&property&"%&#039;" <BR> End If<BR><BR> If yr &#060;&#062; "" Then<BR> SQLQRY= SQLQRY & "AND year = %"&yr&"%"<BR> End If<BR><BR> If surv &#060;&#062; "" Then<BR> SQLQRY= SQLQRY & "AND survey_type LIKE &#039;%"&surv&"%&#039;" <BR> End If<BR><BR> If report &#060;&#062; "" Then<BR> SQLQRY= SQLQRY & "AND report LIKE &#039;%"&report&"%&#039;" <BR> End If<BR><BR> Set objRs = objConn.Execute(SQLQRY) <BR><BR>I get the following error<BR>Microsoft OLE DB Provider for ODBC Drivers error &#039;80004005&#039; <BR>[Oracle][ODBC][Ora]ORA-00911: invalid character <BR>/explor/db/gisdbinfo.asp, line 41 <BR><BR>I have used some Response.Write&#039;s to check that my SQL statements are being properly composed, and they look correct.<BR>Am I missing a special tie character or something to put my statements together? Thanks for any help you can provide.<BR><BR>

  2. #2
    J. Paul Schmidt Guest

    Default RE: Need help trying to build a conditional SQL st

    Don&#039;t use % in a SQL statement unless that part deals with text and uses Like instead of the equals sign.<BR><BR>Best regards,<BR>-Paul<BR><BR>J. Paul Schmidt, MBA<BR>Databases on the Web<BR><BR><BR>

  3. #3
    Join Date
    Dec 1969

    Default Tell me this:

    what happens if the "property" is empty and the year is not empty, ur query reads that if the property is not empty then do whatever but it doesnt say if the property is empty then what, now consider this, if the property is empty then the second sql doesnt make any sense u get it?<BR>year is not empty so the sql reads:<BR><BR>If yr &#060;&#062; "" Then<BR>SQLQRY= SQLQRY & "AND year = %"&yr&"%"<BR>End If<BR><BR>-------<BR>now what does "SQLQRY" mean here, Nothing , Nada<BR><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