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.Sq lClient.SqlData Reader' to type 'System.IConver tible'”

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

Stored procedure looks like this:
[code=sql]
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
[/code]

[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.Paramet ers["@TSMID"].Value;