-1

I have stored procedure which gives( output parameter) the Last updated time of the database. I'm consuming that Output parameter in Asp.net to display it on a label control. I'm getting an error on asp.net page : Procedure or function 'spGetDBLastUpdatedTime' expects parameter '@LastUpdatedTime', which was not supplied.

Can you please let me know , what could I be doing wrong.

Here is the Stored Procedure :

    ALTER PROCEDURE [dbo].[spGetDBLastUpdatedTime]
    -- Add the parameters for the stored procedure here
     @LastUpdatedTime Varchar(20)  OUTPUT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
        SET @LastUpdatedTime =(SELECT 
        convert(Varchar(19),last_user_update,100) as LastUpdatedTime
        FROM sys.dm_db_index_usage_stats
        WHERE database_id = DB_ID( 'EmpDB')
        AND OBJECT_ID=OBJECT_ID('tblEmp'))

        
END

and Here is the code in asp.net to consume it.

private void GetLastUpdatedTime()
    {


        string cs2 = ConfigurationManager.ConnectionStrings["ConCString"].ConnectionString;
        SqlConnection con2 = new SqlConnection(cs2);
        using (SqlCommand command = new SqlCommand("spGetDBLastUpdatedTime", con2))
        {
           


            command.CommandType = CommandType.StoredProcedure;
            //command.Parameters["@LastUpdatedTime"].Direction = ParameterDirection.Output;
            SqlParameter parm = new SqlParameter("@LastUpdatedTime", SqlDbType.Int);
            parm.Direction = ParameterDirection.ReturnValue;

            command.Parameters.Add(parm);
            con2.Open();
            command.ExecuteScalar();
            //LblLastUpdated.Text = (string)command.Parameters["@LastUpdatedTime"].Value;
        }

    }
Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
SaraDob
  • 77
  • 11
  • 1
    Side point: your `con2` connection needs a `using`. – Charlieface Apr 05 '22 at 14:02
  • There's no need for an output parameter here. You could just return the date *as a proper date*. You'd avoid localization and formatting problems too *and* use less bandwidth. – Panagiotis Kanavos Apr 05 '22 at 14:02
  • Why do you want this? If you want to find changes in one or more tables, SQL Server's change tracking would be more efficient and allow you to identify the modified or even deleted rows directly – Panagiotis Kanavos Apr 05 '22 at 14:05

1 Answers1

2

A RETURN value is different from an OUTPUT parameter. Change:

parm.Direction = ParameterDirection.ReturnValue;

To:

parm.Direction = ParameterDirection.InputOutput;

Or:

parm.Direction = ParameterDirection.Output;
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490