SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Thread: mySQL with ASP

  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2003
    Location
    LONDON
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mySQL with ASP

    I was having trouble with access because it was far too slow and the site kept crashing as too many people were accessing the database at the same time (servers errors because of the lock file kept cropping up).

    So, I converted the Access Database to mySQL, used the connection string:

    Code:
    Conn.Open "DRIVER={MySQL ODBC 3.51 Driver};database=dhsweb;server=localhost;uid=root;password=chadwick;option=3;"
    And it connects ok. However, I get the following error on the main page:


    Code:
     
    
    Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [MySQL][ODBC 3.51 Driver][mysqld-4.1.7-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 '5 * from News Where AccessLevel <= 0 Order by Id desc' at line 1 /includes/home.asp, line 54
    Is this a problem with my code or connection string, and how do I fix it?

    Further, is there a way to speed up Access or make it so it allows more than one person to write/read it at the same time?

  2. #2
    SitePoint Evangelist
    Join Date
    Nov 2002
    Posts
    423
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is a problem with one of your SQL statements, not with the actual connection. Post your SQL statement here, for review.

  3. #3
    Phil fillup07's Avatar
    Join Date
    May 2002
    Location
    Jacksonville, FL
    Posts
    1,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yep, there's a few changes in sql between dbs

  4. #4
    SitePoint Enthusiast
    Join Date
    Mar 2003
    Location
    LONDON
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query as Requested

    Code:
     Set rsNews = Server.CreateObject("ADODB.RecordSet") 
    
    RsNews.open "Select TOP 5 * from News Where AccessLevel <= " & Session("AccessLevel") & " Order by Id desc", Conn
    
     If Not rsNews.EOF Then
    Do Until RsNews.EOF

  5. #5
    SitePoint Enthusiast
    Join Date
    Mar 2003
    Location
    LONDON
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    new problem.

    this function doesn't recognise the user based on his/her network username either. Any suggestions? It always returns .Eof

    Code:
     Function Login() 
    Set RSusersystem = Server.CreateObject("ADODB.Recordset")
    RsUsersystem.Open "Select Id,AccessLevel,BanLift From UserSystem Where Network = '" & Request.ServerVariables("LOGON_USER") & "'",Conn
    If RsUserSystem.Eof Then
    Session("AccessLevel") = 0
    Else
    If Rsusersystem("BanLift") > Now Then
    Session("AccessLevel") = 0
    Session("UserId") = RsUsersystem("Id")
    BanRedirect = True
    Else
    Session("AccessLevel") = RsUsersystem("AccessLevel")
    Session("UserId") = RsUsersystem("Id")
    End If
    End If
    RsUsersystem.Close
    Set RSUsersystem = Nothing
    If BanRedirect = True Then
    Response.Redirect ("?request=&msg=You are logged in, but have only limited access as you are currently banned.")
    End If
    End Function

  6. #6
    SitePoint Zealot ashutosh_sns's Avatar
    Join Date
    Aug 2004
    Location
    New Delhi , india
    Posts
    119
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi

    i got the right thing . please remove the top 5 from sql statement. because MYSQL do

    not support this function.
    Ashutosh Tripathi
    Home Page

  7. #7
    SitePoint Evangelist
    Join Date
    Nov 2002
    Posts
    423
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ashutosh_sns
    hi

    i got the right thing . please remove the top 5 from sql statement. because MYSQL do

    not support this function.
    MySQL has a similar to the TOP SQL statement which is LIMIT.
    So if you want the top 5 rows in your result set, just put LIMIT 5 at the end of your SQL statement...


Bookmarks

Posting Permissions

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