ObjectDatasource: Can't get the value of output parameter in the Updated event

Thanks, davrob60

I will check out as soon as I can access the Server -- which will be Tues am.

( I was just working on an ExecuteNonQuery method also since I certainly do not need an ExecuteReader for an Update command.

                           thanks,


                                            Curlew

davrob60

ExecuteNonQuery does not work.

In the UpdateOfficersForApproval I tried:
    ReportForThisOfficer = CInt(cmd.ExecuteScalar())

But the updated event at : intRetVal = CType(e.OutputParameters("ReportForThisOfficer"), Int32) fails to return the value. It returns 0 (And e.outputParameters.Count also returns 0).

    Curlew

did you make sure stored procedure return the value?

exemple, make sure each and evry "RETURN" clause is something like

RETURN @ReportForThisOfficer  

or dont use the @ReportForThisOfficer valrible and directly return the value.

--SET @ReportForThisOfficer =  7  -- succesful update
RETURN 7

davrib60::

RETURN @ReportForThisOffice does not solve.so I took up your second suggestion to take out the Output param and replace with a Return param.

Now I get the error :

     No default member found for type 'Integer'

intRetVal = CType(e.ReturnValue("ReportForThisOfficer"), Int32)

I am looking into this error.  

It seems that the stored Proc has to be giving a return value; The update is working in the database.
Here is my selective code:

SP:

ALTER PROCEDURE [dbo].[UpdateOfficersForApproval]
      -- Add the parameters for the stored procedure here
      @OfficerID int,
    @CertificationNo int,
      @UserType int,
      @Username varchar(75),
      @MyPassword varchar(50)
      -- @ReportForThisOfficer int OUTPUT
      
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    IF EXISTS
(
   SELECT * FROM Officer
   WHERE CertNo = @CertificationNo
   AND UserType = 1  -- Another officer w/ same CertNo has been approved by the TSS Admin.
   -- AND OfficerID <> @OfficerID
)
  BEGIN
      
      -- SELECT @ReportForThisOfficer = 5   -- means we have a duplicate Certification No and cannot do the update
         RETURN 5

  END       
  ELSE

  BEGIN
   
     -- UPdate statements for procedure here
            Update Officer
            SET UserType=@UserType, Username=@Username, MyPassword=@MyPassword
            Where OfficerID=@OfficerID
            IF(@@ERROR <>0) GOTO ERR_HANDLER    

      --      SELECT @ReportForThisOfficer =  7  -- succesful update
            RETURN 7

IN the Class :

       Public Function UpdateOfficersForApproval(ByVal OfficerID As Int32, ByVal CertificationNo As Int32, ByVal UserType As Int16, ByVal UserName As String, ByVal MyPassword As String, ByVal ReportForThisOfficer As Int32) As Int32

            Dim ClassReportForOfficer As Int32
            Dim con As New SqlConnection(_conString)
            Dim cmd As New SqlCommand("UpdateOfficersForApproval", con) 'GetOfficersForApproval is also the name of the SP
            cmd.CommandType = CommandType.StoredProcedure

            cmd.Parameters.AddWithValue("@OfficerID", OfficerID)
            cmd.Parameters.AddWithValue("@CertificationNo", CertificationNo)
            cmd.Parameters.AddWithValue("@UserType", UserType)
            cmd.Parameters.AddWithValue("@Username", UserName)
            cmd.Parameters.AddWithValue("@MyPassword", MyPassword)

            cmd.Parameters.Add("@ReportForThisOfficer", Data.SqlDbType.Int)
            cmd.Parameters("@ReportForThisOfficer").Direction = Data.ParameterDirection.ReturnValue

            Try
                con.Open()

               
                cmd.ExecuteNonQuery()
                ReportForThisOfficer = CType(cmd.Parameters("@ReportForThisOfficer").Value, Int32)
                ClassReportForOfficer = ReportForThisOfficer
            Catch ex As Exception

            Finally
                con.Close()

            End Try

            Return ClassReportForOfficer

        End Function

     Any ideas would be appreciated.


                   

 

what is that code???

CType(e.ReturnValue("ReportForThisOfficer"), Int32)

why are you referencing "ReportForThisOfficer".  

wat do you have in e.ReturnValue?

davrob60, thanks for your response

Declaratively in the ObjectDataSource, I have as one of my UpdateParameters:

<asp:Parameter
          Name="ReportForThisOfficer"
          Direction="ReturnValue"
          Type="Int32" />

that parameter is supposed to get the value that the SP returns, and so I pass that in as a parameter to the UpdateOfficersForApproval function in the ods class. I am trying to assign to ReportForThisOfficer the Return value of the SP. Then I need to access that value in the Updated event of the ods.

                   

since it's a return value and there is one and only one, i think you dont need to reference the variable.

try :

CType(e.ReturnValue, Int32)

http://devicezero.com/blog/?p=125

give me news if it don t work, i guess we need to get rid of the returns. i think it's those return that block  everything from the beginning.

here a link that made me think about that...

http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/Q_23592563.html

CType(e.ReturnValue, Int32)  did not work for me.
My debug displayed: " Return value is: 0... The expected values not returned by SP"
Here is what I did to implement,
    (1) in the ods class
       (a) changed the Function to a Sub
       (b) deleted the ReturnValue parameter
        (c) Try
                con.Open()
                cmd.ExecuteNonQuery()  
            Catch ex As Exception            
            Finally
                con.Close()
            End Try
  (2) SP has no ref to an output parameter; SP states
             "RETURN 5"  or "RETURN 7" based on the IF ELSE Statement
  (3) updated event states: intRetVal = CType(e.ReturnValue, Int32)
                         Any ideas or where this is not right?
                            My thinking is that the SP is returning a value, but the class is not getting the Return value per :  cmd.ExecuteNonQuery() ( I will try something like: MyRetVal = cmd.ExecuteNOnQuery).
(When we do ExecuteNonQuery it returns what the SP retruns, right?)

                                  Thanks

i think to get something from the sp, you need either to use execute scalar :

MyRetVal = cmd.ExecuteScalar

or use a ExecuteReader, maybe you need to keep ref of the parameter object like in the 4guysfromrolla article :

'Create a SqlParameter object to hold the output parameter value
Dim retValParam as New SqlParameter("@ReportForThisOfficer", SqlDbType.Int)

'IMPORTANT - must set Direction as ReturnValue
retValParam.Direction = ParameterDirection.ReturnValue

'Finally, add the parameter to the Command's Parameters collection
myCommand.Parameters.Add(retValParam)

'Call the sproc...
Dim reader as SqlDataReader = myCommand.ExecuteReader()

'Now you can grab the output parameter's value...
Dim retValParam as Integer = Convert.ToInt32(retValParam.Value)

after implementing your changes (using ExecuteReader and adding the ReturnValue parameter, I am able to get a value in the objectdatasource object:

the ReturnValue Param, ReportForThisOfficer, returns 7 (indicating a successfull update) per code below  
    Try
                con.Open()
                Dim MyReader As SqlDataReader = cmd.ExecuteReader()
                MyReader.Close()
                ReportForThisOfficer = CType(cmd.Parameters("@ReportForThisOfficer").Value, Int32)

OK so far so good. However, in the ods updated event
    intRetVal = CType(e.ReturnValue, Int32) returns 0
and
  intRetVal = CType(e.ReturnValue("ReportForThisOfficer"), Int32) returns an error message:
                   " Object variable or With block not set."

I am now going to change the parameter from direction: returnValue to outputParameter
and see if I can get a value in the UPdated event

In the meantime, if you have any insights as to what the error message is saying, please let me know.


                     thanks

                   


 
 
     


   

Bingo!

I changed the Sub  to a Function. We already knew that
 
ReportForThisOfficer = CType(cmd.Parameters("@ReportForThisOfficer").Value, Int32) returns the correct value from the SP; now to get that value to the Updated event, I added this line of code at the end of the function:

     Return ReportForThisOfficer

  thanks for all of your help. I'll award full points and close.

       Your the Man!

                             Curlew

Thanks for staying with me on this one.