Results 1 to 14 of 14
  1. #1
    Join Date
    Nov 2003
    Posts
    22

    Unanswered: Visual Basic to Oracle Connection...Please help

    Hi,

    Can someone please provide me detailed instructions on how I can connect Visual Basic 6.0 Professional to Oracle 8.1.5 (which is on a server) using the Oracle Driver (not Microsoft ODBC Driver)?
    Do I need a DSN and TSN?
    What is the VB code that I use and where would I place it in my VB Project?
    What references/libraries do I need--how would I add them to the project?
    How can I send a SQL queries and retrieve results from the Oracle server?
    As you can tell, this is my first time doing this...please help. I would greatly appreciate it. Thanks.

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    First, do you have the oracle driver installed on your machine ? If it is installed, create a vb project (standard exe) and add the ado data control to this project (project -> components -> ms ado data control). Create the control on your form and go to properties -> connectionstring. Follow the connectionstring dialog chosing the "Use Connection String" Build... option. Choose the appropriate provider and connection information. Once this is completed copy that connection string (this will be used later).

    Let me know when you have gotten this far.

  3. #3
    Join Date
    Nov 2003
    Posts
    22
    rnealejr,

    Thanks. I have that done. Now how can I send in user specified queries, i.e., user enters a phone number, which goes as part of a query and I get the result back from the server where Oracle is on. Thanks for all your help. I really appreciate it.

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    Well first I would work on using ado before jumping into accepting input and returning the values (make sure in your references that microsoft activex data objects 2.X is checked).

    Dim cn As ADODB.Connection 'as you are typing adodb vb will automatically autohelp - if not then you don't have a reference to ado
    Dim rs As ADODB.Recordset

    Set cn = New ADODB.Connection
    cn.Open "put that connection string here"
    Set rs = New ADODB.Recordset
    rs.Open "select field1,field2 from table where ...", cn
    While Not rs.EOF
    MsgBox rs(0) 'this will display the result from field1
    rs.MoveNext
    Wend
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing

  5. #5
    Join Date
    Nov 2003
    Posts
    22
    Thanks for all the help! The below is working, now how do I allow the users to enter in info which goes as part of the query? Also, using the below code the query returned more than one row and I had that many dialog boxes to click "OK" on...can you please tell me how I can have all the rows returned by the query appear inside something like a listbox where it can be scrolled through by the user. Thanks again for all the help, I really appreciate it.



    Originally posted by rnealejr
    Well first I would work on using ado before jumping into accepting input and returning the values (make sure in your references that microsoft activex data objects 2.X is checked).

    Dim cn As ADODB.Connection 'as you are typing adodb vb will automatically autohelp - if not then you don't have a reference to ado
    Dim rs As ADODB.Recordset

    Set cn = New ADODB.Connection
    cn.Open "put that connection string here"
    Set rs = New ADODB.Recordset
    rs.Open "select field1,field2 from table where ...", cn
    While Not rs.EOF
    MsgBox rs(0) 'this will display the result from field1
    rs.MoveNext
    Wend
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    You can break the loop by hitting ctrl-break.

    You can add the field to the listbox:

    List1.AddItem rs(0)

    For the other question, can you be more specific as to what is needed to update ?

  7. #7
    Join Date
    Nov 2003
    Posts
    22
    Thanks! I figured out the other question...but have a new one. How can I display the results (e.g. phone numbers) of a query in a ComboBox and then allow the user to select one of the phone numbers from the ComboBox and then use this phone number as part of the next query (e.g. where phonnumber = <user selected phonenumber from ComboBox). thanks for all the help. i really appreciate it.


    Originally posted by rnealejr
    You can break the loop by hitting ctrl-break.

    You can add the field to the listbox:

    List1.AddItem rs(0)

    For the other question, can you be more specific as to what is needed to update ?

  8. #8
    Join Date
    Feb 2002
    Posts
    2,232
    Do you want an exact match based on the phone number ?

  9. #9
    Join Date
    Nov 2003
    Posts
    22
    Phone number is a primary key in one of the tables and a foreign key in another. So I would like the user to select a phone number from a combobox, and then pass this phone number selected by the user as part of another query for another table in which the phone number is a foreign key. Like there is the "AddItem" feature of ListBox/ComboBox, is there a feature in ComboBox that would allow me to use the phone number the user selected in the next query? Thanks for all your help.

    Originally posted by rnealejr
    Do you want an exact match based on the phone number ?

  10. #10
    Join Date
    Feb 2002
    Posts
    2,232
    You can use the filter method of the recordset object. So basically you would have the combobox tied to a particular query/recordset. When the user selects a phone number you can modify the filter method of the primary recordset to only show those records that have that filter.

  11. #11
    Join Date
    Nov 2003
    Posts
    22
    Do you by any chance know the syntax of this filter method? It would really help. Is there any way I can fetch the highlighted/selected user entry of the ComboBox (like using ComboBox.Container or ComboBox.Selected)? Thanks.

    Originally posted by rnealejr
    You can use the filter method of the recordset object. So basically you would have the combobox tied to a particular query/recordset. When the user selects a phone number you can modify the filter method of the primary recordset to only show those records that have that filter.

  12. #12
    Join Date
    Feb 2002
    Posts
    2,232
    You have 2 recordsets: rs1, rs2

    rs1 is the main recordset
    rs2 populates the combo

    combo will have a click event:
    when a user clicks the combo box to select a phone number you will put the code to handle this event:

    rs1.filter = "phone = " & combo1.text


    Depending on whether you will use a grid or text boxes to display the data to the user the next steps will vary.

  13. #13
    Join Date
    Nov 2003
    Posts
    22
    When I try to display the text selected from a ListBox onto the caption of a label, I get the "Compile error: Argument not optional". Below is the code I'm using, please help:


    Private Sub ListBox2_Click()

    Label17.Caption = ListBox2.Selected

    End Sub



    Originally posted by rnealejr
    You have 2 recordsets: rs1, rs2

    rs1 is the main recordset
    rs2 populates the combo

    combo will have a click event:
    when a user clicks the combo box to select a phone number you will put the code to handle this event:

    rs1.filter = "phone = " & combo1.text


    Depending on whether you will use a grid or text boxes to display the data to the user the next steps will vary.

  14. #14
    Join Date
    Apr 2011
    Posts
    1
    hey....i have done just wat u asked

    now i'm getting error in the connection string
    whnever i try running a query
    for eg. in my login form
    whn i hit login command button
    it says smthing related to connection string

    like "password" invalid identifier
    please help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •