asked on

ASP-Oracle9i COnnection Problem.

Hi People.

i have installed Oracle OLEDB Provider but i am unable to connect to the Oracle 9i DB.

My Page Looks like this

<%
Set objConn = Server.CreateObject("ADODB.Connection")

objConn.Open "Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=scott;Password=tiger;Data Source=ned;"

Set objRs = objConn.Execute("SELECT * FROM EMPLOYEE")

Response.Write "<table border=1 cellpadding=4>"
Response.Write "<tr>"

For I = 0 To objRS.Fields.Count - 1
Response.Write "<td><b>" & objRS(I).Name & "</b></td>"
Next

Response.Write "</tr>"

Do While Not objRS.EOF
Response.Write "<tr>"

For I = 0 To objRS.Fields.Count - 1
Response.Write "<td>" & objRS(I) & "</td>"
Next

Response.Write "</tr>"

objRS.MoveNext
Loop

Response.Write "</table>"

objRs.Close
objConn.Close
%>

Error i get is :
Error Type:
ADODB.Connection (0x800A0E7A)
Provider cannot be found. It may not be properly installed.

mcse20002000

ASKER

I have MDAC 2.7 Installed

buff_butler

Do you have the orracle TNS names entry set up properly? Looking at the code it would be called 'ned'?

aelatik

Try changing the provider from OraOLEDB.Oracle.1 to MSDAORA

ASKER CERTIFIED SOLUTION

joeposter649

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

joeposter649

BTW, MSDAORA has limitations such as you can't acess clobs and blobs.

joeposter649

Here's what I mean about the vbs script...

Create test.vbs with the following code then exucte it at the command prompt...

Set rs = CreateObject("ADODB.Recordset")
cnstr = "Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=scott;Password=tiger;Data Source=ned;" rs.Open "select sysdate from dual", cnstr, 3, 3
WScript.echo rs("sysdate")
rs.Close
Set rs = Nothing

cheyennejk

I use Oracle 9i and the code below works. (This assumes that Oracle is installed in the default port. If not, then the port # must be included in the connection string)

Try something like this:

StrConnect="Provider=MSDAORA.1;Data Source=database_scheme;User Id=user_name;PASSWORD=database_password;"

set objConnection=server.createobject("adodb.connection")
objConnection.open StrConnect

varSQL = "SELECT DISTINCT USER_ID FROM EMPLOYEE"

set rstemp = objConnection.Execute(varSQL)

Do While NOT temp_record_set .EOF

Response.Write "<td><b>" & rstemp (0) & "</b></td>"
rstemp.MoveNext
Loop

rstemp.close
set rstemp=nothing

objConnection.close
set objConnection=nothing