CODEWITHSUNDEEP
vb.netms-access-2010
Lasicak
Lasicak

Reputation: 19

count of rows in MS access database in vb.net

Does anyone know what is wrong?
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

if I start code, I get an error:

System.IndexOutOFRangeException

Upvotes: 0

Views: 1442

Answers (2)

Mary
Mary

Reputation: 15101

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

Upvotes: 1

Caius Jard
Caius Jard

Reputation: 74730

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

Upvotes: 1

Related Questions