"Rich" wrote:

> 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
>