|
|
Posted by Erland Sommarskog on 04/12/06 00:48
Rico (r c o l l e n s @ h e m m i n g w a y . c o mREMOVE THIS PART IN CAPS)
writes:
> I am in the midst of converting an Access back end to SQL Server
> Express. The front end program (converted to Access 2003) uses DAO
> throughout. In Access, when I use recordset.AddNew I can retrieve the
> autonum value for the new record. This doesn't occur with SQL Server,
> which of course causes an error (or at least in this code it does since
> there's an unhandled NULL value). Is there any way to retrieve this
> value when I add a new record from SQL server or will I have to do it
> programmatically in VB?
It's better to use stored procedures to add data, rather than relying on
ADO generating code behind your back. It's easy for the Jet provider
to populate the Autonumber for you, because all operations are in your
process space. But since SQL Server is on the other end of the wire,
there is an extra roundtrip to get the value.
Also, with SQL Server, make sure that all your cursors are client-side.
A sample stored procedure:
CREATE PROCEDURE insert_tbl @a int,
@b datetime,
@c varchar(23),
@id int AS
INSERT tbl (a, b, c)
VALUES (@a, @b, @c)
SELECT @id = scope_identity
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|