Visual Basic
Visual Basic (VB) is an extremely popular and easy to use programming language provided by Microsoft Corporation. VB is based on the BASIC programming language and is mainly used to develop Windows based applications.
Accessing Oracle from Visual Basic[edit]
Connectivity to Oracle is provided via ODBC or OO4O (Oracle Objects for OLE). For more information about ODBC, read the ODBC FAQ. For information about OO4O, read the OO4O FAQ.
Look at these examples:
DAO Example (Data Access Objects):
Dim wstemp As Workspace
Dim dbtemp As Database
Dim rstemp As Recordset
Set wstemp = DBEngine.Workspaces(0)
Set dbtemp = wstemp.OpenDatabase("", False, False, "ODBC;DSN=Oracle;USR=scott;PWD=tiger")
Set rstemp = dbtemp.OpenRecordset(myquery.Text, dbOpenDynaset, dbSQLPassThrough)
howmany = 0
Combo1.Clear
Do Until rstemp.EOF
msgbox rstemp(0)
rstemp.MoveNext
howmany = howmany + 1
Loop
RDO example (Remote Data Objects):
Dim contemp As New rdoConnection
Dim rstemp As rdoResultset
Dim envtemp As rdoEnvironment
Set envtemp = rdoEngine.rdoEnvironments(0)
envtemp.CursorDriver = rdUseServer
' or rdUseOdbc, rdUseNone, rdUseIfNeeded, rdUseClientBatch
With contemp
.Connect = "ODBC;DSN=Oracle;USR=scott;PWD=tiger"
.EstablishConnection rdDriverNoPrompt, false, rdoForwardOnly
' or rdoStatic, rdoKeyset, rdoDynamic
End With
Set rstemp = contemp.OpenResultset("select ...") ' Your SQL here
howmany = 0
With rstemp
Do Until .EOF Or howmany > 2000
msgbox .rdoColumns(0) ' Popup a message box showing the 1st column
.MoveNext
howmany = howmany + 1
Loop
ADO example:
Option Explicit
Public m_adoCnn As New ADODB.Connection
Public m_adoRst As New ADODB.Recordset
Public Sub Command1_Click()
m_adoCnn.ConnectionString = "Provider=MSDAORA;Password=tiger;User ID=scott;Data Source=database"
m_adoCnn.Open
End Sub
Private Sub Form_Unload(Cancel As Integer)
m_adoCnn.Close
End Sub
OO4O example:
Option Explicit
Dim m_oraSession As Object
Dim m_oraDatabase As Object
Private Sub Command1_Click()
Set m_oraSession = CreateObject("OracleInProcServer.XOraSession")
Set m_oraDatabase = m_oraSession.DBOpenDatabase("Database", "user/password", 0&)
MsgBox "Connected to " & m_oraDatabase.Connect & "@" & m_oraDatabase.DatabaseName
End Sub
Private Sub Form_Unload(Cancel As Integer)
Set m_oraDatabase = Nothing
Set m_oraSession = Nothing
End Sub
Counting records in a recordset[edit]
When you do a recordcount and it return only one record in the recordset, while you know there are more records, you need to move to the last record before doing the count. Look at this example.
Dim rs As Recordset rs.MoveLast TxtNumRows.Text = rs.RecordCount
NOTE: Don't forget to do a rs.MoveFirst to get back to the first record again.
