Solved

Insert Records in MySQL database using VB 6.0

Posted on 2011-09-06
16
381 Views
Last Modified: 2012-05-12
If I can get the VB code to read all files in a folder and then print its Filename & Contents to a new file and loop until done, that would be half the work.  After I would need from a MySQL writer the code to embed around each section to insert each one as a new record.  I will then re-write the VB6.0 code with the MySQL instructions to achieve adding all my text files to a table as described below.

I have a text file on my local computer that i want to add its file name and contents into a new record.

File Name needs to go into Field2 and the actual text needs to go into Field3.

This will require two steps since I cannot read my local files in cpanel from my computer i want to create the required file locally in a text file that i will copy and execute in cpanel.

Using VB 6.0 Create a new text file. ( I have about 200 text files, so it needs to loop).  
Read first text file.
Open & write required MySQL syntax to insert record embeding File Name to Field2 and the text contents to field3.
Loop to read all text files.
Print out to new text file.  Now open and copy new text file into procedure section to insert new records in cpanel.
0
Comment
Question by:GarySB
  • 9
  • 7
16 Comments
 
LVL 45

Expert Comment

by:aikimark
ID: 36498035
@GarySB

Do you know how to program?
What code exists so far?
0
 

Author Comment

by:GarySB
ID: 36498154
I have some VB code that will read a text file with a specific name.
I need to read all the text files in my folder regardless of the name and write it out to a new text file with the necessary MySQL instructions around the Filename and text so it can be used to insert the records into a table as explained above.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 36498307
post your code.
0
 

Author Comment

by:GarySB
ID: 36498746
Will have to follow-up tonight.  It is on a different computer.
Basically it sets a string variable to a name and then opens that file.
sets a on error line to resume next and close
It reads in the lines using a for next loop one at a time assigning the text lines to a dim variable.
0
 

Author Comment

by:GarySB
ID: 36498828
Since it has just basic vb code going back to vb 3.0, why do i need to post it?
0
 
LVL 45

Expert Comment

by:aikimark
ID: 36498913
EE is here to help you, not to do work for you.  This isn't rent-a-coder.
0
 

Author Comment

by:GarySB
ID: 36499276
I'll post the code tonight like i said.
Regarding rent-a-coder, every solution i have seen is made up of a routine to get the problem solved.  I'm not asking to write a program, just a loop to read the text files.  If making it with the MySQL is making it seem more difficult, at the very first description i said this could be solved by first just solving the vb loop to read the text files.  I will post a seperate question for the MySQL insert instructions and place it around the vb loop to do what i need, Thank You
0
 
LVL 45

Expert Comment

by:aikimark
ID: 36499286
a file iteration loop will usually involve either the Dir() function or use the FileSystemObject (part of the Microsoft Scripting Runtime library).  

It is also possible to use a FileListbox if you have one on your form.  I did this when I needed to present the files in name order, since the FileListbox automatically sorts the files by name.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:GarySB
ID: 36499391
Thank You, Yes, i have a FileListbox.  .
0
 

Author Comment

by:GarySB
ID: 36504180
This is the code i have.  I added some comments around it.

Global KTYP$(120)

On Error GoTo 1414
E$="LessonName.txt"

Open App.Path & "\LESSONS\" & E$ For Input Shared As #1

For X% = 1 To 999
Input #1, KTYP$(X%)
Next X%
1414 Resume 1415
1415 On Error GoTo 0
Close #1
AmountOfLines% = X% - 1

Now I need to write this lesson to a new text file that will contain all text files and add the MySQL something like this.

Open App.Path & "\LESSONS\MySQL_File" For Output Shared As #1

Print MySQL Insert instructions
Print E$ into Field2
For X% = 1 To AmountOfLines%
Print #1, KTYP$(X%)  MySQL to send it 'INTO FIELD3
Next X%

LOOP TO READ NEXT TEXT FILE
FINISHED, CLOSE #1
0
 
LVL 45

Expert Comment

by:aikimark
ID: 36504427
It looks like you are writing lines to a file called MySQL_File.  Does this represent an output other than to the MySQL database?

Do you want to read all the lines or just the first 120 lines?

Do you need to preserve the lines from the input files in the MySQL record?

Do you need to output the Insert SQL statements or do you need to update the MySQL database?
0
 

Author Comment

by:GarySB
ID: 36505363
It looks like you are writing lines to a file called MySQL_File.  Does this represent an output other than to the MySQL database?
  CREATING THIS FILE IS TOTALY TRANSPARENT TO THE MYSQL DATABASE UNTIL I COPY AND PASTE IT INTO THE PROCEDURE WORKING AREA TO EXECUTE IT.  
THE ACTUAL MYSQL DATABASE HAS A WORKING AREA TO WRITE PROCEDURES TO EDIT THE TABLES.  BECAUSE OF THE AMOUNT OF TEXT FILES I HAVE,  I AM LOOKING TO CREATE THIS INSERT PROCEDURE IN A TEXT FILE THAT I CAN COPY AND PASTE INTO THE PROCEDURE AREA AND EXECUTE TO INSERT MY TEXT FILES INTO NEW RECORDS.  

Do you want to read all the lines or just the first 120 lines?
  ALL LINES NEED TO BE READ LINE BY LINE OR AS A WHOLE.

Do you need to preserve the lines from the input files in the MySQL record?
  YES, BUT THAT WILL  BE AFTER THE TEXT FILE WITH THE INSERT INSTRUCTIONS AND MY TEXT IS READY TO TRY. I HAVE ONE TABLE CALLED LESSON_TBL.  FIELD2 NEEDS TO HAVE THE TEXT FILE NAME FROM MY FOLDER AND FIELD3 NEEDS TO HAVE THE TEXT ITSELF (ALL LINES).

Do you need to output the Insert SQL statements or do you need to update the MySQL database?
    I WILL USE THE NEW FILE CONTAINING ALL OF MY TEXT FILES TO UPDATE THE MYSQL DATABASE.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 36506877
Why not just update the database directly from the program?
0
 

Author Comment

by:GarySB
ID: 36522978
You make that sound so easy.
I thought it would be simpler to use the procedure area to drop the text into.  Since that is where the database can be edited from.  I think it will get to complicated if i try to do it from my program, connection issues i'll have to ask you about.  I'll see if someone will provide a way to combine many text files into one and then place the insert instructions around it so i can drop it into the procedure area of the database to update my table.  Perhaps in the future a better solution can be created as you are suggesting.  Right now i just need to get the database updated, Thank You
0
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
ID: 36526160
This should produce a file that you can test against your test database.

Option Explicit


Public Sub GenerateInsertStatements()
    Dim strFilename As String
    Dim intFN As Integer
    Dim intOutFN As Integer
    Dim strFileData As String
    
    intOutFN = FreeFile
    Open App.Path & "\LESSONS\MySQL_File.SQL" For Output Shared As #intOutFN
    
    strFilename = Dir(App.Path & "\LESSONS\*.txt")
    
    Do Until Len(strFilename) = 0
        If strFilename Like "*.txt" Then
            intFN = FreeFile
            Open App.Path & "\LESSONS\" & strFilename For Input Shared As #intFN
            
            strFileData = Input(LOF(intFN), #intFN)
            
            Close #intFN
            
            Print #intOutFN, "Insert Into Mytablename (Field2 , Field3) Values ('" & strFilename & "' , '" & strFileData & "')"
            
            strFilename = Dir
        End If
    Loop
    
    Close #intOutFN
    
End Sub

Open in new window

0
 

Author Closing Comment

by:GarySB
ID: 36540677
Great!  Thank You Very Much
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
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.
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…
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…

743 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