mySQL with ASP

[dhs]

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:

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:

 

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?

[asp-hosting.ca]

This is a problem with one of your SQL statements, not with the actual connection. Post your SQL statement here, for review.

[fillup07]

yep, there's a few changes in sql between dbs

[dhs]

 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

[dhs]

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

 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

[ashutosh_sns]

hi

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

not support this function.

[asp-hosting.ca]

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...