Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 416
  • Last Modified:

Insert Records in MySQL database using VB 6.0

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
GarySB
Asked:
GarySB
  • 9
  • 7
1 Solution
 
aikimarkCommented:
@GarySB

Do you know how to program?
What code exists so far?
0
 
GarySBAuthor Commented:
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
 
aikimarkCommented:
post your code.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

 
GarySBAuthor Commented:
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
 
GarySBAuthor Commented:
Since it has just basic vb code going back to vb 3.0, why do i need to post it?
0
 
aikimarkCommented:
EE is here to help you, not to do work for you.  This isn't rent-a-coder.
0
 
GarySBAuthor Commented:
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
 
aikimarkCommented:
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
 
GarySBAuthor Commented:
Thank You, Yes, i have a FileListbox.  .
0
 
GarySBAuthor Commented:
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
 
aikimarkCommented:
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
 
GarySBAuthor Commented:
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
 
aikimarkCommented:
Why not just update the database directly from the program?
0
 
GarySBAuthor Commented:
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
 
aikimarkCommented:
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
 
GarySBAuthor Commented:
Great!  Thank You Very Much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 9
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now