count of rows in MS access database in vb.net

Question

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

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, Commented Jan 31, 2021 at 1:42
@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
– Caius Jard, Commented Jan 31, 2021 at 22:05

Mary · Accepted Answer · 2021-02-01 00:58:34Z

1

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

answered Feb 1, 2021 at 0:58

Mary

15.1k3 gold badges20 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lasicak Over a year ago

Thank you very much. You solved my problem.

Mary Over a year ago

@Lasicak The best way to thank someone on Stack Overflow is to accept their answer by clicking the check mark (tick mark) to the left of the answer.

Caius Jard · Accepted Answer · 2021-01-30 21:55:13Z

1

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

answered Jan 30, 2021 at 21:55

Caius Jard

75k6 gold badges61 silver badges95 bronze badges

Collectives™ on Stack Overflow

count of rows in MS access database in vb.net

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related