| Author |
 Topic  |
|
seahorse
Senior Member
USA
1075 Posts |
 Posted - 11 August 2003 : 23:43:19
|
Hi Folks,
I've got an SQL query that runs fine on my test machine using an Access DB, but when I try it out on a DB I exported from Access to mySQL, I get the following error message.
quote:
Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
ODBC driver does not support the requested properties.
I looked up the error message on the MS web site and it says:
quote:
80040E21 OLE DB Specific: The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.
The thing is, I'm not trying to insert any data on this query. Anyone have any idea what's going on? I'm lost.
|
Ken
===============
Worldwide Partner Group
Microsoft |
|
|
RichardKinser
Snitz Forums Admin
USA
16655 Posts |
Posted - 12 August 2003 : 00:39:22
|
| Can you post the query? |
 |
|
|
seahorse
Senior Member
USA
1075 Posts |
Posted - 12 August 2003 : 02:44:44
|
Here you go. I've got a table with aircraft information and I'd like to pull links related to the aircraft when visitors access the aircraft details page. This is the SQL I came up with. It may not be pretty, but it worked on Access. 
planeSQL = "SELECT "
planeSQL = planeSQL & "l.id, l.link_url, l.link_text "
planeSQL = planeSQL & "FROM "
planeSQL = planeSQL & "tblCATEGORY c, "
planeSQL = planeSQL & "tblAIRCRAFT_CAT ac, "
planeSQL = planeSQL & "tblLINK_CATEGORY lc, "
planeSQL = planeSQL & "tblLINK l "
planeSQL = planeSQL & "WHERE "
planeSQL = planeSQL & "c.stype = 'aircraft'"
planeSQL = planeSQL & " AND "
planeSQL = planeSQL & "ac.category_id = c.id "
planeSQL = planeSQL & "AND "
planeSQL = planeSQL & "ac.aircraft_id = " & aircraft
planeSQL = planeSQL & "AND "
planeSQL = planeSQL & "l.id = lc.id_link "
planeSQL = planeSQL & "AND "
planeSQL = planeSQL & "lc.id_category = c.id "
planeSQL = planeSQL & "AND "
planeSQL = planeSQL & "l.delete_flag = '0' "
planeSQL = planeSQL & "ORDER BY l.link_date DESC" |
Ken
===============
Worldwide Partner Group
Microsoft |
Edited by - seahorse on 12 August 2003 02:54:56 |
|
|
|
RichardKinser
Snitz Forums Admin
USA
16655 Posts |
Posted - 12 August 2003 : 04:04:22
|
in one place you have:
"c.stype = 'aircraft'"
and in another you have:
"ac.aircraft_id = " & aircraft
is that supposed to be like that? |
|
|
|
seahorse
Senior Member
USA
1075 Posts |
Posted - 12 August 2003 : 04:32:50
|
Yes.
ac.aircraft_id = " & aircraft
This aircraft is a variable. The other is one of the category items in the category table.
I guess that's a little confusing, huh?
|
Ken
===============
Worldwide Partner Group
Microsoft |
|
|
|
OneWayMule
Dev. Team Member & Support Moderator
Austria
4969 Posts |
|
|
seahorse
Senior Member
USA
1075 Posts |
Posted - 12 August 2003 : 04:43:26
|
Yes. However, it works either way on Access.
I'm not sure if that's the problem on mySQL. I'll give it a try....
No, didn't work. Still get the same error.
|
Ken
===============
Worldwide Partner Group
Microsoft |
Edited by - seahorse on 12 August 2003 05:16:10 |
|
|
|
Davio
Development Team Member
Jamaica
12217 Posts |
Posted - 12 August 2003 : 07:14:19
|
How are you executing the query?
What version of MyODBC are you using? |
Support Snitz Forums
|
|
|
|
seahorse
Senior Member
USA
1075 Posts |
Posted - 12 August 2003 : 11:10:38
|
| I'm not sure. I'd have to ask Gremlin. He's hosting my site. |
Ken
===============
Worldwide Partner Group
Microsoft |
|
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 13 August 2003 : 00:21:00
|
latest myODBC drivers .. but doesn't this look a little suspicious ?
quote:
Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
That looks like the OLEDB Driver being used to me, not a mySQL one. |
Kiwihosting.Net - The Forum Hosting Specialists
|
|
|
|
seahorse
Senior Member
USA
1075 Posts |
Posted - 13 August 2003 : 11:58:21
|
I've double checked. This is what I'm using for my connection string. Copied it from the Snitz config file, if I recall correctly.
"driver=MySQL;server=000.000.000.000;uid=id;pwd=password;database=database" '## MySQL
I'm still lost.  |
Ken
===============
Worldwide Partner Group
Microsoft |
Edited by - seahorse on 13 August 2003 11:59:56 |
|
|
|
Davio
Development Team Member
Jamaica
12217 Posts |
Posted - 13 August 2003 : 13:46:44
|
Is that all of the error that you get? You don't get any line numbers or anything?
Can you post a link to a txt version of your file so we can take a look at the whole code?
Another thing you can try is, put Response.Write planeSql
Response.End right before the query is executed. Copy the sql code that is printed in your browser. Log into your phpmyadmin console. (I know Gremlin's hosting provides you with this). Click on your database from the left hand side. On the right hand side, you sould see a Tab called SQL, click it. It then should give you a text box where you can enter your own sql query. Paste the sql code you copied earlier into the text box. Then execute it.
If it doesn't give you an error, then your sql code is ok. |
Support Snitz Forums
|
|
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 13 August 2003 : 21:48:16
|
seahorse, try using
driver={MySQL ODBC 3.51 Driver} |
Kiwihosting.Net - The Forum Hosting Specialists
|
|
|
|
seahorse
Senior Member
USA
1075 Posts |
Posted - 14 August 2003 : 11:09:29
|
quote:
Originally posted by Davio
Log into your phpmyadmin console. (I know Gremlin's hosting provides you with this). Click on your database from the left hand side. On the right hand side, you sould see a Tab called SQL, click it. It then should give you a text box where you can enter your own sql query. Paste the sql code you copied earlier into the text box. Then execute it.
That's a great idea. I should have thought about that... I've tried it and it works when you try thw SQL on the DB, which is great! So...what does that mean then? Is this an ASP problem/driver problem?
|
Ken
===============
Worldwide Partner Group
Microsoft |
|
|
|
seahorse
Senior Member
USA
1075 Posts |
Posted - 14 August 2003 : 11:13:08
|
Gremlin,
quote:
driver={MySQL ODBC 3.51 Driver}
how would I go about using this? Just use this text in place of my regular driver string? Don't I need to specify the DB, uid, or password when using this?
Also I just wanted to say to all of you, thanks for all of the help. I really appreciate it. |
Ken
===============
Worldwide Partner Group
Microsoft |
|
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 14 August 2003 : 11:14:43
|
| sorry I should have clarified a little more :), just replace the driver={mySQL} part of the connection string with what I posted above |
Kiwihosting.Net - The Forum Hosting Specialists
|
|
|
|
Topic |
|
Topic Locked
