Oracle connection with asp.net v13

Question

i have this problem for long time and not able to solve> Hope i get the solution here. I have installed Oracle express edition 11 g and i have created the table-space and the schema. Then , i have installed the ASP.NET. when i compile the project it shows error with the conn.open().

i tried this connection

    private void clidrop()
    {
        string oradb = "DATA SOURCE=xe;USER ID=AMC; password=amc;";
        OracleConnection conn = new OracleConnection(oradb);

        conn.Open();

        string smst = "select *FROM  TECHNOVA_SMS_CLNT_MTR";
        OracleCommand cmdt = new OracleCommand(smst, conn);
        cmdt.CommandType = CommandType.Text;

        DataSet dst = new DataSet();
        OracleDataAdapter dtt = new OracleDataAdapter(cmdt);
        dtt.Fill(dst);
        clientName.DataSource = dst.Tables[0];
        clientName.DataTextField = "SMS_CLNT_NAME";
        clientName.DataValueField = "SMS_CLNT_ID";
        clientName.DataBind();

        clientName.Items.Insert(0, " --Select--");
        clientName.Items[0].Selected = true;
        clientName.Items[0].Attributes["disabled"] = "Disabled";

        conn.Close();
        conn.Dispose();

    }

the error is :

An unhandled exception occurred during the execution of the current web request

exceptions

Please provide error details, and connection properties you're using (connection strings etc.). — Tetsuya Yamamoto, Sep 29 '17 at 08:33
Server Error in '/' Application. Unhandled Execution Error Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: Oracle.DataAccess.Client.OracleException: Source Error: Line 127: OracleConnection conn = new OracleConnection(oradb); Line 128: Line 129: conn.Open(); Line 130: Line 131: string sms1 = "select SMS_PROJ_ID, SMS_PROJ_NAME from TECHNOVA_SMS_PROJ_MTR, — IbtisamAlbaadri, Sep 29 '17 at 08:36

Cyrille MODIANO · Answer 1 · 2017-09-29T09:11:06.550

0

try to change that:

string oradb = "DATA SOURCE=//localhost:1521/XE;User Id=AMC; password=amc;";

using just XE the driver assumes that a service named XE has been declared in your tnsnames.ora file.

edited Sep 29 '17 at 09:11

answered Sep 29 '17 at 08:44

Cyrille MODIANO

2,246
2
21
33

it shows this error -- Additional information: 'username' is an invalid connection string attribute – IbtisamAlbaadri Sep 29 '17 at 08:50
sorry edited my answer, replace username by "User Id" – Cyrille MODIANO Sep 29 '17 at 08:52
You said: 'username' is an invalid connection string attribute What is the error if you replace it with "User Id" and just change the DATA SOURCE part – Cyrille MODIANO Sep 29 '17 at 09:06
Additional information: External component has thrown an exception. – IbtisamAlbaadri Sep 29 '17 at 09:09
what exception? – Cyrille MODIANO Sep 29 '17 at 09:10
it will be good idea but it is not allowing me cose this is my first contribution in stackoverfllow – IbtisamAlbaadri Sep 29 '17 at 09:16
th exception C++ exceptions , common language runtime exception , GPU Memory and many ... – IbtisamAlbaadri Sep 29 '17 at 09:18

Ohlsen1980 · Answer 2 · 2017-09-29T09:12:19.167

0

Try this code sample (but note that System.Data.OracleClient is deprecated):

using System.Data.OracleClient;
using System.Data;     
class Example 

    {
        private string m_sConnectionString;
        private OracleConnection m_dbConnection = null;

        public OracleConnection Connection
        {
            get
            {
                if (null == m_dbConnection)
                {
                    m_dbConnection = new OracleConnection(m_sConnectionString);
                }
                return m_dbConnection;
            }
        }

        /// <summary>
        /// Open the connection
        /// </summary>
        public void Open()
        {
            if (Connection.State != ConnectionState.Open)
                Connection.Open();
        }

        /// <summary>
        /// Close the connection
        /// </summary>
        public void Close()
        {
            try
            {
                if (null == m_dbConnection)
                    return;
                m_dbConnection.Close();
            }
            catch { }
        }

        /// <summary>
        /// Execucte a SQL command, with open a new connection
        /// </summary>
        /// <param name="query">The SQL Query</param>
        public void Execute(string query)
        {
            Open();
            using (OracleCommand dbSqlCmd = Connection.CreateCommand())
            {
                dbSqlCmd.CommandText = query;
                dbSqlCmd.ExecuteNonQuery();
                dbSqlCmd.Dispose();
            }
        }

        public void Connect(string schema, string password, string datasource)
        {           
            OracleConnectionStringBuilder conn_builder = new OracleConnectionStringBuilder();
            conn_builder.DataSource = datasource.Replace("/", @"\"); //Replace the / with a \ (standard path);           
            conn_builder.Add("Password", password);
            conn_builder.Add("User ID", schema);
            conn_builder.Pooling = true;
            conn_builder.MaxPoolSize = 40;
            conn_builder.MinPoolSize = 40;          
            conn_builder.Unicode = true;
            m_sConnectionString = conn_builder.ConnectionString;            
        }
    }

edited Sep 29 '17 at 09:12

answered Sep 29 '17 at 08:47

Ohlsen1980

294
5
11

You can add the code as a new Class file. Then you can use it like: `Example e = new Example(); e.Connect("user", "password", "TNS_Name_of_Instance"); e.Execute("select 'test' from dual;"); e.Close(); ` – Ohlsen1980 Sep 29 '17 at 08:55
Error 5 The name 'ConnectionState' does not exist in the current context C:\Users\IBTISAM\Documents\Visual Studio 2013\Projects\WebApplication1\WebApplication1\Class1.cs 32 37 WebApplication1 – IbtisamAlbaadri Sep 29 '17 at 09:01
if (Connection.State != ConnectionState.Open) here is the error ConnectionState not defined – IbtisamAlbaadri Sep 29 '17 at 09:07
Do you have a reference to System.Data.OracleClient and System.Data in your class? – Ohlsen1980 Sep 29 '17 at 09:09
yes _ using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Data.OracleClient; – IbtisamAlbaadri Sep 29 '17 at 09:20
System.Data is missing – Ohlsen1980 Sep 29 '17 at 09:22
added. HTTP Error 403.14 - Forbidden The Web server is configured to not list the contents of this directory. – IbtisamAlbaadri Sep 29 '17 at 09:32
OK, but thats not an oracle exception. It seems to depend on your web project, see https://stackoverflow.com/questions/18981118/http-error-403-14-forbidden-the-web-server-is-configured-to-not-list-the-con – Ohlsen1980 Sep 29 '17 at 09:34
You can debug and see, if your Connection.State is Open, to see if your oracle problem is solved... – Ohlsen1980 Sep 29 '17 at 09:35
still it gives me this error. Additional information: External component has thrown an exception. – IbtisamAlbaadri Sep 29 '17 at 09:41
and for exanple private OracleConnection m_dbConnection = null; the m_dbConnection underlined with green. may it means not have been used. – IbtisamAlbaadri Sep 29 '17 at 09:43
No, as i mentioned above, System.Data.OracleClient is deprecated. Thats why visual studio marks it green. Btw, your tooltip in visual studio gives you a hint. – Ohlsen1980 Sep 29 '17 at 09:46

Oracle connection with asp.net v13

2 Answers2