count of rows in MS access database in vb.net

0
votes

does anyone know what i do bad? I am trying to count number of rows in MS access Database

Here is code which i tried:

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

    Dim conn As New OleDbConnection
    conn.ConnectionString = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=F:\Test Database\Database.accdb")
    conn.Open()
    Dim strsql As String
    strsql = "Select count(*) from TABLA"             '" select Panel from PANELS where ID"
    Dim cmd As New OleDbCommand(strsql, conn)
    Dim myreader As OleDbDataReader

    myreader = cmd.ExecuteReader
    myreader.Read()
    PanelsInDatabase = myreader.Item(strsql)
    Label1.Text = PanelsInDatabase
    conn.Close()

    For i As Integer = 0 To PanelsInDatabase - 1
        CreatePanels()
        CreateDeleteButton(_PanelName)
        CreateLabels(_PanelName)
        CreateLabel2(_PanelName)
    Next

End Sub

Thank you in advance

(if i start code i get an error: System.IndexOutOFRangeException)

2
vb.netms-access-2010
As suggested, you should be calling ExecuteScalar if you want to get a single value. That said, you could call ExecuteReader (ExecuteScalar does internally) if you write code that makes sense but yours just doesn't. Where have you ever seen an example of indexing a data reader using a String containing SQL code? You haven't. Every example using the numeric index of a column so why would you expect it to work any other way? - jmcilhinney
@jmcilhinney Item does take a string these days, but you're right in that it'd be an odd sql that caused it to take an sql.. How about SELECT 1 AS "SELECT 1 AS x FROM t" FROM t ? :) - Caius Jard

2 Answers

1
votes

Use ExecuteScalar when you are selecting a single value

    Dim connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=F:\Test Database\Database.accdb"
    Dim sql = "select count(*) from tabla"        

    Using cmd As New OleDbCommand(sql, New OleDbConnection(connStr))
      cmd.Connection.Open()
      Dim ct = CInt(cmd.ExecuteScalar())

    End Using
1
votes

I have separated you user interface code from your database code. Of course, I don't know what CreatePanels is doing or where _PanelName is coming from. In your UI code you call the GetTABLACount function which returns as Integer.

In the database code use Using...End Using blocks for the connection and command so they are properly disposed even if there is an error.

Since you are only retrieving a single piece of data, you can use .ExecuteScalar which returns the first column of the first row of the result set As Object. Use CInt to get the Integer.

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    Dim count = GetTABLACount()
    Label1.Text = count.ToString
    For i As Integer = 0 To count - 1
        CreatePanels()
        CreateDeleteButton(_PanelName)
        CreateLabels(_PanelName)
        CreateLabel2(_PanelName)
    Next
End Sub

Private Function GetTABLACount() As Integer
    Dim dbCount As Integer
    Using conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=F:\Test Database\Database.accdb"),
            cmd As New OleDbCommand("Select count(*) from TABLA", conn)
        conn.Open()
        dbCount = CInt(cmd.ExecuteScalar)
    End Using
    Return dbCount
End Function