How use newest userid in auto INSERT (2.0)

Discussion in 'ASP .Net' started by VB Programmer, Jan 13, 2006.

  1. 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, Jan 13, 2006
    #1
    1. Advertising

  2. 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" <> wrote in message
    news:...
    >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], Jan 13, 2006
    #2
    1. Advertising

  3. 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 [MCP]" <justin@[no_spam_please]aboutfortunate.com> wrote in
    message news:...
    > 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" <> wrote in message
    > news:...
    >>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, Jan 13, 2006
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Jim
    Replies:
    3
    Views:
    395
  2. Jim
    Replies:
    6
    Views:
    405
  3. linkswanted
    Replies:
    1
    Views:
    992
  4. lw1a2
    Replies:
    1
    Views:
    304
    Salt_Peter
    Sep 30, 2008
  5. TdarTdar
    Replies:
    15
    Views:
    295
    André da Silva Carrilho
    Jul 28, 2006
Loading...

Share This Page