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?
TownTalkAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

it_saigeDeveloperCommented:
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-
TownTalkAuthor Commented:
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.
it_saigeDeveloperCommented:
According to MSDN:
Use a Command object to query a database and return records in a Recordset object, to execute a bulk operation, or to manipulate the structure of a database.
Source

-saige-

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TownTalkAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.