Solved

MySQL & Visual Basic 6.0

Posted on 2011-09-04
5
535 Views
Last Modified: 2012-05-12
I'll introduce the criteria step by step.

If you are able to achieve this using only MySQL that is OK.  
I happen to have VB 6.0, so if you need to write a routine in it i can use it.

I need to add text files from my local computer to a MySQL Database table that is online in my websites cpanel.
I have close to two hundred local text files.

Inserting the text files name into field2,
Inserting the text of the file into field3,
and inserting its ID number into field1.
The ID number is in another online table with the text files name as a reference.
The text itself is one page long.


MyDocuments Folder1: (all files in folder needed)
    FileName1.txt
        Text one page long
    FileName2.txt
        Text one page long
    FileName3.txt
        Text one page long
    .
    .
    .


NEW RECORDS NEEDED
MySQL Database Table: (insert new records.)
    Field1       Field2          Field3
       16          FileName1      Text from file  
       28          FileName2      Text from file  
         7          FileName3      Text from file  

MySQL Database ID Number Reference Table:
      ID       Description
        16       FileName1
        28       FileName2
                       7       FileName3
Thank You
0
Comment
Question by:GarySB
  • 2
  • 2
5 Comments
 
LVL 7

Expert Comment

by:Nitin Sontakke
ID: 36482314
Hello,

First of all, i hope you are aware that the on-line database will not be able to access any of resources which are local to your computer (to the best of my current knowledge) for the purpose of security.

As such you will need to generate the INSERT scripts locally taking into consideration all of the necessary data and EXECUTE these in online database - assuming you have all the necessary permissions / access details with you.

Assuming you know VB 6 or VBScript, you can write the script to a text file.

You don't have to worry about the Id column as this information with a key field is available in target database.

Your script for one file should look like:

INSERT INTO Table1 (FIELD1, FIELD2, FIELD3)
SELECT ID, 'Filename1', 'file text' FROM Table2 WHERE filename = 'Filename1';

It is assumed that the file names are unique.

Hope it helps.
0
 

Author Comment

by:GarySB
ID: 36489005
I have all required permissions to add/modify my database.

I think the best approach is to generate a script on my local computer and save it as a text file that i will paste into the online cpanl area to run and insert the records.

In VB6, can you give me a routine that will loop and read my local text files embeding the filename and text around the script syntax needed to insert records?
0
 
LVL 3

Accepted Solution

by:
nrbreen earned 250 total points
ID: 36509599

Sub write_files_to_sql()
Dim fs As Integer, fd As Integer
Dim filename As String, filenumber As Integer
Dim foldername As String, SQLfilename As String
Dim line As String

foldername = "c:\MyDocuments\"   '  Note trailing slash
SQLfilename = "inserts.sql"   ' do not include a path in this variable

fd = FreeFile
Open foldername & "inserts.sql" For Output As fd

fs = FreeFile
filename = Dir(foldername)
While filename <> ""
  If filename <> SQLfilename Then
    filenumber = filenumber + 1
    Open foldername & filename For Input As fs
        '  change the names of the tables if required : names_table, texts_table
    Print #fd, "insert into names_table values(" & filenumber & ", '" & filename & "');"
    Print #fd, "insert into texts_table values(" & filenumber & ", '" & filename & "', '";
    While Not EOF(fs)
      Line Input #fs, line
      Print #fd, Replace(line, "'", "''")    ' must handle embedded single quotes
    Wend
    Print #fd, "');"
    Close fs
  End If
  filename = Dir()
Wend
Close fd

End Sub

Open in new window

0
 
LVL 7

Assisted Solution

by:Nitin Sontakke
Nitin Sontakke earned 250 total points
ID: 36528682
Hello,

Here is the code i believe should serve the purpose. Please note, however, i have not tested it.

Private Sub Command1_Click()
    Dim fso As FileSystemObject, fld As Folder, f As File, fs As TextStream, strText As String
    Dim out As Integer, strOut As String
    out = FreeFile
    Open "D:\TempFolder\Output.sql" For Append Access Write As #out
    Set fso = New FileSystemObject
    Set fld = fso.GetFolder("D:\TempFolder\")   'Put desired folder name here.
    For Each f In fld.Files
        If InStr(1, f.Name, ".txt", vbTextCompare) <> 0 Then
            Set fs = f.OpenAsTextStream(ForReading)
            strText = fs.ReadAll
            fs.Close
        End If
        strOut = "INSERT INTO Table1 (FIELD1, FIELD2, FIELD3) SELECT ID, '" & f.Name & "', '" & Replace(strText, "'", "''") & "' FROM Table2 WHERE filename = '" & f.Name & "';"
        Print #out, strOut
    Next
    Close #out
End Sub

Open in new window


You will need to add the reference to "Microsoft Scripting Runtime" in your VB project.

Any questions, please do let me know.
0
 

Author Closing Comment

by:GarySB
ID: 36586663
Because of time i was not able to test the code and had to load manually.  I will try them out.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
xampp tool 12 56
VB6 - Scroll Mouse wheel on Picturebox 13 73
Showing random records from database 10 44
MS SQL Update query with connected table data 3 38
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 …
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

860 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