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
MehawitchiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vadim RappCommented:
Maybe it's easier to use DatagridView, which is databound to begin with? rather than do it all in code?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vadim RappCommented:
...besides, your stored procedure could be made much simpler - instead of dynamically creating the sql and then running it by sp_executesql, you could specify your SELECT directly, with something like this:

where
ITM.RatingDate BETWEEN isnull(@DateFrom ,'1/1/1900')  and isnull(@DateTo,'12/31/3999')
0
MehawitchiAuthor Commented:
Thank you so much Vadim - This is a great tip. I will give it a try and get back to you later tonight
0
MehawitchiAuthor Commented:
It worked like charm - Thanks again Vadim
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.