mySQL datatypes and updating from ASP

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.
SommardahlAsked:
Who is Participating?
 
alorentzConnect With a Mentor Commented:
No the exclaiamtion point is for VB, but the connection data should be the same.
0
 
alorentzCommented:
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
 
SommardahlAuthor Commented:
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
alorentzCommented:
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
 
SommardahlAuthor Commented:
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
 
alorentzCommented:
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
 
alorentzCommented:
Text datatypes in MySQL are CHAR or VARCHAR...what is longtext?
0
 
SommardahlAuthor Commented:
"ODBC driver does not support the requested properties." on Module.Open Query, Connect, 2, 3

0
 
alorentzCommented:
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
 
SommardahlAuthor Commented:
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
 
SommardahlAuthor Commented:
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
 
alorentzCommented:
VARCHAR(length) [BINARY]  '<----------------should be this

http://www.mysql.com/doc/en/Creating_tables.html
0
 
alorentzCommented:
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
 
alorentzCommented:
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
 
SommardahlAuthor Commented:
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
 
alorentzCommented:
Oh, VARCHAR is limited to 255 characters.

See here:

http://www.mysql.com/doc/en/Column_types.html
0
 
alorentzCommented:
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
 
SommardahlAuthor Commented:
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
 
SommardahlAuthor Commented:
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
 
alorentzCommented:
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
 
alorentzCommented:
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
 
SommardahlAuthor Commented:
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
 
SommardahlAuthor Commented:
I changed the .open statement  back to the original and got the same result.
0
 
alorentzCommented:
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
 
SommardahlAuthor Commented:
I'll try now.
0
 
alorentzCommented:
Oh yeah, and try this, just to see if it works:

Query = "INSERT INTO [Module] ([title]) VALUES ('TESTING')"
connect.execute(query)
0
 
SommardahlAuthor Commented:
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
 
alorentzCommented:
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
 
SommardahlAuthor Commented:
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
 
SommardahlAuthor Commented:
OH WAIT!

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

THIS WORKED! That's why there is a "Testing" after each "TESTING".
0
 
alorentzCommented:
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
 
alorentzCommented:
OK, great....

Disregard last post then!
0
 
alorentzCommented:
So which code worked?
0
 
alorentzCommented:
Maybe because you initialized the table with manual entires, it allowed ADDNEW after that...not sure?
0
 
SommardahlAuthor Commented:
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
 
alorentzCommented:
>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
 
SommardahlAuthor Commented:
By the way, I really appreciate the help. I'm increasing the points because this help is so valuable.
0
 
SommardahlAuthor Commented:
There we go.
0
 
alorentzCommented:
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
 
SommardahlAuthor Commented:
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
 
alorentzCommented:
>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
 
SommardahlAuthor Commented:
I commented out the .update line, but the error still remains. The error is in the line above the .update line.
0
 
SommardahlAuthor Commented:
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
 
SommardahlAuthor Commented:
the last line is line 512
0
 
alorentzCommented:
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
 
alorentzCommented:
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
 
SommardahlAuthor Commented:
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
 
alorentzCommented:
Hmmm....what was the code that actually added testing to the DB?
0
 
alorentzCommented:
One was adding TESTING, the other was adding Testing?

What was code for Testing?
0
 
SommardahlAuthor Commented:
Can I cuss on here? :) I deleted it.
0
 
alorentzCommented:
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
 
alorentzCommented:
Here is how it's done...try to duplicate it:

http://www.mysql.com/products/myodbc/manual.html#ADO_-_rs.addNew
0
 
SommardahlAuthor Commented:
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
 
SommardahlAuthor Commented:
What's with the exclamation point? I've never seen that. Do you think that is where the error lies?
0
 
alorentzCommented:
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
 
SommardahlAuthor Commented:
Ahhh, I think that's in VB.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.