how to deal with this SQLException

[Guest]

Hello,
I have written code in ASP.net 2.0 to insert the values in a SQL
Server Database. the code is as follows

Dim addemp As System.Data.SqlClient.SqlCommand = New
System.Data.SqlClient.SqlCommand("INSERT INTO Department
(department_name,lastname,firstname,title,hiredate,reportsto,photo)
values(@departmentname,@lastname,@firstname,@title,@hiredate,@reportsto,0x0);"
& "SELECT @identity=SCOPE_IDENTITY();" & "SELECT @pointer=TEXTPTR(photo) from
department where department_id=@identity", con)

Now the SQLException is raised as :

'SCOPE_IDENTITY' is not a recognized function name.

I want to know if the syntax of the above statement is correct or not also
how to deal with this exception

Thank you

 

[Patrice]

This function is new in SQL Server 2000. Which version are you using ?

If 2000 or later try perhaps to test just the offending function callinc ase
it would be something else causing theis problem (missing variable
declarations ?)

 

[Kevin Spencer]

I'm not sure why you're getting the specific message you're getting.
However, from reading SQL Server Books Online, it looks to me like the SQL
Statements you are sending are not part of the same Stored Procedure, batch,
or function. Therefore, you should use @@IDENTITY instead, which is not
limited to statements in the same scope. Example:

SELECT @identity = @@IDENTITY;

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Ambiguity has a certain quality to it.

 

[Bruce Barker]

actually scope_identity() should be used instead of @@identity.

scope_identity() is the last identiy assigned in the current context (scope)
by the last statment
@@identity is the last identity assigned by the last statement


for example if table foo has a trigger that inserts into another identity
table foolog

insert foo (1)
select @id = @@identity -- @id is the the identity of the row inserted in
foolog

insert foo (2)
select @id = identity_scope() -- @id is the the identity of the row inserted
in foo


-- bruce (sqlwork.com)