Re: trouble with odbc connection

On 2 Apr 2004 09:17:06 -0800, in comp.databases.oracle.server, tomtailor_at_freesurf.fr (chris) scribbled:

>hello to all readers!
>
>i know my english is terrible, but i hope someone can decrypt this
>posting and is able to help me.

I tried, and got a "possible" solution.

<snip MySQL>

>OH! i forgot:
>the connection under sql+ is working fine and i can perform any sql
>oracle's obbc driver get connection to database at the configure
>dialog.
>but the connectionsring ist *kulz* i think. sorry for doubleposting,

I don't know "kulz", sorry...

>but i thought this is an inportant information.
>under oracle i tryed (after a huge hours surfing)

By "under oracle" I'm not sure what you mean. You did the below in VB, correct?

>strComplete="Driver={Microsoft ODBC for Oracle};" & _

I have never gotten this driver to work in my VB programs.

> "Server=localhost.cta;" & _
> "Uid=Scott;" & _
> "Pwd=******"

You have to Set objConn = new ADODB.Connection before the next line, but then I don't use CreateObject, so I could be wrong.

>Set objConn = CreateObject("ADODB.Connection")
>objConn.open Connect_String(cta), scott, wasx21

What is cta above? If it is a connection string, maybe you meant strComplete?

And you have to set rs = new ADODB.RecordSet before you can do the next line:

>Set rs = objConn.execute ("select * from test;")
>
>Record = rs.Fields(0).Value

You should close rs before you close objConn in the next line...

>objConn.Close

and set rs to nothing first, also.

>Set objConn = Nothing
>msgbox("Done!")
>
>but it did'nt work.

Here is what I came up with in less than half an hour... pasting from previous projects, of course. ;-)

First, you have to register this Oracle connection with M$, going to Control Panel | Administrative Tools | ODBC Data Sources and creating a new DSN. Name it whatever you like, even YourOracleDSN to make testing my code easier. Make sure you can connect. You can also test the connection in VB; if you don't know how you can either ask here (I realize VB is off-topic here) or contact me via email (tredmond at anteon dot com) if everyone here would rather not see it. Now:

New form, with one command button, paste the following code into the form. Then, go to Project | References and check the boxes next to Microsoft Active Data Objects Library 2.7 and Microsoft Active Data Objects Recordset Library 2.7. Then click Run, and you should be good to go.

Option Explicit
Dim objConn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strComplete As String
Dim Record As String

Private Sub Command1_Click()

strComplete = "select * from test"

Set objConn = New ADODB.Connection
objConn.CursorLocation = adUseClient
  objConn.ConnectionString = "Provider=MSDAORA.1;" & _

      "Password=tiger;User Id=scott;Data Source=YourOracleDSN" objConn.Open

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
Set rs = objConn.Execute(strComplete)

Record = rs.Fields(0).Value
MsgBox Record

rs.Close
objConn.Close
Set rs = Nothing
Set objConn = Nothing
MsgBox ("Done!")

End Sub

This code worked for me, and I hope it helps you.

-- 
Teresa Redmond
Programmer/Analyst III
Anteon Corporation
tredmond at anteon dot com