Scribd
Upload
94 views3 pages

ADO Connection

This document discusses how to connect to a SQL Server database and retrieve data using ADO (ActiveX Data Objects) in Visual Basic. It describes creating an ADO Connection object to open a connection, an ADO Recordset object to retrieve records from a table, and how to loop through the records, access field values, and determine when all records have been retrieved. The code sample shows how to connect to a SQL Server database, execute a query to retrieve records from a table, display the total number of records and field values on a form, and close the connection.

Uploaded by

Mehtaz Shirin
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
94 views3 pages

ADO Connection

This document discusses how to connect to a SQL Server database and retrieve data using ADO (ActiveX Data Objects) in Visual Basic. It describes creating an ADO Connection object to open a connection, an ADO Recordset object to retrieve records from a table, and how to loop through the records, access field values, and determine when all records have been retrieved. The code sample shows how to connect to a SQL Server database, execute a query to retrieve records from a table, display the total number of records and field values on a form, and close the connection.

Uploaded by

Mehtaz Shirin
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
You are on page 1/ 3

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 Basic We 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 Application For 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 buttons click object. Dim MyConnObj As New ADODB.Connection Dim myRecSet As New ADODB.Recordset To create a Connection to a database we use the Connection objects Open method: 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 objects Open mentod to fetch records from a table (myTable): 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: Msgbox myRecSet(0) The index 0 maps to the first field, 1 maps to data in the second field, and so onHowever 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: 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. 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. 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 Never mind I missed the part where you have to activate the ActiveX 2.8. Go to Tools>References and then activate the ActiveX control.

You might also like