How use newest userid in auto INSERT (2.0)

[VB Programmer]

I have a table called 'MyUsers'. Some fields are as follows:
UserId (p key to tie it to aspnet_Users), MyAddress, MyCity, MyState, etc...

After I do a Membership.CreateUser to add the new member to aspnet_Users I
want to run the Insert command from my SqlDataSource, using the new UserId.
(I guess it's ProviderUserKey?)

The Insert command for my SqlDataSource is stated as:
InsertCommand="INSERT INTO [MyUsers] ([UserId], [FirstName], [LastName],
[MyPhone], [MyAddress], [MyCity], [MyState], [MyZip], [ExpYears],
[ExpSponsor], [ExpSponsorUrl], [ExpClass], [ExpWebsiteUrl], [ExpComments])
VALUES (@UserId, @FirstName, @LastName, @MyPhone, @MyAddress, @MyCity,
@MyState, @MyZip, @ExpYears, @ExpSponsor, @ExpSponsorUrl, @ExpClass,
@ExpWebsiteUrl, @ExpComments)"

I want to use the NEW UserId for the Insert command. Any ideas?

 

[S. Justin Gengo [MCP]]

VB Programmer,

In your stored procedure you use @@Identity to get the just created id. You
may then use that new id in a new statement right there in the same stored
procedure or return it. To return it you would do something like:

@Name As nvarchar(100),
@NewId int output

INSERT COMMAND HERE;

SET @NewId = SELECT @@Identity

RETURN

In .NET 2.0 you also need to specify the output parameter as part of your
insert statement.

Here's an article that shows one way of doing this:
http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.inserted.aspx



--
Sincerely,

S. Justin Gengo, MCP
Web Developer / Programmer

www.aboutfortunate.com

"Out of chaos comes order."
Nietzsche

 

[VB Programmer]

Thanks. Is there any example that shows how to do something like this (the
simplest way) in ASP.NET 2.0...

1. Simple CreateAddress webform with several textboxes (txtAddress, txtCity,
txtState), 1 save button.
2. Click Save button and the data is inserted into the Addresses table.

I know how to do it with ADO.NET code, but how can I do it codeless, using I
suppose the SqlDataSource?

Thanks.

VB Programmer,

In your stored procedure you use @@Identity to get the just created id.
You may then use that new id in a new statement right there in the same
stored procedure or return it. To return it you would do something like:

@Name As nvarchar(100),
@NewId int output

INSERT COMMAND HERE;

SET @NewId = SELECT @@Identity

RETURN

In .NET 2.0 you also need to specify the output parameter as part of your
insert statement.

Here's an article that shows one way of doing this:
http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.inserted.aspx



--
Sincerely,

S. Justin Gengo, MCP
Web Developer / Programmer

www.aboutfortunate.com

"Out of chaos comes order."
Nietzsche

I have a table called 'MyUsers'. Some fields are as follows:
UserId (p key to tie it to aspnet_Users), MyAddress, MyCity, MyState,
etc...

After I do a Membership.CreateUser to add the new member to aspnet_Users
I want to run the Insert command from my SqlDataSource, using the new
UserId. (I guess it's ProviderUserKey?)

The Insert command for my SqlDataSource is stated as:
InsertCommand="INSERT INTO [MyUsers] ([UserId], [FirstName], [LastName],
[MyPhone], [MyAddress], [MyCity], [MyState], [MyZip], [ExpYears],
[ExpSponsor], [ExpSponsorUrl], [ExpClass], [ExpWebsiteUrl],
[ExpComments]) VALUES (@UserId, @FirstName, @LastName, @MyPhone,
@MyAddress, @MyCity, @MyState, @MyZip, @ExpYears, @ExpSponsor,
@ExpSponsorUrl, @ExpClass, @ExpWebsiteUrl, @ExpComments)"

I want to use the NEW UserId for the Insert command. Any ideas?