RETURN VALUES FOR SP

[jadams0173]

I'm converting a VB 6 app to 2005. I'm having a hard time getting back my paraments from a SP. Can someone help?

        SQLCmd.Connection = clOpenConn.clOpenConnSQL
        SQLCmd.CommandType = CommandTypeEnum.adCmdStoredProc ' adCmdStoredProc
        SQLCmd.CommandText = "USP_MASTER_SCHEDULER_FORM_LOAD"
        With SQLCmd
            'PICKS THAT CAN BE RELEASED
            sqlparm = New SqlParameter("@TOTALPICKS_OUT", SqlDbType.Int)
            sqlparm.Direction = ParameterDirection.Output
            .Parameters.Add(sqlparm)
            'FEEDERS THAT CAN BE RELEASED
            sqlparm = New SqlParameter("@TOTALFEEDERS_OUT", SqlDbType.Int)
            sqlparm.Direction = ParameterDirection.Output
            .Parameters.Add(sqlparm)
            'releasable hours.
            sqlparm = New SqlParameter("@ATRHRS_OUT", SqlDbType.Decimal)
            sqlparm.Direction = ParameterDirection.Output
            .Parameters.Add(sqlparm)
            'hrs releasd for the current day.
            sqlparm = New SqlParameter("@TotalHoursRelToday_out", SqlDbType.Decimal)
            sqlparm.Direction = ParameterDirection.Output
            .Parameters.Add(sqlparm)
           
            'feeders released current day
            sqlparm = New SqlParameter("@TOTALFEEDERSRELTODAY_OUT", SqlDbType.Int)
            sqlparm.Direction = ParameterDirection.Output
            .Parameters.Add(sqlparm)
            'picks released current day
            sqlparm = New SqlParameter("@TOTALPICKSRELTODAY_OUT", SqlDbType.Int)
            sqlparm.Direction = ParameterDirection.Output
            .Parameters.Add(sqlparm)
            'HRS released from days past that do not show complete
            sqlparm = New SqlParameter("@OLD_HRS_OUT", SqlDbType.Decimal)
            sqlparm.Direction = ParameterDirection.Output
            .Parameters.Add(sqlparm)
            
            sqlreader = .ExecuteReader
            'sqlreader.Close()
            frmLines.lblFeedersRel.Text = .Parameters("@TOTALFEEDERSRELTODAY_OUT").Value
            frmLines.lblHRSRel.Text = .Parameters("@TotalHoursRelToday_out").Value
            frmLines.lblPicksRel.Text = .Parameters("@TOTALPICKSRELTODAY_OUT").Value
            frmLines.lblATRHRS.Text = .Parameters("@ATRHRS_OUT").Value
            frmLines.lblAvailPicks.Text = .Parameters("@TotalPIcks_out").Value
            frmLines.lblAvailFeeders.Text = .Parameters("@TotalFeeders_out").Value
            frmLines.lblAgedHours.Text = .Parameters("@OLD_HRS_OUT").Value
        End With

 

[RiverGuy]

Since you're not looping through the resultset anyways, use SQLCmd.ExecuteNonQuery() and do away with your DataReader completely. You should get the output values that way.

 

[jadams0173]

Hey RiverGuy. Thanks for the reply. I will be looping through it. Down towards the bottom is where the parameters are. The is a select at the top. The OUTPUT parms in the SP are indepenedent of the select. This worked in VB6. I had me rs and my output params. Maybe I should move the output parms to a seperate sp?

 

[RiverGuy]

You could return two resultsets in the same stored procedure. I'd do that over two separate stored procedures. But then again, there may be a way to accomplish this with the Ouput Variables that I'm not aware of.