locked
can't connect to Oracle from code behind but can through aspx page RRS feed

  • Question

  • User-193293338 posted

    I have a new site that I am making for internal usage and need to connect to oracle to pull info.  After getting past the 32 vs 64 bit issue I now am having problems connecting from the code behind.  The weird part is I can connect just fine from the html on the aspx page.  Here's the html section:

       <asp:SqlDataSource ID="TrimDataSource1" runat="server" 
       ConnectionString="<%$ ConnectionStrings:Trim %>"
       ProviderName="<%$ ConnectionStrings:Trim.ProviderName %>"
        SelectCommand="select * from tsexfieldv where evfielduri = '2'" >
       </asp:SqlDataSource>
       <asp:GridView ID="GridView1" runat="server" AllowPaging="True"
          DataSourceID="TrimDataSource1">
       </asp:GridView>

    here's my connection in web.config:

    <add name="Trim" providerName="System.Data.OracleClient" connectionString="Data Source=xxxxx;Persist Security Info=True;User ID=mmmm;Password=nnnnn;" />

    and here is the code behind:

    Imports Oracle.DataAccess
    Imports System.Data.SqlClient
    Imports System.Data
    Imports System.Web.Security.MembershipUser
    
    Partial Class EDMS_Stats
       Inherits System.Web.UI.Page
    
       Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
          Dim ssql As String
          Dim myConn1 As String = ConfigurationManager.ConnectionStrings("Trim").ConnectionString
          Dim conn As New Oracle.DataAccess.Client.OracleConnection(myConn1)
          ssql = "select count(*) from tsexfieldv where evfielduri = '1'"
          Dim scmd3 As New Oracle.DataAccess.Client.OracleCommand(ssql, conn)
          TotalStoredLabel.Text = scmd3.ExecuteScalar.ToString
       End Sub
    End Class

    If I include the code behind, it throws a:  TypeInitializationException was unhandled by user code - The type initializer for 'Oracle.DataAccess.Client.OracleConnection' threw an exception

    looking at the details will show "the provider is not compatible with the version of Oracle client" which is an odd error considering I can fill a grid no problem as long as I don't move the code behind the aspx page.  I have tried copying the Oracle.DataAccess.dll (along with several others per a different post) to the bin directory of my project.   I have also tried removing the provider name parameter from the connection in the web.config but that just breaks the one that does work.   Just for grins I also tried changing my project to .NET 3.5 but that just caused more errors.

    Oracle client is 11g - database is 10 - .net 4.0

    Friday, September 30, 2011 12:51 PM

All replies

  • User269602965 posted

    Do not use providerName="System.Data.OracleClient"

    It has been deprecated (no longer supported) by Microsoft

    use providerName="Oracle.DataAccess.Client"

    In your WHERE clause, good ideal to use BIND variables and not static values for security and coding reuse reasons

    Examples of how I connect:  In this case I am updating a table

      Imports System.Xml.Linq.XElement
      
        Public Shared Sub updateUnitsActiveFlag(ByVal decQuantity As Decimal)
          ' Insert Quantity into new row Units table'
          Dim connectionString As String = ConfigurationManager.ConnectionStrings("OracleConnStr").ConnectionString
          Try
            Dim SQL =
            <SQL>
            INSERT INTO {YOURSCHEMANAME}.UNITS
            (UNITS_SEQ, QUANTITY) VALUES (UNIT_SEQ.NextVal, :BindVarQuantity)
            </SQL>
            Using conn As New OracleConnection(connectionString)
              Using cmd As New OracleCommand(SQL.Value, conn)
                cmd.Parameters.Clear()
                cmd.Parameters.Add("BindVarQuantity", OracleDbType.Decimal, decQuantity, ParameterDirection.Input)
                conn.Open()
                cmd.ExecuteNonQuery()
              End Using
            End Using
          Catch ex As Exception
            AppCalls.WriteToEventLog(ex, "Inserting UNITS.QUANTITY failed", "AppCalls.updateUnitsActiveFlag.vb")
          End Try
      End Sub
    In this case I am filling a RadGrid object
    Try
    	Dim connectionString As String = ConfigurationManager.ConnectionStrings("OracleConnStr").ConnectionString
    	Dim SQL = _
    		<SQL>
    		 SELECT PRODUCT_CLASS, AMOUNT_ANNUAL_CONTRACT, COUNT, TOTAL_VALUE
    		 FROM {YourSchemaName}.VW_COUNT_CONTRACTS
    	</SQL>
    	Using conn As New OracleConnection(connectionString)
    		Using cmd As New OracleCommand(SQL.Value, conn)
    			conn.Open()
    			Using oda As New OracleDataAdapter(cmd)
    				Dim ds As New DataSet()
    				oda.Fill(ds)
    				Me.RadGrid1.DataSource = ds
    				Me.RadGrid1.MasterTableView.DataSource = ds
    			End Using
    		End Using
    	End Using
    Catch ex As Exception
    End Try

    Friday, September 30, 2011 7:53 PM