locked
connect to oracle 10g from asp.net RRS feed

  • Question

  • User-1104918573 posted

    hello.
    while long search and issues with connection to oracle database,i installed oracle database 10g express edition or XE,32 bits.
    i have win 7 and 64bits machine.
    so i tried to connect to this database after setting the ODBC driver (32 bits driver),but the problem is:
    i successful connected from windows application,but failed from asp.net and i use the same code.
    this is the error from asp.net:

        ERROR [NA000] [Microsoft][ODBC driver for Oracle][Oracle]ORA-06413: Connection not open.
        ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed
        ERROR [01000] [Microsoft][ODBC Driver Manager] The driver doesn't support the version of ODBC behavior that the application requested (see SQLSetEnvAttr).
    and i tried to connect from another laptop using win 7 32 bits,it worked from asp.net and windows application.
    so i don't figure where is the error,and why it worked from windows application but it didn't from asp.net.
    this is the code:
        using System;
        using System.Collections.Generic;
        using System.Linq;
        using System.Web;
        using System.Web.UI;
        using System.Web.UI.WebControls;
        using System.Data.Odbc;
        public partial class _Default : System.Web.UI.Page
        {
        protected void Page_Load(object sender, EventArgs e)
        {
        String con = "DRIVER={Microsoft ODBC for Oracle};DSN=XE;UID=hr;PWD=hr;SERVER=localhost;";
        OdbcConnection conx = new OdbcConnection(con);
        conx.Open();
        OdbcCommand command = new OdbcCommand("insert into test values('hi')", conx);
        command.ExecuteNonQuery();
         
        conx.Close();
        }
        }
    thank you very much.
    Saturday, July 30, 2011 1:20 PM

Answers

  • User269602965 posted

    For ASP.NET the connection string is best placed in the connection string section of WEB.CONFIG,

    and is called by ASP.NET code as like this example below where a DATE_CONTRACT_EXPIRES is being updated.

    This line gets my connection string named "OraConnStr" from web.config

        Dim connectionString As String = ConfigurationManager.ConnectionStrings("OraConnStr").ConnectionString

    Imports System.Xml.Linq.XElement
    Public Shared Sub updateUnitDateContractExpires(ByVal decUnitSeq As Decimal) ' Update the CONTRACTSCHEMA.UNITS.DATE_CONTRACT_EXPIRES to available most future value ' Called from Service Contracts UPDATE Dim connectionString As String = ConfigurationManager.ConnectionStrings("AuthenticatedOracleConnectionString").ConnectionString Try Dim SQL = <SQL> UPDATE CONTRACTSCHEMA.UNITS SET DATE_CONTRACT_EXPIRES = (SELECT DISTINCT LAST_VALUE(a.DATE_END_CONTRACT) OVER (ORDER BY a.DATE_END_CONTRACT NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS DATE_END_CONTRACT FROM CONTRACTSCHEMA.SERVICE_CONTRACTS a WHERE a.UNIT_SEQ = :decUNITSEQ), SEND_CONTRACT_FLAG = 'Send contract' WHERE UNIT_SEQ = :decUNITSEQ </SQL> Using conn As New OracleConnection(connectionString) Using cmd As New OracleCommand(SQL.Value, conn) cmd.Parameters.Clear() cmd.Parameters.Add("decUNITSEQ", OracleDbType.Decimal, decUnitSeq, ParameterDirection.Input) conn.Open() cmd.ExecuteNonQuery() End Using End Using Catch ex As Exception AppCalls.WriteToEventLog(ex, "Updating Units.DATE_CONTRACT_EXPIRES failed", "AppCalls.updateUnitDateContractExpires.vb") End Try End Sub
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 1, 2011 8:05 PM

All replies

  • User-125547262 posted

    I would definitely recommend using ODP.NET to connect to Oracle. You can expect to get much better perfomance

    http://www.oracle.com/technetwork/topics/dotnet/index-085163.html

    http://www.oracle.com/technetwork/database/windows/downloads/index-101290.html

    Saturday, July 30, 2011 1:57 PM
  • User-1104918573 posted

    thank you for your reply.

    i have any problem to use any type of connection,but the problem that they don't work.

    i tried before ODP.NET,and i have this exception:

    string oradb = " Data Source = XE; User Id =hr; Password =hr;" ;
     OracleConnection conn = new OracleConnection(oradb);
     conn.Open();

    so i have the exception:
    ORA-12154: TNS:could not resolve the connect identifier specified
    and i tried and i tried with no success.

    and if i try this connection string:

    string oradb = "Data Source=(DESCRIPTION=(ADDRESS_LIST="
                    + "(ADDRESS=(PROTOCOL=TCP)(HOST=computerName)(PORT=1521)))"
                    + "(CONNECT_DATA=(SERVER=DEDICATED) (SERVICE_NAME=XE)));"
                    + "User Id=hr;Password=hr;"; 
    i have this exception:
    ORA-6413: Connection not open.

    only ODBC worked in windows application not in asp.net.
    Saturday, July 30, 2011 4:52 PM
  • User269602965 posted

    ODP.NET is the way to go.

    ODBC is slloooooowwwwww.

    On major mistake developers make with Oracle is failure to install on local virtual static IP using Microsoft Loopback Adapter (Windows installation guide for Oracle) with all other network adapters temporarily disabled during the installation phase.  If Oacle installs on an adpater with a non-static IP, you will have TNS errors if the development hard wired IP changes as in DHCP environment.

    There are several ways to do connection strings with Oracle.  While one method may not work for you, other methods often will. 

    http://www.connectionstrings.com/oracle

    You should also read the ODP.NET developer guide to get started with proper coding for database connectivity.

    http://download.oracle.com/docs/cd/E11882_01/win.112/e18754.pdf

    Sunday, July 31, 2011 6:29 PM
  • User-1104918573 posted

    thank you for your reply.

    my problem is that all the type of connection to oracle worked for me,but in windows application not in asp.net.

    i tired ODP.NET and worked but in windows applications.

    thank you again.

    Monday, August 1, 2011 5:32 PM
  • User269602965 posted

    For ASP.NET the connection string is best placed in the connection string section of WEB.CONFIG,

    and is called by ASP.NET code as like this example below where a DATE_CONTRACT_EXPIRES is being updated.

    This line gets my connection string named "OraConnStr" from web.config

        Dim connectionString As String = ConfigurationManager.ConnectionStrings("OraConnStr").ConnectionString

    Imports System.Xml.Linq.XElement
    Public Shared Sub updateUnitDateContractExpires(ByVal decUnitSeq As Decimal) ' Update the CONTRACTSCHEMA.UNITS.DATE_CONTRACT_EXPIRES to available most future value ' Called from Service Contracts UPDATE Dim connectionString As String = ConfigurationManager.ConnectionStrings("AuthenticatedOracleConnectionString").ConnectionString Try Dim SQL = <SQL> UPDATE CONTRACTSCHEMA.UNITS SET DATE_CONTRACT_EXPIRES = (SELECT DISTINCT LAST_VALUE(a.DATE_END_CONTRACT) OVER (ORDER BY a.DATE_END_CONTRACT NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS DATE_END_CONTRACT FROM CONTRACTSCHEMA.SERVICE_CONTRACTS a WHERE a.UNIT_SEQ = :decUNITSEQ), SEND_CONTRACT_FLAG = 'Send contract' WHERE UNIT_SEQ = :decUNITSEQ </SQL> Using conn As New OracleConnection(connectionString) Using cmd As New OracleCommand(SQL.Value, conn) cmd.Parameters.Clear() cmd.Parameters.Add("decUNITSEQ", OracleDbType.Decimal, decUnitSeq, ParameterDirection.Input) conn.Open() cmd.ExecuteNonQuery() End Using End Using Catch ex As Exception AppCalls.WriteToEventLog(ex, "Updating Units.DATE_CONTRACT_EXPIRES failed", "AppCalls.updateUnitDateContractExpires.vb") End Try End Sub
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 1, 2011 8:05 PM