How use newest userid in auto INSERT (2.0)

  • Thread starter Thread starter VB Programmer
  • Start date Start date
V

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

S. Justin Gengo said:
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 said:
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?
Click to expand...
Click to expand...
 
Back
Top