Problems connection to database

[Pendejo]

Okay, this is my problem.

I have to write a serviced component to access an MS Access Database through ODBC. I created a system DSN in the ODBC Manager of Windows (xp service pack1). I wrote a little client to see if this system DSN was working and it worked.

Next I wrote a class with exactly the same code to access the database and deployed it as a serviced component. This is my code

-----------------------------------------------------
using System;
using System.EnterpriseServices;
using System.Collections;
using Microsoft.Data.Odbc;

[assembly: ApplicationName("LessenRooster")]

[assembly: ApplicationActivation(ActivationOption.Server)]

namespace thesis
{
public interface ILessenroosterCOM
{
string dagProgramma();
}



[Transaction(TransactionOption.Required)]
[JustInTimeActivation]

public class LessenroosterCOM : ServicedComponent, ILessenroosterCOM
{
public LessenroosterCOM(){}

public string dagProgramma()
{
string result;
string con = "dsn=tester;";
string sql = "Select * from reservatie";

OdbcConnection connection = new OdbcConnection(con);
OdbcCommand cmd = new OdbcCommand(sql);

cmd.Connection = connection;
try
{
connection.Open();
OdbcDataReader reader = cmd.ExecuteReader();

reader.Read();
DateTime datum2 = reader.GetDateTime(2);
result = datum2.ToString();

}

catch(Exception exc)
{
result = exc.ToString();
}
finally
{
connection.Close();
}
return result;
}
}
}

---------------------------------------------------

However, when I write a client to access the Database with this component I get following error.


Microsoft.Data.Odbc.OdbcException: ERROR [25000] [Microsoft][ODBC-stuurprogrammabeheer] De registratie van de transactie van het aanroepend object is mislukt

(Translation: the registration of the transaction of the calling object failed)

ERROR [IM006] [Microsoft][ODBC-stuurprogrammabeheer] SQLSetConnectAttr van het stuurprogramma is mislukt

(Translation: SQLSetConnectAttr driver failed)

at Microsoft.Data.Odbc.OdbcConnection.Open()
at thesis.LessenroosterCOM.dagProgramma() in d:\development-area\thesis.net\lessenroostercom\class1.cs:line 42


I looked this failure code up on the internet and I most of the time I found information that said that the problem was with the fact that the database would not be a system resource. However, I created the dsn as a system dsn. I just dont get.
Can anybody please help me,
thanx in advance

PS: I got the same kind of exceptions when I tried to connect through OleDb

 

[APaule]

Im not sure, but as far as I know, the old DSNs and UDLs dont work with ADO.NET any longer.
So you have to hardcode your connectionstring.

 

[Pendejo]

could be, but even when I use OleDb connection it failed, at exactly the same place.

Btw, is it possible to connect to an MsAccess database through ADO connection???

 

[APaule]

First of all, use OleDb.
You replace the System.Data.Odbc with System.Data.OleDb, and all odbc-objects with oledb-objects (as far as I can see the only difference in your code is the "odbc"-prefix of the objects)
Then you set your con variable to:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=YourAccessDB"

This should do the job.

 

[Pendejo]

The trick with ole db didnt do the job. But I have found the problem (so I think). It seems that Ms Access doesnt support transaction done by MST. When I put the Transaction attribute to disabled it works just fine.

Thanx for all your help