Solved

mySQL datatypes and updating from ASP

Posted on 2004-03-20
56
729 Views
Last Modified: 2012-05-04
URL: http://www.schoolcentrix.com/admin/upgrade-mysql.asp

I get the following error:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Driver Manager] Invalid string or buffer length
/admin/upgrade-mysql.asp, line 512

Here's my code:
SET Module = CreateObject("ADODB.RecordSet")
Query = "SELECT * FROM Module WHERE Title = '" & modules(a) & "'"
Module.Open Query, Connect, adOpenStatic, adLockOptimistic, adCmdText
Module.AddNew
Module("Title") = strModules
Module.Update 'Line 512

Datatypes:
In mySQL, the title col is type longtext. strModules is a simple string (14 characters long). There are more cols in the table, all int's and longtext's.

I don't understand why I am having this much trouble with datatypes. There must be something small that I am missing.
0
Comment
Question by:Sommardahl
  • 30
  • 26
56 Comments
 
LVL 31

Expert Comment

by:alorentz
ID: 10642434
Why are you selecting a record and adding a record at the same time?  What is supposed to happen?  Are you adding a record or updating a record?
0
 

Author Comment

by:Sommardahl
ID: 10642446
SET Module = CreateObject("ADODB.RecordSet")
Query = "SELECT * FROM Module WHERE Title = '" & modules(a) & "'"
Module.Open Query, Connect, adOpenStatic, adLockOptimistic, adCmdText
Module.AddNew
Module("Title") = strModules
Module.Update 'Line 512

I will probably not use correct language or terminology, but the way I understand it is this: The SELECT statement places the records in the recordset, Module. Then, I use Module.AddNew to create a new record, add a value to the TITLE column, then "attempt" to module.update the recordset.

I have never learned to use the OTHER sql commands like UPDATE, INSERT, or ALTER. I don't really care to either, since this method has worked for me thusfar.

So, back to my original question... any takers?
0
 
LVL 31

Expert Comment

by:alorentz
ID: 10642456
No need for the attitude, remember you're the one asking for help.  All I did was ask a question to better establish your goals!

Query = "INSERT INTO [Module] ([title]) VALUES ('" & strModules & "')"
connect.execute(query)
0
 

Author Comment

by:Sommardahl
ID: 10642473
No attitude... I had no idea you were going to stick with it (still new to experts-exchange). Thanks for sticking with me. I would like to make this work with my current method without having to use the INSERT statement. I have over 50,000 lines of code using this method and I'm stying to make the switch from Access to MySQL without working myself to death. It all works FINE with MS-Access.

I'm certain the problem lies in a difference in datatypes. Check out the original post and see what you think. This must be a certain "pickiness" that mySQL has that Access doesn't.
0
 
LVL 31

Expert Comment

by:alorentz
ID: 10642483
Try this to test:

SET Module = CreateObject("ADODB.RecordSet")
Query = "SELECT * FROM Module where [TITLE] = 'NONE123'"  '<----------create empty recordset to add records...quicker this way!
Module.Open Query, Connect, 2, 3
Module.AddNew
Module("Title") = "Testing"
Module.Update


Any errors?
0
 
LVL 31

Expert Comment

by:alorentz
ID: 10642489
Text datatypes in MySQL are CHAR or VARCHAR...what is longtext?
0
 

Author Comment

by:Sommardahl
ID: 10642494
"ODBC driver does not support the requested properties." on Module.Open Query, Connect, 2, 3

0
 
LVL 31

Expert Comment

by:alorentz
ID: 10642496
This then::

SET Module = CreateObject("ADODB.RecordSet")
Query = "SELECT * FROM Module where [TITLE] = 'NONE123'"  '<----------create empty recordset to add records...quicker this way!
Module.Open Query, Connect, adOpenStatic, adLockOptimistic, adCmdText
Module.AddNew
Module("Title") = "Testing"
Module.Update


Any errors?
0
 

Author Comment

by:Sommardahl
ID: 10642499
mySQL Website: http://www.mysql.com/documentation/mysql/bychapter/manual_SQL_Syntax.html#CREATE_TABLE

type:
    TINYINT[(length)] [UNSIGNED] [ZEROFILL]
  | SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
  | MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
  | INT[(length)] [UNSIGNED] [ZEROFILL]
  | INTEGER[(length)] [UNSIGNED] [ZEROFILL]
  | BIGINT[(length)] [UNSIGNED] [ZEROFILL]
  | REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL]
  | NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL]
  | CHAR(length) [BINARY | ASCII | UNICODE]
  | VARCHAR(length) [BINARY]
  | DATE
  | TIME
  | TIMESTAMP
  | DATETIME
  | TINYBLOB
  | BLOB
  | MEDIUMBLOB
  | LONGBLOB
  | TINYTEXT
  | TEXT
  | MEDIUMTEXT
  | LONGTEXT <------------------------------------------------------------------ (Here's where I got it from)
  | ENUM(value1,value2,value3,...)
  | SET(value1,value2,value3,...)
0
 

Author Comment

by:Sommardahl
ID: 10642508
SET Module = CreateObject("ADODB.RecordSet")
Query = "SELECT * FROM Module where [TITLE] = 'NONE123'"  '<----------create empty recordset to add records...quicker this way!
Module.Open Query, Connect, adOpenStatic, adLockOptimistic, adCmdText
Module.AddNew
Module("Title") = "Testing"
Module.Update

I used this and got the same result. The only difference is the Query String.
0
 
LVL 31

Expert Comment

by:alorentz
ID: 10642511
VARCHAR(length) [BINARY]  '<----------------should be this

http://www.mysql.com/doc/en/Creating_tables.html
0
 
LVL 31

Expert Comment

by:alorentz
ID: 10642515
Module("Title") = "Testing" '<---------this was different...should hardcode all elements when debugging.

I think there's a problem with the LONGTEXT datatype.  May want to make it VARCHAR, and try again.
0
 
LVL 31

Expert Comment

by:alorentz
ID: 10642522
LONGTEXT is not right...should be VARCHAR

LONGTEXT  -
A BLOB or TEXT column with a maximum length of 4294967295 or 4GB (2^32 - 1) characters. See section 13.2.5.1 Silent Column Specification Changes. Up to MySQL version 3.23 the client/server protocol and MyISAM tables had a limit of 16MB per communication packet / table row, from version 4.x, the maximum allowed length of LONGBLOB or LONGTEXT columns depends on the configured maximum packet size in the client/server protocol and available memory. See section 11.3.2 The BLOB and TEXT Types.
0
 

Author Comment

by:Sommardahl
ID: 10642529
I'll do that now. Will this VARCHAR give me the same results as, say, a "memo" datatype in MS-Access?

Also, I have numeric values I need to be able to put into this DB... my first instinct was to use the INT datatype (since the range of acceptable values fell WELL within my needs.

I have regular text items as well, which don't require as much space as the MEMO datatype provided... I assumed to use the TEXT datatype.

Do you have any wisdom to impart in that direction?

I'm changing the datatype now and testing.
0
 
LVL 31

Expert Comment

by:alorentz
ID: 10642533
Oh, VARCHAR is limited to 255 characters.

See here:

http://www.mysql.com/doc/en/Column_types.html
0
 
LVL 31

Expert Comment

by:alorentz
ID: 10642546
The TEXT, MEDIUMTEXT, and LONGTEXT types are normally associated with the Access memo datatype. Their sizes are 65K, 16M, and really big(~4Bil), accordingly.  

You intially said 14 character, so figure VARCHAR would be better.  But may need LONGTEXT if it's a memo field.


What is the error message with this?: use this...

SET Module = CreateObject("ADODB.RecordSet")
Query = "SELECT * FROM Module"  '<----------create empty recordset to add records...quicker this way!
Module.Open Query, Connect, adOpenDynamic, adLockOptimistic
Module.AddNew
Module("Title") = "Testing"
Module.Update
0
 

Author Comment

by:Sommardahl
ID: 10642550
I tried it. It still stops at the same Module.Open line. I replaced yours with my original query string, and it got past the open statement. Now it stops at the Module("Title") = "Testing" statement. Here's the error:

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

0
 

Author Comment

by:Sommardahl
ID: 10642554
SET Module = CreateObject("ADODB.RecordSet")
Query = "SELECT * FROM Module"  '<----------create empty recordset to add records...quicker this way!
Module.Open Query, Connect, adOpenDynamic, adLockOptimistic
Module.AddNew
Module("Title") = "Testing" '**************** Line 506
Module.Update

Same results as my last message:

Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
/admin/upgrade-mysql.asp, line 506
0
 
LVL 31

Expert Comment

by:alorentz
ID: 10642558
Note: this error can also be an ODBC driver problem?

Can you select and display records from MySQL?  Just write value to a page from a recordset...

You need to make sure you can connect first?

SET Module = CreateObject("ADODB.RecordSet")
Query = "SELECT * FROM Module"  '<----------create empty recordset to add records...quicker this way!
Module.Open Query, Connect, adOpenDynamic, adLockOptimistic

if not module.eof then
   response.write "Value:" & module.fields[0].value
end if
0
 
LVL 31

Expert Comment

by:alorentz
ID: 10642563
Actually this:  we'll go back to th original open statement.

SET Module = CreateObject("ADODB.RecordSet")
Query = "SELECT * FROM Module"  '<----------create empty recordset to add records...quicker this way!
Module.Open Query, Connect, adOpenStatic, adLockOptimistic, adCmdText

if not module.eof then
   response.write "Value:" & module.fields[0].value
end if
0
 

Author Comment

by:Sommardahl
ID: 10642573
Unfortunately, there is no data in the database for me to display. After programatically creating the database, this part of this app is the FIRST place where it tries to place data into the DB.

I think I am connecting, since the error generally comes after the .open statement.
0
 

Author Comment

by:Sommardahl
ID: 10642577
I changed the .open statement  back to the original and got the same result.
0
 
LVL 31

Expert Comment

by:alorentz
ID: 10642583
Can't you enter data through MySQLAdmin or whatever interface you're using?

Some examples of adding records:

http://forums.aspfree.com/archive/t-20266
http://dbforums.com/arch/5/2003/2/682190
http://www.aspcode.it/forum/forum.asp?tab=2&cod=747
0
 

Author Comment

by:Sommardahl
ID: 10642586
I'll try now.
0
 
LVL 31

Expert Comment

by:alorentz
ID: 10642595
Oh yeah, and try this, just to see if it works:

Query = "INSERT INTO [Module] ([title]) VALUES ('TESTING')"
connect.execute(query)
0
 

Author Comment

by:Sommardahl
ID: 10642605
The INSERT statement caused the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[MySQL][ODBC 3.51 Driver][mysqld-3.23.58]You have an error in your SQL syntax near '[Module] ([title]) VALUES ('TESTING')' at line 1
/admin/upgrade-mysql.asp, line 506

I was able to insert data into the Module table.

Here's the URL to view the results:
http://www.schoolcentrix.com/admin/upgrade-mysql.asp

So, I am able to get data back out of the DB.
0
 
LVL 31

Expert Comment

by:alorentz
ID: 10642611
test3
test2
test1

Are they records in the DB?

How bout this: without brackets

Query = "INSERT INTO Module (title) VALUES ('TESTING')"
connect.execute(query)
0
 

Author Comment

by:Sommardahl
ID: 10642617
Yes, they are records. Your new statement worked fine, as you can see: http://www.schoolcentrix.com/admin/upgrade-mysql.asp

So, will there be a way to make it work using my original method?
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:Sommardahl
ID: 10642622
OH WAIT!

Module.AddNew
Module("Title") = "Testing"
Module.Update

THIS WORKED! That's why there is a "Testing" after each "TESTING".
0
 
LVL 31

Expert Comment

by:alorentz
ID: 10642625
I see...that's good!

Try this:

SET Module = CreateObject("ADODB.RecordSet")
Module.Open "MODULE", Connect, adOpenKeySet, adLockOptimistic, adCmdTable
Module.AddNew
Module("Title") = strModules
Module.Update
0
 
LVL 31

Expert Comment

by:alorentz
ID: 10642627
OK, great....

Disregard last post then!
0
 
LVL 31

Expert Comment

by:alorentz
ID: 10642630
So which code worked?
0
 
LVL 31

Expert Comment

by:alorentz
ID: 10642634
Maybe because you initialized the table with manual entires, it allowed ADDNEW after that...not sure?
0
 

Author Comment

by:Sommardahl
ID: 10642646
It never stopped at .addnew... it always stopped at .update.

Like the current problem. At Module("Title") = "Testing", I get "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."
0
 
LVL 31

Expert Comment

by:alorentz
ID: 10642651
>It never stopped at .addnew... it always stopped at .update

That's what I meant.....UPDATE is the only thing that actuallky does anything..so it will stop at update for any errors within the ADDNEW block.

>Like the current problem. At Module("Title") = "Testing",

What code is adding the Testing record then?
0
 

Author Comment

by:Sommardahl
ID: 10642653
By the way, I really appreciate the help. I'm increasing the points because this help is so valuable.
0
 

Author Comment

by:Sommardahl
ID: 10642654
There we go.
0
 
LVL 31

Expert Comment

by:alorentz
ID: 10642659
I'm not to familiar with MySQL actually...maybe you can take out the module.update line and it will add the record appropriately with just the ADDNEW line?
0
 

Author Comment

by:Sommardahl
ID: 10642663
I commented the "INSERT INTO Module (title) VALUES ('TESTING " & TIME & "')" and connect.execute out so that we could test more accurately. Right now, after deleting all the testing records, the only thing adding that hard-coded "testing" is the following code:

Module.AddNew
Module("Title") = "Testing" ' *************** Line 512
Module.Update

Which causes the following error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
/admin/upgrade-mysql.asp, line 512
0
 
LVL 31

Expert Comment

by:alorentz
ID: 10642667
>Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

This error is due to the recordset no being opened appropriately.  What is the entire current code block?
0
 

Author Comment

by:Sommardahl
ID: 10642671
I commented out the .update line, but the error still remains. The error is in the line above the .update line.
0
 

Author Comment

by:Sommardahl
ID: 10642673
SET Module = CreateObject("ADODB.RecordSet")
Query = "SELECT * FROM Module"  '<----------create empty recordset to add records...quicker this way!
Module.Open Query, Connect, adOpenStatic, adLockOptimistic, adCmdText
'Query = "INSERT INTO Module (title) VALUES ('TESTING " & TIME & "')"
'connect.execute(query)
DO UNTIL Module.EOF
      Response.Write(Module("Title") & "<br>")
      Module.Movenext
LOOP
Module.AddNew
Module("Title") = "Testing"' added VIA Module.Update"
0
 

Author Comment

by:Sommardahl
ID: 10642676
the last line is line 512
0
 
LVL 31

Expert Comment

by:alorentz
ID: 10642677
Try this: comment out the rest....

SET Module = CreateObject("ADODB.RecordSet")
Module.Open "MODULE", Connect, adOpenKeySet, adLockOptimistic, adCmdTable
Module.AddNew
Module("Title") = strModules
Module.Update
0
 
LVL 31

Expert Comment

by:alorentz
ID: 10642680
Damn, too much code flying around:

This is what you should try:

SET Module = CreateObject("ADODB.RecordSet")
Module.Open "MODULE", Connect, adOpenKeySet, adLockOptimistic, adCmdTable
Module.AddNew
Module("Title") = "Testing"
Module.Update

0
 

Author Comment

by:Sommardahl
ID: 10642683
Module.Open "MODULE", Connect, adOpenKeySet, adLockOptimistic, adCmdTable

Results in:
Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
ODBC driver does not support the requested properties.
/admin/upgrade-mysql.asp, line 503

0
 
LVL 31

Expert Comment

by:alorentz
ID: 10642689
Hmmm....what was the code that actually added testing to the DB?
0
 
LVL 31

Expert Comment

by:alorentz
ID: 10642695
One was adding TESTING, the other was adding Testing?

What was code for Testing?
0
 

Author Comment

by:Sommardahl
ID: 10642696
Can I cuss on here? :) I deleted it.
0
 
LVL 31

Expert Comment

by:alorentz
ID: 10642713
Hmm...tough one.

no undo code huh...oh well.

Try this:

SET Module = CreateObject("ADODB.RecordSet")
Query = "SELECT * FROM Module WHERE Title = '" & modules(a) & "'"
Module.Open Query, Connect, adOpenKeySet, adLockOptimistic, adCmdText
Module.AddNew
Module("Title") = "Testing"
Module.Update
0
 
LVL 31

Expert Comment

by:alorentz
ID: 10642722
Here is how it's done...try to duplicate it:

http://www.mysql.com/products/myodbc/manual.html#ADO_-_rs.addNew
0
 

Author Comment

by:Sommardahl
ID: 10642723
I got more of the same. I have an idea. If it doesn't interest you, we'll keep on doing what we're doing until you get sick of it and go to bed.

If you are pretty sure you would be able to eventually figure it out, I'd like to pay you through paypal to help me with this. This website is nice, but we're wearing out the refresh button. I have AOL and MSN IM. We could work on this there and I can send you files, etc. What do you say?
0
 

Author Comment

by:Sommardahl
ID: 10642726
What's with the exclamation point? I've never seen that. Do you think that is where the error lies?
0
 
LVL 31

Expert Comment

by:alorentz
ID: 10642730
That's not permitted on the forum, but I cannot stop you from reviewing my profile and finding my contact information ;-)

http://www.experts-exchange.com/Web/Web_Languages/ASP/M_1976286.html
0
 

Author Comment

by:Sommardahl
ID: 10642732
Ahhh, I think that's in VB.
0
 
LVL 31

Accepted Solution

by:
alorentz earned 350 total points
ID: 10642733
No the exclaiamtion point is for VB, but the connection data should be the same.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now