Solved

Inserting Text Paragraphs into MySQL DB from VB.NET

Posted on 2011-03-23
1
600 Views
Last Modified: 2012-05-11
I am trying to load portions of 300,000KB files into a table of MySQL database. The portions may contain many pages of news articles with various punctuation elements, including parethesis, quotes and semicolons.

I attempted to compile an "Insert" statement to insert the text, but for reasons realted to punctuation that failed miserably.  

Another approach I am considering is to dump the text into another data file "file.data" and initiate a bulk insert operation. The problem with that approach is I have many 300,000KB files I need to process and reading such large files from the hard drive, loading portions of these files in memory, creating temp file on the harddrive for bulk operations, then having MySQL Server read the temp files from the Hard Drive seens very slow and redundant.

There is got to be a simple way of sending data that is already stored in an object in memory as a packet to MySql Server for processing...

The points will be awarded to an expert who can advise how to efficiently load text stored in VB.NET StringCollection object into a MySQL DB table column of type "Text", while preserving the original formatting of the text.

Thank you.
0
Comment
Question by:cyber-33
1 Comment
 
LVL 74

Accepted Solution

by:
käµfm³d   👽 earned 500 total points
ID: 35202998
The only "punctuation" you should have to worry about when inserting text data into the DB is the single quote ( ' ) since that's what SQL Server uses to denote strings. All you should need to do is double-up single quotes in your source text. This is known as "escaping" and causes such a quote to be considered part of the data and not a string "marker." You could use a simple Replace() to do this:

    data = data.Replace("'", "''")  ' one single quote replaced by two singe quotes; all occurrence ls
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Creating and Managing Databases with phpMyAdmin in cPanel.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

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