VB and oracle

Links i Referred

http://www.orafaq.com/faqmsvb.htm

http://support.microsoft.com/default.aspx?scid=kb;en-us;300596&sd=tech


Create a System DSN in Windows XP

1. Click Start, point to Control Panel, double-click Administrative Tools, and then double-click Data Sources(ODBC).
2. Click the System DSN tab, and then click Add.
3. Click the database driver that corresponds with the database type to which you are connecting, and then click Finish.
4. Type the data source name. Make sure that you choose a name that you can remember. You will need to use this name later.
5. Click Select.
6. Click the correct database, and then click OK.
7. Click OK, and then click OK.

o Create a System DSN in Windows 2000
1. Click Start, point to Programs, point to Administrative Tools, and then double-click Data Sources (ODBC).

NOTE: In Windows 2000 Professional, click Start, point to Settings, click Control Panel, double-click Administrative Tools, and then double-click Data Sources (ODBC).
2. Click the System DSN tab.
3. Click Add.
4. Click the database driver that corresponds with the database type to which you are connecting, and then click Finish.
5. Type the data source name. Make sure to you a name that you will remember. You will need to use this name later.
6. Click Select.
7. Click the correct database, and then click OK.
8. Click OK in the next two dialog boxes.




ow does one connect to Oracle from VB?
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 this 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

# Back to top of file
Why is there only one record in my recordset?
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.