Error message in SQL statement for varying criteri

Results 1 to 2 of 2

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>

  2. #2
    Join Date
    Dec 1969

    Default RE: Error message in SQL statement for varying cri

    Have you tried to debug this yourself?<BR><BR>Add a response.write SQL in your code just before you open your recordset. Read the SQL statement and see if it is formatted correctly. If not match where it is not formatted correctly to the code that generates that section. <BR><BR>I could tell you right know what your problem is but that would not help you in the long run, try debugging your code .. it is not the easiest thing to do in ASP but a well place Response.Write will expose wonders. When dealing with Access you can run the query in your access database to aid in debugging.<BR><BR>Since I never really read this forum I will give you a hint:<BR>table &#060;-- if that is really the name of your table you have to use [table] its a keyword

Posting Permissions

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