GarySB
asked on
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.
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.
ASKER
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.
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.
post your code.
ASKER
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.
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.
ASKER
Since it has just basic vb code going back to vb 3.0, why do i need to post it?
EE is here to help you, not to do work for you. This isn't rent-a-coder.
ASKER
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
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
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.
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.
ASKER
Thank You, Yes, i have a FileListbox. .
ASKER
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
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
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?
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?
ASKER
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.
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.
Why not just update the database directly from the program?
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great! Thank You Very Much
Do you know how to program?
What code exists so far?