CODEWITHSUNDEEP
vb.netvisual-studio-2010visual-studio
user1532468
user1532468

Reputation: 1753

Record count varies from query

I am struggling to see why my record count is varying from my query statement. Now being a new user, it is something I have obviously not coded correctly, but cannot see it. If I run my query in access it returns the correct number of records, but in vb.Net, it returns a completely different figure.

For example, In my code 

Sub filllistview()
    Try
        'creatconn()
        oledbCnn.ConnectionString = My.Settings.storageConnectionString
        oledbCnn.Open()
        'Dim oledbCmd As OleDbCommand = New OleDbCommand("Select TOP 100 * from Postings WHERE Customer = '" & newvar & "' ORDER BY Date DESC", oledbCnn)
        'dr = oledbCmd.ExecuteReader()

        'drcount = Convert.ToInt32(dr("RowCount"))
        Dim oledbCmd As OleDbCommand = New OleDbCommand("Select TOP 100 * from Postings " & _
                           "WHERE Customer = ? ORDER BY Date DESC", oledbCnn)
        oledbCmd.Parameters.AddWithValue("@p1", newvar)
        Using dr = oledbCmd.ExecuteReader()


            'clear items in the list before populating with new values
            ListView1.Items.Clear()

            While dr.Read()

                ListView1.Items.Add(CDate(dr(4)).ToShortDateString()).UseItemStyleForSubItems = False
                ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(dr(1).ToString())
                ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(dr(11).ToString())
                ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(dr(7).ToString())
                With ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(CDbl(dr(5)).ToString("C"))
                    If CDbl(dr(5)) < 0 Then
                        .ForeColor = Color.Red
                        .BackColor = Color.Gainsboro
                        '.Font = New Font(Font.FontFamily, Font.Size, FontStyle.Bold)
                    Else
                        .ForeColor = tmpColor
                    End If
                End With
                ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(dr(14).ToString())
                ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(dr(3).ToString())
                count += 1

            End While
        End Using
        MessageBox.Show(CStr(ListView1.Items.Count - 1))
        MessageBox.Show(count.ToString)

        'If (count < 100) Then
        '    MessageBox.Show("less than 100")

        'Else
        '    MessageBox.Show(count.ToString)
        'End If

        'autosize each column in listview
        For Each col As ColumnHeader In ListView1.Columns
            col.Width = -2
        Next col

        'refresh the list with new data
        ListView1.Refresh()

        'Enable the posting previous button
        'btnNextPostings.Enabled = True

    Catch ex As Exception
        MessageBox.Show(ex.Message)
    Finally
        'dr.Close()
        oledbCnn.Close()
    End Try

    'btnNextPostings.Enabled = ListView1.Items.Count > 100

    gbxPostings.Text = "Client Postings for: " & "( " & newvar & " )" & " Total Records: " & count

    If (ListView1.Items.Count < 100) Then
        btnNextPostings.Enabled = False
        btnNextPostings.Text = "No data to show"
    Else
        btnNextPostings.Enabled = True
        btnNextPostings.Text = "Next " & CStr(ListView1.Items.Count - 1) & " Postings"
    End If

End Sub

Where have I gone wrong. Many thanks

Upvotes: 0

Views: 307

Answers (1)

Steve
Steve

Reputation: 216313

It seems that you forget to call the ExecuteReader on the command, so, if the reader instance has already been initialized by a previous command you are looping on the content previously loaded.

Dim oledbCmd As OleDbCommand = New OleDbCommand("Select TOP 100 * from Postings " & _
                               "WHERE Customer = ? ORDER BY [Date] DESC", oledbCnn)
oledbCmd.Parameters.AddWithValue("@p1", newvar)
Using OleDbDataReader dr = oledbCmd.ExecuteReader()
   While dr.Read()
      ....
   End While
End Using

I have also changed your command text from string concatenation to a parameterized query. It is important to use parameterized queries to avoid sql injections and parsing problems. Another change is to Using Statement around the disposable objects (the most important one is the connection but in your code is not clear how you initialize it). Finally the word DATE is a reserved keyword with Acces Jet Engine used by the OleDbProvider when you connect to Access db. You need to put square brackets around this word.

Of course, if you want an exact count of the items inside the ListView you should remove (clear) the previous content with a call to ListView.Items.Clear() before starting the reading loop and to be sure to have to count global variable correctly initialized to zero.

Upvotes: 1

Related Questions