[VB6] ADO Beginners Tutorial For Database Conectivity

Hai,
Here in this tutorial, I am going to explain, how to do almost all the functions with a database and a VB program.
I will tell you step by step.

Open your project. Then click Project menu. Then click References menu. From the dialog box, put tick mark against Microsoft ActiveX Data Object 2.x library. Thus you had made the refrence to the ADO.

Then write the below two lines of code in the General Declaration of the code:

Code:

Option Explicit
Private cn As ADODB.Connection 'this is the connection
Private rs As ADODB.Recordset ‘this is the recordset

Then write the below code in the Load event of the form:

Code:

Set cn = New ADODB.Connection
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source= c:\Akhil\database.mdb"  'you can use the path of your database in here
    cn.Open
    Set rs = New ADODB.Recordset
    rs.Open "student", cn, adOpenKeyset, adLockPessimistic, adCmdTable 'here [b]student[/b] is the name of the table.

FillData 'this is a sub for loading all data from the database to the textboxes, which will be explained in the below sections

Now write the below code in Unload event of the form:

Code:

Private Sub Form_Unload(Cancel As Integer)
rs.close
cn.close
Set rs  = nothing
Set cn = nothing
End Sub

The above code is for freeing up the memory by removing all the objects we had created from the memory..

For Adding new data, the below code is written inside the cmdAdd command button:

Code:

Private Sub cmdAdd_Click()
      With rs
        .AddNew
          .Fields("name") = text1.text
          .Fields("age") = text2.text
          .Fields("course") = text3.text
        .Update
      End With 
      End Sub

Here name, age, course are the field name of the student table in the database.

Remember we are using MS Access database. If you are using any other database technology, then then connectionstring will be some what different.
ie. this line of code:

Code:

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source= c:\Akhil\database.mdb"

And for the Next button:

Code:

Private Sub cmdNext_Click()
If Not rs.EOF Then rs.MoveNext
If rs.EOF Then
    Beep
    rs.MoveLast
End If
FillData 'func
End Sub

For the Previous button:

Code:

Private Sub cmdPrevious_Click()
If Not rs.BOF Then rs.MovePrevious
If rs.BOF Then
    Beep
    rs.MoveFirst
End If
FillData 'func
End Sub

For loading data from database to textboxes:

Code:

Public Sub FillData()

If Not (rs.EOF Or rs.BOF) Then
    txtName.Text = rs.Fields("name")
    txtFees.Text = rs.Fields("fees")
    txtDuration.Text = rs.Fields("duration")
    txtDiscount.Text = rs.Fields("discount")
    lblDiscountAmount.Caption = rs.Fields("newfees")
Else
    Beep 'alert
End If

End Sub

For Edit button:

Code:

Private Sub cmdEdit_Click()
      With rs
          .Fields("name") = text1.text
          .Fields("age") = text2.text
          .Fields("course") = text3.text
        .Update
      End With
      End Sub