locked
identity value returned as 0. RRS feed

  • 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.

    <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>

    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