Using Scope_Identity with a SQLDataSource in ASP.NET

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

.

Relevant Pages

  • RE: Issue with DataView and DataRowView
    ... I'm using a dataview to filter and sort the ... records so that I can print all of the Florida client together in column then ... Private Sub WriteOutDataAs String, ... Dim dr As DataRowView ...
    (microsoft.public.dotnet.general)
  • Re: Like operator is not working with Dataview.RowFilter
    ... I'd see if I could convert it a string and try the string compare). ... Its working fine when data type of searching column is "string". ... Dim dv As New DataView ...
    (microsoft.public.dotnet.languages.vb)
  • Using Scope_Identity with a SQLDataSource in ASP.NET
    ... statement in my InsertCommand. ... Dim myInsertCommand As String ... Dim dv As DataView = ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Navigation per Buttons
    ... Dim objDataAdapter As OleDb.OleDbDataAdapter ... Dim objDataView As DataView ... Dim Vorname, Nachname As String ... Gruß Wibke ...
    (microsoft.public.de.german.entwickler.dotnet.asp)
  • RE: populate datagrid
    ... Dim dvEventStatuss As New DataView ... Public Function Do_DBConnection(ByVal strTBLName As String, ... String, ByVal strConnStrToUse As String, ByVal strWhoCalledMe As String, ... ByVal objVisitor As clsSiteFunctions.clsUser) As DataView ...
    (microsoft.public.dotnet.framework.aspnet)