[RESOLVED] Display Record Count in a label

[Jamie_Garland]

Hello,

How to i display how many records that i have in a label on my main form for example if i had 3 table called Cusotmers, Orders, Products

How do i get it to show the total amount of each table on label 1 label 2 label 3?

[Cimperiali]

first, search in your books or in google how to perform a query like a
"select count(*) from Cusotmers" via Ado.Net (I do believe you do not want to getinvolved with Entity Framework or the like right now, do you?)
Once you're able to peform that query and get back a result, assigning it to the text property of a label should not be an issue for you anymore.

[Jamie_Garland]

i use oledb this is my connention

Module modConnection
  
    Public conn As New System.Data.OleDb.OleDbConnection
    Public strConnString As String
 
    Public Sub connectDatabase()
        strConnString = "PROVIDER=microsoft.Jet.OleDb.4.0;Data Source=data.mdb"
        conn.ConnectionString = strConnString
        conn.Open()
    End Sub
    Public Sub DisconnectDatabase()
        If conn.State = ConnectionState.Open Then
            conn.Close()
        Else
            'continue
        End If
    End Sub
 
    Public Function getDate() As String
        Return Format(Date.Today, "Short Date")
    End Function
 
    Public Function getTime() As String
        Return Format(Date.Now, "Long Time")
    End Function
End Module

[jmcilhinney]

Do you want the actual data as well or just the count? If it's the former, do you want it to update when the user adds or deletes rows?

[jmcilhinney]

By the way, don;t do this:

    Public Function getDate() As String
        Return Format(Date.Today, "Short Date")
    End Function

    Public Function getTime() As String
        Return Format(Date.Now, "Long Time")
    End Function

For a start, you are using inconsistent naming conventions. You have some methods using camel casing and some using Pascal casing. Method names should always use Pascal casing.

Secondly, Date.Today actually uses Date.Now internally any way so there's no point using Date.Today when formatting a date string.

Finally, don't use the Format function in VB.NET. If you want a specific format then call ToString on the Date itself and pass the format string, otherwise use one of the specific methods dedicated to the four standard formats.

    Public Function GetDate() As String
        Return Date.Now.ToShortDateString()
    End Function

    Public Function GetTime() As String
        Return Date.Now.ToLongTimeString()
    End Function

[Jamie_Garland]

I only want to show the ID total for how many records and when people update the database it changes from 5 to 6 and when deletes a record from 6 - 5?.

[Jamie_Garland]

Can anyone help?.

[jmcilhinney]

Can anyone help?.

Please don't make useless posts like this. We're all around the world in different time zones. We'll get to your question if and when we can.

[jmcilhinney]

I only want to show the ID total for how many records and when people update the database it changes from 5 to 6 and when deletes a record from 6 - 5?.

This begs another question. Let's say that you and I both have your application on different machines. If I make a change to the database, are you saying that you want your instance of the app to reflect that, or are you saying atht each instance of the app only reflects changes that it makes itself?

[Jamie_Garland]

Oh i'm sorry didn't mean you bump my post.

[Jamie_Garland]

What i want it to do is when i add a new customer to my database once he has been added successfully and i close the form. I want the count to go up from 1 to 2 on frmMain like refresh the page once the data has been saved?.

So it displays something like this,

You currently have 4 customers and once record is added it then changes to you currently have 5 customers and so on?.

I'm using a Msaccess database for this project I only want it to show on my instance but if i send you the updated database it then shows on your instance also as the database has been updated?.

[jmcilhinney]

Follow the CodeBank link in my signature and check out my thread on Retrieving & Saving Data. One of the examples is for retrieving a single value. It uses the SUM function but you can use COUNT. Just execute such a query each time you want to update the Label, e.g. when the main form loads and whenever any adding or deleting is or may be done.

[Jamie_Garland]

Is it parameters one?.

[Jamie_Garland]

Hello,

I tried this code to display the count of all the records but i keep getting an error like this.

ERROR:

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

it also highlights this

dr = cmd.ExecuteReader()

Private Sub frmMain_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
 
        Dim strCnn As String = "PROVIDER=microsoft.Jet.OleDb.4.0;Data Source=Data\data.mdb"
        Dim strSql As String = "SELECT * FROM tblCustomers"
        Dim cnn As New OleDbConnection(strCnn)
        Dim cmd As New OleDbCommand(strCnn, cnn)
        Dim dr As OleDbDataReader
        cnn.Open()
        dr = cmd.ExecuteReader()
        While (dr.Read())
            Label1.Text = (dr(0).ToString())
        End While
    End Sub

[jmcilhinney]

Jamie, you're not the only one unfortunately but, seriously, did you even read what I posted? I said SPECIFICALLY to check out the example for retrieving a single value. You have not done that. If you can't read what's posted and follow simple instructions then how can you expect the correct result? That's not actually the cause of that error but you might want to start with code that at least attempts to do the right thing, which that does not. Read post #12 and do what it says.