C# SQL stored procedure (which inserts) - pass parameters and retrieve parameter

Question

I have a stored procedure on my server that inserts some parameters and returns the ID that was inserted. I am writing a form to do this easily but I cannot seem to get the parameter which is passed back.

To save you doing a whole bunch of possibly pointless reading, it's probably better to just pay attention to my C# code and let me know what I need to do in order to pass parameters and get one in return.

C# Default.aspx

connection = new SqlConnection(ConfigurationManager.AppSettings["ConnectionInfo"]);
sql = "aStoredProc";

command = new SqlCommand(sql, connection);
command.CommandType = CommandType.StoredProcedure;

command.Parameter.Add(new SqlParameter("@FirstName", SqlDbType.VarChar)).Value = sFirstname;
command.Parameter.Add(new SqlParameter("@SurName", SqlDbType.VarChar)).Value = sSurname;

connection.Open(); 
int ID = command.ExecuteNonQuery();
connection.Close();

SQL aStoredProc

IF EXISTS(SELECT * FROM aTable WHERE ID = @ID)
  -- User exists, update details
  BEGIN
    BEGIN TRAN
      UPDATE aTable
        SET 
          FirstName = @FirstName,
          SurName = @SurName,
          LastUpdate = GetDate()
        WHERE ID = @ID

        IF (@@Error != 0)
          ROLLBACK TRAN
        ELSE
          COMMIT TRAN
        END
ELSE
  -- New user
  BEGIN
    BEGIN TRAN
      INSERT aTable (
        FirstName,
        SurName,
        GetDate()
      )
        VALUES (
          @FirstName, 
          @SurName,
          @LastUpdate
      ) 

      SELECT @ID = @@IDENTITY

      IF (@@Error != 0)
        ROLLBACK TRAN
      ELSE
        COMMIT TRAN
      END

The parameter @ID is listed in the stored proc as:

@ID (int, Input/Output, No default)

and proc has 'Return integer'. This used to work fine with a VBA solution prior to a SQL Server 2005 upgrade.

Thanks in advance.

Solution 1

connection = new SqlConnection(ConfigurationManager.AppSettings["ConnectionInfo"]); 
sql = "aStoredProc"; 
command = new SqlCommand(sql, connection); 
command.CommandType = CommandType.StoredProcedure; 
command.Parameter.Add(new SqlParameter("@FirstName", SqlDbType.VarChar)).Value = sFirstname; 
command.Parameter.Add(new SqlParameter("@SurName", SqlDbType.VarChar)).Value = sSurname; 
command.Parameter.Add(new SqlParameter("@SurName", SqlDbType.VarChar)).Value = sSurname; 
SqlParameter ParamId = cmd.Parameters.Add( "@Id", SqlDbType.Int);
ParamId.Direction = ParameterDirection.InputOutput;
command.Parameter.Add(ParamId);
connection.Open();  
command.ExecuteNonQuery(); 
int ID = ParamId.Value;
connection.Close();

1. you have to add output paramter in Parameter collection.
2. Read Value like above.

Solution 2

You have an error in your SQL, it should look like this:

 INSERT aTable (FirstName,SurName,LastUpdate)
    VALUES (@FirstName, @SurName, GetDate() ) 

Not like this:

 INSERT aTable (
    FirstName,
    SurName,
    GetDate()
  )
    VALUES (
      @FirstName, 
      @SurName,
      @LastUpdate
  ) 

Comments

[Tim M.]

See stackoverflow.com/questions/8633821/… for a similar question/answer.

[Mitch Wheat]

you have a logic flaw in stored proc. (in the else 'new user' branch): any statement executing before inspecting @@ERROR will reset @@ERROR

[marc_s]

I would also recommend to use SCOPE_IDENTITY() instead of anything else to grab the newly inserted identity value. See this blog post for an explanation as to WHY

[marc_s]

Another hint: when you define SqlParameter with a datatype of varchar - always define a length! Otherwise, you end up with parameters of 1 character length!! So use this: new SqlParameter("@FirstName", SqlDbType.VarChar, 100)) (or replace 100 with whichever value you want/need...)

[Matt Rowles]

Thanks mate will try this tomorrow.

[Matt Rowles]

Yeah it's not my exact SQL, just a rough idea of what it is.

[Matt Rowles]

I think 'vendor' should be 'ParamId'. But this errors at the line 'int ID = ParamId.Value;' - cannot convert object to int.

[Matt Rowles]

Yes I tried parsing/typecasting but to no avail. I'll keep fiddling, thanks for your help mate

[phougatv]

command is an object of class SqlCommand, and SqlCommand class doesn't have a property with the name: Direction. In command.Direction the object must be of SqlParameter class. Isn't that right?