Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2005
    Posts
    23

    Unanswered: MySQL syntax error

    Some of my pages include paging where the variable starts with a certain letter. This worked fine in access but when I try to run it with mysql it says...

    Microsoft OLE DB Provider for ODBC Drivers error '80040e37'

    [MySQL][ODBC 3.51 Driver][mysqld-4.0.17-nt]You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT username, status FROM users WHERE username Like 'A%' OR

    /userview.asp, line 37

    My codings like this...

    Code:
    <%
    Dim pageno,totalpages,rsuserview,pagecount,sortby,startletter, alphabet, rsmusic
    
    startletter = Request.QueryString("startletter")
    
    if startletter = "" then
      startletter = "A"
    end if
    
    Const adOpenForwardOnly = 0
    Const adLockReadOnly = 1
    Const adCmdTableDirect = &H0200
    Const adUseClient = 3
    %>
    <%
    if startletter = "nonalphabet" then
      sql = "SELECT username, status FROM users WHERE username Not Like 'a%'"
      for alphabet = 98 to 122
    	sql = sql & " AND username Not Like '" & chr(alphabet) & "%'"
      next
      sql = sql & " ORDER BY username ASC"
    
    elseif startletter = "all" then
      sql = "SELECT username, status FROM users ORDER BY username ASC"
    
    else
      sql = "SELECT username, status FROM users WHERE username LIKE '" & startletter & "%' ORDER BY username"
    end if
    Set rsuserview = Server.CreateObject("ADODB.Recordset")
    
      rsuserview.PageSize = 100
      rsuserview.CursorLocation = adUseClient
    
     rsuserview.Open sql, conn, adOpenForwardOnly, adLockReadOnly, _
       adCmdTableDirect
    %>
    and line 37 is...

    Code:
     rsuserview.Open sql, conn, adOpenForwardOnly, adLockReadOnly, _
       adCmdTableDirect

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I dont think you can use
    adCmdTableDirect
    in an non native jet application, try accmdtable, or possibly accmdunknown, or failing that nothing.
    HTH

  3. #3
    Join Date
    Jan 2005
    Posts
    23
    yup, i got rid of it and it worked, thanks healdem

Posting Permissions

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