VB6 and MySQL Database Navigation and Process

A. Software Requirements

To create an application that uses Visual Basic 6 and MySQL. You need to download and install the MySQL Community Edition, MySQL Query Browser and MyODBC 5.1 driver. When you install the MySQL Community Edition, make sure that you take note of the username and password you have provided.

B. Create the Database

Use mysql query browser to create a new database and table.

Create a database named, "dblog" and a table named, "tblog" that with the following fields and their datatypes and length values: id(int, 10), name(varchar, 50) and password (varchar, 10).


C. Create the Interface
Create the interface using Visual Basic 6 using labels, textboxes, command button and datagrid.
When adding datagrid and adodc recordset, enable the Microsoft Datagrid Control and Microsoft ADODC Control by clicking Project in the menu bar then Components. In the components dialog box under control tab, look and enable the Miscrosoft Datagrid Control and Microsoft ADODC control.

control properties values








D. Visual Basic 6 codes

D.1 Add a module in the project window by right clicking and select add -> module. Then cut and paste the following code for MySQL database connection

'Database Connectivity
Option Explicit
Public SERVER_PATH As String
Public db As ADODB.Connection

Public Sub SetConnection(SERVER As String, DATABASE As String, UID As String, PWD As String)
On Error GoTo ShowError
Set db = New ADODB.Connection
With db
.ConnectionString = "DRIVER={MySQL ODBC 5.1 Driver};" & _
"SERVER=" & SERVER & ";" & _
"DATABASE=" & DATABASE & ";" & _
"UID=" & UID & ";" & _
"PWD=" & PWD & ";" & _
"OPTION = " & 1 + 2 + 8 + 32 + 2048 + 16384
.ConnectionTimeout = 30
.CursorLocation = adUseClient
.Open
End With
Exit Sub
ShowError:
MsgBox "Cannot establish connection. Please contact the Server.", vbOKOnly + vbCritical, "SERVER"
End Sub


D.2 After doing the database connection, then put this codes in their respective buttons.

'Declaration of form variables
'variable that stores SQL statement
Dim sql As String

Dim rs As ADODB.Recordset

'variable that stores true when user clicks the add button or false when the clicks the edit button
Dim blnadd As Boolean

'variable that stores the last position of the database pointer when user clicks the add or edit button
Dim pos As Variant

'variable that stores text value of txtid when user clicks the edit button
Dim intid As Integer

'Database Management codes
'
Private Sub cmdAdd_Click()

Call openlock(True)
blnadd = True

If rs.RecordCount <> 0 Then
pos = rs.Bookmark
End If

txtid.Text = ""
txtusername.Text = ""
txtpassword.Text = ""
End Sub

Private Sub cmdCancel_Click()
rs.Bookmark = pos
Call show_current
Call openlock(False)
End Sub

Private Sub cmdDelete_Click()
Dim response As Variant

If rs.RecordCount = 0 Then
Exit Sub
End If

response = MsgBox("Do you want to delete this record?", vbYesNo + vbQuestion, "Message")
If response = vbYes Then
sql = "delete from tbuser where id = '" & txtid.Text & "'"
Set rs = db.Execute(sql)
sql = "select * from tbuser"
Set rs = db.Execute(sql)
Set DataGrid1.DataSource = rs
Call show_current
End If

End Sub

Private Sub cmdEdit_Click()
intid = Val(txtid.Text)
Call openlock(True)
End Sub


Private Sub cmdUpdate_Click()
If Len(txtusername.Text) = 0 Then
MsgBox "Please type your username.", vbo + vbInformation, "Message"
Exit Sub
End If
If Len(txtusername.Text) = 0 Then
MsgBox "Please type your password.", vbo + vbInformation, "Message"
Exit Sub
End If

If blnadd = True Then
sql = "insert into tbuser(id,user, password) values ('" & txtid.Text & "',' " & txtusername.Text & " ', ' " & txtpassword.Text & "')"
Set rs = db.Execute(sql)
ElseIf blnadd = False Then
sql = "update tbuser set id='" & txtid.Text & "', user='" & txtusername.Text & "', password = '" & txtusername.Text & "' where id= '" & intid & "'"
Set rs = db.Execute(sql)

End If

sql = "select * from tbuser"
Set rs = db.Execute(sql)
Set DataGrid1.DataSource = rs
Call show_current
Call openlock(False)
End Sub


Private Sub DataGrid1_Click()
Call show_current
End Sub

'
Private Sub Form_Load()
Call SetConnection("localhost", "dblog", "root", "OtlPHP07")
sql = "select * from tbuser"
Set rs = db.Execute(sql)
Set DataGrid1.DataSource = rs
Call show_current
Call openlock(False)
End Sub

'Subroutine to show that current position of the pointer
Private Sub show_current()
If rs.RecordCount = 0 Then
Exit Sub
End If
txtid.Text = rs.Fields("id").Value
txtusername.Text = rs.Fields("user").Value
txtpassword.Text = rs.Fields("password").Value
End Sub

'Subroutine to enable or disable textboxes and command buttons
Private Sub openlock(key As Boolean)

If key = True Then
txtid.Locked = False
txtusername.Locked = False
txtpassword.Locked = False
cmdAdd.Enabled = False
cmdEdit.Enabled = False
cmdDelete.Enabled = False
cmdFirst.Enabled = False
cmdnext.Enabled = False
cmdPrevious.Enabled = False
cmdLast.Enabled = False

cmdUpdate.Enabled = True
cmdCancel.Enabled = True

Else
txtid.Locked = True
txtusername.Locked = True
txtpassword.Locked = True
cmdAdd.Enabled = True
cmdEdit.Enabled = True
cmdDelete.Enabled = True
cmdFirst.Enabled = True
cmdnext.Enabled = True
cmdPrevious.Enabled = True
cmdLast.Enabled = True

cmdUpdate.Enabled = False
cmdCancel.Enabled = False
End If

End Sub




'Database Navigation codes
Private Sub cmdFirst_Click()
If rs.RecordCount = 0 Then
Exit Sub
End If
rs.MoveFirst
Call show_current

End Sub

Private Sub cmdLast_Click()
If rs.RecordCount = 0 Then
Exit Sub
End If
rs.MoveLast
Call show_current
End Sub

Private Sub cmdnext_Click()
If rs.RecordCount = 0 Then
Exit Sub
End If
rs.MoveNext
If rs.EOF Then
rs.MoveLast
End If
Call show_current
End Sub

Private Sub cmdPrevious_Click()
If rs.RecordCount = 0 Then
Exit Sub
End If
rs.MovePrevious
If rs.BOF Then
rs.MoveFirst
End If
Call show_current
End Sub