result from stored procedure

Discussion in 'ASP .Net' started by René de Leeuw, Oct 23, 2003.

  1. 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
     
    René de Leeuw, Oct 23, 2003
    #1
    1. Advertising

  2. 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" <> wrote in message
    news:%...
    > 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
    >
    >
    >
     
    Cowboy \(Gregory A. Beamer\), Oct 23, 2003
    #2
    1. Advertising

  3. Thanks for your effort. The code is working fine now.

    Best regards,
    René.

    "Cowboy (Gregory A. Beamer)" <> wrote in
    message news:...
    > 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" <> wrote in message
    > news:%...
    > > 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
    > >
    > >
    > >

    >
    >
     
    René de Leeuw, Oct 23, 2003
    #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. Viktor Popov

    stored procedure result comparision

    Viktor Popov, Jul 20, 2004, in forum: ASP .Net
    Replies:
    9
    Views:
    558
    Viktor Popov
    Jul 20, 2004
  2. Lacka
    Replies:
    2
    Views:
    866
    Lacka
    Dec 31, 2004
  3. Mike P
    Replies:
    0
    Views:
    3,330
    Mike P
    Jun 19, 2006
  4. AlexWare
    Replies:
    2
    Views:
    776
    Paul Uiterlinden
    Oct 23, 2009
  5. Michael Tan
    Replies:
    32
    Views:
    1,023
    Ara.T.Howard
    Jul 21, 2005
Loading...

Share This Page