Solved

MySQL insert of TEXT data type from VB6 OLEDB/ADO

Posted on 2007-04-01
6
3,008 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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

Three Considerations for Containers

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read our article on Experts Exchange.

Question has a verified solution.

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

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
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 process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses

628 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