"Steve_at_BF" <Steve_at_BF@ newsgroups.nospam> wrote in message
news:97B57CE2-EB4E-4B68-88B2-5E17D20A246F@microsoft.com...
> Using Visual Studio 2005, SQL Server 2005
>
> It once worked but now in the code I get no value returned for the output
> parameter @Msg (I get an empty string).  However in query analyzer I get
> the
> right value.
>
> Stored proc:
> ALTER PROCEDURE [dbo].[GetBad_SCS_AllocationUp] @inSCS_ID INT, @inSNet_ID
> INT, @Msg nvarCHAR (23) output
> AS
>
> SET NOCOUNT ON
> Declare @Cnt INT
> Declare @Finished BIT
> Declare @tmp TABLE (LineNumber1 INT, LineNumber2 INT)
> Declare @tmp1 TABLE (SCS_ID_Upstream INT)
> Set @Finished=0
> Set @Msg='OK'
> CREATE TABLE #A (
> [SCS_ID_Downstream] [int],
> [SKU_ID_NeededFromUpstream] [int],
> [SCS_ID_Upstream] [int],
> [AllocationPercentUpstream] [int]
> )
> INSERT #A
> SELECT ...
> Set @Cnt=(Select count(*) from @tmp)
> IF @cnt > 0
>  BEGIN
>    Set @Finished=1
>    set @Msg='Duplicates'
>    SELECT LineNumber1,LineNumber2 from @tmp
>  END
> drop table #x
> (more similar code)
> **********************
> code:
>
> objDataReaderSQLNew = New DataReaderSQLNew
>
> objDataReaderSQLNew.CreateNewSQLcommand("GetBad_SCS_AllocationUp")
>            With objDataReaderSQLNew.PublicCmdSelect
>                '.CommandTimeout = 20 'seconds
>                .Parameters.Add("@inSCS_ID", SqlDbType.Int)
>                .Parameters(0).Value = lclSCS_ID
>                .Parameters.Add("@inSNet_ID", SqlDbType.Int)
>                .Parameters(1).Value = lclSnet_ID
>                .Parameters.Add("@Msg", SqlDbType.NVarChar, 23)
>                .Parameters(2).Direction = ParameterDirection.Output
>                '
>                Try
>                    Call
> objDataReaderSQLNew.CreateInternalSQLDataReaderFromCmd()
>                Catch ex As Exception
>                    objDataReaderSQLNew.closeDatareaderAndConnection()
>                    Throw New System.SystemException("failed")
>                End Try
>                lclErrMsg = Convert.ToString(.Parameters(2).Value)
>            End With
> ************************
> in query analyzer this gives the right value:
> DECLARE @Msg NVARCHAR (23)
> exec GetBad_SCS_AllocationUp 11, 2, @Msg output
> SELECT @Msg as msg
> --
> Steve

"Dan Guzman" wrote:

> The output parameter values won't be available until all stored procedure
> results are read.  Assuming your CreateInternalSQLDataReaderFromCmd method
> invokes ExecuteReader, you'll need to first process the returned reader
> object.
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "Steve_at_BF" <Steve_at_BF@ newsgroups.nospam> wrote in message
> news:97B57CE2-EB4E-4B68-88B2-5E17D20A246F@microsoft.com...
> > Using Visual Studio 2005, SQL Server 2005
> >
> > It once worked but now in the code I get no value returned for the output
> > parameter @Msg (I get an empty string).  However in query analyzer I get
> > the
> > right value.
> >
> > Stored proc:
> > ALTER PROCEDURE [dbo].[GetBad_SCS_AllocationUp] @inSCS_ID INT, @inSNet_ID
> > INT, @Msg nvarCHAR (23) output
> > AS
> >
> > SET NOCOUNT ON
> > Declare @Cnt INT
> > Declare @Finished BIT
> > Declare @tmp TABLE (LineNumber1 INT, LineNumber2 INT)
> > Declare @tmp1 TABLE (SCS_ID_Upstream INT)
> > Set @Finished=0
> > Set @Msg='OK'
> > CREATE TABLE #A (
> > [SCS_ID_Downstream] [int],
> > [SKU_ID_NeededFromUpstream] [int],
> > [SCS_ID_Upstream] [int],
> > [AllocationPercentUpstream] [int]
> > )
> > INSERT #A
> > SELECT ...
> > Set @Cnt=(Select count(*) from @tmp)
> > IF @cnt > 0
> >  BEGIN
> >    Set @Finished=1
> >    set @Msg='Duplicates'
> >    SELECT LineNumber1,LineNumber2 from @tmp
> >  END
> > drop table #x
> > (more similar code)
> > **********************
> > code:
> >
> > objDataReaderSQLNew = New DataReaderSQLNew
> >
> > objDataReaderSQLNew.CreateNewSQLcommand("GetBad_SCS_AllocationUp")
> >            With objDataReaderSQLNew.PublicCmdSelect
> >                '.CommandTimeout = 20 'seconds
> >                .Parameters.Add("@inSCS_ID", SqlDbType.Int)
> >                .Parameters(0).Value = lclSCS_ID
> >                .Parameters.Add("@inSNet_ID", SqlDbType.Int)
> >                .Parameters(1).Value = lclSnet_ID
> >                .Parameters.Add("@Msg", SqlDbType.NVarChar, 23)
> >                .Parameters(2).Direction = ParameterDirection.Output
> >                '
> >                Try
> >                    Call
> > objDataReaderSQLNew.CreateInternalSQLDataReaderFromCmd()
> >                Catch ex As Exception
> >                    objDataReaderSQLNew.closeDatareaderAndConnection()
> >                    Throw New System.SystemException("failed")
> >                End Try
> >                lclErrMsg = Convert.ToString(.Parameters(2).Value)
> >            End With
> > ************************
> > in query analyzer this gives the right value:
> > DECLARE @Msg NVARCHAR (23)
> > exec GetBad_SCS_AllocationUp 11, 2, @Msg output
> > SELECT @Msg as msg
> > --
> > Steve
>
>
>