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

[janetopps]

here is another code error, for a page called add.asp , a page that adds articles to the mysql database.

i'm using this as the database connection string.

Set MyConn = Server.CreateObject("ADODB.Connection")
    MyConn.Open "Driver={MySQL ODBC 3.51 Driver}; Server=657.343.500.82; uid=aname; pwd=passwprd; database=anothername; option=3; port=3306;" 

and i get this error when i try to use this page to add an article


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

[MySQL][ODBC 3.51 Driver][mysqld-5.0.45]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 '[fldName] ASC' at line 1

/Admin/news_add.asp, line 97

the code on line 97 is:

    RS.Open SQL, MyConn    

i'd appreciate a response from anyne who could tell me what to try for code, or what the error means, i'm not very knowledgable about web designing and html

thanks

[janetopps]

to follow up on the second post, this is an update

the code on line 97 which is returning the error is this

 
        RS.Open SQL, MyConn

i added this in front of it, as i read somewhere that it would show the problem,

response.write(sql)
response.end

and this is what it showed on the web after i added the response write thing



SELECT ID, fldNAME, ID as ID1, 0 as ID2, 0 as ID3 FROM nm_tbl_cate WHERE fldLEVEL = 1 UNION SELECT nm_tbl_cate.ID, CONCAT(PARENT.fldNAME , ' > ' , nm_tbl_cate.fldNAME) AS NAME, PARENT.id, nm_tbl_cate.ID, 0 FROM nm_tbl_cate, nm_tbl_cate AS PARENT WHERE (nm_tbl_cate.fldLEVEL = 2) AND (nm_tbl_cate.fldPID =PARENT.ID) UNION SELECT nm_tbl_cate.ID, CONCAT(PARENT.fldNAME , ' > ' , SUBPARENT.fldNAME , ' > ' , nm_tbl_cate.fldNAME) AS NAME, PARENT.id, SUBPARENT.id, nm_tbl_cate.ID FROM nm_tbl_cate, nm_tbl_cate AS PARENT, nm_tbl_cate AS SUBPARENT WHERE (nm_tbl_cate.fldLEVEL = 3) AND (nm_tbl_cate.fldPID =PARENT.ID) AND (nm_tbl_cate.fldSID =SUBPARENT.ID) ORDER BY [fldName] ASC





just for the record, i'm using the trial version of Full Convert 4,

http://www.spectralcore.com/fullconv...s=0&from=lbnag
to convert my access database to mysql, i seem to have migrated files, as they show, but have a few errors to sort out,




any tips anyone? thanks in advance

[JamieHowarth0]

Hi there,

What happens if you try to run the queries in the MySQL Query Analyser?

codegecko

[janetopps]

Hi there,

What happens if you try to run the queries in the MySQL Query Analyser?

codegecko

I havn't run them in query analayser, i don't anything about html, databases and such,

Do i do that in the conversion software im using? thanks for your help, Cedegecko

[janetopps]

I thought i should add this, so that anyone who may be able to help can see,

After doing the Full Convert data transfer in their trial version, I get these 5 error reports



nm_tbl_news
Row #1957, Column fldCONTENT: Unspecified error
Row #1958, Column fldCONTENT: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done

nm_tbl_news_cate
(DATA) Lost connection to MySQL server during query

nm_tbl_comment
(CONSTRAINTS) Cannot add or update a child row: a foreign key constraint fails (`databasename/#sql-c0c_41e6e2`, CONSTRAINT `nm_tbl_newsnm_tbl_comment` FOREIGN KEY (`fldNEWS_ID`) REFERENCES `nm_tbl_news` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION)

nm_tbl_relations
(CONSTRAINTS) Cannot add or update a child row: a foreign key constraint fails (`databasename/#sql-c0c_41e6e2`, CONSTRAINT `nm_tbl_newsnm_tbl_relations` FOREIGN KEY (`fldPID`) REFERENCES `nm_tbl_news` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION)

Someone told me that these messages may be due to their limitations in the trial version, which adds watermarks to every 5th record. Once you purchase, the full version of course they don't show, and i don't think that's what causing the errors

[JamieHowarth0]

Hi there,

Firstly, download MySQL Query Browser from here. Then, copy and paste your queries into the Query Browser and try and run them.
The Query Browser will give you a better indication of what's wrong with your query, if it doesn't run. If the query does run then we know that your SQL code is fine and that we need to look at other sources to your problem.

Hope it helps.

codegecko

*EDIT* The MySQL GUI Tools (Query Browser is just one bit of software in the GUI Tools package) also contains a Migration Toolkit for moving your data from Access, SQL Server and other databases to MySQL. And it has no restrictions. Try using that to move your database across and see if that helps.

[janetopps]

Hi there,

Firstly, download MySQL Query Browser from here. Then, copy and paste your queries into the Query Browser and try and run them.
The Query Browser will give you a better indication of what's wrong with your query, if it doesn't run. If the query does run then we know that your SQL code is fine and that we need to look at other sources to your problem.

Hope it helps.

codegecko

*EDIT* The MySQL GUI Tools (Query Browser is just one bit of software in the GUI Tools package) also contains a Migration Toolkit for moving your data from Access, SQL Server and other databases to MySQL. And it has no restrictions. Try using that to move your database across and see if that helps.

OK i'm downloading it now, but as i said im a complete novice,

what is "query" ie. which part of the above should i put into the query analyser

[JamieHowarth0]

This bit:

SELECT ID, fldNAME, ID as ID1, 0 as ID2, 0 as ID3 FROM nm_tbl_cate WHERE fldLEVEL = 1
UNION
SELECT nm_tbl_cate.ID, CONCAT(PARENT.fldNAME , ' > ' , nm_tbl_cate.fldNAME) AS NAME, PARENT.id, nm_tbl_cate.ID, 0 FROM nm_tbl_cate, nm_tbl_cate AS PARENT WHERE (nm_tbl_cate.fldLEVEL = 2) AND (nm_tbl_cate.fldPID =PARENT.ID)
UNION
SELECT nm_tbl_cate.ID, CONCAT(PARENT.fldNAME , ' > ' , SUBPARENT.fldNAME , ' > ' , nm_tbl_cate.fldNAME) AS NAME, PARENT.id, SUBPARENT.id, nm_tbl_cate.ID FROM nm_tbl_cate, nm_tbl_cate AS PARENT, nm_tbl_cate AS SUBPARENT WHERE (nm_tbl_cate.fldLEVEL = 3) AND (nm_tbl_cate.fldPID =PARENT.ID) AND (nm_tbl_cate.fldSID =SUBPARENT.ID)
ORDER BY [fldName] ASC
 

This is a SQL query - SQL stands for Structured Query Language.

codegecko

[janetopps]

I copied and pasted the query above, into the space at the top of the query browser, and i think it ran on the information_schema folder first as the software recommended i add the schema folder. it didn't execute and i got this message at the bottom

Unknown table 'nm_tbl_cate' in information_schema

I then logged in again to the query browser, and this time ignored the schema request and got into my main folder. It didn't run and it showed this message

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 '[fldName] ASC' at line 1

[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 "Description" 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!