What am I doing wrong?

Dev Shed Forums Sponsor:

SlickEdit: Code in over 40 languages across 7 platforms. SlickEdit’s unmatched power, speed, and flexibility allows even the most accomplished developers to write better code faster. Download a free trial today!

August 15th, 2003, 08:54 AM

gerire

Registered User

Join Date: Aug 2003

Location: Ireland, looking in on the rest of you

Posts: 14

Time spent in forums: 9 m 7 sec
Reputation Power: 0

Trouble with DSN and connecting to Oracle

Hopefully I wont be making a habit of this, but my VB brain is still acting mad, but its getting there.
Problem. I have an Oracle9i DB setup and have created and committed tables under the system user name. Problem is reading the data from VB, it keeps throwing up errors.

Quote:

Dim conn As ADODB.Connection
Dim SqlQuery As String
____________________________________
Set conn = New ADODB.Connection
Set conn = New ADODB.Connection
conn.ConnectionString = _
"Driver={Microsoft ODBC for Oracle};" & _
"UID=SYSTEM;PWD=MANAGER"
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
conn.Open
rs.AddNew
rs.Fields("name") = UCase(frmDrinks.txtNewDrink.Text)
rs.Fields("price") = UCase(frmDrinks.txtNewPrice.Text)
rs.Update
rs.Close

I get a runtime error on the conn.open line. Any ideas or solutions much appreciated at this moment.
I do have the following libraries activated:
Microsoft activeX data oblects 2.1 library
Microsoft activeX data oblects Recordset 2.5 library
Am i missing a library that i need?
Error:-2147467259

Ger

Last edited by gerire : August 20th, 2003 at 10:07 AM.

August 15th, 2003, 01:29 PM

nopoints

Contributing User

Join Date: Jul 2003

Location: Windsor ON, Canada

Posts: 459

Time spent in forums: 13 h 44 m 22 sec
Reputation Power: 7

what is the description of the error? is it a compile error or does it occur during execution?

oh and stop putting sh!t in the coffee

__________________
Programmer's Corner

August 15th, 2003, 02:26 PM

gerire

Registered User

Join Date: Aug 2003

Location: Ireland, looking in on the rest of you

Posts: 14

Time spent in forums: 9 m 7 sec
Reputation Power: 0

The code is on a button that when pressed will send the data to the DB into the fields described.

When the button is pressed this is the error that shows up.

BTW, I barely put milk or sugar in the coffee, never mind flavours lol.

Thanks for your time

August 15th, 2003, 03:20 PM

nopoints

Contributing User

Join Date: Jul 2003

Location: Windsor ON, Canada

Posts: 459

Time spent in forums: 13 h 44 m 22 sec
Reputation Power: 7

in your connection string:

Code:

conn.ConnectionString = _ 
    "Driver={Microsoft ODBC for Oracle};" & _ 
    "UID=SYSTEM;PWD=MANAGER"

where do you specify the database? are you trying to connect via DSN (ODBC) or not DSN?

August 15th, 2003, 07:09 PM

gerire

Registered User

Join Date: Aug 2003

Location: Ireland, looking in on the rest of you

Posts: 14

Time spent in forums: 9 m 7 sec
Reputation Power: 0

Code:

Driver={Microsoft ODBC for Oracle};" & _
        "Server=MyOracleServer;" & _
        "UID=SYSTEM;PWD=MANAGER"

I got that as a connection string from else where but it still throws up the same error at the same point.
From my understanding using the system UID connects to all tables in the system profile that were created in sql*plus under that user name.
Am I way off here?

August 18th, 2003, 03:28 PM

Kronique

Junior Member

Join Date: Aug 2003

Location: Danbury,CT

Posts: 14

Time spent in forums: < 1 sec
Reputation Power: 0

Dim conn As New ADODB.Connection
Dim SqlQuery As String
conn.ConnectionString "dsn=dsnname;UID=login;PWD=passwd"
conn.Open

You shud be able to open a connection with this when you have configured a dsn. go to odbc data sources in control panel to configure a dsn to the respective oracle db.

also use ADO 2.5 library as reference

August 18th, 2003, 03:37 PM

Kronique

Junior Member

Join Date: Aug 2003

Location: Danbury,CT

Posts: 14

Time spent in forums: < 1 sec
Reputation Power: 0

Dim conn As New ADODB.Connection
Dim SqlQuery As String
conn.ConnectionString = "Provider=MSDAORA;User ID=login;password=passwd;Data Source=oracle db ;"
Dim rs As New ADODB.Recordset

Alternately, if you wish to use OLEDB provider,this is the method.be sure to provide the right name for provider and the db server name (check ur tnsnames).the provider i used connects to oracle 8, check with oracle 9i requirements

August 20th, 2003, 07:09 AM

gerire

Registered User

Join Date: Aug 2003

Location: Ireland, looking in on the rest of you

Posts: 14

Time spent in forums: 9 m 7 sec
Reputation Power: 0

hey thanks for your help but it is still not connecting right. I have no DSN set-up and this is probably the definate problem. Do I need to set up a user DSN, System DSN or a File DSN. Which driver should I use for this, Microshafts connection to Oracle or the Oracle connection for 92?

You imput, trust me, is greatly received
Ger

August 20th, 2003, 08:21 AM

Onslaught

/(bb|[^b]{2})/

Join Date: Nov 2001

Location: Somewhere in the great unknown

Posts: 4,829

Time spent in forums: 1 Day 23 h 5 m 8 sec
Reputation Power: 88

You should set up a system DSN and when you do so, would would actually be using oracle's connection through odbc.
That is one part of what odbc does in this case. It identifies a driver to use for this dsn identified. Then when you connect via ado using the dsn userid and password it checks the odbc driver manager and gets the necessary driver to connect with. Hence the reason for such a simple connection string.
Another method would be to do something along the following lines:

Code:

Dim conn as ADODB.Connection

Set conn = CreateObject("ADODB.Connection")
conn.Open "Your DSN", "Your UserID", "Your Password"

But you have to have the odbc dsn set up correctly first.

Also, in the future please use an acceptable subject title. You can refer to the sticky thread at the top of this forum for more information on how to post a question.

__________________
Joe

How To Ask FAQ | How To Be A Hacker | Hacker Mindset | A FreeBSD Diary

#10

August 20th, 2003, 10:36 AM

gerire

Registered User

Join Date: Aug 2003

Location: Ireland, looking in on the rest of you

Posts: 14

Time spent in forums: 9 m 7 sec
Reputation Power: 0

I have tried everybodies suggestions and have set up DSN connection via Microsofts ODBC for Oracle. Ans still I am getting the same error and it highlights the same bit of text:

Im at a loss to what is wrong here. I have an entire program coded and just need to get it to talk to the DB

#11

August 20th, 2003, 11:31 AM

Onslaught

/(bb|[^b]{2})/

Join Date: Nov 2001

Location: Somewhere in the great unknown

Posts: 4,829

Time spent in forums: 1 Day 23 h 5 m 8 sec
Reputation Power: 88

Ok, questions:
1) Have you installed the Oracle 9i Drivers?
2) Can you test the connection to the database?

#12

August 20th, 2003, 12:28 PM

gerire

Registered User

Join Date: Aug 2003

Location: Ireland, looking in on the rest of you

Posts: 14

Time spent in forums: 9 m 7 sec
Reputation Power: 0

I went to the 'oracle universal installer' which says that Oracle ODBC driver 9.2.0.1.0 is installed. Sql*plus can create, write to and read from the data base. And by using 'the enterprise management console, this shows that the tables and values are added in correctly.

#13

August 20th, 2003, 01:18 PM

Onslaught

/(bb|[^b]{2})/

Join Date: Nov 2001

Location: Somewhere in the great unknown

Posts: 4,829

Time spent in forums: 1 Day 23 h 5 m 8 sec
Reputation Power: 88

Show the complete code that you are currently using now to try and connect to the Oracle server.

#14

August 20th, 2003, 02:11 PM

gerire

Registered User

Join Date: Aug 2003

Location: Ireland, looking in on the rest of you

Posts: 14

Time spent in forums: 9 m 7 sec
Reputation Power: 0

Code:

Dim conn As New ADODB.Connection
Dim SqlQuery As String
_________________________________________________
Private Sub cmdModifyPrices_Click()

   Set conn = New ADODB.Connection
        'conn.ConnectionString = _
        '"Driver={Microsoft ODBC for Oracle};" & _
        '"Server=MyOracleServer;" & _
        '"UID=SYSTEM;PWD=MANAGER"
    conn.ConnectionString = _
    "Driver={Microsoft ODBC for Oracle};" & _
    "dsn=ora9icon;UID=System;PWD=manager1"
    conn.Open
    
    Dim rs As ADODB.Recordset
                Set rs = New ADODB.Recordset
                    conn.Open
    rs.AddNew
                rs.Fields("name") = UCase(frmDrinks.txtNewDrink.Text)
                rs.Fields("price") = UCase(frmDrinks.txtNewPrice.Text)
    rs.Update
    rs.Close

End Sub

#15

August 20th, 2003, 02:25 PM

agsuresh

Junior Member

Join Date: Jun 2002

Location: India

Posts: 5

Time spent in forums: < 1 sec
Reputation Power: 0

Hi

put a ADODC control on a form. use the connection string to set up a connection walk through the steps. if all is right you will see the tables in the data base. come back to start. copy the connection string to the form you are using . It should work fine. You may use dataenvironment in a similar manner. here you can actualy test connection. you may use
Debug.print dataenvironment1.Connection1.ConnectionString to get the connection string. copy and paste this from Immediate window to your app.

Hope this helps.

Suresh