Handling output Sqlparameter

[rogerford]

Hi,
I am trying to retrieve a value from database, based on that value I want to insert records into DB.Let’s say I am retrieving tsmid which serves as the output parameter in the stored procedure.

.net/C# code is

public void getTSmid()
        {
            int tsid=0;
           tsid = System.Convert.ToInt32(SqlHelper.ExecuteReader(SqlHelper.tConnectionString,
            CommandType.StoredProcedure, "GetTSMID_SP",
            new SqlParameter("@PID", this.PId), 
            new SqlParameter("@EN", this.En),
            new SqlParameter("@ESG",this.Esg),
            new SqlParameter("@TSMID", SqlDbType.Int, 4, ParameterDirection.Output, true, 5, 0, "TSMId", DataRowVersion.Current, tsid)));
            TSM = tsid;
        }
 

I have the DB connection classes in DLL and above code in BLL.
I am holding the output value into the integer tsid variable which is in the end assigned to TSM variable. I am getting the following error “Unable to cast object of type 'System.Data.SqlClient.SqlDataReader' to type 'System.IConvertible'”

MY Q’: how to handle a output parameter in ASP.net?

Stored procedure looks like this:

CREATE PROCEDURE [dbo].[GetTSMID_SP] 
 
@EN Integer,
@PID Integer,
@ESG varchar(25),
@TSMID integer OUTPUT
AS
DECLARE @ TSMID int
DECLARE @total  int
 
SELECT @total=Count(*) from tbl_Timer where EN =@EN and  PID =@ PID and  ESG =@ESG
    if( @total = 0)
        Begin
            INSERT INTO tbl_Timer(EN, PID,  ESG)
            VALUES(@EN, @ PID,  @ESG)
            SELECT @ TSMID = @@Identity
        end
    else
        begin
            select @ TSMID=TSMID from tbl_Timer where EN =@ EN and  PID =@PID and  ESG =@ESG
        end
 

[Plater]

Well I am not sure why you are trying to convert your DataReader to an int, but yes that is the wrong thing to do.
Assuming your SQL procedure is correct (not sure what it's doing, its very nested)
If you added the SqlParameter for your output value correctly. (What you have it a bit over zealous but will probably work)

All you need to do is call the Execute function and then get the value from the parameter:
tsid= (int)sc.Parameters["@TSMID"].Value;