OBDC Oracle connection in a ASP.net web page

  • Thread starter Thread starter Guest
  • Start date Start date
G

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: TNS:protocol adapter error
[IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed
[Microsoft][ODBC driver for Oracle][Oracle]ORA-00928: missing SELECT keyword

Thanks,
Ben.
 
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.
 
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
 
Back
Top