How to get Rec.Nr asigned by Access DB in current Dataset without using DS-FILL

[Valentino]

Hello everyone,

Im new to this forum and I have a question about the use of ADO.NET within VB.NET (2010 Express version).

On the internet I found a great example project (homeandlearn) to learn how to work with ADO.NET from VB.NET. Like many others I formerly worked with DAO and I'm moving towards ADO.NET now.

The DataBase project, using a Connection$ against an MS Access 2007 DB through OLE_DB provider works partially fine already. I use a CommandBuilder CB to update data from the Dataset DS to the DataBase and for the DB-fields in general this works fine, except for the ID-Field which is managed by Access itself using Autonum. The recordnumbers generated by Access are not passed back to the Dataset unless I start a complete new DataSet Fill procedure after each new added record!

My Question : Does anybody know , when I add a new record, how I can get the recordnr asigned by MS Access back into my current dataset (so also showing on my app.form) without starting a new DS-Fill procedure after adding a new record?

I temporary solved this by labeling all new record recordnrs on the screen with '[New]'. After starting a complete new DS-Fill the recordnrs are then exposed on the form again.

This is how I add a new record :

'DS contains the dataset, DA is the data-adapter
dsNewRow = ds.Tables("AddressBook").NewRow()
'Fill in the fields..
ds.Tables("AddressBook").Rows.Add(dsNewRow)
da.Update(ds, "AddressBook")

When i retrieve the ID-field from the DB with [..].item(index) then I notice that the value is NULL, so there is no recordnumber available within the DB yet? When I look from within Access 2007 itself I can see this recordnumber.

Thanks in advance for your reply and greetings,

Valentino

 

[jmcilhinney]

The simple fact is that you can't. You need to requery the database, plain and simple. If it's a single-user app then you can query for just the last ID in that table, but that's no help if you added multiple records. Basically, if you want to do this then don't use Access. Use a proper, service-based database like SQL Server Express. In that case you can simply add a SELECT statement to the end of your INSERT statement and the DataSet will get updated automatically with the new ID. Some VS tools even do this by default, e.g.

INSERT INTO MyTable (Column1, Column2) VALUES (@Column1, @Column2); SELECT @ID = SCOPE_IDENTITY()

 

[Valentino]

Hello jmcilhinney,

Thanks for your repply. Now guess what, I just found the solution elsewhere on the net and it works great!

' Set the MissingSchemaAction property to AddWithKey because Fill will not cause primary
' key & unique key information to be retrieved unless AddWithKey is specified!

so just add the next line before the DS-FILL ...

da.MissingSchemaAction = MissingSchemaAction.AddWithKey

and from now on the PK in the Dataset is the same as in the DB. So this solution works great with MS Access Databases as well.

I hope others will also benefit from this solution.

Greetings,

Valentino

 

[jmcilhinney]

That is actually not a solution at all. It may appear so, but it's not.

What's actually happening is that, by setting that property, you are telling the DataTable to use the same PK schema as the database. As such, your DataTable will auto-generate IDs for the new rows you add. The thing is, those are only temporary IDs and there is no specific relationship between them and the final IDs generated by the database.

Consider the following scenario. You have 100 records in your database table already, with IDs 1 to 100. You perform a query that gets the first 50 of those records, so your DataTable contains IDs 1 to 50. If you now add a row to your DataTable, it will have ID 51, but when you insert that row into the database, it will have ID 101.

Consider the following scenario. You have 100 records in your database table already, with IDs 1 to 100. You perform a query that gets all those records into a DataTable. You add a new row to that DataTable, so it will have ID 101. You then attempt to insert that record into the database. The database generates ID 101, but an error occurs and the record can't be saved. You attempt to save the record again and this time it works, so the record will now have ID 102, because the same auto-generated ID is never generated twice.

There are situations where what you're doing will work, but there are also many where it won't. It should absolutely NOT be relied upon because it was never intended for that purpose. IDs generated by a DataTable should only be considered temporary and NEVER assume that the database will use the same ID.

As I said, what you want CANNOT be done using Access. If you need that functionality then you can't use Access, plain and simple.

 

[Valentino]

hello jmcilhinney,

thanks for your reply.

hmm, so .AddWithKey is not the solution here. In that case thats pretty disappointing. I guess I have the following options now :

- switch to SQL Server Express. I havent worked with it though and wonder if and how I can keep track of data during test. In Access its easy to take a quick view at data in tables, but as far as I know SQL works through services layers only?

- Switch back to DAO where it has always worked

- Switch off autonum in MS Access and Keep the assignment of PK's in my own hands and saving the latest assigned PK in a small separate file?

greetings,

Valentino

 

[jmcilhinney]

With SQL Server Express you have two options:

1. Create a database in Management Studio Express that will remain permanently attached. Your app will connect to that database and you can view the contents of that database in Management Studio Express at any time. In that case, you don't deploy the actual database with the app so you will need one or more scripts to build it. These can be generated from your original database by Management Studio Express.

2. Add a database directly to your project. You build the schema and add default data through Visual Studio and the database only gets attached when you run the project. The original database is copied to the output folder along with your EXE and that's what gets attached. As such, you can only view the data in it via your app.

Both options have their pros and cons. the second option is available to the Express edition of SQL Server only.