OBDC Oracle connection in a ASP.net web page

[Guest]

Hi,

I have a problem connecting to Oracle using and ODBC connection in a ASP.Net
web page.

The TNS Names works fine because when I create a DSN it works, and it works
in SQL Plus.

Here are the error I get depending on the connection string:
Using the right server (Oracle 9i), and the Native Oracle Driver
Driver={Oracle in OraHome92};Server=MyServer;Uid=MyUser;Pwd=MyPW;
or
Driver={Microsoft ODBC for Oracle};Server=MyServer;Uid=MyUser;Pwd=MyPW;

[Oracle]ORA-12154: TNS:could not resolve service name
[Microsoft][ODBC Driver Manager] The driver doesn't support the version of
ODBC behavior that the application requested (see SQLSetEnvAttr).

Using an other server (Oracle 8i), and the Microsoft ODBC driver
Driver={Oracle in OraHome92};Server=Server2;Uid=MyUser;Pwd=MyPW;
or
Driver={Microsoft ODBC for Oracle};Server=Server2;Uid=MyUser;Pwd=MyPW;

[Ora]ORA-12560: TNSrotocol adapter error
[IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed
[Microsoft][ODBC driver for Oracle][Oracle]ORA-00928: missing SELECT keyword

Thanks,
Ben.

 

[Stephen Barrett]

Grant ASPNET, "Network Service", IUSR_<machinename>, and IWAM_<machinename>
Read, Read & Execute, and List Contents rights to the root folder of your
Oracle installation, Recycle IIS, and it should work.

 

[Guest]

1. Right mouse click on the References when you are looking at the Project
Explorer ->Add Reference-> and select the System.Data.OracleClient namespace.

2. add on top of the code behind: Imports System.Data.OracleClient (in VB)
or using System.Data.OracleClient; (in C#)

3. Create a function like this to test your connection:

Public Function DataSet1(ByVal PARAM1_VALUE As String, ByVal PARAM2_VALUE As
Integer) As DataSet

dim ds = New DataSet
dim cmd = New OracleCommand
cmd.CommandText = "OracleSchema.OracleTable.StoredProcName"
cmd.Parameters.Clear()
cmd.Parameters.Add(New OracleParameter("res_cur",
OracleType.Cursor)).Direction = ParameterDirection.Output
cmd.Parameters.Add(New OracleParameter("PARAM1", OracleType.Char,
2)).Direction = ParameterDirection.Input
cmd.Parameters.Add(New OracleParameter("PARAM2",
OracleType.Number)).Direction = ParameterDirection.Input
cmd.Parameters("PARAM1").Value = PARAM1_VALUE
cmd.Parameters("PARAM2").Value = PARAM2_VALUE
cmd.CommandType = CommandType.StoredProcedure
strCon = AppSettings("DBConnectionString") 'This where you store the
'connection string to ORALCE in web.config
'e.g. in the web.config you would have
'<configuration>
'<appSettings>
' <add key="DBConnectionString" value="user id=mysuername;data
' source=schemaName;password=mypassword;Connection Lifetime=3600" />
'</appSettings>
con = New OracleConnection(strCon)
con.Open()
cmd.Connection = con
da = New OracleDataAdapter(cmd)
da.Fill(ds, "TABLE1")
da.Dispose()
con.Close()
return ds
End If