0

I have an insert command for which I need the identity row from.

<asp:SqlDataSource ID="dsUser" runat="server" ConnectionString="<%$ ConnectionStrings:Link %>"
DeleteCommand="DELETE FROM User WHERE (uID = @uID)"
InsertCommand="INSERT INTO User(uID, FirstName, LastName, UserLogin, UserPassword, UserEmail) VALUES (NEWID(), @FirstName, @LastName, @UserLogin, @UserPassword, @UserEmail) SET @NewUserId=SCOPE_IDENTITY();"
<DeleteParameters>
    <asp:Parameter Name="uID" />
</DeleteParameters>
<InsertParameters>
    <asp:Parameter Name="FirstName" />
    <asp:Parameter Name="LastName" />
    <asp:Parameter Name="UserLogin" />
    <asp:Parameter Name="UserPassword" />
    <asp:Parameter Name="UserEmail" />
    <asp:Parameter DbType="Guid" Direction="Output" Name="NewUserId" />
</InsertParameters>
</asp:SqlDataSource>

I'm trying to access the NewUserId when the dsUser value is inserted.

Private Sub dsUser_Inserted(sender As Object, e As SqlDataSourceStatusEventArgs) Handles dsUser.Inserted
    Dim dvarl = e.Command.Parameters("@NewUserId").Value
    Response.Redirect("editUser.aspx?uid=" + e.Command.Parameters("@NewUserId").Value)
End Sub

Once the data is inserted I want to redirect to the edit screen of the newly added User.

Debugging this doesn't really give any clues as to why the output param is not being SET properly

debugger

I'm making progress debugging and found a new error message:

enter image description here

Which results: Operand type clash: numeric is incompatible with uniqueidentifier

Kyle
  • 126
  • 8
  • I'm surprised that SQL code works at all. I would have thought that you'd need a semicolon after the `INSERT` statement, before the `SET`. – jmcilhinney Mar 10 '23 at 01:25
  • You would have to DECLARE the variable as OUTPUT in the SQL script. I'm surprised it doesn't error out actually. – Crowcoder Mar 10 '23 at 01:26
  • @Crowcoder, that's not the case. Setting the `Direction` of the command parameter is how it's done. ADO.NET takes care of the rest. – jmcilhinney Mar 10 '23 at 01:28
  • @jmcilhinney I guess that explains why it doesn't error. But I'm still going to have to verify for myself. – Crowcoder Mar 10 '23 at 01:48
  • Would there be any problem if I just generated the Guid with the Guid class in VB.net (Vs SQL newid()) and just saved that and used it later after insertion? – Kyle Mar 10 '23 at 06:12
  • @Kyle yes, that could be a problem because non-sequential guids like that make poor primary keys. There are ways to generate sequential guids but not with System.Guid. – Crowcoder Mar 10 '23 at 11:03
  • 1
    @Kyle it works when I try it. Are you sure there is no exception being swallowed? If I force an exception it doesn't raise until after the inserted event handler executes. – Crowcoder Mar 10 '23 at 13:05
  • @Crowcoder I debugged further and found an exception like you suggested. I updated the post. – Kyle Mar 10 '23 at 20:45

1 Answers1

1

Alright so I think there was an issue getting the identity/output from the Guid as mentioned here: SCOPE_IDENTITY() for GUIDs?

The solution I came up with was to set the output parameter with the NEWID() and use the parameter in the insert:

InsertCommand="SET @NewUserId=NEWID() INSERT INTO User(uID, FirstName, LastName, UserLogin, UserPassword, UserEmail) VALUES (@NewUserId, @FirstName, @LastName, @UserLogin, @UserPassword, @UserEmail)"

Everything now works!

Kyle
  • 126
  • 8