Visual Basic 6.0 and SQL Server Connectivity

In order to develop easy, user friendly but reliable software, we can use Visual Basic 6.0 and SQL Server. Visual Basic provides us with a nice IDE to create user interface very easily. The only complex thing is database connectivity. Here I would like to show you the connectivity between VB and SQL Server.

First of all, it is better to create a module from the Project Explorer window. And in the General Declaration of that module we need to declare 3 objects i.e. RecordSet, Connection and Command. In order to access these 3 objects we need to add the reference of Microsoft ActiveX Data Object 2.1 or any other version from Project à Reference menu.

And we can create a Procedure in it, in order to reuse the procedure from every form where we need the database connectivity. So this procedure will reduce the size of our code. After writing all necessary objects and procedure the module will look like below-

Public rs As Recordset
Public con As Connection
Public com As Command
_____________________________________
Public Sub prcConnect ()
Set rs = New Recordset
Set con = New Connection
Set com = New Command
With con
. ConnectionString = "data source= shafiq; initial catalog= studentadmin; user id= sa; password=1234;" [for SQL Server Authentication]
. ConnectionString = "data source= shafiq; initial catalog= studentadmin; integrated security=SSPI" [for Windows NT authentication]
.Provider = "SQLOLEDB"
.Open
End With
com.ActiveConnection = con
End Sub

Then open a new form and create the necessary interface to display the required field from the database. And on form load event, write the following codes-

Private Sub Form_ Load ()
prcConnect
rs.Open "select * from student", con, adOpenDynamic, adLockOptimistic
rs.MoveFirst
While Not rs.EOF
Combo1.AddItem rs.Fields ("Roll") & ""
rs.MoveNext
Wend
rs.MoveFirst
prcDisplay
' App.HelpFile = MYHELPPROJECT.hlp
txtRoll.Visible = False
End Sub

Here is the code of prcDisplay () function-

Public Sub prcDisplay ()
Combo1.Text = rs.Fields ("Roll") & ""
txtName.Text = rs.Fields ("Name") & ""
txtAddress.Text = rs.Fields ("Address") & ""
txtBatch.Text = rs.Fields ("Batch") & ""
End Sub  

There are several ways to navigate between the records of the database i.e. we can use buttons or combo box etc. I have used here a combo box, so on choose of a roll number from the combo box, the relevant record of that particular roll number should be displayed. Here is the code on click of combo box event to do so-

Private Sub Combo1_ Click()
rs.MoveFirst
Dim check As Integer
check = Combo1.ListIndex
rs.Move check
prcDisplay
End Sub  

For adding a new record into the database we can write the following code on click of a button-

Private Sub cmdNew_ Click ()
Combo1.Visible = False
TxtRoll.Visible = True
cmdUpdate.Visible = False
cmdSave.Visible = True
For Each Control In frmStudent
If TypeOf Control Is TextBox Then
Control.Text = ""
End If
Next
Rs.MoveLast
rs.AddNew
txtRoll.SetFocus
End Sub  

For saving a new record in database, we can write the following code-

Private Sub cmdSave_ Click ()
rs.Fields ( "Roll") = txtRoll.Text & ""
rs.Fields ( "Name") = txtName.Text & ""
rs.Fields ( "Address") = txtAddress.Text & ""
rs.Fields ( "Batch") = txtBatch.Text & ""
rs.update
MsgBox "Saved Successfully", vbInformation, "Saved"
Combo1.Visible = True
Combo1.AddItem txtRoll.Text & ""
txtRoll.Visible = False
rs.Requery
Combo1.Refresh
cmdUpdate.Visible = True
cmdSave.Visible = False
End Sub 

Updating data in database is depended on data type of the field. For updating the record in database, we can write the following code-

Private Sub cmdUpdate_ Click ()
If txtName.Text = "" Then
rs.Fields ( "Name") = Null
Else
rs.Fields ( "Name") = txtName.Text
End If
If txtAddress.Text = "" Then
rs.Fields ( "Address") = Null
Else
rs.Fields ( "Address") = txtAddress.Text
End If
If txtBatch.Text = "" Then
rs.Fields ( "Batch") = Null
Else
rs.Fields ( "Batch") = txtBatch.Text
End If
rs.update
MsgBox "Saved Successfully", vbInformation, "Saved"
End Sub

OR we can write like follows-

Private Sub cmdUpdate_ Click ()
On Error GoTo err_q
rs.Fields ( "Name") = txtName.Text & ""
rs.Fields ( "Address") = txtAddress.Text & ""
rs.Fields ( "Batch") = txtBatch.Text & ""
rs.update MsgBox "Saved Successfully", vbInformation, "Students Detail"
Exit Sub
err_q :
MsgBox "Could not connect to the server", vbCritical, "Student Admin"
End Sub 

For deleting a record from the database, we can write the following code-

Private Sub cmdDelete_ Click ()
com.CommandText = "delete from student where roll='" & Combo1 & "'"
com.Execute MsgBox "Record has been deleted ", vbInformation, "Delete Record"
Combo1.RemoveItem Combo1.ListIndex
rs.MoveFirst
rs.Requery
Combo1.Refresh
prcDisplay
End Sub