connecting to oracle

[kondakindi]

hi all,

I am new to asp. I need to connect to an oracle database which is on the same system as the IIS. I wrote the following code for it

<%
dim conn,rs
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider = MSDAORA;User ID=admin;Password=admin;Data Source=myodbc;DATABASE=test;"
Set rs = server.CreateObject("ADODB.Recordset")
rs.open "select * from users",conn
while not rs.eof
response.write(rs.fields(0) & "," & rs.fields(1))
wend

%>

on running this code i am getting this error..

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed
/test.asp, line 13


i dont where i am going wrong... plz help me with this

Thanks

 

[Sheco]

Which line is line 13?

Is this happening when you try to open the connection or when you try to use it?

After opening the connection, check the State property of the connection object to make sure that it is equal to 1.

Also check that conn.Errors.Count = 0

 

[DotNetGnat]

for ODBC:

"Driver={Microsoft ODBC for Oracle};Server=OracleServer.world;Uid=Username;Pwd=asdasd;"
for OLEDB:

"Provider=msdaora;Data Source=MyOracleDB;User Id=UserName;Password=asdasd;"

-DNG

 

[Sheco]

Also this site can be helpful:

http://www.connectionstrings.com

Another trick is to use a Visual Interdev Data Project to build a connection string.

 

[steven290]

try these

<%
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Driver={Microsoft ODBC for Oracle};Server=OracleServer.world;Uid=xyz;Pwd=xyz;Database=tplp;"
Set rs = conn.Execute("SELECT * FROM tblname")

or

<%
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider = MSDAORA;User ID=myuser;Password=mypwd;Data Source=myserver;DATABASE=mydb;"
Set rs = conn.Execute("SELECT * FROM tblname")
%>

or

<%
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=MSDASQL;DRIVER={Microsoft ODBC for ORACLE};UID=User;PWD=Password;Server=Your_TNSNames_Alias;DATABASE=mydb;"
Set rs = conn.Execute("SELECT * FROM tblname")
%>

 

[kondakindi]

I tried using this code
<%
dim conn,rs
Set conn = CreateObject("ADODB.Connection")
conn.ConnectionString = "Driver={Microsoft ODBC for Oracle};Server=OracleServer.world;Uid=admin;Pwd=admin;Database=test"
Set rs = server.CreateObject("ADODB.Recordset")
rs.open "select * from users",conn
while not rs.eof
response.write(rs.fields(0) & "," & rs.fields(1))
wend
%>

i am getting the same error... line13 is the line where i am specifying the connection string.

what is the server name? how do u find what the name of the server is.

thanks.

 

[Sheco]

You should be able to specify the connection string to be anything without getting an eror.

This should not throw an error:
conn.ConnectionString = "blah blah fake whatever"


But if you used that string then you could get an error when trying one of these:
conn.Open
or
rs.open "select * from users",conn

 

[kondakindi]

sorry abt that... ya it is giving me an error in the line where i open the recordset.

can u tell me what would be the server name in the field server of the connection string.

 

[Sheco]

That is a really question for your DBA.

You might be able to figure it out by looking at the config or any other program on your computer that might talk to Oracle or look in to see if you have a static tnsnames.ora file floating around on your computer.

TNS is like DNS for oracle.

 

[Sheco]

oh, and to complete the analogy, the tnsnames.ora file works like a hosts file.

 

[DotNetGnat]

do you have the oracle client software installed on your machine?? It will help you to find out the information. Also it would be better if you contact your DBA to get the information.

Thanks

-DNG

 

[kondakindi]

i did that and got the server host name from tnsnames.ora file.

and the code is like this

dim conn,rs
Set conn = CreateObject("ADODB.Connection")
conn.ConnectionString = "Driver={Microsoft ODBC for Oracle};Server=lovinasd;Uid=admin;Pwd=admin;Database=test"
Set rs = server.CreateObject("ADODB.Recordset")
rs.open "select * from users",conn
while not rs.eof
response.write(rs.fields(0) & "," & rs.fields(1))
wend


this code when ru gives the following error...

Error Type:
ADODB.Recordset (0x800A0E7D)
The connection cannot be used to perform this operation. It is either closed or invalid in this context.
/test.asp, line 16


line 16 is the line where the recordset is opening the connection( rs.open ..........)

 

[DotNetGnat]

ok try again something like this since you now have the correct server name:

conn.ConnectionString = "Provider=msdaora;Data Source=yoursourcename; Uid=admin; Pwd=admin"

-DNG

 

[Sheco]

try explicitly opening the connection:

...
conn.ConnectionString =  "Driver={Microsoft ODBC for Oracle};Server=lovinasd;Uid=admin;Pwd=admin;Database=test"

[red]
conn.Open
If conn.State <> 1 Then
  Response.Write "<BR>Failure Opening ADO Connection!"
  Response.End
End If
[/red]

Set rs = server.CreateObject("ADODB.Recordset")

...

 

[Turkbear]

Hi,
Switch to DSN-less ADO connection:

myconn= Server.CreateObject("ADODB.Connection");
myconn.ConnectionTimeout = 15;
myconn.CommandTimeout = 30;
myConnString="Provider=MSDAORA;Password=secret;User ID=youruser;Data Source=tnsnames_entry;"
myconn.ConnectionString=myConnString
myconn.Open;


Notice, No Server/Host name needed, just the tnsnames.ora entry..
Also, I prefer to get the records using this method:

SqlStr = "select * from users";
rs = myconn.Execute(SqlStrEmp);


Just a preference..

PS: this was in a JavaScript function, so some mods may be
needed for VBScript..

 

[Sheco]

that stuff that I put in red there was just to show explicitly opening a connection and where you would do it within the confines of the code provided... it is not an affirmation of the validity of the surrounding code. No warranties express or implied. Professional driver on a closed track. Your milage may vary. Do not try this at home. Price for Tax Title & License.

 

[sun7]

my connection script..

Dim DBName, DB_user, DB_pass
Dim DBconn

DBName = "****"
DB_user = "****"
DB_pass = "*****"

set DbConn = Server.CreateObject("ADODB.Connection")
with DbConn
.provider = "MSDAORA.1"
.properties("Data Source") = DBName
.Properties("User Id") = DB_user
.properties("Password") = DB_pass
.CommandTimeout = 300
.CursorLocation = 3 'adUseClient
.Open
end with