Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 334
  • Last Modified:

connect vb to oracle

how can i connect vb to oracle using code?
my front end is vb?
then please suggest me answer.
0
vijay_bp
Asked:
vijay_bp
  • 4
  • 3
  • 2
  • +4
1 Solution
 
Elmo_Commented:
Vijay,

Here are a couple of examples

I hope they help.

Cheers,

Ed.

    ' 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

    '------------------------------------------------------------------------------------------
     
    ' DAO Example (Data Access 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)
        ' Give a message box of the 1st column
        .movenext
        howmany = howmany + 1
    Loop
       
     '------------------------------------------------------------------------------------------
    'ADO Example
    Dim conn As ADODB.Connection
    Dim rs As Recordset
    conn.Open "...", "...", "..."
    '           ^DSN   ^User  ^Password
    Set rs = conn.Execute("SELECT * FROM theTable")
    Do While Not rs.EOF
       MsgBox rs(i).Value
       rs.movenext
    Loop

0
 
rkot2000Commented:


INFO: Visual Basic Accessing an Oracle Database Using ADO (Q176936)

http://support.microsoft.com/search/preview.aspx?scid=kb;en-us;Q176936

HOWTO: Retrieve Resultsets from Oracle Stored Procedures (Q174679)
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q174679
0
 
Éric MoreauSenior .Net ConsultantCommented:
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
paulllCommented:
To use ADO to connect to an Oracle database, you need to install Oracle Client.  MS makes an Oracle Provider but it will not open all the field types that Oracle has from versions 8i and up.
0
 
Elmo_Commented:
vijay_bp,

Could you please close out this question?  You have been supplied with answers that should accomplish what you have asked for.


To date you have asked four questions and accepted answers for none of these.  If you do not close out your questions.  I will ask a moderator to do so.


Cheers,

Ed.
0
 
paulllCommented:
Install Oracle client for the Oracle version you need to access.  Get it from Oracle.  Install as any user type (User, Programmer, etc.).

If an Oracle database is not set up with Oracle client install, search your orant/network/admin directory for "tnsnames.ora"

You need an entry such as:
TEST =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = server.orabox.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = TEST)
    )
  )

Where "TEST" is the server name, you must know the server name.  DNS will not necessarily take care of this if Oracle is on a UNIX box, etc.
server.orabox.com is the address refecting the IP address, you must know the address.
1521 is the PORT, you must know the port.

In project references, choose "Microsoft ActiveX Data Objects 2.5" in Win2000 or
"Microsoft ActiveX Data Objects 2.1" and "Microsoft ActiveX Data Objects Recordset 2.1" in Win95/98

You need to install DCOM in Win95/98 also.  The Install Wizard will automatically include it.
-------------------------------------------------------
Private ConDB As ADODB.Connection
Private rs As ADODB.Recordset
Private ProviderType, ServerName As String, SQL_String As String
Private User As String, UserPassword As String

ProviderType = "Provider=OraOLEDB.Oracle" & User & UserPassword & ";Data Source=" & ServerName & ";DistribTx=0"
ConDB.Open ProviderType

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open SQL_String, ConDB, adOpenKeyset, adLockOptimistic, adCmdText

This will open an Oracle database if you have rights for the tables with the User and UserPassword set correctly.  I have never ever seen it fail.

Paul
0
 
Elmo_Commented:
To Paulll,

You should NEVER propose an Answer when there are already valid solutions for the questioner to review.

Even when experts give answers they still only submit them as comments.

These practices are listed in the EE Guidlines.

Please review them.

Cheers,

Ed.
0
 
paulllCommented:
Sorry, I thought I'd try to be thorough and get him finished. I wondered why I almost never see any answers, just comments.  I'll stick to comments just to be safe.

Paul
0
 
Elmo_Commented:
Much Appreciated.
0
 
Éric MoreauSenior .Net ConsultantCommented:
Please maintain:

Questions Asked 4
Last 10 Grades Given  
Question Grading Record 0 Answers Graded / 0 Answers Received
0
 
kodiakbearCommented:
Dear paulll
I've rejected your proposed answer as Experts Exchange holds an experiment to work without the answer button.

See: <http://www.experts-exchange.com/jsp/communityNews.jsp>
Paragraph: Site Update for Wednesday, November 06, 2002

By this rejection the Asker will be notified by mail and hopefully he will take his responsibility to finalize the question or post an additional comment.
The Asker sees a button beside every post which says "Accept This Comment As Answer" (including rejected answers) -- so if he/she thinks yours is the best, you'll be awarded the points and the grade.

EXPERTS: I will return in seven days to close this question.
Please leave your thoughts and recommendations here

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Thanks !

kodiakbear
Experts Exchange Moderator
0
 
DanRollinsCommented:
Hi vijay_bp,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Accept Elmo_'s comment(s) as an answer.

vijay_bp, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer
0
 
NetminderCommented:
Per recommendation, force-accepted.

Netminder
EE Admin
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now