Reading dates in a spreadsheet to and from a DataSet

Question

Hi!

My application uses a badly arranged Excel spreadsheet as a data source. I say badly designed because there's no unique identifier to each row aside from the column where dates and times are found.

Long story short, my app uses the date and time on each row as an identifier to retrieve information from the other columns in the row. The idea is that the user will select a date/time from a ListBox at which time, the app will loop through the DataSet and find the date/time in the right column and display the rest of the info in that row.

My issue is that the ListBox control isn't being populated and I can't see why...

Here's a sample row from the spreadsheet: (Each | character represents a cell border)

Team - FNB | O | 2010/02/18 08:59:24 | 5034 | Frederico Turnbridge | 27839963586 | SA - MOBILE - (ZA) | | 69 | O_NORMAL | | 00:01:06 |R 2.83

Here's my code:

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim j As Integer = 0
    If TextBox1.Text.Length = 4 Then
        For i As Integer = 0 To CallData.Tables(0).Rows.Count - 1
            If CallData.Tables(0).Rows(i)(3).ToString = TextBox1.Text _
            And CallData.Tables(0).Rows(i)(2).ToString > DateTimePicker1.ToString _
            And CallData.Tables(0).Rows(i)(2).ToString < DateTimePicker2.ToString Then
                ListBox1.BeginUpdate()
                ListBox1.Items.Add(CallData.Tables(0).Rows(i)(2).ToString)
                ListBox1.EndUpdate()
                j = j + 1
            End If
        Next

        Label1.Text = j & " records found."
    End If
End Sub

This basically works on a search where the user will specify a term to search for (be it a telephone number or (in this case) a telephone extension) and a date range. Now, I know for a fact that the spreadsheet (and thus, the DataSet) has information where the date column - column index 2 - falls within the date range I'm specifying and the column after that contains my search term, but I'm not getting any results.

Any ideas?

Answer 1

The issue may be in your date comparison.

CallData.Tables(0).Rows(i)(2).ToString > DateTimePicker1.ToString

This code compares two String values, not two dates. If the format is anything other than yyyy/mm/dd, the comparison won't have the desired result.

Beyond the comparison issue, I'd suggest using ADO.NET to retrieve data from the Excel file rather than your current approach.

Answer 2

OK, serious Newbie error here.

I went back and tried MsgBox(DateTimePicker1.ToString) and the result was not what I thought I was getting.

What I should've done was:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim j As Integer = 0
    If TextBox1.Text.Length = 4 Then
        For i As Integer = 0 To CallData.Tables(0).Rows.Count - 1
            If CallData.Tables(0).Rows(i)(3).ToString = TextBox1.Text _
            And CallData.Tables(0).Rows(i)(2).ToString > DateTimePicker1.Value.ToString _
            And CallData.Tables(0).Rows(i)(2).ToString < DateTimePicker2.Value.ToString Then
                ListBox1.BeginUpdate()
                ListBox1.Items.Add(CallData.Tables(0).Rows(i)(2).ToString)
                ListBox1.EndUpdate()
                j = j + 1
            End If
        Next

        Label1.Text = j & " records found."
    End If
End Sub

Changing DateTimePicker1.ToString to DateTimePicker1.Value.ToString solves the problem.