MSDAORA vs MSDAORA.1

[sandease]

I am in a group programming situation with two other programmers. Just received a new PC, WindowsXP pro sp2, installed Oracle 8i, C# & .Net to connect to Oracle databse. My connecton string is as follows: strConn = "Provider=MSDAORA.1;User ID=myID;Password=Mypwd;Data Source=address.of.server";

My question is why do I HAVE to use Provider=MSDAORA.1 and the other two programmers can use Provider=MSDAORA to connect to the same database? If I use Provider=MSDAORA, I get an error. We have the same tnsnames.ora files and all the same software. What caused the difference? My code needs to be portable so I want to configure everything the same as theirs. We can't figure out what to change on my PC.

 

[CassidyHunt]

If your using .Net then you really should use a managed connection. It makes life a lot easier.

I am more familiar with vb.net than I am c# so I will code the connection for you in vb.net. I know there are several online code converters and utility called reflection that will translate.

Remember to set a reference to: system.data.oracleclient.dll

Here is code to access a stored procedure or package in Oracle

imports system.data.oracleclient

    Public Function GetData()
        Dim cn As New System.Data.OracleClient.OracleConnection
        Dim cm As New System.Data.OracleClient.OracleCommand

        Dim ds As New DataSet

        cn.ConnectionString = "data source=Database;user id=user;password=password;"
        cm.Connection() = cn

        cm.CommandText = "BillingReport.lineitemheader"
        cm.CommandType = CommandType.StoredProcedure

        cm.Parameters.Add("jobid", System.Data.OracleClient.OracleType.VarChar, 80).Value = "S37690"
        cm.Parameters.Add("v_cursor", System.Data.OracleClient.OracleType.Cursor).Direction = ParameterDirection.Output

        Dim oda As New System.Data.OracleClient.OracleDataAdapter(cm)

        Try
            oda.Fill(ds, "results")
        Catch ex As Exception
            Throw ex
        End Try

        Return ds

    End Function

Here is the code to handle a SQL statement

    Public Function GetData()
        Dim cn As New System.Data.OracleClient.OracleConnection
        Dim cm As New System.Data.OracleClient.OracleCommand

        Dim ds As New DataSet

        cn.ConnectionString = "data source=database;user id=user;password=password;"
        cm.Connection() = cn

        cm.CommandText = "Select * from table"
        cm.CommandType = CommandType.Text



        Dim oda As New System.Data.OracleClient.OracleDataAdapter(cm)

        Try
            oda.Fill(ds, "results")
        Catch ex As Exception
            Throw ex
        End Try

        Return ds

    End Function

let me know if you need any more help.

Cassidy

 

[sandease]

Cassidy, Thanks for your help. What we are still wondering is why am I forced to use MSDAORA.1 as my provider instead of MSDAORA? We coded the same console app on three PC's. The other two programmers could connect to the database using provider=MSDAORA and if I used that I'd get an error. If I used provider=MSDAORA.1, I would successfully connect. Any suggestions/observations?

 

[CassidyHunt]

MSDAORA requires you to set a worker thread by default. In older releases of the MDAC it may had been turned off for you or your coworkers. Try this for your connection string.

conn.open "Provider=MSDAORA;User ID=User;Password=Password;Data Source=database;Extended
Properties=NoWorkerThread;"

A question I do have is OraOledb is a much better provider for ADO and gives you a lot more ability. Is there a reason your using MSDAORA over OraOledb? For that matter if your using .Net why you wouldn't use the Oracleclient and managed dataset?

Let me know if this works.

Cassidy

 

[sandease]

Cassidy, Thanks again. First off we are using MSDAORA because we don't know any better. None of us have taken any .net classes and we are attempting to learn how to use it on our own, sharing with each other what we have learned.
I'll read about OraOledb and attempt to use it in the app I'm writing.

Here's something strange I just found. I searched my PC for msado15.dll which is supposed to tell me what version of MDAC is on my PC and that file does not exist on my PC. I am working over a network, could my PC be using one from one of the other 3 PC's I'm logged onto? Could that be why I am getting error when I attempt to connect?

 

[CassidyHunt]

I doubt it unless you have something configured more than what is installed. My guess is that you are using MDAC 2.5 at the very least.

Here is how you check your MDAC version

http://support.microsoft.com/default.aspx?scid=kb;en-us;301202

If your trying to develop robust code in .Net I really suggest taking advantage of ADO.Net and not confuse things with legacy ADO. Here are my reasons:

1. 2 Fold increase in record access and performance.
2. Managed datasets use like code and business object models easily implemented across any application.
3. COM is dieing an agonizing death. Everytime you create a DLL or recreate one on COM you need a reboot. .Net Component model is dynamic and real time.
4. LESS CODE!!!

There are ton of tutorial sites that demonstrate all sorts of data access with .Net. I suggest using msdn.microsoft.com as much as you can. They have a best practices site that will teach you just about anything.

Try that tool and see if that helps.

Cassidy

 

[sandease]

Thank you, you have helped immensely. I will take your advice and start using ADO,NET. I did find, after searching hidden files, that I have version 2.81.1117.0 MDAC on my PC.
Thank you again!