Mysql error after migrating from Ms Access on an asp page

[janetopps]

I am trying to migrate from an MS access to MySQL and am having problems. I have an asp pages on my website.

<% ' Dim SQL_REC, RS_REC, NID, TITLE, POSTED, NEWS_LISTING, SHOW_HL, MyConn_REC, I_REC, SUMMARY
 
 
 
    ' ------------------------------------------------------------------------------------------------------------------
 
        ' DATABASE CONNECTION
   Set MyConn = Server.CreateObject("ADODB.Connection")
                MyConn.Open "Driver={MySQL ODBC 3.51 Driver}; Server=231.555.600.43; uid=anothername; pwd=password; database=thename; option=3; port=3306;"

however, on the web, the page shows the following error

ADODB.Recordset error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

/example/inc_recent_news .asp, line 41

The code on line 41 is

RS_REC.Open SQL_REC, MyConn_REC

Does anyone know what i need to change in my database connection string above? i;d appreciate if anyone post the code as i should use,


many thanks

[JamieHowarth0]

Try removing the square brackets from your query and try running the code again.

codegecko

[janetopps]

codegecko, thanks for your suggestions

I ran it again, on the main database, and this time no problem, it pulled up about 150 results in the panel below, and no error messages.

Does this mean that the square brackets are causing the problem in this page? how do i fix this?

FYI I ran a query on other errors im getting eg

SELECT TOP 1 ID FROM nm_tbl_comment WHERE fldNEWS_ID = 3324 AND ID > 585 ORDER BY ID ASC

and i received the response above when i did the response write thing, i copied and pasted it into the query browser, and i got this

error 1064

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 '1 ID, `ID` FROM nm_tbl_comment WHERE fldNEWS_ID = 3324 AND ID > 585 ORDER BY ID' at line 1

So from this, i am deducing that MS ACCESS databases use certain symbols that MySQL doesn't allow?

[JamieHowarth0]

Hi there,

Absolutely correct. MS Access uses square brackets [ and ] to denote field names or to make use of reserved words in the context of field names (such as using "Descriptio n" as a field name).

Your first query ran fine so the error isn't in your SQL code, it's with your ASP code and the way it's being constructed.
Your second query does appear to have a syntax error so when I'm less tired (it's 2am and I have 250 pages of RFC's to read over the weekend.... insane fun!), I will take a look further at your code and see if I can provide any more help.

codegecko

*EDIT* Having just done a search of the MySQL online documentation, you want to use SELECT MAX(ID). TOP is a MS Access/SQL Server equivalent of MAX - i.e. the row with the highest computed column value of the data being selected. Simply switch the TOP 1 ID to MAX(ID) and you should be sorted!