Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > result from stored procedure

Reply
Thread Tools

result from stored procedure

 
 
René de Leeuw
Guest
Posts: n/a
 
      10-23-2003
Hi,

Can someone provide me with code how to send a string (@name) to the test
table. And how to read the result column wich contains the just generated
testid value from the test table. Here details of the table and the stored
procedure. Thanks in advance.

Regards René.

CREATE TABLE [test] (
[testid] [int] IDENTITY (1, 1) NOT NULL ,
[name] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE proc ins_test
@name char (10)
AS

SET NOCOUNT ON
BEGIN TRAN

INSERT INTO test
(name)
VALUES (@name)

SELECT @@identity AS result

COMMIT TRAN
SET NOCOUNT OFF
GO



 
Reply With Quote
 
 
 
 
Cowboy \(Gregory A. Beamer\)
Guest
Posts: n/a
 
      10-23-2003
Something like this (note: you can get more explicit, which is a good
thing):

// Connection string, stored proc name and value for parameter
string connString = "{Your connection string here}";
string sprocName = "ins_test";
string nameValue = "value for @name parameter";

// This is used to return the identity value
int IdentityValue;

SqlConnection conn = new SqlConnection(connString);

SqlCommand cmd = new SqlCommand(sprocName, conn);

// Make sure you set command type to sproc
cmd.CommandType = CommandType.StoredProcedure;

// add a new parameter for @name
cmd.Parameters.Add(new SqlParameter("@name", nameValue));

// Error handling for running command
try
{
conn.Open();
IdentityValue = cmd.ExecuteScalar(); //returns only one value
conn.Close();
}
catch(Exception ex)
{
// Code for exception. If not doing anything, do not use catch
}
finally
{
conn.Dispose();
}

You will want to play with this a bit, as it is bare minimum. I did not test
this code, so it may have some typos. The methodology is the same for
VB.NET, so you can step through the idea and code to your language.

One additional note: It is better to use SCOPE_IDENTITY than @@IDENTITY in
most instances. Look up SCOPE_IDENTITY in books online.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

************************************************** ********************
Think Outside the Box!
************************************************** ********************
"René de Leeuw" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Hi,
>
> Can someone provide me with code how to send a string (@name) to the test
> table. And how to read the result column wich contains the just generated
> testid value from the test table. Here details of the table and the stored
> procedure. Thanks in advance.
>
> Regards René.
>
> CREATE TABLE [test] (
> [testid] [int] IDENTITY (1, 1) NOT NULL ,
> [name] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
>
> CREATE proc ins_test
> @name char (10)
> AS
>
> SET NOCOUNT ON
> BEGIN TRAN
>
> INSERT INTO test
> (name)
> VALUES (@name)
>
> SELECT @@identity AS result
>
> COMMIT TRAN
> SET NOCOUNT OFF
> GO
>
>
>



 
Reply With Quote
 
 
 
 
René de Leeuw
Guest
Posts: n/a
 
      10-23-2003
Thanks for your effort. The code is working fine now.

Best regards,
René.

"Cowboy (Gregory A. Beamer)" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
> Something like this (note: you can get more explicit, which is a good
> thing):
>
> // Connection string, stored proc name and value for parameter
> string connString = "{Your connection string here}";
> string sprocName = "ins_test";
> string nameValue = "value for @name parameter";
>
> // This is used to return the identity value
> int IdentityValue;
>
> SqlConnection conn = new SqlConnection(connString);
>
> SqlCommand cmd = new SqlCommand(sprocName, conn);
>
> // Make sure you set command type to sproc
> cmd.CommandType = CommandType.StoredProcedure;
>
> // add a new parameter for @name
> cmd.Parameters.Add(new SqlParameter("@name", nameValue));
>
> // Error handling for running command
> try
> {
> conn.Open();
> IdentityValue = cmd.ExecuteScalar(); //returns only one value
> conn.Close();
> }
> catch(Exception ex)
> {
> // Code for exception. If not doing anything, do not use catch
> }
> finally
> {
> conn.Dispose();
> }
>
> You will want to play with this a bit, as it is bare minimum. I did not

test
> this code, so it may have some typos. The methodology is the same for
> VB.NET, so you can step through the idea and code to your language.
>
> One additional note: It is better to use SCOPE_IDENTITY than @@IDENTITY in
> most instances. Look up SCOPE_IDENTITY in books online.
>
> --
> Gregory A. Beamer
> MVP; MCP: +I, SE, SD, DBA
>
> ************************************************** ********************
> Think Outside the Box!
> ************************************************** ********************
> "René de Leeuw" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
> > Hi,
> >
> > Can someone provide me with code how to send a string (@name) to the

test
> > table. And how to read the result column wich contains the just

generated
> > testid value from the test table. Here details of the table and the

stored
> > procedure. Thanks in advance.
> >
> > Regards René.
> >
> > CREATE TABLE [test] (
> > [testid] [int] IDENTITY (1, 1) NOT NULL ,
> > [name] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> > ) ON [PRIMARY]
> > GO
> >
> > CREATE proc ins_test
> > @name char (10)
> > AS
> >
> > SET NOCOUNT ON
> > BEGIN TRAN
> >
> > INSERT INTO test
> > (name)
> > VALUES (@name)
> >
> > SELECT @@identity AS result
> >
> > COMMIT TRAN
> > SET NOCOUNT OFF
> > GO
> >
> >
> >

>
>



 
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
1. Ruby result: 101 seconds , 2. Java result:9.8 seconds, 3. Perl result:62 seconds Michael Tan Ruby 32 07-21-2005 03:23 PM
How to call parameterized stored procedure to return result set? Lacka ASP .Net 2 12-31-2004 03:39 PM
stored procedure result comparision Viktor Popov ASP .Net 9 07-20-2004 08:50 PM



Advertisments