By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,086 Members | 1,919 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,086 IT Pros & Developers. It's quick & easy.

Mysql error after migrating from Ms Access on an asp page

P: 20

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




Expand|Select|Wrap|Line Numbers
  1. <% ' Dim SQL_REC, RS_REC, NID, TITLE, POSTED, NEWS_LISTING, SHOW_HL, MyConn_REC, I_REC, SUMMARY
  2.  
  3.  
  4.  
  5.     ' ------------------------------------------------------------------------------------------------------------------
  6.  
  7.         ' DATABASE CONNECTION
  8.    Set MyConn = Server.CreateObject("ADODB.Connection")
  9.                 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
Oct 16 '08 #1
Share this Question
Share on Google+
12 Replies


P: 20
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.


Expand|Select|Wrap|Line Numbers
  1. Set MyConn = Server.CreateObject("ADODB.Connection")
  2.     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:

Expand|Select|Wrap|Line Numbers
  1.     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
Oct 16 '08 #2

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

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

Expand|Select|Wrap|Line Numbers
  1.  
  2.         RS.Open SQL, MyConn
i added this in front of it, as i read somewhere that it would show the problem,

Expand|Select|Wrap|Line Numbers
  1. response.write(sql)
  2. 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
Oct 16 '08 #3

codegecko
Expert 100+
P: 533
Hi there,

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

codegecko
Oct 16 '08 #4

P: 20
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
Oct 17 '08 #5

P: 20
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
Oct 17 '08 #6

codegecko
Expert 100+
P: 533
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.
Oct 17 '08 #7

P: 20
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
Oct 17 '08 #8

codegecko
Expert 100+
P: 533
This bit:

Expand|Select|Wrap|Line Numbers
  1. SELECT ID, fldNAME, ID as ID1, 0 as ID2, 0 as ID3 FROM nm_tbl_cate WHERE fldLEVEL = 1
  2. UNION
  3. 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)
  4. UNION
  5. 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)
  6. ORDER BY [fldName] ASC
  7.  
This is a SQL query - SQL stands for Structured Query Language.

codegecko
Oct 17 '08 #9

P: 20
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
Oct 17 '08 #10

codegecko
Expert 100+
P: 533
Try removing the square brackets from your query and try running the code again.

codegecko
Oct 17 '08 #11

P: 20
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

Expand|Select|Wrap|Line Numbers
  1. 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?
Oct 17 '08 #12

codegecko
Expert 100+
P: 533
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!
Oct 31 '08 #13

Post your reply

Sign in to post your reply or Sign up for a free account.