Re: trouble with odbc connection

thx for your posting, but i still have troubles!

i done this in vb script, so i cut the "as" statement at the dim lines and i cut the on_click sub() ... end sub, but it wouldn't work as script. error sas "no adobc defined"

further information:
the script runns on the server, so host = localhost the database name = cta.kin, SID = cta
the table name = test
the user = Scott
the windwos for oracle and oracle in oracle driver are both installed.  -> i would prefer the oracle driver (read about some troubles with the MS driver)

here the modified to script code:

Option Explicit
Dim objConn
Dim rs
Dim strComplete
Dim Record

strComplete = "select * from test"

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

      "Password=***;User Id=scott;Data Source=cta" objConn.Open

Set rs = 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!")

Teresa Redmond <XKEAAGIPVIEZ_at_spammotel.co> wrote in message news:<289fd99942aafc47923a081931724fbe_at_news.teranews.com>...
> 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.
Received on Sat Apr 03 2004 - 02:24:18 CST