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.f ldNAME , ' > ' , nm_tbl_cate.fld NAME) AS NAME, PARENT.id, nm_tbl_cate.ID, 0 FROM nm_tbl_cate, nm_tbl_cate AS PARENT WHERE (nm_tbl_cate.fl dLEVEL = 2) AND (nm_tbl_cate.fl dPID =PARENT.ID) UNION SELECT nm_tbl_cate.ID, CONCAT(PARENT.f ldNAME , ' > ' , SUBPARENT.fldNA ME , ' > ' , nm_tbl_cate.fld NAME) 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.fl dLEVEL = 3) AND (nm_tbl_cate.fl dPID =PARENT.ID) AND (nm_tbl_cate.fl dSID =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_cat e
(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_relation s
(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_sch ema 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_sch ema

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