MS access, Recordsets and VB.NET

Q_Ball

Hey all,

trying to get my head around VB.NET. I've set up a connection to an access database that doesnt read data. When i quiz the database i get an exception telling me that the row/column has no data, which is not true. On top of that from what i can google, MS has replaced the Recordset with datareader which is a forward only reader. I want to be able to traverse a recordset, going forward and backward along the results of a query.

How do i get this to work!!!

Never thought i'd say this but i miss ADODB.Recordset

all the best

theexis

The DataSet object is closer to a Recordset but way more powerful - this object represents an in-memory database that you can populate, update and submit back to the original data source. DataReader is really an optimised way of reading forward sequentially. Take a look at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_vbcode/html/vbtskcodeexamplereadingaccessdataintodataset.asp for a starting point.

Q_Ball

I hate MSDN. Anytime I go looking for anything i can never find an answer. But thats just me and search engines i guess.

So the dataset pretty much reduces the amount of transactions performed on a database, and only committing the changes when specified programmatically? Sounds like a nice way of preventing dirty reads et al

theexis

Not a lot different from an updatable recordset if I recall though - Begin/End batch?

However, for large updates its not really that performant since it'll call a specific query for every change. Probably OK for Access but roundtripping to a SQL server isn't a good idea...

bonkey

Post your non-functional code and it might be easier to help...

Kernel32

theexis wrote:

Not a lot different from an updatable recordset if I recall though - Begin/End batch?

However, for large updates its not really that performant since it'll call a specific query for every change. Probably OK for Access but roundtripping to a SQL server isn't a good idea...

A dataset can contain many datatables with constraints and cascade rules. It is definitely different to an updatable recordset. If you use a typed dataset then you have built in type checking as well which is very powerful and can route out problems at compile time rather than run time.

Why isn't roundtripping to SQL Server a good idea? With connection pooling I don't see any problem with it?

bonkey

theexis wrote:

However, for large updates its not really that performant since it'll call a specific query for every change. Probably OK for Access but roundtripping to a SQL server isn't a good idea...

Unless all the changes are uniform (i.e. the same modification to every record) then batch updating requires the exact same number of update statements as an "ordinary" approach.

QBall wrote:

I want to be able to traverse a recordset, going forward and backward along the results of a query.

If you don't mind me asking...why do you want to do this?

(Its not as daft a question as it sounds)

jc