Using Scope_Identity with a SQLDataSource in ASP.NET

[blouie]

I'm combining a "SELECT Scope_Identity..." statement with an insert
statement in my InsertCommand. When I try to retrieve the value into a

variable, it generates an error stating that my collumn
"myScope_Identity" is invalid. If I run the SQL statements in Query
Analyzer, it works just fine. In Enterprise Manager, it does not
generate an error but it doesn't return any data back.

Any help would be greatly appreciated.


Luis


Dim myInsertCommand As String
Dim myCM_FName As String = Me.txtCM_FName_II.Text
Dim myCM_LName As String = Me.txtCM_LName_II.Text
Dim myCM_MInit As String = Me.txtCM_MInit_II.Text
Dim myCM_NickName As String = Me.txtCM_Nickname_II.Text
Dim myCM_Comments As String = Me.txtCM_Comments_ET.Text
Dim myCM_AddedBy As String = "0"
Dim myCM_ChangedBy As String = "0"
Dim myCM_ChangedByDate As Date = Now()


myInsertCommand = "INSERT INTO tbl_Customer_CustomerMaster "
myInsertCommand += " (CM_FName, CM_LName,
CM_MInit, CM_NickName, CM_Comments, CM_AddedBy, CM_ChangedBy,
CM_ChangedByDate) "
myInsertCommand += "VALUES (N'" & myCM_FName & "', N'" &
myCM_LName & "', N'" & myCM_MInit & "', N'" & myCM_NickName & "', "
myInsertCommand += " N'" & myCM_Comments & "', N'0',

N'0', "
myInsertCommand += " { fn NOW() }); "


myInsertCommand += "SELECT SCOPE_IDENTITY() AS
myScope_Identity; "


Me.sdsInsertCustomerEntity.InsertCommand = myInsertCommand


'Programmatically access the SqlDataSource - get back a
DataView
Dim myScopeIdentity As String


Try
Me.sdsInsertCustomerEntity.Insert()


Dim dv As DataView =
CType(Me.sdsInsertCustomerEntity.Select(DataSourceSelectArguments.Empty),

DataView)
For Each dr As DataRow In dv.Table.Rows
myScopeIdentity = dr("myScope_Identity").ToString()
Next
Me.EditMSG.Text = myScopeIdentity & " - <u>" &
Me.txtCM_LName_II.Text & ", " & Me.txtCM_FName_II.Text & "</u> was
inserted successfully!!!"
Catch ex As Exception
Me.EditMSG.Text = "ERROR - Please check data and try
again!"
End Try