AddNew concept with System.Data.OleDb
I've used the data adapter wizards for sql server and they can do this stuff
automatically. Just tried the ole db ones with an Access db, and they
"tried". The wizard has a specific option to "refresh the db" to get
identities/autonumbers, defaults and other db calculated fields. It
essentially generates a sql insert statement, and follows this with a select
statement to get back the autonumber. This raises an error as access can't
handle multiple statements in one hit. I don't really know why it bothered -
the ole db stuff can't really be aware or Access' capabilities.
I used to do this in two ways with access. Either stear away from
autonumbers, or follow each insert with a select. The later is easy in some
cases (while laborious, cumbersome and unperformant), IF you've just
inserted a value/values that can uniquely identify the row by
itself/themselves. Sometimes this is not the case (and usually the reason
you're using autonumbers in the first place) so here you'll need to use some
heavyweight locks and transactions. Otherwise you could just "hope" that the
highest autonumber in the table belongs to the record you just inserted?!
Email if you want some pointers on locking.
I'd recommend steering away from autonumbers and generating your own. This
is one of the reasons I go for sql server even where it's not needed; easy
if you've got access to one. Maybe ado.net supports access xp better? No
idea. And maybe this will be fixed for the full release. Who knows? Or
perhaps there's someway of defining a query/macro in access now (didn't used
to be) that can do multiple statements (aka stored procedures).
Good luck.
Quote:
> Have you actually managed to insert a new record into the db? Or is it
just
> finding the autonumber after it's been inserted that's the problem? You
> might like to mail me directly as I don't really monitor this group.
> > I am using an access db. I have used the AddNew in ADODB to retrieve
> > the last record but need the same for my .Net C# implementation. I
> > would be Ideal if someone could point me in the right direction.
> > On Tue, 6 Nov 2001 08:42:27 -0000, "Martin Rogan"
> > >Seeing as you're using oledb, as oppossed to the sql ones, I assume
> you're
> > >not using sql server. You probably won't be able to use a statement
like
> > >> I had this problem in my project also. What I did to resolve this is
to
> > >use
to
> > >get
> > >> the autonumber of the record just added.
> > >> EC
> > >> > How do I add a new record like AddNew in the System.Data.OleDb.
> > >> > Since there is an autonumber in the table I need this number to
> > >> > generate other records in other tables.
