ADO & MySQL

[Dug]

Hi There, Ok I've been trying to get something to work all day yesterday and I've read the code back to front and it looks fine to me, so I'm having to ask for help.Obviously I've included the DBConnector.asp file in the page, and declaired the variables etc. As far as I know, there are two different ways to do this. The first one is:

objRS("FirstName") = Request.Form("f_name")objRS("LastName") = Request.Form("l_name")objRS("Email") = Request.Form("email")objRS("Username") = Request.Form("username")objRS("Password") = Request.Form("password")objRS("Group") = Request.Form("user-level")objRS.UpdateobjRS.CloseSet objRS = Nothing

And the other way:

sql = "UPDATE users SET "sql = sql & "FirstName='" & Request.Form("f_name") & "'"sql = sql & "LastName='" & Request.Form("l_name") & "'"sql = sql & "Email='" & Request.Form("email") & "'"sql = sql & "Username='" & Request.Form("username") & "'"sql = sql & "Password='" & Request.Form("password") & "'"sql = sql & "Group='" & Request.Form("user-level") & "'"sql = sql & "WHERE Email = '" & Request.Form("email") & "'"conn.Execute sql

And this is the error I'm always getting:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'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 'Group='owner' WHERE (FirstName='Douglas' AND LastName='McGregor' AND Email='doug' at line 1/admin/users/UpdateUserStatus1.asp, line 35

Is this a fault with my code or is there an incompatibility between ASP and ODBC?Thanks!Dug

[Dug]

I've actually just found the solution.It was to do with the recordset names on the script not matching the names in the database.D'oh.

[justsomeguy]

It might also be that you used the word "group" as a column name. With MySQL and several other DBMSs, group is a reserved word (for the GROUP BY clause). If you want to name your column as a reserved word, you need to enclose it with backquotes:sql = sql & "`Group`='" & Request.Form("user-level") & "'"Also, there's a third way to do database operations without using a connection object at all.

<%@LANGUAGE="JavaScript"%><%var MM_dbcon_STRING="provider=sqloledb;Data Source=database_server;Initial Catalog=database_name;User ID=user;Password=password;";var dbcon = Server.CreateObject("ADODB.RecordSet");dbcon.ActiveConnection = MM_dbcon_STRING;dbcon.Open("SELECT * FROM users");while (!dbcon.EOF){  Response.Write("first name: " + dbcon.Fields.Item("fname").value);  dbcon.MoveNext();}dbcon.Close();%>