Remove From My Forums

identity value returned as 0.

Question

User818671590 posted

I have a contact info that can be used to turn the contact into a customer, to do so I want to send the id from the contact to the other form and to do this I created a stored procedure that returns the id using SCOPE_IDENTITY(), it works in SQL server and returns the identity correctly but when I try to aquire it in Visual Studio and set it in a label for testing it reads as 0.

my stored procedure is as follows.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE IdentityTest
      @FirstName varchar(50),
      @LastName varchar(50),
    
      @id int output
AS
BEGIN
      SET NOCOUNT ON;
      INSERT INTO  Employees (FirstName, LastName)
      VALUES (@FirstName, @LastName)
      SET @id=SCOPE_IDENTITY()
      RETURN  @id
END

</div> <div class="code_block">in my form.aspx.cs I have the following to acess the DB.</div> <div class="code_block"></div> <div class="code_block"></div> <div class="code_block">

protected void Button1_Click(object sender, EventArgs e)
    {

        
        
        SqlCommand cons = new SqlCommand("IdentityTest", cncon);
        
        cons.CommandType = CommandType.StoredProcedure;
        


        cons.Parameters.Add("@FirstName", SqlDbType.NVarChar);
        cons.Parameters.Add("@LastName", SqlDbType.NVarChar);
 
        cons.Parameters.Add("@id", SqlDbType.Int);



        

        cons.Parameters["@FirstName"].Value = nom.Value;

        cons.Parameters["@LastName"].Value = dirclie.Value;

        cons.Parameters["@id"].Value = IdCliente;
        string IdClientesString = IdCliente.ToString();

        cncon.Open();
        
        cons.ExecuteNonQuery();
        
        Label1.Text = IdClientesString;
        
        cncon.Close();

</div> <div class="code_block">WHen I press the button, the data is successfully inserted into the DB, but my label reads as 0, meaning the correct id was not taken from the @id parameter.</div> <div class="code_block"></div> <div class="code_block"></div>

Saturday, October 8, 2016 7:56 PM

Answers

User-821857111 posted

You need to specify the direction of an out parameter and capture its value:

int ID;
string connect = @"Server=.\SQLExpress;Database=Northwind;Trusted_Connection=Yes;";
using (SqlConnection conn = new SqlConnection(connect))
{
  using (SqlCommand cmd = new SqlCommand(query, conn))
  {
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@Category", Category.Text);
    cmd.Parameters.Add("@CategoryID", SqlDbType.Int, 0, "CategoryID");
    cmd.Parameters["@CategoryID"].Direction = ParameterDirection.Output;
    conn.Open();
    cmd.ExecuteNonQuery();
    ID = (int)cmd.Parameters["@CategoryID"].Value;
  }
}

See here: http://www.mikesdotnetting.com/article/54/getting-the-identity-of-the-most-recently-added-record

Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM

Sunday, October 9, 2016 8:03 PM

Answered by:

identity value returned as 0.

Question

Answers