asked on

Connection

I'm new at both Oracle and ASP. In tnsnames.ora have:
SER1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = machine1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ser)
)

For "ser": For SQL+ use:
login: bylj
pass: byljpass
HostString: ser

Now want to connect my site I first need to set up an ODBC-connection:

ODBC-DataSource-Administrator/System-DSN/Add/Oracle8-ODBC-Driver-Setup-Window:

What do I put under "DataSource":
ServiceName: ?? (WHAT DO I PUT HERE?)
UserID: ?? (WHAT DO I PUT HERE?)

Once an ODBC is constructed, need to make a connection-string. How do I make one using the following example for MS-Access:

strConnect = "Provider=MSDataShape;Data Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source="C:\db.MDB;"
Set conn = Server.CreateObject("ADODB.Connection")conn.Open strConnect

Need help

ASKER CERTIFIED SOLUTION

jitganguly

membership

This solution is only available to members.

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

Start Free Trial

bylj

ASKER

jitganguly: Firstly, thanks. I'm using DataShaping and therefore would like to get the connection in the following format:

strConnect = "Provider=MSDataShape;Data Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source="C:\db.MDB;"
Set conn = Server.CreateObject("ADODB.Connection")conn.Open strConnect

need help.

harnal

Why use a DSN? You can pass a DSN-Less connection string directly to the provider, and not have to fool around with ODBC and DSN. It is much easier.

Example:

'Define database object
dim dbObj
set dbObj=server.createobject("ADODB.Connection")

'Set database connection string (DSN-LESS of Course) :)

''''''''''''''''''''''''''''''''''''''''''''''''''
'This one is for current Oracle Driver
''''''''''''''''''''''''''''''''''''''''''''''''''

dim dbStr
dbStr= "Driver={Microsoft ODBC for Oracle};" & _
"Server=OracleServer.world;" & _
"Uid=UserID;" & _
"Pwd=Password;"
''''''''''''''''''''''''''''''''''''''''''''''

''''''''''''''''''''''''''''''''''''''''''''''''''
'Or use this one for older Oracle Connections.
''''''''''''''''''''''''''''''''''''''''''''''''''

dim dbStr
dbStr= "Driver={Microsoft ODBC for Oracle};" & _
"ConnectionString=OracleServer.world;" & _
"Uid=UserID;" & _
"Pwd=Password;"
''''''''''''''''''''''''''''''''''''''''''''''''''

dbObj.Open dbStr 'Open database connection.

Hope this helps out.

~ch

jitganguly

You can try with something like

"Provider=MSDAORA;Data Source=serverName;User ID=userName; Password=userPassword;"

http://msdn.microsoft.com/library/psdk/dasdk/pg_a4c9v.htm

bylj

ASKER

jitganguly: With
"Provider=MSDAORA;Data Source=serverName;User ID=userName; Password=userPassword;"

Do I need to setup an ODBC connectivity? Also how can I find the serverName?(My machine-name:"machine1", webserver:"localhost")

jitganguly

No you don't need to create a odbc dsn.Server name shoudl be same as tnsname in tnsnames.ora. Also try to give .world name i.e. ser1.world

bylj

ASKER

jitganguly:

<%
strConnect = "Provider=MSDAORA;Data Source=ser1.world;User ID=wtuser; Password=wtuser;"
Set conn = Server.CreateObject("ADODB.Connection")conn.Open strConnect
strSQL = "SELECT * FROM Customer;"

Set rs = conn.Execute(strSQL)

Response.write rs(0)
Response.write rs(1)

conn.close
SET rs=NOTHING
SET conn=NOTHING
%>

I'm getting the following error:
"Error Type:
Microsoft OLE DB Provider for Oracle (0x80004005)
Oracle error occurred, but error message could not be retrieved from Oracle.
/testOracle.asp, line 7"

Please help

jitganguly

byli,
I can not access my mail.So could not see your reply.
Now let me see your problem.
1. Are you able to got SQLPLus ?
2. Can you ping your oracle server by tnsping80 located in oracle/bin directory ?
3. You do not need a semicolon after the sql statement

strSQL = "SELECT * FROM Customer;" ' Wrong
strSQL = "SELECT * FROM Customer" ' right

bylj

ASKER

1. Are you able to got SQLPLus ?
Yes, I can into SQL+

2. Can you ping your oracle server by tnsping80 located in oracle/bin directory ?
How do I it? (I'm new so please bear with me)

3. You do not need a semicolon after the sql statement
Thanks

Still stuck...

jitganguly

If SQLPlus iw working ok, you don't need to ping it. The way to do is go to oracle\bin in dos prompt and type tnsping80 yourservername

1.Which Mdac you have ? Try to use 2.5
2. Did you install oracle client on the web server ? SQL*Net etc. ?
3. Why don't you try connecting with a DSN first
4. I am not sure about this connection

"strConnect = "Provider=MSDAORA;Data Source=ser1.world;User ID=wtuser; Password=wtuser;"
Set conn = Server.CreateObject("ADODB.Connection")conn.Open strConnect"

Try
strConnect = "Provider=MSDAORA;Data Source=ser1.world;User ID=wtuser; Password=wtuser;"
Set conn = Server.CreateObject("ADODB.Connection")

conn.Open strConnect ' This line needs to be in the next of the previous line

5. Check this link
http://support.microsoft.com/support/kb/articles/Q193/2/25.ASP

Moondancer

jitganguly

I think I deserve some points here.

Netminder

Admin notified of User neglect. Force-accepted by
Netminder
Community Support Moderator
Experts Exchange