none
Concatenated Fields work in SQL Query but not in the Windows Form RRS feed

  • Question

  • My stored procedure works correctly when queried in Management Studio

    ALTER PROCEDURE [dbo].[usp_Get_Log_TestInfo_Dex_Row_Id]

     

                    @SampleNo                                        int = 11131,

                    @TestId1a                                            varchar(3) = "305",

                    @strConcatenate                              varchar(8) = " "

     

    AS

    BEGIN

     

    SET NOCOUNT ON;

     

    --concatenate to one parameter @strConcatenate

    SET @strConcatenate = CONVERT(nvarchar,@SampleNo) + @TestId1a

     

    SELECT Dex_Row_Id

    FROM Analytical_Sample_Log_ResultsInfo

    WHERE LTrim(Str(SampleNo)) + TestId1a = @strConcatenate

    END

     

    Returns Dex_Row_Id value of 36090 (integer and Primary Key) which is correct

    However, when I try to Read in the Function, I find reader.Read is False

    Public Shared Function GetDex_Row_Id1(ByVal strConcatenate As String) As clsTest

     

            MsgBox(strConcatenate)         MessageBox displays 11131305 which is correct

     

            Dim test As New clsTest

            Dim cnSQL As SqlConnection = dbLIMS.GetLIMSConnection

            Dim selectCommand As New SqlCommand()

            selectCommand.Connection = cnSQL

            selectCommand.CommandText = "usp_Get_Log_TestInfo_Dex_Row_Id"

            selectCommand.CommandType = CommandType.StoredProcedure

            selectCommand.Parameters.AddWithValue("@strConcatenate", strConcatenate)

             Try

                cnSQL.Open()

                Dim reader As SqlDataReader = selectCommand.ExecuteReader(CommandBehavior.SingleRow)

                If reader.Read Then

                    test.Dex_Row_Id = reader("Dex_Row_Id").ToString

                Else

                    test = Nothing

                End If

                reader.Close()

            Catch ex As SqlException

                Throw ex

            Finally

                cnSQL.Close()

            End Try

             Return test

         End Function

    Friday, August 7, 2015 4:50 PM

Answers

  • Commenting out the green lines in the stored procedure solved the problem for me:

    ALTERPROCEDURE[dbo].[usp_Get_Log_TestInfo_Dex_Row_Id]

    --   @SampleNo int = 0,

    --   @TestId1a varchar(3) = " ",

    @strConcatenate varchar(8)="11131183"

    AS

    BEGIN

    SETNOCOUNTON;

    --   concatenate to one parameter @strConcatenate

    --   SET @strConcatenate = CONVERT(nvarchar,@SampleNo) + @TestId1a

    SELECTDex_Row_Id

    FROMAnalytical_Sample_Log_ResultsInfo

    WHERELTrim(Str(SampleNo))+TestId1a =@strConcatenate

    END

    • Marked as answer by LHendren Friday, August 7, 2015 6:19 PM
    Friday, August 7, 2015 6:19 PM