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

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

Answer 1

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