www.webdeveloper.com
Results 1 to 13 of 13

Thread: [Microsoft][ODBC Microsoft Access Driver] Syntax error (comma) in query expression

  1. #1
    Join Date
    May 2005
    Posts
    43

    [Microsoft][ODBC Microsoft Access Driver] Syntax error (comma) in query expression

    I am having a problem with some of my code. I have a site where you enter your zipcode and it would give you all the drop-off locations with your area. If you enter a zip code that is not in the Cincinnati area it lets you know that it is not a valid zip code. When you enter the zipcode...45219 (which is a good zipcode) it gives me the following error:

    Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
    [Microsoft][ODBC Microsoft Access Driver] Syntax error (comma) in query expression


    The odd thing about this is if you hit the back button and then the forward button on the wb browser it works correctly. Odd

    Here is the code that it is referring too in the error message:

    Code:
    dim rsgetlocs :set rsgetlocs = conn.execute("SELECT distinct ((69.1*(Latitude - " &  rsZip("latitude")& "))*(69.1*(Latitude - " & rsZip("latitude") & "))+(69.1*( Longitude - " & rsZip("longitude") & ")*cos( " & rsZip("latitude") & "/57.3)*(69.1*(Longitude - " &  rsZip("longitude") & ")*cos( " & rsZip("latitude") & "/57.3)))) as disc,dropofflocs.* FROM dropofflocs WHERE Latitude <= ("&rsZip("latitude")&" + ("&passedradius&" / ((6076 / 5280) * 60))) AND Latitude >= ("&rsZip("latitude")&" - ("&passedradius&" / ((6076 / 5280) * 60)))	AND Longitude >= ("&rsZip("longitude")&" - ("&passedradius&" / (((cos(cdbl("&rsZip("latitude")&" * 3.141592653589 / 180)) * 6076) / 5280) * 60))) AND Longitude <= ("&rsZip("longitude")&" + ("&passedradius&" / (((cos(cdbl("&rsZip("latitude")&" * 3.141592653589 / 180)) * 6076) / 5280) * 60)))")
    Here is the active website so that you can see what I am talking about when you hit the back button and then the forward button. You can use 45219 as the zipcode.

  2. #2
    Join Date
    Feb 2003
    Posts
    2,745
    looks ok. might try putting space between , and dropofflocs

    as disc,dropofflocs.*

    but that shouldn't matter. were u gonna post a link...?

  3. #3
    Join Date
    May 2005
    Posts
    43

  4. #4
    Join Date
    May 2005
    Posts
    43
    I am guessing that no one knows where to start with this one. Do you think that it could have been an update with the IIS server that is conflicting with this? I had this running smoothly for several months before it messed up. I am trying to see what changes are IT department has done in the last few months. I didn't know if anyone else has run into this problems while updating.

  5. #5
    Join Date
    Feb 2003
    Posts
    2,745
    i don't get the error when i try it. it just works. though u do hve a javascript error on that page... trying to call isValidate() function that doesn't exist...

  6. #6
    Join Date
    May 2005
    Posts
    43
    i don't get the error when i try it. it just works. though u do hve a javascript error on that page... trying to call isValidate() function that doesn't exist...
    What browser version are you using? I have tried this on several different computers on different internet connections and still to not get it to work. It still comes up with the error code. Plus, I found out that some times I could not use the back button to even view it correctly. Thanks for looking into this for me. I am completely stumped on this one.

  7. #7
    Join Date
    Feb 2003
    Posts
    2,745
    IE 6 xp pro sp2. i just checked again -- still works. you dont have javascript turned off on your browsers do u?

  8. #8
    Join Date
    May 2005
    Posts
    43
    I am running the exact samething. Where is it located under the tools - internet options menu. I couldn't find one that said enable/disable javascript.

  9. #9
    Join Date
    Dec 2003
    Location
    England, UK
    Posts
    432
    it's not there. it's in the security tab/custom level bit. it's called active scripting.

  10. #10
    Join Date
    May 2005
    Posts
    43
    Russell, thanks for all your help on this!

    yeah I found it. It is enabled. I don't know what the problem is. I change the way the webpage connects to the db hoping that would do something and I received a new error with the same line.

    Old connection:

    dim Databasestring : Databasestring="Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.Mappath("\_private\sw\recyclefinder.mdb")
    dim Conn : set Conn = server.CreateObject("ADODB.Connection")
    Conn.open Databasestring

    New connection:

    Dim Conn, dbPath
    dbPath = Server.Mappath("\_private\sw\recyclefinder.mdb")
    Set Conn = Server.CreateObject("ADODB.Connection")
    Conn.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & dbPath


    NEW ERROR:

    ADODB.Recordset error '800a0cc1'
    Item cannot be found in the collection corresponding to the requested name or ordinal.

    /sw(new)/recyclefinder/recyclefinderresults3.asp, line 120


    I am going to include the entire code for the error page hopefully you will be able to see what the problem is. I still can't believe that it is working on your machine. If anyone else is reading this thread if you could take a look at the page and let me know if it works for you. Again, the web address is:

    http://www.hcdoes.org/sw(new)/recycl...yclefinder.htm
    use 45219 for the zipcode.


    Following is the code for the page:

    Code:
    <%@ Language=VBScript %>
    
    <%
     Dim Conn, dbPath
      dbPath = Server.Mappath("\_private\sw\recyclefinder.mdb") 
      Set Conn = Server.CreateObject("ADODB.Connection")
      Conn.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & dbPath
    
    
    dim passedzipcode : passedzipcode = request.form("passedzipcode")
    dim passedradius : passedradius = request("passedradius")
    dim passedround : passedround = request("passedround")
    dim rowonecolor : rowonecolor = request("rowonecolor")
    dim rowtwocolor : rowtwocolor = request("rowtwocolor")
    dim rowthreecolor : rowthreecolor = request("rowthreecolor")
    dim dsname : dsname = request("dsname")
    
    'if passedzipcode<>"" then
    		passedzipcode1 = passedzipcode
    		passedzipcode2 = isNumeric(passedzipcode)
    		if passedzipcode2=False or passedzipcode1="" then
    			errormessage = "Please pass a valid Zip Code"
    		end if		
    'end if
    
    'if passedradius<>"" then
    		passedradius1 = passedradius
    		passedradius2 = isNumeric(passedradius)		
    		if passedradius2=False or passedradius1="" then
    			errormessage = "Please pass a valid mile range"
    		end if		
    'end if
    
    if errormessage<>"" then 
    	Response.Redirect "recyclefinderform.asp?errmss="&errormessage
    end if
    %>
    <HTML>
    <Head>
    <Title>Recycle Finder</Title>
    <link href="/sw(new)/recyclefinder/border.css" rel="stylesheet" type="text/css">
    </Head>
    <body bgcolor="#fbf2d2" leftmargin="12px" topmargin="0" marginwidth="0" marginheight="0"
    
    <div align="left">
      <table width="760" height="36" border=0 cellpadding=0 cellspacing=0>
        <tr height="30">
          <td height="36" valign=top>        <a href="/sw(new)/recyclefinder/recyclefinderform.asp"><font color="<%=rowthreecolor%>"><br>
            New
            Search</font></a>
          <h2 align="left">Recycling Drop-off locations for <font color="#FF0000"><strong><%=passedzipcode%></strong></font></h2></td>
        </tr>
      </table>
      <table width="760" border=1 cellpadding=8 cellspacing=0 class="unnamed1">
        <%
    					dim rsZip :set rsZip = conn.execute("SELECT * FROM dropofflocs, zipcodes WHERE zipcodes.ZipCode="&passedzipcode)
    					'dim rsgetlocs :set rsgetlocs = conn.execute("SELECT * FROM zipcodes a, Dropoffs b WHERE a.Latitude <= (18.49352 + (5 / ((6076 / 5280) * 60))) AND a.zipcode = b.zipcode AND a.Latitude >= (18.49352 - (5 / ((6076 / 5280) * 60))) AND a.Longitude >= (67.135883 - (5 / (((cos(cdbl(18.49352 * 3.141592653589 / 180)) * 6076) / 5280) * 60))) AND a.Longitude <= (67.135883 + (5 / (((cos(cdbl(18.49352 * 3.141592653589 / 180)) * 6076) / 5280) * 60)))")
    					if not rsZip.EOF then
    				%>
        <tr>
          <td valign=center align=center width="15%" bgcolor="#006A00" class="unnamed1"> <font color="#FFFFFF"><strong>Township</strong></font></td>
          <td valign=center align=center width="13%" bgcolor="#006A00" class="unnamed1"> <font color="#FFFFFF"><strong>Location</strong></font></td>
          <td valign=center align=center width="18%" bgcolor="#006A00" class="unnamed1"> <font color="#FFFFFF"><strong>Address</strong></font></td>
          <td valign=center align=center width="35%" bgcolor="#006A00" class="unnamed1"> <font color="#FFFFFF"><strong>Acceptable
                Items</strong></font></td>
          <td valign=center align=center width="18%" bgcolor="#006A00" class="unnamed1"> <font color="#FFFFFF"><strong>More
                Info</strong></font></td>
          <td valign=center align=center width="19%" bgcolor="#006A00" class="unnamed1"> <font color="#FFFFFF">&nbsp;</font> </td>
          <%
    					
    					'dim rsgetlocs :set rsgetlocs = conn.execute("SELECT * FROM zipcodes a, Dropoffs b WHERE a.Latitude <= ("&rsZip("latitude")&" + ("&passedradius&" / ((6076 / 5280) * 60))) AND a.zipcode = b.zipcode AND a.Latitude >= ("&rsZip("latitude")&" - ("&passedradius&" / ((6076 / 5280) * 60)))	AND a.Longitude >= ("&rsZip("longitude")&" - ("&passedradius&" / (((cos(cdbl("&rsZip("latitude")&" * 3.141592653589 / 180)) * 6076) / 5280) * 60))) AND a.Longitude <= ("&rsZip("longitude")&" + ("&passedradius&" / (((cos(cdbl("&rsZip("latitude")&" * 3.141592653589 / 180)) * 6076) / 5280) * 60)))")
    					dim rsgetlocs :set rsgetlocs = conn.execute("SELECT distinct ((69.1*(Latitude - " &  rsZip("latitude")& "))*(69.1*(Latitude - " & rsZip("latitude") & "))+(69.1*( Longitude - " & rsZip("longitude") & ")*cos( " & rsZip("latitude") & "/57.3)*(69.1*(Longitude - " &  rsZip("longitude") & ")*cos( " & rsZip("latitude") & "/57.3)))) as disc, dropofflocs.* FROM dropofflocs WHERE Latitude <= ("&rsZip("latitude")&" + ("&passedradius&" / ((6076 / 5280) * 60))) AND Latitude >= ("&rsZip("latitude")&" - ("&passedradius&" / ((6076 / 5280) * 60)))	AND Longitude >= ("&rsZip("longitude")&" - ("&passedradius&" / (((cos(cdbl("&rsZip("latitude")&" * 3.141592653589 / 180)) * 6076) / 5280) * 60))) AND Longitude <= ("&rsZip("longitude")&" + ("&passedradius&" / (((cos(cdbl("&rsZip("latitude")&" * 3.141592653589 / 180)) * 6076) / 5280) * 60)))") 
    					
    					count = 1
    					while not rsgetlocs.EOF
    					if(count Mod 2 = 0) then
    						strBg = rowtwocolor
    					else
    						strBg = rowonecolor
    					end if				
    				%>
        <tr>
          <td>
            <%						
    
    						'Dim sSql 
    						'sSql = "SELECT * FROM zipcodes a, Dropoffs b WHERE a.Latitude <= ("&rsZip("latitude")&" + ("&passedradius&" / ((6076 / 5280) * 60))) AND a.zipcode = b.zipcode AND a.Latitude >= ("&rsZip("latitude")&" - ("&passedradius&" / ((6076 / 5280) * 60)))	AND a.Longitude >= ("&rsZip("longitude")&" - ("&passedradius&" / (((cos(cdbl("&rsZip("latitude")&" * 3.141592653589 / 180)) * 6076) / 5280) * 60))) AND a.Longitude <= ("&rsZip("longitude")&" + ("&passedradius&" / (((cos(cdbl("&rsZip("latitude")&" * 3.141592653589 / 180)) * 6076) / 5280) * 60)))"
    						'response.write (sSql)
    						'response.end
    						'Response.WRite "SELECT * FROM zipcodes a, Dropoffs b WHERE a.Latitude <= ("&rsZip("latitude")&" + ("&passedradius&" / ((6076 / 5280) * 60))) AND a.zipcode = b.zipcode AND a.Latitude >= ("&rsZip("latitude")&" - ("&passedradius&" / ((6076 / 5280) * 60)))	AND a.Longitude >= ("&rsZip("longitude")&" - ("&passedradius&" / (((cos(cdbl("&rsZip("latitude")&" * 3.141592653589 / 180)) * 6076) / 5280) * 60))) AND a.Longitude <= ("&rsZip("longitude")&" + ("&passedradius&" / (((cos(cdbl("&rsZip("latitude")&" * 3.141592653589 / 180)) * 6076) / 5280) * 60)))"
    						%>
          </td>
        </tr>
        <tr>
          <td height="37" align=center valign=middle textarea style="font-weight:bolder" bgcolor="<%=strBg%>" class="unnamed1"> <%=rsgetlocs("City")%> </td>
          <td valign=top align=left bgcolor="<%=strBg%>" class="unnamed1"> <%=rsgetlocs("Name")%>
          <td valign=top align=left bgcolor="<%=strBg%>" class="unnamed1"> <%=rsgetlocs("Address")%>
          <td valign=top align=left bgcolor="<%=strBg%>" class="unnamed1"> <%=rsgetlocs("AcceptedItems")%> </td>
          <td valign=top align=left bgcolor="<%=strBg%>" class="unnamed1"> <%=rsgetlocs("MoreInfo")%>
          <td valign=middle align=center bgcolor="<%=strBg%>" class="unnamed1"><a href="<%=rsgetlocs("Link")%>"><img src="http://us.i1.yimg.com/us.yimg.com/i/us/mp/gr/mplogo.gif" width="99" height="13" border=0 align=middle alt="[ Yahoo! Maps ]"><br>
            MapIt</a> 
        </tr>
        <%
    				rsgetlocs.movenext
    				count = count+1
    				wend
    				else%>
        <tr>
          <td><div align="center"><font color="#FF0000"><strong><%=passedzipcode%></strong></font> is
                not a valid zip code. Recycle Finder is for <strong>Hamilton County,
                Ohio Residents only.</strong> </div>
          </td>
        </tr>
        <%end if
    Conn.Close
    Set Conn = Nothing
    				%>
      </table>
      <table width="760" border="0" cellspacing="0" cellpadding="0">
        <tr>
          <td height="34"><a href="/sw(new)/recyclefinder/recyclefinderform.asp"><font color="<%=rowthreecolor%>">New Search</font></a></td>
        </tr>
      </table>
    Last edited by tagriffith; 09-21-2006 at 10:11 PM.

  11. #11
    Join Date
    May 2005
    Posts
    43
    I am guessing that no one has an answer for this problem? I have included a .zip file with the .asp files and database file.

    Could this be a server update problem? Like I said in a previous post... this was working great for months and now it is not. I have run out of ideas and things to try.
    Last edited by tagriffith; 09-27-2006 at 11:19 AM.

  12. #12
    Join Date
    May 2005
    Posts
    43
    I got it to work. For some reason the longitude and latitude numbers in the database was being read into the code with comma's. It works great now!

  13. #13
    Join Date
    Oct 2005
    Posts
    1
    Hi can anyone assist please??

    Getting this error:

    Microsoft OLE DB Provider for ODBC Drivers error '80004005'

    [Microsoft][ODBC Microsoft Access Driver] Syntax error in union query.

    /coshh/riskassdb/dbCreate.asp, line 97

    Below is the code:

    if isValid then
    set conn = server.CreateObject("ADODB.Connection")
    conn.open "riskAssDB"
    conn.execute "insert into tblCOSHH (AssessmentNumber, ActivityAssessed, OriginalAssessment, ReviewDate, AssessmentBy, Signatory) " _
    & " values (" _
    & "'" & Request.Form("assNum") & "', " _
    & "'" & Request.Form("act") & "', " _
    & "'" & Request.Form("sDate") & "', " _
    & "'" & Request.Form("rDate") & "', " _
    & "'" & Request.Form("assessor") & "', " _
    & "'" & Request.Form("signatory") & "')"
    response.redirect "dbCreate_success.asp"
    conn.close

    end if

    end if
    %>

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles