Home » Developer & Programmer » Data Integration » How to connect oracle to Visual Basic.
|
Re: How to connect oracle to Visual Basic. [message #93415 is a reply to message #93408] |
Tue, 14 December 2004 11:06 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
eilison
Messages: 22 Registered: August 2003
|
Junior Member |
|
|
sample code of connecting Oralce db in VB can be found in microsoft website, www.microsoft.com & msdn.microsoft.com
similar sample code may also be found in oracle website, otn.oracle.com
but in my experience, stuff about this from microsoft are more comprehensive.
if you're lost about which way to go when you have to make a decision with ADO, OLEDB, ODBC, etc, see also the below:
=========================================
OLE DB vs. ODBC
ODBC has been and remains a very important and successful data access standard. The ODBC technology and third-party market have matured to a point where ODBC is an ideal technology for accessing SQL databases. On the other hand, OLE DB has an improved architecture that provides a significant advantage over ODBC because providersno longer have to implement a SQL relational engine to expose data. An integral part of OLE DB is an OLE DB provider that enables OLE DB consumers to talk to ODBC drivers.
With ODBC, services such as cursoring and query processing need to be implemented by each ODBC driver writer. This represents overhead and possible inconsistency for both for the ODBC driver author and their end users. With OLE DB, reusable service components handle the processing chores for a variety of data providers.
OLE DB simplifies the process of writing data providers, which means they should be developed faster and be of a higher quality. It also reduces the number of components installed on data consumer machines.
Use the following tips to guide your choice of which technology to use:
If you are accessing standard relational databases from a non-OLE environment, ODBC is the best choice.
If you want to expose a data interface to nontabular data or use data from a source that does not support SQL, OLE DB is the best choice.
If you are programming in a COM environment, OLE DB is the best choice.
If you want to build interoperable database components, OLE DB is the only choice.
Technical differences between ODBC and OLE DB are summarized in the following table.
ODBC OLE DB
Data access API Database component APIs
C-level API COM API
SQL-based tabular data All tabular and multidimensional data
SQL-based standard COM-based standard
Native providers provide all features Component architecture allows reusable components to provide common features
Special Features and Benefits of OLE DB
As application developers move to solutions designed as reusable components, OLE DB enables business-centered components to behave and communicate like mini-databases, both as data consumers of other data sources and as providers. This capability is the basis for new, simpler ways to build applications based on components.
Access to all your corporate data regardless of storage format or location
This capability extends across corporate data as well as Internet data, and across all types of data providers beyond SQL databases.
Simplified programming model for corporate developers
When a developer uses a tool or language that supports OLE DB, different data sources can behave as a single, homogeneous source. From ADO, developers can create business applications that link many data sources.
Interoperable data-centered components
Business components can excrete data change events, consume OLE DB data, and provide OLE DB data. In this way, business components can perform very complex processing and synchronize with other components while exposing simple, table-like interfaces.
Components that act as virtual tables for quicker application development
Most graphical development tools automate loading OLE DB result sets from queries directly into user-interface controls. This technique is called data binding. Without OLE DB, more programming is required to access the result set and load the user-interface controls. Additionally, OLE DB supports events, or callback interfaces, which allow these controls to be more responsive and efficient.
Integration with the Microsoft suite of application development products and technologies
OLE DB is a useful technology in its own right, but it becomes even more compelling as the broad suite of Microsoft enterprise tools and technologies integrate and extend one another's capabilities.
Full integration with ODBC
OLE DB data consumer tools and languages have full access to all ODBC drivers and ODBC-based data.
ADO vs. DAO or RDO
ADO is now the standard data access language for Microsoft tools. The current versions of Internet Information Server, Internet Explorer, Visual Basic, Visual InterDev? Visual C++, and Microsoft Visual J++? have all been written to use ADO as their primary data access language.
Among the many benefits of ADO is a common language for accessing data. Whatever tool you are using, you can use the same code to query and manipulate data. This allows for much greater and easier code reuse across applications than was possible in the past.
Therefore, if you are designing an application now, you should use ADO, unless there are features you need that are not available in ADO but are available in one of the alternative technologies. Be sure to look at the features that are available through the ADO Extensions for Data Definition Language and Security (ADOX) library and the Jet and Replication Objects (JRO) library for features that were previously available only through DAO.
If you are using DAO or RDO, you should still think about how to move over to ADO when it supersedes these. That way, when the time comes, you will have an easier job migrating to ADO.
Following are scenarios in which you might still want to use DAO:
You have an existing application that uses DAO with Microsoft Jet, and you want to convert the application to use ODBC data to achieve better performance.
You need to create specific user and group PIDs when managing security access in a Microsoft Jet database.
You need security control over Microsoft Access objects stored in a Microsoft Jet database.
You need to lock a table exclusively in a Microsoft Jet database.
You need to retrieve implicit permissions on objects, in addition to explicit permissions.
You need to identify indexes that result from the creation of a foreign key in a relationship.
Following are scenarios in which you might still want to use RDO:
You are developing in Visual Basic 5.0, and you want to take advantage of binding controls with RDO.
ADO, DAO, RDO Feature Comparison
To help you decide which technology to use and also to determine if ADO meets your needs today, the following table presents a list of major features found in ADO, DAO, and RDO. ("X" indicates that the feature is present.)
=========================================
hope it helps!
eilison
|
|
|
Re: How to connect oracle to Visual Basic. [message #93418 is a reply to message #93408] |
Fri, 17 December 2004 19:40 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
kundan
Messages: 5 Registered: August 2001
|
Junior Member |
|
|
Page 37: Introduction to ADO
View Page Comments Leave a Comment
ADO stands for ActiveX Data Objects. ADO is the upgrade to both DAO and RDO. You can use this library of objects in conjunction with, or in place of the ADO Data Control for better database manipulation. ADO is much more in tune with database access on the internet.
To use the objects you must make a reference to them in your project. Go to Project/References and check the highest version of Microsoft ActiveX Data Objects. This may be anywhere from 2.0 to 2.6.
Setting up a Connection object is your first task. Create your connection object like this:
Dim oCn As ADODB.Connection
Set oCn = New ADODB.Connection
Next you must build a connection string and use it as an argument for the Open method. At a minimum, you'll specify a Provider string which indicates the type of the database, and a Data Source string which is simply the file name. Individual setting=value combinations of the connection string are separated with semi-colons.
Dim sConnect As String
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:databasemyaccessfile.mdb"
oCn.Open sConnect
The above value for Provider refers to an Access 2000 file (Jet 4.0). For Access 97 use 'Microsoft.Jet.OLEDB.3.51' (Jet 3.51).
All of the above replaces the OpenDatabase method of the Workspace object in DAO.
Once your connection is established, you can get your recordsets by creating an 'ADODB.Recordset' object and then using it's Open method. This method will accept the name of a table or query, or an SQL string. You'll need to add the name of the connection object and three more parameters - the type of recordset to open (CursorType), the type of record locking (LockType), and type of command (CommandType). The type of command refers to whether you are fetching a table (adCmdTable), a query (adCmdStoredProc), or using an SQL string (adCmdText).
Dim oCn As ADODB.Connection
Dim oRs As ADODB.Recordset
Set oCn = New ADODB.Connection
Set oRs = New ADODB.Recordset
oCn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:databasemyaccessfile.mdb"
oRs.Open "users", oCn, adOpenKeyset, adLockOptimistic, adCmdTable
Other options for the CursorType are adOpenDynamic, adOpenForwardOnly, and adOpenStatic. The static cursor is read only, similar to the Snapshot type from DAO. Forward Only recordsets only allow a single loop through the records. (MovePrevious would not be allowed.) The Keyset is a new term with ADO.
Keyset and Dynamic are similar recordsets which allows all types of movement and changes to the data, but the Keyset is preferred in a multi-user database where changes by other users are not needed immediately. The Dynamic recordset will show changes by other users immediately (automatic refreshing), but will of course take a chunk out of performance.
If multiple users try to update the same record at the same time, an error will occur. When locking your records, you will either be optimistic that this error won't occur, or you will be pessimistic and figure this error is likely to happen. In optimistic locking, other users are only locked out of the record(s) when the Update method is called - probably just a split second. In pessimistic locking, other users are locked out for the entire period that the records are being accessed and modified.
The ADODB.Recordset's Open method replaces the OpenRecordset method from DAO's Database object.
--------------------------------------------------------------------------------
The examples below will assume that module level Recordset and Connection object (oRs & oCn) have been property created in Form_Load and buttons named cmdAddNew, cmdFindUser, cmdUpdate, and cmdDelete.
The users table from the code above has an AutoNumber ID field, and Name and EMail text fields. As long as your recordset is not a read-only type, you can add a new record like this:
Private Sub cmdAddNew_Click()
With oRs
.AddNew
.Fields("Name") = txtName.Text
.Fields("EMail") = txtEMail.Text
.Update
End With
End Sub
You wouldn't assign a value to the ID field because the next sequential number would be automatically inserted by the database.
Some programmers know that the Fields collection is the default collection for the ADODB.Recordset and the Text property is the default property for a TextBox and might shorten this same code to:
oRs.AddNew
oRs("Name") = txtName
oRs("EMail) = txtEMail
oRs.Update
--------------------------------------------------------------------------------
To update or delete a particular record, you would first need to make the record current. You could do this two ways - use an SQL string with a WHERE clause when opening the recordset, or use the Find method with the correct criteria. The criteria used for the Find method is exactly like the SQL WHERE clause without the WHERE keyword.
Private Sub cmdFindUser_Click()
Dim uid As Variant
Do Until IsNumeric(uid) And Len(uid) > 0
uid = InputBox("Enter your ID to change your personal information.")
'User did not enter anything or pressed cancel
If uid = "" Then Exit Sub
Loop
'Using an SQL string - comment out if using Find method
oRs.Open "SELECT [[Name]], [[EMail]] FROM users WHERE [[ID]] = " & _
uid & ";", oCn, adOpenKeyset, adLockOptimistic, adCmdText
'Using Find method - comment out if using SQL string method
oRs.Open "users", oCn, adOpenKeyset, adLockOptimistic, adCmdTable
oRs.Find "[[ID]] = " & uid
If oRs.RecordCount > 0 Then
txtName = oRs("Name")
txtEMail = oRs("EMail")
End If
'At this point, the user edits the TextBoxes and
clicks the update button or clicks the Delete button
End Sub
Private Sub cmdUpdate_Click()
oRs("Name") = txtName
oRs("EMail") = txtEMail
oRs.Update
End Sub
Private Sub cmdDelete_Click()
oRs.Delete
txtName = ""
txtEMail = ""
End Sub
Now, a variety of errors may occur if you use this code, two of which come to mind. The Recordset will not be open if the user clicks Cancel or enters nothing and clicks OK in the InputBox, and the Recordset will have no current record if the ID entered is not valid. You could throw some On Error Resume Next statements in each of the procedures, but that may leave the user confused. So for the most part I'll the leave that as an exercise in error trapping for the VB student.
--------------------------------------------------------------------------------
Make sure to close and destroy your ADO objects when they're no longer needed. If they are module level, do this in Form_Unload. If they are procedure level, do this right in the procedure.
oRs.Close
oCn.Close
Set oRs = Nothing
set oCn = Nothing
|
|
|
|
|
query [message #93441 is a reply to message #93418] |
Sun, 16 January 2005 18:52 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
resleef c y
Messages: 1 Registered: January 2005
|
Junior Member |
|
|
hello
i am doing mca course and i am doing a project.i want to connect oracle and vb.so kindly send some useful tips to the above email id
resleef c y
|
|
|
|
thanking u [message #93468 is a reply to message #93418] |
Fri, 11 February 2005 01:29 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
resleef
Messages: 1 Registered: February 2005
|
Junior Member |
|
|
thanks for the information u have given in reply to my question "how to connect oracle with visual basic
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Sep 07 19:41:41 CDT 2024
|