CODEWITHSUNDEEP
sqldatabasevb.net
Samuel Rose
Samuel Rose

Reputation: 17

Working out how many records are in a database or dataset in vb

I am currently working on a project where I need to know how many records are in a single table in a database as the number of records changes dependent on inputs made by the user. Is there any way to program a function that will work out the number of records is my database?

Here is the code I have at current which isn't working. 

    dbProvider = "PROVIDER=Microsoft.JET.OLEDB.4.0;"
    TheDatabase = "/TeachersData.mdb"
    FullDatabasePath = CurDir() & TheDatabase
    dbSource = "Data Source = " & FullDatabasePath

    con.ConnectionString = dbProvider & dbSource

    con.Open()
    NoOfRecords = "SELECT COUNT(*) FROM Teachers"
    sql = "SELECT * FROM Teachers"

    RecordNumbers = New OleDbDataAdapter(NoOfRecords, con)
    da = New OleDbDataAdapter(Sql, con)
    da.Fill(ds, "TeachersData")

    con.Close()
End Sub

Sub VerifyDetails()
    For i = 0 To Len(RecordNumbers)
        If Initials.Text = ds.Tables("TeachersData").Rows(i).Item(1) Then
            Salt = ds.Tables("TeachersData").Rows(i).Item(3)
        End If
    Next

Upvotes: 0

Views: 59

Answers (1)

Steve
Steve

Reputation: 216303

You are making some confusion with two OleDbDataAdapters. You prepare one with the correct query (RecordNumbers) but then you fill another one (da).
But you really don't need any OleDbDataAdapter for this. Just an OleDbCommand to execute

con.Open()
Dim cmd = new OleDbCommand("SELECT COUNT(*) FROM Teachers", con)
Dim count = Convert.ToInt32(cmd.ExecuteScalar())

Now count contains the number of records present in the Teachers table.

Upvotes: 0

Related Questions