Why INSERT, then SELECT

  • Thread starter Thread starter Andy Sze
  • Start date Start date
A

Andy Sze

Hi,

When add a DataAdapter via design mode, Visual Studio auto add SELECT follows
the INSERT command.

Here is the sample of Chapter 4 - <ADO.NET Step by Step> .

I delete the SELECT command, it also work. Why ?

Thanks.


----------------------------------------------------------------
INSERT INTO Categories
(CategoryName, Description)
VALUES (@CategoryName, @Description);
SELECT CategoryID, CategoryName, Description
FROM Categories
WHERE (CategoryID = @@IDENTITY)
 
Hi Andy,

Suppose your Categories table have several child rows that also needs
Parent rows id then how will you insert them ?

Basically SELECT @@IDENTITY returns the last IDENTITY inserted
this way you will have values and could be used in child rows.

Regards,
FLu.
 
Your dataset row gets updated by the result set of the SELECT. Not only
does the identity column need to be updated in your dataset, but what if
some columns were modified in UPDATE triggers, or some columns have default
values that your code didn't supply, or some columns are computed columns?
This SELECT is how any such column updates get back to your dataset.
 
Thanks to Fluker and Brad Williams.

See your message and test the sample program again, I konw the why.

1. Categories have 8 Records,
CategoryID is Identity field, last generated count of CategoryID = 8.
2. Delete the SELECT follows INSERT.
3. Run the sample program, Insert a record
CategoryID = 9 (program auto generate)
CategoryName = 'Name AA'
Description = 'Desc AA'
Call UPDATE trigger
Database and Screem is same.
4. Delete the record(CategoryID = 9) and update
5. Close the program
6. Run the Program
7. Insert a record
CategoryID = 9 (program auto generate)
CategoryName = 'Name BB'
Description = 'Desc BB'
Call UPDATE trigger
Database: CategoryID = 10 (database auto generate)
Screen : CategoryID = 9 (program auto generate)

If have SELECT follows INSERT, it will retrieve value from database
and update DataSet
Call UPDATE trigger:
Database: CategoryID = 10 (database auto generate)
Screen : CategoryID = 10 (DataSet be updated by SELECT)


Regards.
 
Back
Top