MySQL insert of TEXT data type from VB6 OLEDB/ADO

I'm creating a dictionary in mySQL and populating it through VB6.  When I try to insert a TEXT definition, I'm getting an error.  

Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=MySqlProv;Data Source=dictionary;Password=password;User ID=root;Location=localhost;"
conn.CursorLocation = adUseClient
conn.Open

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim mystream As ADODB.Stream
Set mystream = New ADODB.Stream
mystream.Type = adTypeText


rs.Open "SELECT * FROM words WHERE 1=0", conn, adOpenDynamic, adLockOptimistic
rs.AddNew
mystream.Open
mystream.LoadFromFile "C:\test.txt"

rs!word = "test"
rs!definition = mystream.Read
rs.Update
mystream.Close
rs.Close
conn.Close

When I execute this, I get a Visual Basic "Run-time error '3219': Operation is not allowed in this context.
If I comment out "rs!definition = mystream.Read", then I get another error:
Run-time error '-2147467259 (80004005)': Insufficient base table information for updating or refreshing.

I'm new to VB databases, so any help on this would be much appreciated.
meerkat2040Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raynard7Commented:
Hi,

I'm not sure why you are using this particular connection string, and why you are using streams - not just normal recordset objects,

what version of mysql are you using?

if you look at

http://dev.mysql.com/tech-resources/articles/visual-basic-datatypes.html

and follow the directions to increase the buffer length, and just cast the text value as a string it should work.
0
meerkat2040Author Commented:
Thanks.

I had copied the code from the BLOB binary data insert example on the mySQL page.  That doesn't work either.

 I thought that since the TEXT data is very long and contains special characters, that it would be better to input it as a stream.  Anyway, I removed the special characters and inserted it as a string in an conn.Execute statement.

For some reason, the definition is ending up with a bunch of extra spaces.

The inserted string looks like this:
 "<p><hw>Free</hw> <pr>(fr<emac/)</pr>, <pos>a.</pos> <amorph>[<pos>Compar.</pos> <adjf>Freer</adjf> <pr>(-<etil/r)</pr>; <pos>superl.</pos> <adjf>Freest</adjf> <pr>"

But when I go back and fetch it from the table it has a bunch of extra spaces, like this:

" < p > < hw > Free < /hw > < pr > (fr < emac/) < /pr > , < pos > a. < /pos > < amorph > [ < pos > Compar. < /pos > < adjf > Freer < /adjf > < pr > (- < etil/r) < /pr > ; < pos > superl. "

Any idea what's going on here?
0
meerkat2040Author Commented:
I'm using mySQL 4.1.8.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

NovaDenizenCommented:
I've never used visual basic.  It looks to me like you're:

1. Running a select query that will return no records.
2. Creating a recordset object to iterate over the empty result of the select query.
3. Using the recordset object to create and submit new records into the database.

This may be common practice in VB database scripts, but to my eye it's shockingly horrid. :)

When you run a select query in mysql you get a list of records back.  You can't use these records to somehow go back upstream and modify the table they came from.  SELECT is a read-only operation.

If you want to add records then you have to use an INSERT query.

INSERT INTO WORDS (word, definition) VALUES ('wordgoeshere', 'definitiongoeshere');

You should use a parameterized query here, but I don't know how to do one of those with an ADODB.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
meerkat2040Author Commented:
Yeah, I should have said that.  I removed that SELECT that brings up the empty recordset and just did it through an INSERT like you suggested.

It's wierd, when I use SQL Server, the definition inserts properly without any excess spaces, but when I use MySQL, the defintion has excessive spaces as I stated above.

Obviously, I'm new to using VB to work with databases.  I'm just using the VB string functions to parse through the tagset that's used in the public domain 1913 Webster dictionary.
0
meerkat2040Author Commented:
Looks like the spaces was a bug with the mySQL OLEDB driver.  I switched to ODBC v5 and the text comes across fine without the spaces surround the > and < signs.  Thanks for your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.