DataAdapter and inserting into a table with a AUTO Counter field?

[David]

When using a DataAdapter to insert new records into a table, can ADO
obtain the value of a AUTO Counter field, eg. one generated by the
database, and place it back into the datatable/row during the insert?
The only way I can see to do this is to insert the information and
then refresh the table.

Also, I need to know if it is possible for DBs that allow output
parameters, ex. SQL Server, and for generic ODBC and OLE databases,
ex. Access, mySql, etc. This is more of an issue with mySql at this
point.

 

[Nathan Sokalski]

As for inserting a record into a table that has a counter (some databases
call these sequences), how you would do that depends on the database. The
two databases I have used with ADO.NET are Microsoft Access and Oracle. For
Microsoft Access, you need to specify which fields you are submitting values
for, and don't include the AutoNumber field as one of these, it will be
given a value even though you are not submitting one. For Oracle, just do it
the same way you would doing it from SQL*Plus. In other words, use
sequencename.NEXTVAL as the value (you would replace sequencename with the
name of the sequence you want the next value of).

Yes, if the database supports output parameters, then you can use them.
Unless you use special characters, anything in your CommandText is handled
by the database. I have only used mySQL a couple times, so I can't give you
much help with that specifically, but the ASP.NET end of pretty much all
databases is pretty much the same.

 

[Cor Ligthert [MVP]]

David,

In addition to Nathan, SQLServer places the given number back in the
DataTable.
Another reason to take SQLExpress above Access (when using SQLClient)

Nevertheless do I use forever the uniqueidentifier (Guid) instead of the
autonumber.

Cor

 

[W.G. Ryan - MVP]

For access you are going to need to hit the db again. This should help.

http://msdn.microsoft.com/library/d...cpconretrievingidentityorautonumbervalues.asp

If the DB supports output params, they can be used but AFAIK, Access
doesn't.