Error message in SQL statement for varying criteri

Results 1 to 3 of 3

Thread: Error message in SQL statement for varying criteri

  1. #1
    Peter7394 Guest

    Default Error message in SQL statement for varying criteri

    I am reposting this as the other thread has been buried. Previous posts were under the name "Peter", but I am new here and did not know that it was in conflict with another poster here.<BR><BR>I was advised by Bill Wilkinson to use the basic code below to write an SQL statement where the user specifies 1 to 4 or more criteria in the WHERE part of the statement. <BR><BR>I am getting an error message after the last line. Error message is below. <BR><BR>Microsoft OLE DB Provider for ODBC Drivers error &#039;80040e14&#039; <BR><BR>[Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause. <BR><BR>/final travel web/RhFormProc/cr_gp_proc.asp, line 79 <BR><BR><BR>Here is the code full code for this part of the page: <BR><BR>&#060;% <BR>&#039;---Open connection to database. <BR><BR>Dim objConn <BR>Set objConn = Server.CreateObject("ADODB.Connection") <BR>objConn.ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & DatabasePath & ";" <BR>objConn.Open <BR><BR><BR>&#039;---Request Form Information <BR>dim group_pref, activity, cruiseline, cruise_dest <BR>group_pref = Request.Form("group_pref") <BR>activity = Request.Form("activity") <BR>cruiseline = Request.Form("cruiseline") <BR>cruise_dest = Request.Form("cruise_dest") <BR>Response.Write (group_pref) <BR>Response.Write (activity) <BR>Response.Write (cruiseline) <BR>Response.Write (cruise_dest) <BR><BR>&#039;---Determine if form filled with type of cruise- <BR><BR>If Request.Form("group_pref") = "" then <BR>Response.Redirect "cruise_selection.asp" <BR>End If <BR><BR>&#039;---Compose the SQL Statement from whatever is filled out from form <BR>Dim SQL <BR>SQL = "SELECT * FROM table WHERE 1=1 " <BR><BR>If group_pref &#060;&#062; "No_Preference" Then <BR>SQL = SQL & " AND Group_Name = &#039;" & group_pref & "&#039;" <BR>End If <BR><BR>If activity &#060;&#062; "No_Preference" Then <BR>SQL = SQL & " AND Activity_Code = &#039;" & activity & "&#039;" <BR>End If <BR><BR>If cruiseline &#060;&#062; "No_Preference" Then <BR>SQL = SQL & " AND Cruiseline = &#039;" & cruiseline & "&#039;" <BR>End If <BR><BR>If cruise_dest &#060;&#062; "No_Preference" Then <BR>SQL = SQL & " AND Cruise_Dest = &#039;" & cruise_dest & "&#039;" <BR>End If <BR><BR>&#039;---Create a recordset instance and execute SQL Statement <BR>dim objRS <BR>Set objRS = Server.CreateObject("ADODB.Recordset") <BR>objRS.Open SQL, objConn, , adLockReadOnly, adCmdTable <BR><BR>Any ideas as to what is wrong with the code.<BR><BR>Thanks<BR><BR><BR><BR>

  2. #2
    Join Date
    Dec 1969

    Default RE: Error message in SQL statement for varying cri

    response.write your sql statment, it makes debugging alot easier

  3. #3
    Peter7394 Guest

    Default Response.Write Select statement- still have error

    Yes, I corrected error- did not have real table name. I changed the table name and also set init = 0 which was previously declared, since it may not have liked 1=1<BR><BR>Here is the statement-<BR><BR>SELECT * FROM ListedCrGpSpecs WHERE init=0 AND Group_Name = &#039;Theme Group Cruises&#039; <BR><BR>Error message says syntax is wrong in From.

Posting Permissions

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