Another Oracle DB connection problem

[jcs1953]

This is driving me nuts! I read the Faq (amazing amount of info there) and followed the suggestions but to no avail.
Here is my code:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim connectionString As String = "Data Source= JCS\SQLEXPRESS;Initial Catalog=FMC_ACCESS;Integrated Security=True"
Using Connection As New SqlConnection(connectionString)
Connection.Open()
Dim sqlCMD As System.Data.IDbCommand = Connection.CreateCommand()
sqlCMD.CommandText = "SELECT ROUND(CMPatientDataFile.Result_Numeric,2),CMPatientDataFile.Run_Date FROM CMPatientDataFile WHERE CMPatientDataFile.Pat_ID = '10364.0' AND CMPatientDataFile.Test_Name = 'GLUC' ORDER BY CMPatientDataFile.Run_Date"
Dim mytable As DBTable = New DBTable(sqlCMD.ExecuteReader())
Dim result() As Double = mytable.getCol(0)
Dim RunDate() As Date = mytable.getColAsDateTime(1)

(Rest isn't important)
End Using
End Sub

This works quite nicely and makes a very pretty chart based on the values retrieved from the SQL DB.

But I also have an Oracle 10g Express DB and need to run the same code with the exception of the connect string and the query. I can't get it to connect. It shows error 40, can't find the database.

Now:
1. I CAN connect to the Oracle DB and retrieve data with a query if I drag the "SQLDataSource" to the design page and configure it properly. I dumped the data into a gridview. It worked great. (Not using the code on the aspx.vb page)
2. I can connect and run a query using a program called 'Query Reporter'.
3. I downloaded the program in the Faq to find the connection string. Using OLE DB Provider for Oracle it connected and gave this as a connect string: Provider=MSDAORA.1;Password=hr;User ID=hr;Data Source=xe;Persist Security Info=True
Using the Provider= part causes an error (not supported) so I tried it without that part and received an error. Below is the error report.
ANY help would be so greatly appreciated!

System.Data.SqlClient.SqlException was unhandled by user code
Class=20
ErrorCode=-2146232060
LineNumber=0
Message="A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"
Number=2
Server=""
Source=".Net SqlClient Data Provider"
State=0
StackTrace:
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at _Default.Page_Load(Object sender, EventArgs e) in Z:\Dev_Websites\SQLConnect_label\Default.aspx.vb:line 11
at System.Web.UI.Control.OnLoad(EventArgs e)
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

 

[ca8msm]

The SqlConnection object is from System.Data.SQLClient which is the namespace for the SQL Server objects. You will either need to use System.Data.OracleClient (with an OracleConnection) or use ADO.

Mark,

Website Design Darlington
Experts, Information, Ideas & Knowledge
ASP.NET Tips & Tricks

 

[jcs1953]

Thanks Mark. I got it to work using OracleClient.
When I am able to access the data using the sqldatasource control isn't it using sqlclient?

 

[jmeckley]

Don't use DataSource controls. they are neither testable or debugable and data access has no place in the presentation layer.

You can also make your code Database agnostic using DbProviderFactory object. and configuring the Provider in the web.config

this is possible using the ADO.Net interfaces (IDbConnection, IDbCommand, IDbParameter) instead of the concrete implementations (Sql, Oracle, OleDb). google for more information.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[jcs1953]

Thanks Jason,
I've been looking into the DbProviderFactory object at

http://msdn.microsoft.com/en-us/library/dd0w4a2z(VS.80).aspx.

It seems in the example that they use a function to retrieve the connection string based on the Provider name and then another function to open the database connection. Is this a good way to do it? By that I mean is there a big advantage to using functions in this case?
I have two databases to connect to (Oracle ans MSSQL). I need to query both and combine the results for a drop down box.
I need to repeat this many times for different queries. The reason for this is that the old data is MSSQL (and the data won't ever change) and the Oracle database contains the new data. So to get all the data pertaining to a patient I have to query both and then combine. If you have any thoughts on the best way to do this I would really like to hear your suggestions. Cheers

 

[Turkbear]

Hi,
You could, if you wish, create a database link in Oracle to the SqlServer data and just query the Oracle database ( see this info) -

http://download.oracle.com/docs/cd/B19306_01/server.102/b14232/admin.htm#HETER004

...I believe you can also do it the other way around, but I do not know SqlServer that well..


.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[ca8msm]

Yes, you're right it can be done from SQL Server as well (they're known as Linked Servers). If you went down that route, you could even union the results of the two queries together and only have to make one connection from your code.

Mark,

Website Design Darlington
Experts, Information, Ideas & Knowledge
ASP.NET Tips & Tricks

 

[jcs1953]

Turkbear,
Unfortunately I only have Read Only access to the Oracle database so I can't get access to make any configuration changes. (And believe me I only want Read Only access)
Mark,
Using a UNION query would make life easier for sure. Can you point me to some code examples? I'm using Visual Web Developer 2005 and VB. I've googled but no luck.

 

[jcs1953]

I did some more investigating on linked servers and found that some people said that there was a problem using queries with joins. The queries to MSSQL are pretty straight forward but the queries to Oracle have at least 3 joins. Also both queries use parameters. Is this a problem?

 

[Turkbear]

Hi,
If the joins are using indexed fields ( and the where clauses use them as well) it should not present a problem , if not, the speed of the result being returned from the query will be determined by the least efficient access path in one of the joined tables..



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[jmeckley]

I would approach the databases as atomic containers. if there is information between the 2 that need to be aggregated, do this in code.

Jason Meckley
Programmer
Specialty Bakers, Inc.