What am i doing wrong?

gerire

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.

Dim conn As ADODB.Connection
Dim SqlQuery As String
____________________________________

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?

Ger

Evil Phil

I do notice that you're connection string is missing a server name. You should use something like:

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

Also you could just use the ActiveX Data Object 2.1 Library, that has the Recordset objects in there. Better yet, use 2.5. Unless there's some reason for using both Data Object and Recordset libraries?

gerire

put that in and its the exact same error at the same location, sorry for delay in responding
Ger

Evil Phil

What's the error message?

gerire

Oh s**t I thought I had included the pic in the previous post

Evil Phil

I look up the error number and it mentioned permissions but didn't relate directly to Oracle. You could try trapping the error when its raised : it will give you a much better idea of what's happening.

Try something like*:

public sub ConnectDB()
On Error goto Err

   'ADO Code goes here

   Exit Sub
Err:
    MsgBox Err.Description & vbCrLf & Err.Source, vbOKOnly
exit sub

The message box will give you much better detail than just an error number. The Err object can return a lot of detail, it is your friend


* Disclaimer: Code may contain errors as its only included for illustration purposes

bonkey

Ummm....noticing that its an ODBC driver...

are you trying to do a DSN-less connection, or use a DSN?

If you have set up a DSN for teh DB, then your connection string needs a "DSN=blah" added to it.

If, on the other hand, you're not using a DSN, then don't you need the server defined in the Net8 Client Configuration utility in order for the ODBC driver to work....or is my memory completely fscked?

jc

Kernel32

I assume you have installed the Oracle Client tools and done all the Net8 setup stuff on the machine your are building the VB appliation on?

gerire

ok whats this net8 stuff? probably a simple thing that I may have flown past during installation? What do I need to do if this is what is missing on my machine?

bonkey

AS part of an Oracle install you need to install the Oracle "transport layer" which is called Net8.

Basically, every client machine which wishes to connect to an Oracle server requires a client-install of the Net8 libraries.

After that, you can use the Net8 Configuration Manager (or other tools) to define a connection to a database.

jc

gerire

ok i need this machine to be the host for the oracle and also the client for the vb development. As far as I remember I chose the standalone installation so this should have covered those scenarios.
Is there a way of checking that it is installed correctly? And enabling it if it isn't set up properly? What should the settings be. Oraclle isn't my strong point never was, but I have to use it I have a lot of coding done at this stage and the Oracle side of things seems to be holding me back.
Does anybody think I should uninstall the lot and go for a fresh reinstall of oracle or is there a way to get this transport layer working etc?
Ger

bonkey

If you installed the Oracle server on the same machine, then you have net8.

To be honest, I would recommend you find a step-by-step idiots guid to querying oracle from visual basic. A quick google-search should find one for you.

jc

Mick L

You can confirm that your connection string is valid by doing the following (Assuming you have MS Access):

Create a DSN to connect to your Oracle database.
Create an Access database and create a linked table to your Oracle database using the DSN.

Open the MSysObjects table in Access (might need to go to Tools>Options>View>System Objects). There will be an entry in the table for the link you created, including the Connection string.

If you can view data in the table then the problem is not with your connection string and may be something to do with a missing reference, driver or something.