Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Stored Procedure

Reply
Thread Tools

Stored Procedure

 
 
Leon Shaw
Guest
Posts: n/a
 
      07-19-2003
How do I implement a stored procedure to insert a new member in a database
then return the primary key of that member back to the application to be use
in another table?


 
Reply With Quote
 
 
 
 
John Knoop
Guest
Posts: n/a
 
      07-19-2003
You could use this SQL code:

SET NOCOUNT ON;
BEGIN TRAN;
INSERT...;
SELECT @@identity AS newID;
COMMIT TRAN;
SET NOCOUNT OFF;

/john

"Leon Shaw" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> How do I implement a stored procedure to insert a new member in a database
> then return the primary key of that member back to the application to be

use
> in another table?
>
>



 
Reply With Quote
 
 
 
 
Steve C. Orr, MCSD
Guest
Posts: n/a
 
      07-19-2003
Have your SQL Server query Select @@Identity after it does the insert.
Then you can get the value back like this:
newId = (int)MyCommand.ExecuteScalar();

--
I hope this helps,
Steve C. Orr, MCSD
http://Steve.Orr.net


"Leon Shaw" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> How do I implement a stored procedure to insert a new member in a database
> then return the primary key of that member back to the application to be

use
> in another table?
>
>



 
Reply With Quote
 
Leon Shaw
Guest
Posts: n/a
 
      07-19-2003
Will This Work? And how do I get this MemberId in a variable in code and
pass it to another table?
CREATE PROCEDURE Add_Member
(
@FirstName varchar(50)
@LastName varchar(50)
@etc varchar(50)
)
AS
INSERT INTO Member
(
FirstName,
LastName,
etc
)
VALUES
(
@FirstName
@LastName
@etc
)
SELECT @MemberID = @@IDENTITY

"Steve C. Orr, MCSD" <(E-Mail Removed)> wrote in message
news:%23K%(E-Mail Removed)...
> Have your SQL Server query Select @@Identity after it does the insert.
> Then you can get the value back like this:
> newId = (int)MyCommand.ExecuteScalar();
>
> --
> I hope this helps,
> Steve C. Orr, MCSD
> http://Steve.Orr.net
>
>
> "Leon Shaw" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > How do I implement a stored procedure to insert a new member in a

database
> > then return the primary key of that member back to the application to be

> use
> > in another table?
> >
> >

>
>



 
Reply With Quote
 
Steve C. Orr, MCSD
Guest
Posts: n/a
 
      07-19-2003
No, don't select the identity value into a private stored procedure variable
or you'll never be able to get to it.
Instead the final line should look like this:
SELECT @@IDENTITY

Alternately you could define @MemberID as an output parameter for your
sproc. Then use ADO.NET parameter objects. After you execute the query,
check that parameter and it should be filled with the identity value.
Here's more info:
http://msdn.microsoft.com/library/de...classtopic.asp
http://msdn.microsoft.com/library/de...isualbasic.asp

--
I hope this helps,
Steve C. Orr, MCSD
http://Steve.Orr.net



"Leon Shaw" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Will This Work? And how do I get this MemberId in a variable in code and
> pass it to another table?
> CREATE PROCEDURE Add_Member
> (
> @FirstName varchar(50)
> @LastName varchar(50)
> @etc varchar(50)
> )
> AS
> INSERT INTO Member
> (
> FirstName,
> LastName,
> etc
> )
> VALUES
> (
> @FirstName
> @LastName
> @etc
> )
> SELECT @MemberID = @@IDENTITY
>
> "Steve C. Orr, MCSD" <(E-Mail Removed)> wrote in message
> news:%23K%(E-Mail Removed)...
> > Have your SQL Server query Select @@Identity after it does the insert.
> > Then you can get the value back like this:
> > newId = (int)MyCommand.ExecuteScalar();
> >
> > --
> > I hope this helps,
> > Steve C. Orr, MCSD
> > http://Steve.Orr.net
> >
> >
> > "Leon Shaw" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > How do I implement a stored procedure to insert a new member in a

> database
> > > then return the primary key of that member back to the application to

be
> > use
> > > in another table?
> > >
> > >

> >
> >

>
>



 
Reply With Quote
 
Leon Shaw
Guest
Posts: n/a
 
      07-19-2003
So how do I get that identity column inside a variable to be pass to another
table that reference that member?
"Steve C. Orr, MCSD" <(E-Mail Removed)> wrote in message
news:%23D6%(E-Mail Removed)...
> No, don't select the identity value into a private stored procedure

variable
> or you'll never be able to get to it.
> Instead the final line should look like this:
> SELECT @@IDENTITY
>
> Alternately you could define @MemberID as an output parameter for your
> sproc. Then use ADO.NET parameter objects. After you execute the query,
> check that parameter and it should be filled with the identity value.
> Here's more info:
>

http://msdn.microsoft.com/library/de...classtopic.asp
>

http://msdn.microsoft.com/library/de...isualbasic.asp
>
> --
> I hope this helps,
> Steve C. Orr, MCSD
> http://Steve.Orr.net
>
>
>
> "Leon Shaw" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Will This Work? And how do I get this MemberId in a variable in code and
> > pass it to another table?
> > CREATE PROCEDURE Add_Member
> > (
> > @FirstName varchar(50)
> > @LastName varchar(50)
> > @etc varchar(50)
> > )
> > AS
> > INSERT INTO Member
> > (
> > FirstName,
> > LastName,
> > etc
> > )
> > VALUES
> > (
> > @FirstName
> > @LastName
> > @etc
> > )
> > SELECT @MemberID = @@IDENTITY
> >
> > "Steve C. Orr, MCSD" <(E-Mail Removed)> wrote in message
> > news:%23K%(E-Mail Removed)...
> > > Have your SQL Server query Select @@Identity after it does the insert.
> > > Then you can get the value back like this:
> > > newId = (int)MyCommand.ExecuteScalar();
> > >
> > > --
> > > I hope this helps,
> > > Steve C. Orr, MCSD
> > > http://Steve.Orr.net
> > >
> > >
> > > "Leon Shaw" <(E-Mail Removed)> wrote in message
> > > news:(E-Mail Removed)...
> > > > How do I implement a stored procedure to insert a new member in a

> > database
> > > > then return the primary key of that member back to the application

to
> be
> > > use
> > > > in another table?
> > > >
> > > >
> > >
> > >

> >
> >

>
>



 
Reply With Quote
 
Steve C. Orr, MCSD
Guest
Posts: n/a
 
      07-20-2003
In the example I gave the identity of the record you just inserted will be
in the newId variable. Then you can pass it wherever you want, perhaps
putting it into a SQLParameter object to call some other query.

--
I hope this helps,
Steve C. Orr, MCSD
http://Steve.Orr.net


"Leon Shaw" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> So how do I get that identity column inside a variable to be pass to

another
> table that reference that member?
> "Steve C. Orr, MCSD" <(E-Mail Removed)> wrote in message
> news:%23D6%(E-Mail Removed)...
> > No, don't select the identity value into a private stored procedure

> variable
> > or you'll never be able to get to it.
> > Instead the final line should look like this:
> > SELECT @@IDENTITY
> >
> > Alternately you could define @MemberID as an output parameter for your
> > sproc. Then use ADO.NET parameter objects. After you execute the

query,
> > check that parameter and it should be filled with the identity value.
> > Here's more info:
> >

>

http://msdn.microsoft.com/library/de...classtopic.asp
> >

>

http://msdn.microsoft.com/library/de...isualbasic.asp
> >
> > --
> > I hope this helps,
> > Steve C. Orr, MCSD
> > http://Steve.Orr.net
> >
> >
> >
> > "Leon Shaw" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > Will This Work? And how do I get this MemberId in a variable in code

and
> > > pass it to another table?
> > > CREATE PROCEDURE Add_Member
> > > (
> > > @FirstName varchar(50)
> > > @LastName varchar(50)
> > > @etc varchar(50)
> > > )
> > > AS
> > > INSERT INTO Member
> > > (
> > > FirstName,
> > > LastName,
> > > etc
> > > )
> > > VALUES
> > > (
> > > @FirstName
> > > @LastName
> > > @etc
> > > )
> > > SELECT @MemberID = @@IDENTITY
> > >
> > > "Steve C. Orr, MCSD" <(E-Mail Removed)> wrote in message
> > > news:%23K%(E-Mail Removed)...
> > > > Have your SQL Server query Select @@Identity after it does the

insert.
> > > > Then you can get the value back like this:
> > > > newId = (int)MyCommand.ExecuteScalar();
> > > >
> > > > --
> > > > I hope this helps,
> > > > Steve C. Orr, MCSD
> > > > http://Steve.Orr.net
> > > >
> > > >
> > > > "Leon Shaw" <(E-Mail Removed)> wrote in message
> > > > news:(E-Mail Removed)...
> > > > > How do I implement a stored procedure to insert a new member in a
> > > database
> > > > > then return the primary key of that member back to the application

> to
> > be
> > > > use
> > > > > in another table?
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >

> >
> >

>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
procedure as argument in procedure AlexWare VHDL 2 10-23-2009 09:14 AM
'Procedure or function <stored procedure name> has too many arguments specified',,,ARGH! Mike P ASP .Net 0 06-19-2006 01:19 PM
Sending email through Stored procedure Patrick ASP .Net 2 02-15-2005 08:29 AM
How to modify default Stored Procedure template in VS.NET database project ES ASP .Net 2 08-20-2004 08:19 PM
New Stored Procedure Template in .Net Sarmad Aljazrawi ASP .Net 0 12-16-2003 11:36 AM



Advertisments