ASP connection to Oracle

[dinober]

Dear All,

I tried to connect to Oracle by ASP as follow.

1>Dim conn,param,rs,sql
2>set conn=Server.CreateObject("ADODB.Connection")
3>param="driver={Oracle ODBC driver}"
4>conn.Open param & ";ODBC;DSN=ora;UID=abc;PWD=abc;"
5>sql="select * from table-1"
6>set rs=conn.execute(sql,dbSQLPassThrough)
7>response.write rs.fields(1).name
8>response.write "=" & rs.fields(1).value

All processes works fine except line <8>.
It seems no current record, but there are data in that table.

Where is the bug or syntax error?

Thanks you all!!

[HarryW]

This isn't actually an answer to your question, it's just an enquiry from someone who's done some ASP and DB stuff but not a lot.

I've never seen the .name and .value properties of a field, I always just used rs.fields(index) or rs.fields("fieldname"), and that gave the value. Is .value the default then, if you don't specify the whole thing?

[dinober]

Dear,
rs.fields(index)
rs.fields("fieldname")
rs.fields(index).value
are all the same.

and rs.fields(index).name is the field name for that field.

Thanks for your reply.

[HarryW]

Ah right, thanks

Maybe you should rs.MoveFirst so that you know that there is a current record. I'm not sure what the current record is when you first create a recordset, perhaps it's not got a current record yet.

[dinober]

yes, I tried that too.

but if I use rs.moveFirst ... this will cause error.
That is to say, it thinks rs is an empty recordset.
But actually the database is not empty...

And I thought the wrong recordset may be taken, but

7>response.write rs.fields(1).name ---> print the correct field name!

I use the same way by VB, and connect to the same oracle data.
It's works fine.

[monte96]

Why are you using dbSQLPassThrough when you are connecting to the Oracle datasource directly?

I thought dbSQLPassThrough is for accessing ODBC datasources through Jet/DAO.

[dinober]

Dear,

I am very appreciated your response.
You are right, but it's not working without dbSQLPassThrough neither.

Maybe my whole idea is not good.

What is the proper way to connect Oracle from ASP?

I'd tried:
(1)ODBC -- good for VB, but not ASP
(2)oo4o -- after getting oo4o driver, still miss something

Or I have to establish middle component object like webClass to be used for ASP?

Thanks for all of you!!

[monte96]

1) Make sure the Oracle client is installed on the server. Without it, you will not be able to connect properly.

2) Make sure TNS names are set up correctly on the server.

3) Use this:

Dim cnConn

Set cnConn = Server.CreateObject("ADODB.Connection")
cnConn.Open "Provider=MSDAORA;Data Source=" & AnyValidTNSName & ", " & USERID & ", " & PASSWORD

I've found this to be the most reliable way to open an oracle connection. I usually use an include file that has the USERID and PASSWORD variables declared as constants

[dinober]

Very appreciated your answer!!!!

Maybe the version problem, I need to modify as follow:

Dim USERID, PSW,TSN
USERID="xxx"
PSW="xxx"
TSN="yyy"
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=MSDAORA;Data Source=" & TSN & "; User ID=" & USERID & ";Password= " & PSW

Now the process is ok!

Thanks all my lovely friends!

[dinober]

I deploy the above code to another machine, and find error:
"provider cannot be found".

I also download ole db , and MDAC 2.6 from Microsoft, but still the same.

What package is the msdaora.dll included in?

Thanks!!!

As monte96 said before, did you install the Oracle driver on the machine you deployed to? You need the Oracle driver to talk to an Oracle database.

[dinober]

Originally posted by MadWorm
As monte96 said before, did you install the Oracle driver on the machine you deployed to? You need the Oracle driver to talk to an Oracle database.

Oracle drivers are loaded, I can use net8 to connect to Oracle.

As I said, it run well in one machine.
But when I tried to deploy same code to another machine,
it miss some drivers.

This afternoon, I installed oo4o and some Oracle stuffs to that machine, it's ok again.

I am just wondering, when using provider MSADORA, which dll or files are necessary.

Thanks for you answer.

[monte96]

You can download the MDAC component checker which may help you resolve issues with MDAC drivers:

MDAC Component Checker

You need to install both the Oracle Client software and MDAC.