Select @ identity in access

Does the ACCESS database contain select @ identity? The answer is yes. However, access can only execute one SQL statement at a time. Multiple SQL statements must be executed multiple times, which is a limitation. In SQL Server, You can execute multiple SQL statements at a time. Access uses jet-SQL, and SQL server uses a T-SQL, which is quite different in usage.

 

However, N consecutive statements can be executed in access, as shown below:
Cmd. commandtext = "insert into mytable (N1, N2) values (22, 11 )";
Int COUNT = cmd. executenonquery ();
Cmd. commandtext = "select @ identity ";
Int newid = (INT) cmd. executescalar ();

 

Select @ identity is the keyword used to retrieve the automatic number of the previous statement.

 

 

You can use executereader () in SQL server to execute several SQL statements together at the same time.
Petshop 4.0 has the following usage:
Using (sqldatareader RDR = cmd. executereader (commandbehavior. closeconnection )){
// Read the returned @ err
RDR. Read ();
// If the error count is not zero throw an exception
If (RDR. getint32 (1 )! = 0)
Throw new applicationexception ("data integrity error on order insert-rollback issued ");
}
The SQL statement of the CMD object is as follows (obtained in debugging ):
Declare @ ID int;
Declare @ err int;
Insert into orders values
(@ Userid, @ date, @ shipaddress1, @ shipaddress2, @ shipcity, @ shipstate, @ shipzip, @ shipcountry, @ billaddress1, @ billaddress2, @ billcity, @ billstate, @ billzip, @ billcountry, 'ups', @ total, @ billfirstname, @ billlastname, @ shipfirstname, @ shiplastname, @ authorizationnumber, 'us _ en ');
Select @ ID = @ identity;
Insert into orderstatus values (@ ID, @ ID, getdate (), 'P ');
Select @ err =@@ error;
Insert into lineitem values (@ ID, @ linenumber0, @ itemid0, @ quantity0, @ price0 );
Select @ err = @ err + @ error;
Insert into lineitem values (@ ID, @ linenumber1, @ itemid1, @ quantity1, @ price1 );
Select @ err = @ err + @ error;
Select @ ID, @ err