Unable to Retrieve Data from a Parameterized Stored Procedure into a ListView in a Windows Application

Hello Experts,

I've been struggling all day yesterday trying to retrieve data from a SQL database using parameterized stored procedure in ADO.net/VB.net  Windows application.

Basically, I'm trying to load a ListView with data retrieved from SQL table. The user is presented with a window form which has List view, two datetimepickers for DateFrom and DateTo parameters. After selecting the From/To dates, the user clicks on a button to retrieve the data, and this is where I'm stuck

The below Stored Procedure is working fine when I run it in Management Studio. However, when I attempt to run it from the application (Please see vb script below), I'm able to retrieve the field list, but I'm not getting any data.

--Stored Procedure code

ALTER PROC [dbo].[spGetMonthlyDataImport]

      (
      
            @DateFrom nvarchar(12) = NULL,
            @DateTo nvarchar(12) = NULL,
            @RowsOut Integer OUTPUT
      )
AS
DECLARE @Select nvarchar(MAX) = ''
DECLARE @DataFilter nvarchar(500) =''
DECLARE @SQL nvarchar(MAX) =''

-- Build the Filter Clause

IF @DateFrom IS NOT NULL AND @DateTo IS NOT NULL
BEGIN
SET @DataFilter = @DataFilter +
      '(ITM.RatingDate BETWEEN ''' + @DateFrom + ''' and ''' + @DateTo + ''')'
END


IF LEN(@DataFilter) > 5 BEGIN SET @DataFilter =  ' WHERE ' + @DataFilter END

SET @Select = @Select + 'SELECT FTM.FilesTrackerID, convert(date,FTM.ImportDate, 102 ) AS ImportDate, '
SET @Select = @Select + '(Left(DATENAME(MM,ITM.RatingDate),3) + ' + '''-''' + ' + DATENAME(YY,ITM.RatingDate)) AS WavePeriod, '
SET @Select = @Select + '(S.Name + ' + '''/''' + '+ M.MarketCode + ' + '''/''' + '+ Aud.AudProfileCode + ' + '''/''' + ' + ITM.SheetDay) AS WaveDetails '
SET @Select = @Select + 'FROM Wave.FilesTrackerMonthly AS FTM INNER JOIN '
SET @Select = @Select + 'Wave.ImportTrackerMonthly AS ITM ON FTM.FilesTrackerID = ITM.FilesTrackerID INNER JOIN '
SET @Select = @Select + 'Wave.Supplier AS S ON ITM.SupplierID = S.SupplierID INNER JOIN '
SET @Select = @Select + 'Wave.Market AS M ON ITM.MarketID = M.MarketID INNER JOIN '
SET @Select = @Select + 'Wave.AudProfile AS Aud ON ITM.AudProfileID = Aud.AudProfileID '

SET @SQL = @Select + @DataFilter + ' ORDER BY FTM.ImportDate DESC'

      Print @SQL

      EXEC sp_executesql  @SQL

set @RowsOut = @@Rowcount
return (5)

-- End of Stored Procedure code


Below is the vb.net script I'm using to retrieve the data

   Private Sub PopulateFilesTrackerListView()
        ' This procedure populates FilesTracker ListView


        Try
Dim cnSQL As SqlConnection
    Dim cmSQL As SqlCommand
    Dim drSQL As SqlDataReader
cnSQL = New SqlConnection(cnString) ' cnString is a shared variable for the connection string and is already defined elsewhere



            cnSQL.Open()
            cmSQL = New SqlCommand("spGetMonthlyDataImport", cnSQL)


            cmSQL.CommandType = CommandType.StoredProcedure

            Dim RetValue As SqlParameter = cmSQL.Parameters.Add("RetValue", SqlDbType.Int)
            RetValue.Direction = ParameterDirection.ReturnValue

            'Create and assign value to "DateFrom" parameter
            Dim prmDataFrom As SqlParameter = cmSQL.Parameters.Add("@DateFrom", SqlDbType.NVarChar)
            prmDataFrom.Direction = ParameterDirection.Input

            'Assign Null value if no date is entered
            If DateFrom = "" Then
                prmDataFrom.Value = DBNull.Value
            Else
                prmDataFrom.Value = DateFrom
            End If


            'Create and assign value to "DateTo" parameter
            Dim prmDataTo As SqlParameter = cmSQL.Parameters.Add("@DateTo", SqlDbType.NVarChar)
            prmDataTo.Direction = ParameterDirection.Input

            'Assign Null value if no date is entered
            If DateTo = "" Then
                prmDataTo.Value = DBNull.Value
            Else
                prmDataTo.Value = DateTo
            End If

            Dim RowsOut As SqlParameter = cmSQL.Parameters.Add("@RowsOut", SqlDbType.Int)
            RowsOut.Direction = ParameterDirection.Output


            drSQL = cmSQL.ExecuteReader()
            lvMonthlyData.Clear()


            'Adding the needed columns to the ListView
            'Using VB .Net and an ADO.Net SqlDataReader named drSQL

            Dim shtFieldCntr As Short
            Dim lvColumn As ColumnHeader

            For shtFieldCntr = 0 To drSQL.FieldCount() - 1
                'Create Column Header
                lvColumn = New ColumnHeader()
                'Set its text to the name of the field
                lvColumn.Text = drSQL.GetName(shtFieldCntr)
                'add Column to ListView
                lvMonthlyData.Columns.Add(lvColumn)
            Next

            lvColumn = Nothing

            MsgBox(CStr(RowsOut.Value))

            'Adding the ListItems to the ListView with the data
            'Using VB .Net and an ADO.Net SqlDataReader named drSQL
            Dim itmListItem As ListViewItem

            Do While drSQL.Read
                itmListItem = New ListViewItem()

                If drSQL.IsDBNull(drSQL(0)) Then
                    itmListItem.Text = ""
                Else
                    itmListItem.Text = drSQL(0)
                End If

                For shtFieldCntr = 1 To drSQL.FieldCount() - 1
                    If drSQL.IsDBNull(shtFieldCntr) Then
                        itmListItem.SubItems.Add("")
                    Else
                        itmListItem.SubItems.Add(drSQL.GetString(shtFieldCntr))
                    End If
                Next shtFieldCntr

                lvMonthlyData.Items.Add(itmListItem)
            Loop
            'End Example Code


            drSQL.Close()
            cnSQL.Close()

            Me.lblCount.Text = "Count: " & lvMonthlyData.Items.Count



        Catch e As SqlException
            MsgBox(e.Message, MsgBoxStyle.Critical, "SQL Error")

        Catch e As Exception
            MsgBox(e.Message, MsgBoxStyle.Critical, "General Error")
        End Try
    End Sub

' End of VB.net code

Please note that both "DateFrom"  and "DateTo" are of String type and are assigned values from the DateTimePickers on the form itself:

DateFrom = CStr(FromDate.Value)
DateTo = CStr(ToDate.Value)



Many thanks for your help

Thank you