SQL Insert and Primary Key value....

[SteveoAtilla]

Hello!

 

I have a large ASP application that runs on a SQL Server back-end database.

 

I run NUMEROUS SQL queries (SELECT, INSERT, UPDATE, DELETE, etc.), and in many cases, I need to insert a record, and then query the database for the Primary Key field (defined as an Identity field in SQL).

 

Is there a way to write and execute an INSERT query so that it returns the Primary Key field?

 

Here is an example of one of the INSERT commands.

 

INSERT INTO dbo.EmployeeMaster (EmployeeName, EmployeeInitials, EmployeeEMail)
VALUES ('Oswald, Steve', 'SJO', 'StevenJOswald@gmail.com')
    

 

There is one more field in the table, EmployeeMasterPK, which is defined as an Identity field.

 

How could I write that query to return a value? I have far too many SQL statements in the website to switch it to Stored Procedures at this point...

 

Thanks for any help or suggestions you can offer!

 

Steve

[Nerseus]

There are two common ways to get an identity out of SQL Server: @@IDENTITY and SCOPE_IDENTITY().

 

It should be as simple as adding a SELECT to your SQL. The string becomes:

INSERT INTO dbo.EmployeeMaster (EmployeeName, EmployeeInitials, EmployeeEMail)
VALUES ('Oswald, Steve', 'SJO', 'StevenJOswald@gmail.com')
SELECT @@IDENTITY

 

Then change your C#/VB code to use the ExecuteScalar method call (where you are probably currently using ExecuteNonQuery).

 

If you need more details let us know. If so, please post the relevent C#/VB code that executes the SQL. If you're using SqlParameter objects for example.

 

-nerseus

[SteveoAtilla]

Worked perfectly!!!

 

nerseus,

 

That worked perfectly! Thanks!