This article deals with connecting to a sql server database. I am using ADO.
What is ADO?ADO stands for
ActiveX Data Objects. It is a way by which your application can access data that resides in a database. Here the term database is a general term; it includes a huge variety of specific databases - excel sheets, text files, sql server, jet database, etc. In object oriented terminology, ADO is simply an object model that allows us to interact with a database so that we can fetch, add or modify data. At the top level we have a
Connection object. This object allows us to establish a connection with a database. The best part you really do not have to bother about the details about the underlying database. ADO supports a large collection of databases as mentioned earlier. At the next level comes the
Command object and the
Recordset object. The ADO Command object is used to execute queries against a database. If you are retrieving information from a database you have to store it in a Recordset object. I guess this will do for the brief introduction for ADO.
ADO in Visual BasicWe have to include a reference to a ADO library in our project. For this exercise it does not matter which library you use, however, for the sake of mentioning I have used the ActiveX Data Objects 2.8 Library. So start a new Standard Exe project. Select the References item from the Project menu and select the necessary dll. (It will be listed as
Microsoft ActiveX Data Objects 2.8 Library).
The ApplicationFor this exercise I am using a simple form. I have a button dragged and dropped on the form. Here I plan to simply use a Connection object and a Recordset object to retrieve records. These two objects shall do to serve our purpose for the moment. I am not using any control to display data; I intend to display data on the form itself. This exercise will show you how to fetch records, find the total number of records fetched and how to access the values of each field.
We have to create instances of a Connection and a Recordset object in our button’s click object.
CODE
Dim MyConnObj As New ADODB.Connection
Dim myRecSet As New ADODB.Recordset
To create a Connection to a database we use the Connection object’s Open method:
CODE
MyConnObj.Open _
"Provider = sqloledb;" & _
"Data Source={ServerName};" & _
"Initial Catalog={DatabaseName};" & _
"User ID={UserName};" & _
"Password={Password};"
Here we have provided all the details at a stretch. Note the use of semicolons. You have to edit all the parameters in the braces to cater to your requirement. In the example below I have used my own information.
Next we use the Recordset object’s Open mentod to fetch records from a table (myTable):
CODE
myRecSet.Open “select * from myTable”, MyConnObj, adOpenKeyset
The Open method pertaining to the Recordset object actually takes four parameters. The first is the source. The second is the Connection object. If you have two or more Connections take care of mapping the correct Connection object to the RecordSet object. The third parameter refers to the cursor type. The last parameter refers to the type of locking implemented.
Let us suppose that my table has three fields. You can refer to a data in the first field using the RecordSet object as:
CODE
Msgbox myRecSet(0)
The index 0 maps to the first field, 1 maps to data in the second field, and so on…However this analogy will not allow you to get the next record! So how do we get the information pertaining to the next record?
This is because the Recordset points to the first record fetched. We have to tell the RecordSet object to point to the next record. Only if this is done we can make use of the above analogy to fetch information of the second record. So to point to the next record use the MoveNext method of the RecordSet:
CODE
myRecordSet.MoveNext
So now the question comes: you know how to open a connection, you know how to fetch records, and you know how to point to the next record. But how will you know when the records fetched exhausts? How will you know if the last record has reached?
There are two ways. To know the numbers of records before hand we can use the
RecordCount property of the Recordset object.
CODE
Msgbox “Total no of records = ”& myRecSet.RecordCount
But the RecordCount will only give a proper count if we have used the adOpenKeyset or the adOpenStatic enums in the Open method of the RecordSet object.
Another way is to iterate; we can move the pointer to the next record and check if the
EOF property is True or not. This is what I have used to print data on the form.
CODE
Private Sub Command1_Click()
Dim MyConnObj As New ADODB.Connection 'ADODB Connection Object
Dim myRecSet As New ADODB.Recordset 'Recordset Object
Dim sqlStr As String ' String variable to store sql command
MyConnObj.Open _
"Provider = sqloledb;" & _
"Data Source=172.16.1.60;" & _
"Initial Catalog=TESTATV;" & _
"User ID=sa;" & _
"Password=p@ssW0rd;"
sqlStr = "select * from employee"
myRecSet.Open sqlStr, MyConnObj, adOpenKeyset
MsgBox "Total Number of records = " & myRecSet.RecordCount
Dim i As Integer 'variable to keep count
i = 1
Print "#"; Tab; "ID"; Tab; "Name"; Tab; "Salary"
Print ""
While Not myRecSet.EOF ' Loop until endd fo file is reached
Print i; Tab; myRecSet(0); Tab; myRecSet(1); Tab; myRecSet(2)
'0- 1st filed, 1- 2nd Field and so on...
myRecSet.MoveNext 'Moves the RecordSet pointer to the next position
i = i + 1
Wend
MyConnObj.Close
End Sub
Connecting to a SQL Server database using ADODB, How to connect to a SQL server using Visual Basic 6.0
3 Mar, 2007 - 12:37 PM
| 