0

I have a contact info form in Visual Studio (using C#) that can be used to turn the contact into a customer, to do so I want to send the id from the contact that was just created 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, convert the int into string 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

In my form.aspx.cs I have the following code to acess the DB.

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();
}
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Marcus
  • 31
  • 5

1 Answers1

0

You need to define the @id parameter as an output parameter in your C# code:

SqlParameter idParam = cons.Parameters.Add("@id", SqlDbType.Int);
idParam.Direction = ParameterDirection.Output;

and after executing your query, you need to read out the value:

cons.ExecuteNonQuery();
int newIdValue = Convert.ToInt32(cons.Parameters["@id"].Value);
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459