Upload and View RTF files in Database using Visual Basic

Problem:

Create a program in visual basic that will allow the user to upload rtf files in a database. The program will be able to compile those uploaded files and display it in a list format. From the list of uploaded files, the user will be able to select a certain file, then the program will display the content of rtf file selected by the user.

Solution:

We will use vb6 and ms access as the database of the program. We will be using also listview control to provide the list of uploaded files on the database, for displaying the content of the rtf file we will include the RichTextBox control to our project. Commond dialog control is for browsing of rtf files.

By the way RTF stands for Rich Text Format, it is a mix between rich text files and plaintext files.

Let’s begin the tutorial

We will first create our database.

1. Create a MS Access database and save it as data.mdb.
2. Create and save the table as rtfFile
3. Add the following field:

id – Autonumber
filepath – Text (Short Text)
nameoffile – Text (Short Text)

The table should look like the image below:

4. Don’t forget to save the table then close it.

Let’s proceed to our Visual Basic project.

1. Open your vb6 program, select Standart EXE.
2. We will first add Microsoft ActiveX Data Objects 2.5 Library to our project reference.
3. On the project menu select References. A dialog box will appear, kindly look and check the Microsoft ActiveX Data Objects 2.5 Library, click OK.
   
4. The next thing is to add the components needed (CommonDialog, RichTextBox, Listview). To add the said controls kindly click the Project menu then Components. Check the following components:

5. We will now design our forms, we need two forms:

Form1:

3 command buttons
1 textbox
1 listview
1 richtextbox

Sample Form Design:

Form 2:

2 labels
2 textboxes
3 command buttons
1 common dialog

Sample Form Design:

6. We will add a module to the project (Project menu, Add Module) and save it as modMain
7. Paste the code below to the newly created module:

'variable declarations
Public dbCN As New ADODB.Connection
Public rtfpath As String
'Connect to database
Public Function DBConnect() As Boolean
On Error GoTo OpenErr
Dim MSDatabase
Set dbCN = New ADODB.Connection
MSDatabase = App.Path & "\" & "data.mdb"
dbCN.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MSDatabase & ";Persist Security Info=False;Jet OLEDB:Database Password ="
DBConnect = True
Exit Function
OpenErr:
MsgBox "Error Opening " & MSDatabase & vbNewLine & Err.Description, vbCritical, "Open Database Error"
DBConnect = False
End Function

Code Explanation:

This module contains the global variables and the connection string to our ms access database.

8. Let’s back to our Form1. We will create to functions or procedures.

The first is ListOfFile Sub:

Sub ListOfFile()
Dim localrs As New ADODB.Recordset, msql As String
If localrs.State = adStateOpen Then localrs.Close
msql = "Select nameoffile from rtfFile "
localrs.Open msql, dbCN
listQuickLaunch.ListItems.Clear
Do While Not localrs.EOF
Set lstitem = listQuickLaunch.ListItems.Add(, , localrs(0).Value)
localrs.MoveNext
Loop
End Sub

Code Explanation:

This will populate the listview with the list of files uploaded in the database.

The second is GetFilename Sub:

Sub GetFilename()
On Error Resume Next
Dim lstitem As ListItem, a As Integer, localrs As New ADODB.Recordset, msql As String
If localrs.State = adStateOpen Then localrs.Close
msql = " select filepath from rtfFile where nameoffile='" & listQuickLaunch.SelectedItem.Text & "'"
localrs.Open msql, dbCN, adOpenDynamic, adLockOptimistic
Do While Not localrs.EOF
rtfpath = localrs(0).Value
txtpath.Text = localrs(0).Value
localrs.MoveNext
Loop
End Sub

Code Explanation:

This will retrieve the path of the selected rtf file.

9. On the load event of the form kindly paste the code:

DBConnect
ListOfFile

Code Explanation:

This will connect to our database and display the list of uploaded files on the database.

10. To refresh the list, double click the Refresh List button and paste the code.

ListOfFile

11. To open the Form2 (form for uploading rtf files) double click Upload Files button and paste.

Form2.Show

12. On the double click (DblClick) event of the listview, kindly paste the code.

If listQuickLaunch.ListItems.Count > 0 Then
GetFilename
rt1.FileName = rtfpath
Else
MsgBox "No file(s) on the list", vbCritical, ""
End If

Code Explanation:

This will get the path of the selected file then display it in the richtextbox control.

Let’s proceed to the Form2 of the program

1. Create a AddFile Sub procedure

Public Sub AddFile()
Dim localrs As New ADODB.Recordset, msql As String
If localrs.State = adStateOpen Then localrs.Close
msql = " select * from rtfFile where nameoffile='" & txtname & "'"
localrs.Open msql, dbCN, adOpenDynamic, adLockOptimistic
If localrs.RecordCount >= 1 Then
MsgBox "Duplicate record found.", vbCritical, ""
Exit Sub
End If
With localrs
.AddNew
!Filepath = txtpath.Text
!nameoffile = txtname.Text
.Update
End With

MsgBox “New entry successfully saved to the record.”, vbInformation, “”

End Sub

Code Explanation:

This will save the details of the rtf file to the database.

2. To browse for the rtf files, double click the browse button and paste the code.

With CommonDialog1
.DialogTitle = "Browse File"
.Filter = "RTF Files(*.rtf)|*.rtf"
.ShowOpen
txtpath.Text = (CommonDialog1.FileName)
End With

3. Finally to upload or save the record to the database, double click the upload button.

If Trim(txtname.Text) = "" Then
MsgBox "Please enter name of file.", vbCritical, ""
txtname.SetFocus
ElseIf Trim(txtpath.Text) = "" Then
MsgBox "Please enter the lesson file path.", vbCritical, ""
txtpath.SetFocus
Else
AddFile
Unload Me
End If

Save and Run the project.