Using ADO to Communicate with the Database, Part 1 - Implementing the Code
(Page 5 of 5 )
Step-III (Implementing the Code)
1. Double Click the form Window, it will open the Editor Window of “frmMyAddrBook”
2. Declare some Variables in the General Declaration Section of the Code.
Dim objConn As ADODB.Connection ‘ Connection Object
Dim objRs As ADODB.Recordset
Dim strConnString As String ‘ Variable to Hold the Connection String
Dim strQstr As String ‘ Variable to store Query
Dim sSelID As Double
Dim sOperateMode As Strign
3. Write a procedure to clear the controls on the form
Private Sub ClearControls()
‘ This procedure will clear the controls on the form.
TxtName=””
TxtAddr=””
TxtTel=””
End Sub
4. Call this procedure from ther Clear Button Click Event
Private Sub cmdClear_Click()
ClearControls ‘ it can be called like Call ClearControls
End Sub
5. Establish Connection with The database
Private Function bgetConnected()As Boolean
On Error goto ConnErr
GetConnectedb=False
Set objConn = New ADODB.Connection
StrConnStr= "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=” App.path & “mydeb.mdb"
Objconn.Open StrConnStr
bGetConnected=true
Exit Function
ConnErr:
Msgbox “Unable to Connect to the Database becaue” & Err.Description, VbInformation
End Function
6. Loading the Already Existing data to the List
7. Now Call This procedure in the Form_Load Event so that data will remain blank
Private Sub Form_Load()
Call ClearContriols ‘ Calling procedure to clear the input controls
SOperateMode=””
BgetConnected ‘ Establishing Connection with the Database
End Sub
8. Add the Code below to Add Button Click Event
Private cmdAdd_Click()
Call Clearcontrols ‘ Clear Data
Operatemode=”Add” ‘ Seting Operation Mode
End Sub
9. Add a procedure to validate the Data, like Name and Address Must be entered before we save the Data.
Private Function bValidateData() As Boolean
BValidateData=False ‘ Default we r assuming that Data is Invalid
If (Trim(txtName)=”” Then
Msgbox”Please Enter Name”,Vbinformation,App.title
Trim(txtName.Setfocous ‘Seting focus to Name filed
Exit Function
Endif
‘’’ Place similar Validation for Address here
‘ .... .... ...
BValidateData=True
End Function
10. Now We need to Save the data, so we will be writing a single procedure which will save the Data (Add/Edit/Delete)
Private bSaveData() As Boolean
On Error goto ErrHandlr ‘ incorporating Error Handler
BSaveData=false
StrQstr=””
If sOperateMode=”Add” Then
StrQstr=”Insert Into tblAddrBook (Name, Addr,Tel) Values(“
StrQstr=strQstr & “’” & txtName & “’,” & txtAddr & “’,’” & txtTel &”’’)”
Else if sOperateMode=”Edit” Then
StrQstr=”Update tblAddrBook Set”
StrQstr=strQstr & “ Name=’” & txtName & “’,”
StrQstr=strQstr & “ Addr=’” & txtAddr & “’,”
StrQstr=strQstr & “ Tel=’” & txtTel & “’”
StrQstr=strQstr & “ Where conID=” & val(sSelID)
Else If sOperateMode=”Del” Then
StrQstr=”Delete From tblAddrBook Where conID=” & Val(sSelID)
Endif
‘ Now We will Use the Connection Object’s Execute Method to Run Our Query
objConn.Execute strSql
bSaveData=true
Exit Function
ErrHandlr:
Msgbox “Unable to Save The Data Because “ & Vbcrlf & Err.Description , vbinformation, app.title
End Function
11. Now We Need to Call the Function to Save The data
Private cmdSave_Click()
If bSaveData Then ‘ If data has been Saved Successfully Then Give a message
Msgbox “Contact has been Added Succefully”
Call PopulateData ‘ Refreshing the List of already Added data
End Sub
12. Now we will be deleting the data Selected by the User
Private cmdDelete_Click()
If Val(sSelId)=0 Then ‘Use has not selected any Data to delete
Msgbox “Please select a Contact to Delete”
LstContact.setfocous
Exit Sub
End if
If bSaveData Then
Msgbox “the Contact has been deleted”
Call ClearControls ‘ Clear the Controls
Call PopulateData ‘ repopulate The data
End if
End Sub
13. Now we need to write a procedure to populate the data into the list Box.
Private Function PopulateData()
Dim dctr As double
Objrs.activeConnection=ObjConn
Objrs.LockType=adLockOptimistic
Objrs.CursorType=adOpenStatic
Objrs.CursorLocation=adUseClient
StrQstr=”Select * from tblAddrBook Order By Name”
Objrs.open StrQstr,,,,adcmdtext
If objrs.RecordCount>0 Then
For dCtr=1 to Objrs.Recordcount
LstContact.addItem Objrs!Name ‘ Displaying Name Only
‘Populating ID in the Item Data
LstContact.ItemData(lstContact.NewIndex)=Objrs!Id ‘
Objrs.movenext
Nect dCtr
Endif
If objrs.state=1 Then ‘ If Connection is Open
Objrs.close
Endif
End function
14. Again we need a procedure to Display the Contact Information as the user selects a contact from the List Box
Private Function DispData()
Objrs.activeConnection=ObjConn
Objrs.LockType=adLockOptimistic
Objrs.CursorType=adOpenStatic
Objrs.CursorLocation=adUseClient
StrQstr=”Select * from tblAddrBook Where ID=”& Val(selID)
Objrs.open StrQstr,,,,adcmdtext
If objrs.RecordCount>0 Then
TxtName= Objrs!Name ‘ Displaying Name
TxtAddr= Objrs!Name
TxtTel= Objrs!Tel
Endif
If objrs.state=1 Then ‘ If Connection is Open
Objrs.close
Endif
End Function
15. Now We have to write some code to display the detail information in the input controls, when user double clicks a contact in the List box_ Add The Code below into the Double_Click Event of the lstContact
Private Sub lstContact_DoubleClick(Byval Index as integer)
SelID=lstContact.itemdata(lstContact.ListIndex)
Call DispData ‘ Calling Function to display the contact information
End Sub
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
/ 34 
