mySQL datatypes and updating from ASP

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?

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?

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)

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.

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?

Text datatypes in MySQL are CHAR or VARCHAR...what is longtext?

"ODBC driver does not support the requested properties." on Module.Open Query, Connect, 2, 3

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?

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,...)

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.

VARCHAR(length) [BINARY]  '<----------------should be this

http://www.mysql.com/doc/en/Creating_tables.html

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.

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.

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.

Oh, VARCHAR is limited to 255 characters.

See here:

http://www.mysql.com/doc/en/Column_types.html

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

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.

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

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

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

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.

I changed the .open statement  back to the original and got the same result.

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

I'll try now.

Oh yeah, and try this, just to see if it works:

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

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.

test3
test2
test1

Are they records in the DB?

How bout this: without brackets

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

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?

OH WAIT!

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

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

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

OK, great....

Disregard last post then!

So which code worked?

Maybe because you initialized the table with manual entires, it allowed ADDNEW after that...not sure?

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."

>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?

By the way, I really appreciate the help. I'm increasing the points because this help is so valuable.

There we go.

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?

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

>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?

I commented out the .update line, but the error still remains. The error is in the line above the .update line.

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"

the last line is line 512

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

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

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

Hmmm....what was the code that actually added testing to the DB?

One was adding TESTING, the other was adding Testing?

What was code for Testing?

Can I cuss on here? :) I deleted it.

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

Here is how it's done...try to duplicate it:

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

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?

What's with the exclamation point? I've never seen that. Do you think that is where the error lies?

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

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

Ahhh, I think that's in VB.