scope_Identiy() problem vs @@Identity with daAdapter.InsertCommand

[Guest]

Hello,

I am having a problem using Scope_Identity() when inserting a row to a sql
server (sql server 2000 standard Edition) table using a sql
DataAdapter.InsertCommand. The row gets inserted but it does not display on
the datagridview on my client app (vb2005). I just get a blank row. But if
I use @@Identity in the return statement then the new row shows up correctly
in the datagridview. The code I am using follows. Note: I used this same
code on Sql server 2000 personal edition - loaded on my workstation, and I
did not have a problem using Scope_Identity(). But on the Standard Edition -
which is server based - I have a problem using Scope_Identity(). The newly
inserted row shows up in the datagridview but does not display any data when
I use Scope_Identity(). If I clear the dataset (ds) and refill it - then
the new row shows up with the new data. If I use @@Identity then the new
rows shows up and displays the new data in the datagridview right away. Is
there something I need to change when using Scope_Identity()?

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button3.Click
Dim dr As DataRow, i As Integer
dr = ds.Tables("tbl1").NewRow
ds.Tables("tbl1").Rows.Add(dr)
da.InsertCommand = New SqlCommand
da.InsertCommand.Connection = conn

'--this insert command returns data correctly
'da.InsertCommand.CommandText = "Insert Into tbl1(tName, fld2) Values(@p1,
@p2); Select tID, tName, fld2 from tbl1 Where tID = @@Identity"

'--this insert command does not display any return data
da.InsertCommand.CommandText = "Insert Into tbl1(tName, fld2) Values(@p1,
@p2); Select tID, tName, fld2 from tbl1 Where tID = Scope_Identity()"

da.InsertCommand.Parameters.Add(New SqlParameter("@p1", SqlDbType.VarChar,
50))
da.InsertCommand.Parameters.Add(New SqlParameter("@p2", SqlDbType.VarChar,
50))
da.InsertCommand.Parameters("@p1").Value = "test5"
da.InsertCommand.Parameters("@p2").Value = "test6"
da.Update(ds, "tbl1")

'--this will write the new Identity value when using @@Identity but not
Scope_Ident
Console.WriteLine("*" & dr.Item(0).ToString & "*")
End Sub


Thanks,
Rich

 

[Guest]

Hello

First of all I would like ask you that is there any Before Insert Trigger
associated with Tbl1? If so, then SCOPE_IDENTITY returns a NULL value. If
that is the case, then this should be the problem.