Solved

MySQL insert of TEXT data type from VB6 OLEDB/ADO

Posted on 2007-04-01
6
2,979 Views
Last Modified: 2013-12-25
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.
0
Comment
Question by:meerkat2040
  • 4
6 Comments
 
LVL 35

Expert Comment

by:Raynard7
ID: 18833285
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
 

Author Comment

by:meerkat2040
ID: 18833478
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
 

Author Comment

by:meerkat2040
ID: 18833759
I'm using mySQL 4.1.8.
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 22

Accepted Solution

by:
NovaDenizen earned 250 total points
ID: 18836167
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
 

Author Comment

by:meerkat2040
ID: 18840735
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
 

Author Comment

by:meerkat2040
ID: 18840934
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

895 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

13 Experts available now in Live!

Get 1:1 Help Now