identity value returned as 0. [Answered]RSS

• • Reply
  tengusoldier

  None

  0 Points

  1 Post

  identity value returned as 0.

  Oct 08, 2016 07:56 PM|tengusoldier|LINK

  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.

  <div class="code_block">

  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>

• • Reply
  Mikesdotnett...

  All-Star

  188201 Points

  27258 Posts

  Moderator

  Re: identity value returned as 0.

  Oct 09, 2016 08:03 PM|Mikesdotnetting|LINK

  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

  ASP.NET Tutorials | Learn Entity Framework Core | Learn Razor Pages | Learn Dapper