Microsoft KB Archive/308071

Knowledge Base

How To Access an Oracle Database by Using the OleDbDataReader and Visual Basic .NET

Article ID: 308071

Article Last Modified on 5/13/2007

---

APPLIES TO

• Microsoft ADO.NET 1.1
• Microsoft ADO.NET 1.0
• Microsoft Visual Basic .NET 2003 Standard Edition
• Microsoft Visual Basic .NET 2002 Standard Edition

---

This article was previously published under Q308071

For a Microsoft Visual C# .NET version of this article, see 308448.

For a Microsoft Visual Basic 6.0 version of this article, see 176936.

This article refers to the following Microsoft .NET Framework Class Library namespace:

• System.Data.OleDb

IN THIS TASK

SUMMARY

• Requirements
• Steps to Retrieve Data from an Oracle Database

REFERENCES

SUMMARY

This article demonstrates how to use the ADO.NET OleDbDataReader class to retrieve data from an Oracle database.

back to the top

Requirements

The following list outlines the recommended hardware, software, network infrastructure, and service packs that you need:

• Microsoft Windows 2000 Professional, Windows 2000 Server, Windows 2000 Advanced Server, or Windows NT 4.0 Server
• Microsoft Data Access Components (MDAC) version 2.6 or later
• Oracle Client tools (installed on the computer)
• Microsoft Visual Studio .NET

This article assumes that you are familiar with the following topics:

• Visual Studio .NET
• ADO.NET fundamentals and syntax
• Oracle connectivity

back to the top

Steps to Retrieve Data from an Oracle Database

1. In Oracle SQL*Plus or any Oracle Client tool that allows you to run data definition language (DDL) statements, follow these steps:

   1. Create a table named TestTable as follows:

      Create Table TestTable (c1 char(5));
                              

   2. Insert data into TestTable as follows:

      Insert into TestTable c1 values('Test1');
      Insert into TestTable c1 values('Test2');
      Insert into TestTable c1 values('Test3');
                              

2. Start Visual Studio .NET.
3. Create a new Windows Application in Visual Basic .NET. Form1 is created by default.
4. Make sure that your project contains a reference to the System.Data namespace, and add a reference to it if it does not.
5. Drag a Button control to Form1, and change its Name property to btnTest.

6. Use the Imports statement on the System, System.Data, and System.Data.OleDb namespaces so that you are not required to qualify declarations in those namespaces later in your code.

   Imports System
   Imports System.Data
   Imports System.Data.OleDb
                       

7. Copy and paste the following code in the code window after the "Windows Form Designer generated code" section:

   Private Sub btnTest_Click(ByVal sender As System.Object, _
   ByVal e As System.EventArgs) Handles btnTest.Click
       Dim sConnectionString As String _
           = "Provider=MSDAORA.1;User ID=scott;password=tiger;"_
             "Data Source=myOracleServer;Persist Security Info=False"
       Dim mySelectQuery As String _
           = "SELECT * FROM TestTable where c1 LIKE ?"
       Dim myConnection As New OleDbConnection(sConnectionString)
       Dim myCommand As New OleDbCommand(mySelectQuery, myConnection)
      
       'Set the parameter value.
       myCommand.Parameters.Add("@p1", OleDbType.Char, 5).Value = "Test%"
       
       'Open connection to Oracle database.
       myConnection.Open()
       
       'Populate the DataReader.
       Dim myReader As OleDbDataReader = myCommand.ExecuteReader()
       Dim RecordCount as Integer
       Try
           While myReader.Read()
               RecordCount = RecordCount + 1
               MessageBox.Show(myReader.GetString(0).ToString())
           End While
           If RecordCount = 0 then
               MessageBox.Show("No data returned")
           Else
               MessageBox.Show("Number of records returned: " & RecordCount)
           End If
       Catch ex As Exception
           MessageBox.Show(ex.ToString())
       Finally
           
          'Close all objects.
           myReader.Close()
           myConnection.Close()
       End Try
   End Sub
                       

8. Save your project.
9. On the Debug menu, click Start to run your project.
10. Click the button to display the data.

back to the top

REFERENCES

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

176936 INFO: Visual Basic Accessing an Oracle Database Using ADO

For more information about ADO.NET objects and syntax, see the following topic in the Microsoft .NET Framework Software Development Kit (SDK) documentation or MSDN Online:

Accessing Data with ADO.NET
http://msdn2.microsoft.com/en-us/library/e80y5yhx(vs.71).aspx

For more general information about ADO.NET or Visual Basic .NET, refer to the following MSDN newsgroups:

microsoft.public.dotnet.framework.adonet

microsoft.public.dotnet.languages.vb

back to the top

Keywords: kbhowtomaster kbsystemdata KB308071

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.