asked on

ADODB in C#

I've been developing in MS Access for 20 years and now i'm trying to move an application into C#. I've got an issue with ADODB that I don't understand. I'll apologise in advance because this takes some explaining....

In MsAccess there are some situations where I have multiple recordsets open and use transaction processing. There have been instances when I got transaction errors because of having too many recordsets open. I have always found the solution when I change the .CursorLocation property to adUseClient instead of adUserServer. So instead of opening the recordset like this:

RS.Open Criteria, Connection, dbOpenDynamic, dbLockTypeOptomistic

I have got used to do it the long winded way because the .Open method doesn't allow me to set the CursorLocation property. So in vb I do it this way:

With RS
.ActiveConnection = Connection
.Source = Criteria
.CursorType = adOpenDynamic
.CursorLocation = adUseClient
.LockType = adLockTypeOptomistic
.Open
End With

So that gives me a recordset which is immune from transaction errors caused by having too many recordsets in a transaction. And now I want to do the same in C#. Once again, the .Open method doesn't allow me to supply the CursorLocation, so I wrote the following code:

RS.ActiveConnection = Connection;
RS.Source = Criteria; // error occurs here.
RS.CursorType = ADODB.CursorTypeEnum.adOpenForwardOnly;
RS.LockType = ADODB.LockTypeEnum.adLockReadOnly;
RS.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
RS.Open();

When the above code executes, it faults at the 2nd line where RS.source is assigned. The error is:

"Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another."

So I assume I am using the wrong data type. I am using a string type variable. I found however that I can still do this....

RS.ActiveConnection = Connection;
RS.CursorType = ADODB.CursorTypeEnum.adOpenForwardOnly;
RS.LockType = ADODB.LockTypeEnum.adLockReadOnly;
RS.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
RS.Open(Criteria);

If I supply the string variable as an argument to the .Open function, then it works just fine. So how do I assign the .Source property explicitly?

it_saige

It's because when you specify the Source property the record set attempts to build it as an ADODB.Command object. The ADODB.Command class contains it's own definition for ActiveConnection. What this means is that the ActiveConnection property in your record set object gets overwritten by the one specified in the command object.

Try this instead:

ADODB.Command Command = new ADODB.Command();
Command.ActiveConnection = Connection;
Command.CommandText = Criteria;
RS.Source = Command;
RS.CursorType = ADODB.CursorTypeEnum.adOpenForwardOnly;
RS.LockType = ADODB.LockTypeEnum.adLockReadOnly;
RS.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
RS.Open();

Open in new window

You could also probably use a type initializer (much the same way you would in VB using a With statement); e.g. -

ADODB.RecordSet RS = new ADODB.RecordSet() {
	CursorLocation = ADODB.CursorLocationEnum.adUseClient,
	CursorType = ADODB.CursorTypeEnum.adOpenForwardOnly,
	LockType = ADODB.LockTypeEnum.adLockReadOnly,
	Source = new ADODB.Command() { ActiveConnection = Connection, CommandText = Criteria }
};
RS.Open();

Open in new window

-saige-

TownTalk

ASKER

Yes that works, and it is so different from the ADODB in visual basic. In vb you would write something like:

Criteria = "Select Price from Stock Where StockCode='ABC123'"

RS.Source = Criteria

The Source property is simply an SQL Select Statement. My understanding of an ADODB command object is that it is used to perform an action like UPDATE, INSERT or DELETE.... So it seems counterintuitive to use an ADODB command object as a parameter for an ADODB.Recordset object.

ASKER CERTIFIED SOLUTION

it_saige

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

TownTalk

ASKER

yes, i'm just surprised that the command object isn't necessary when I call the .Open method. In there I can just supply a string variable containing the select statement. That part at least is the same as vb.

I certainly find it strange that I have to declare two ADODB objects in order to open one recordset. Thanks for your help.

Thanks for your help