ADODB code for Database in Visual Basic 6.0

Objective:

  1. To learn how to connect Oracle with Visual Basic 6.0
  2. To learn how to use class modules in Visual Basic 6.0

Implementation:

Sample Application – the application contains a form, which is used to collect customer information such as name, phone number and address and record that information in the Oracle database.  Each customer has unique id, which is automatically generated.

DBConnector.cls

‘local variable(s) to hold property value(s)

Private mvarConnection As ADODB.Connection ‘local copy

Private mvarRecordset As ADODB.Recordset ‘local copy

Private mvarCommand As ADODB.Command

Public Property Let Command(ByVal vData As ADODB.Command)

‘used when assigning a value to the property, on the left side of an assignment.

‘Syntax: X.recordset = 5

mvarCommand = vData

End Property

Public Property Get Command() As ADODB.Command

‘used when retrieving value of a property, on the right side of an assignment.

‘Syntax: Debug.Print X.recordset

Set Command = mvarCommand

End Property

Public Property Let Recordset(ByVal vData As ADODB.Recordset)

‘used when assigning a value to the property, on the left side of an assignment.

‘Syntax: X.recordset = 5

mvarRecordset = vData

End Property

Public Property Get Recordset() As ADODB.Recordset

‘used when retrieving value of a property, on the right side of an assignment.

‘Syntax: Debug.Print X.recordset

Set Recordset = mvarRecordset

End Property

Public Property Let Connection(ByVal vData As ADODB.Connection)

‘used when assigning an Object to the property, on the left side of a Set statement.

‘Syntax: Set x.connection = Form1

mvarConnection = vData

End Property

Public Property Get Connection() As ADODB.Connection

‘used when retrieving value of a property, on the right side of an assignment.

‘Syntax: Debug.Print X.connection

Set Connection = mvarConnection

End Property

Private Sub Class_Initialize()

Set mvarConnection = New ADODB.Connection

Set mvarRecordset = New ADODB.Recordset

Set mvarCommand = New ADODB.Command

mvarConnection.Open “Provider=MSDAORA;Data Source=SIDV2X;User;Password=temp1;”

MsgBox “connection successful”

End Sub

Customer.cls

‘local variable(s) to hold property value(s)

Private mvarCustomerID As Integer ‘local copy

Private mvarCustomerName As String ‘local copy

Private mvarPhoneNumber As String ‘local copy

Private mvarAddress As String ‘local copy

Public Sub AddNewCustomer()

Dim connector As New DBConnector

‘GENERATE CUSTOMER ID

Dim queryCustomerID As String

Dim regno As Integer

queryCustomerID = “select customerid from customer”

connector.Recordset.Open queryCustomerID, connector.Connection

If Not connector.Recordset.EOF Then

While Not connector.Recordset.EOF

regno = connector.Recordset!CustomerID

connector.Recordset.MoveNext

Wend

regno = regno + 1

Else

regno = “100”

End If

connector.Recordset.Close

‘INSERT THE NEW CUSTOMER INFORMATION AS A RECORD INTO THE TABLE

confirm = MsgBox(“Do you want to Save?”, vbYesNo, “Save New Information?”)

Select Case confirm

Case vbYes

Dim insertQuery As String

insertQuery = “insert into customer values (” & regno & “,'” & mvarCustomerName & “‘,'” & mvarPhoneNumber & “‘,'” & mvarAddress & “‘)”

connector.Recordset.Open insertQuery, connector.Connection, adOpenKeyset, adLockOptimistic

Status = MsgBox(“Record Successfully Saved”, vbInformation, “Information”)

End Select

connector.Connection.Close

Set connector = Nothing

End Sub

Public Property Let Address(ByVal vData As String)

‘used when assigning a value to the property, on the left side of an assignment.

‘Syntax: X.PhoneNumber = 5

mvarAddress = vData

End Property

Public Property Get Address() As String

‘used when retrieving value of a property, on the right side of an assignment.

‘Syntax: Debug.Print X.PhoneNumber

Address = mvarAddress

End Property

Public Property Let PhoneNumber(ByVal vData As String)

‘used when assigning a value to the property, on the left side of an assignment.

‘Syntax: X.PhoneNumber = 5

mvarPhoneNumber = vData

End Property

Public Property Get PhoneNumber() As String

‘used when retrieving value of a property, on the right side of an assignment.

‘Syntax: Debug.Print X.PhoneNumber

PhoneNumber = mvarPhoneNumber

End Property

Public Property Let CustomerName(ByVal vData As String)

‘used when assigning a value to the property, on the left side of an assignment.

‘Syntax: X.CustomerName = 5

mvarCustomerName = vData

End Property

Public Property Get CustomerName() As String

‘used when retrieving value of a property, on the right side of an assignment.

‘Syntax: Debug.Print X.CustomerName

CustomerName = mvarCustomerName

End Property

Public Property Let CustomerID(ByVal vData As Integer)

‘used when assigning a value to the property, on the left side of an assignment.

‘Syntax: X.CustomerName = 5

mvarCustomerID = vData

End Property

Public Property Get CustomerID() As Integer

‘used when retrieving value of a property, on the right side of an assignment.

‘Syntax: Debug.Print X.CustomerName

CustomerID = mvarCustomerID

End Property

Form to get Customer information

NewCustomer.frm

Private Sub cmdSubmitNewCustomer_Click()

If (txtCustomerName.Text = “”) Then

MsgBox “Customer Name should not be empty!”

Exit Sub

End If

If (txtPhoneNumber.Text = “”) Then

MsgBox “Phone Number should not be empty!”

Exit Sub

End If

If (txtAddress.Text = “”) Then

MsgBox “Phone Number should not be empty!”

Exit Sub

End If

Dim objCustomer As Customer

Set objCustomer = New Customer

‘objCustomer.CustomerID = Val(txtRegNo.Text)

objCustomer.CustomerName = txtCustomerName.Text

objCustomer.PhoneNumber = txtPhoneNumber.Text

objCustomer.Address = txtAddress.Text

objCustomer.AddNewCustomer

txtAddress.Text = “”

txtPhoneNumber.Text = “”

txtCustomerName.Text = “”

End Sub

Private Sub txtCustomerName_KeyPress(KeyAscii As Integer)

‘check whether is valid alphabetic characters is pressed

‘space allowed

If Not ((KeyAscii >= 65 And KeyAscii <= 90) Or (KeyAscii >= 97 And KeyAscii <= 122) Or KeyAscii = 32 Or KeyAscii = 8) Then

MsgBox “Enter only alphabetic characters!”

KeyAscii = 0

End If

End Sub

Private Sub txtPhoneNumber_KeyPress(KeyAscii As Integer)

‘check whether is valid numerical characters is pressed

‘space allowed

If ((KeyAscii >= 65 And KeyAscii <= 90) Or (KeyAscii >= 97 And KeyAscii <= 122) Or KeyAscii = 32) Then

MsgBox “Enter only numerical characters!”

KeyAscii = 0

End If

End Sub

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: