How use newest userid in auto INSERT (2.0)

13 Jan 2006 5:55 PM

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?

13 Jan 2006 9:36 PM

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

Show quoteHide quote

"VB Programmer" <d***@emailme.com> wrote in message
news:eY70OqGGGHA.3080@TK2MSFTNGP10.phx.gbl...
>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?
>

13 Jan 2006 9:45 PM

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.

Show quoteHide quote

"S. Justin Gengo [MCP]" <justin@[no_spam_please]aboutfortunate.com> wrote in
message news:OWyIzlIGGHA.2444@TK2MSFTNGP11.phx.gbl...
> 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" <d***@emailme.com> wrote in message
> news:eY70OqGGGHA.3080@TK2MSFTNGP10.phx.gbl...
>>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?
>>
>
>