Solved

MySQL insert of TEXT data type from VB6 OLEDB/ADO

Posted on 2007-04-01
6
2,970 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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

757 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

19 Experts available now in Live!

Get 1:1 Help Now