Finding the returned value from a SQL query

[ProfReynolds]

Okay, so the SQL SPROC looks like this:

ALTER PROCEDURE [dbo].[UserLogIn] 
	@UserID int = 0,
	@HistoryID int OUTPUT 
AS

Begin
SET NOCOUNT ON;
INSERT INTO UserHistory
   (LOGINID, LogInDateTime)
VALUES (@UserID, GETDATE())

Set @HistoryID = SCOPE_IDENTITY()

End

Then I go to my C# code, and use the following:

public int AddLogInDateTime(int USERID)
{
   string connectionStr = 
       ConfigurationManager.AppSettings["tdssql"];
   SqlConnection connectObj = new 
       SqlConnection(connectionStr);
   SqlCommand commandObj = new SqlCommand("UserLogIn", 
       connectObj);
   commandObj.CommandType = CommandType.StoredProcedure;
   commandObj.Parameters.Add
       ("@UserID", SqlDbType.Int).Value = 1;
   commandObj.Parameters.Add
       ("@HistoryID", SqlDbType.Int).Direction = 
          ParameterDirection.ReturnValue;


   try
   {
      commandObj.Connection.Open();
      commandObj.ExecuteNonQuery();
      HistoryID = Convert.ToInt32
         (commandObj.Parameters["@HistoryID"].Value);
      commandObj.Connection.Close();
      return returnValue;
   }

   catch
   {
      commandObj.Connection.Close();
      return 0;
   }
}

My problem is that when I include the return value for the identity, the ExecuteNonQuery fails, and it goes in to the catch.

If I remove the identity value (from the C# and the SQL), the insert statement works fine.

I need that History ID, as it helps me track user log-ins, and log-outs, throughout the program.

Thanks!

 

[JurkMonkey]

Maybe you want to look at the ExecuteScalar method?

http://www.ubuntu.com/

 

[ProfReynolds]

Here's what I've got for ExecuteScalar:

C# Code

   try
   {
      commandObj.Connection.Open();
      int HistoryID = 
         Convert.ToInt32(commandObj.ExecuteScalar());
      commandObj.Connection.Close();
      return HistoryID;
   }

SPROC Code

ALTER PROCEDURE [dbo].[UserLogIn]
    @UserID int = 0,
    @HistoryID int OUTPUT
AS

Begin
SET NOCOUNT ON;
INSERT INTO UserHistory
   (LOGINID, LogInDateTime)
VALUES (@UserID, GETDATE())

Select Scope_Identity()

End

Problem is, it renders the same problem. It doesn't finish the try, and goes in to the Catch during the execution of the query.

 

[phinoppix]

Hi,
Using your first stored proc, change the direction type.

   commandObj.Parameters.Add
       ("@HistoryID", SqlDbType.Int).Direction =
          [COLOR=blue]ParameterDirection.Output[/color];

To get the value, try this...

    obj value = (int)commandObj.Parameters["@HistoryID"].Value;

I've gotten rusty with SQL and ADO, but I hope this will help