• (2 Pages)
  • +
  • 1
  • 2

Connecting to a SQL Server database using ADODB How to connect to a SQL server using Visual Basic 6.0 Rate Topic: **--- 3 Votes

#1 deostroll  Icon User is offline

  • New D.I.C Head
  • member icon

Reputation: 3
  • View blog
  • Posts: 30
  • Joined: 06-April 06

Posted 03 March 2007 - 01:37 PM

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 button’s 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 object’s 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 object’s 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 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:
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



Is This A Good Question/Topic? 2
  • +

Replies To: Connecting to a SQL Server database using ADODB

#2 salvadorursua  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 2
  • Joined: 03-July 07

Posted 03 July 2007 - 07:55 PM

How are registers used for connecting to MYSQL especially if it is in windows server?
Was This Post Helpful? 1
  • +
  • -

#3 haidan_dan  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 14
  • Joined: 29-May 08

Posted 02 June 2008 - 07:30 PM

I've try to implement your code, but it give me an error on this line

Dim MyConnObj As New ADODB.Connection

it says User-defined type not defined, is that anything i must do i implement this code?could you help me to understand this porblem
Was This Post Helpful? 0
  • +
  • -

#4 CosineMkt  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 2
  • Joined: 19-September 08

Posted 19 September 2008 - 12:50 PM

I have the exact same problem. For the following command [Dim MyConnObj As New ADODB.Connection] it gives the error "User-Defined Type Not Defined" when I go to run the program. I looked at the worthless msdn website and it said it needed to be replaced to avoid conflict with ADO but never said what to replace it with.
Was This Post Helpful? 0
  • +
  • -

#5 CosineMkt  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 2
  • Joined: 19-September 08

Posted 19 September 2008 - 01:04 PM

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.
Was This Post Helpful? 1
  • +
  • -

#6 Paulkush  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 25-September 09

Posted 25 September 2009 - 10:00 AM

View Postsalvadorursua, on 3 Jul, 2007 - 06:55 PM, said:

How are registers used for connecting to MYSQL especially if it is in windows server?


Can this code work over a net work connecting vb to mysql
Was This Post Helpful? 0
  • +
  • -

#7 bharath.karthikeyan  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 23-November 09

Posted 23 November 2009 - 04:03 AM

Very Good post...A few optimistation for QTP's VB compliance...The code worked w/o a hitch....Thx a lot,Much appreciated! :)

By any chance...a code to execute an already existant stored procedure in SQL server would be really appreciated from your side.
Was This Post Helpful? 0
  • +
  • -

#8 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

Reputation: 463
  • View blog
  • Posts: 4,255
  • Joined: 25-March 09

Posted 23 November 2009 - 03:03 PM

very good tutorial. :)
Was This Post Helpful? 0
  • +
  • -

#9 bohemian9485  Icon User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 38
  • Joined: 05-August 09

Posted 18 February 2010 - 12:24 AM

Thank you very much for pointing out the right way to connecting the SQL server database. Just want to point out that if don't move the record pointer, the Recordcount will not give a correct answer. so I think you should use a MoveLast method before using the Recordcount.
Was This Post Helpful? 0
  • +
  • -

#10 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

Reputation: 463
  • View blog
  • Posts: 4,255
  • Joined: 25-March 09

Posted 18 February 2010 - 05:51 AM

View Postbohemian9485, on 17 February 2010 - 11:24 PM, said:

Thank you very much for pointing out the right way to connecting the SQL server database. Just want to point out that if don't move the record pointer, the Recordcount will not give a correct answer. so I think you should use a MoveLast method before using the Recordcount.

incorrect the recordcount will return the correct number of records but you may have problems getting the data so before doing the while loop for all the records it is recommended to go to first record then loop all the records. but in all this time of using it i never rly encounter so much time that error(it dose happen often using ADODC). it may be because of the version of ADODB also.

This post has been edited by NoBrain: 18 February 2010 - 05:52 AM

Was This Post Helpful? 0
  • +
  • -

#11 bohemian9485  Icon User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 38
  • Joined: 05-August 09

Posted 18 February 2010 - 05:56 PM

View PostNoBrain, on 18 February 2010 - 07:51 PM, said:

View Postbohemian9485, on 17 February 2010 - 11:24 PM, said:

Thank you very much for pointing out the right way to connecting the SQL server database. Just want to point out that if don't move the record pointer, the Recordcount will not give a correct answer. so I think you should use a MoveLast method before using the Recordcount.

incorrect the recordcount will return the correct number of records but you may have problems getting the data so before doing the while loop for all the records it is recommended to go to first record then loop all the records. but in all this time of using it i never rly encounter so much time that error(it dose happen often using ADODC). it may be because of the version of ADODB also.


I copied the code to my test project and and recordcount gave a -1 count. I added a MyRecSet.MoveLast before the recordcount so I could get the correct number of records in the table. Of course I moved the record pointer back to the first record before using the print loop.
Was This Post Helpful? 0
  • +
  • -

#12 Guest_Arif*


Reputation:

Posted 22 February 2010 - 10:35 PM

View Postsalvadorursua, on 03 July 2007 - 06:55 PM, said:

How are registers used for connecting to MYSQL especially if it is in windows server?

Was This Post Helpful? 0

#13 Guest_satish*


Reputation:

Posted 05 April 2010 - 11:14 PM

View PostArif, on 22 February 2010 - 09:35 PM, said:

View Postsalvadorursua, on 03 July 2007 - 06:55 PM, said:

How are registers used for connecting to MYSQL especially if it is in windows server?


really very helpful. Thank you very much.
Was This Post Helpful? 0

#14 luca90  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 22-April 08

Posted 13 April 2010 - 11:38 AM

View Postdeostroll, on 03 March 2007 - 12:37 PM, said:

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 button’s 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 object’s 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 object’s 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 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:
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




no:
Dim MyConnObj As New ADODB.Connection
Dim myRecSet As New ADODB.Recordset

yes:
Dim MyConnObj As ADODB.Connection
Dim myRecSet As ADODB.Recordset

i think:-)
simply a suggestion
Was This Post Helpful? 0
  • +
  • -

#15 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

Reputation: 463
  • View blog
  • Posts: 4,255
  • Joined: 25-March 09

Posted 15 April 2010 - 05:57 AM

so what you say is
Dim MyConnObj As ADODB.Connection
Dim myRecSet As ADODB.Recordset

Set MyConnObj As New ADODB.Connection
Set myRecSet As New ADODB.Recordset



instead of
Dim MyConnObj As New ADODB.Connection
Dim myRecSet As New ADODB.Recordset



why?
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2