Progress Bar count users + Timer

[luckydead]

Hello,
Can someone guide me or help me , because i cannot find any information on google about this.

I'm trying to find a way how to make this:
- Timer - that will reload progress bar after lets say 5min , name: Timer1
- Progress bar - that will count how much users have , name: ProgressBarShow

I use ms access as database

and my sql is like this :

Public ServerStatus As String = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Path & File & ";")
    Public conn As New OleDbConnection(ServerStatus)
    Public cmd As New OleDbCommand

Public Sub ConnectDB() 'Check for DB Status
        If conn IsNot Nothing Then
            conn.Close()
        End If
        Try
            conn.Open()
            MsgBox("Connected")
        Catch ex As Exception
            MsgBox("Database Error:[" & ex.Message & "]")
        End Try
    End Sub

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

Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
Try
                With cmd
                    conn.Open()
                    .Connection = conn
                    .CommandText = "Select Count(*) From Accounts order by ID"
                    'How to create it after i cant find information

                    ProgressBarShow.value += 1  ' I think this is correct fo increase number
End With
            Catch ex As Exception
                MsgBox(ex.Message)
            Finally
                cmd.Dispose()
                If conn IsNot Nothing Then
                    conn.Close()
                End If
            End Try
        End If
End Sub

[jmcilhinney]

Your code makes little sense and you haven't provided an actual explanation of what it's supposed to do. In your Tick event handler, you set the CommandText of a command to the same value each time, so why not just set it once and be done with it? You also don't bother to execute that command, so what's the point of it? If you want to retrieve data from a database then you should have done some research on how to retrieve data from a database. There are examples and explanations of that all over the place and every one of them would have included executing the command.

If you want to get a single value from a database, as appears to be the case here, then you should be calling ExecuteScalar on your command. ExecuteScalar exists specifically to get the value from the first column of the first row of a result set and is the obvious choice if you are getting the result of an aggregate function like COUNT. By the way, what's the point of your ORDER BY clause? How exactly will the order affect the count?

Also, I don't see why you would be using a ProgressBar in this case. What exactly are you measuring the progress of?

[luckydead]

i just want to present in circle progress bar the total amount of users in database is the idea,
Thank you for the information about executescalar. so it will be something like that ? :

Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
Try
                With cmd
                    conn.Open()
                    .Connection = conn
                    .CommandText = "Select Count(*) From Accounts order by ID"
                    'How to create it after i cant find information
                    Dim Resulta = cmd.ExecuteScalar()
                    If Convert.ToInt32(Resulta) > 0 Then
                    ProgressBarShow.value += Resulta  ' I think this is correct fo increase number
                    Else
                        cmd.Dispose()
                        If conn IsNot Nothing Then
                            conn.Close()
                        End If
                    Return
                    End If
End With
            Catch ex As Exception
                MsgBox(ex.Message)
            Finally
                cmd.Dispose()
                If conn IsNot Nothing Then
                    conn.Close()
                End If
            End Try
        End If
End Sub

[jmcilhinney]

Did you test that code? Did it work? If yes and yes then you have your answer. If you haven't tested the code, why haven't you? If you did test it and it didn't work then why aren't you telling us that and explaining what did happen? I still don't see how a ProgressBar is appropriate in this situation but you can use what you want. The actual issue you were struggling with was the fact that you weren't querying the database and now you know how to do that.

[luckydead]

Yes i had tested it now, and i receive error message "the connection was not closed" msgbox i think it comes from :
Catch ex As Exception
MsgBox(ex.Message)

is it possible my login function to cause this error?

Public Sub Loggin()
        If Login.txtUsername.Text = "" Or Login.txtPassword.Text = "" Then
            MsgBox("Please enter Username and Password!")
            Login.txtUsername.Focus()
        Else
            Try
                With cmd
                    conn.Open()
                    .Connection = conn
                    .CommandText = "Select * From Accounts WHERE Accname = @GetLogin and Accpass = @GetPass"
                    .Parameters.AddWithValue("@GetLogin", Login.txtUsername.Text)
                    If Encr >= 1 Then
                        .Parameters.AddWithValue("@GetPass", Encrypt.EncryptPassword(Login.txtPassword.Text))
                    ElseIf Encr = 0 Then
                        .Parameters.AddWithValue("@GetPass", Login.txtPassword.Text)
                    End If

                    'Starting The Query
                    Adapter.SelectCommand = cmd
                    Reader = cmd.ExecuteReader
                    .Parameters.Clear()
                    'Validate User
                    If Reader.HasRows = 0 Then
                        Login.txtUsername.Text = ""
                        Login.txtPassword.Text = ""
                        Login.txtUsername.Focus()
                        Return
                    Else
                        Dim authorityid = 0
                        While Reader.Read()
                            authorityid = Reader.Item("Admin")
                        End While
                        If authorityid = 1 Then
                            'Login Admin User
                            AdminMenu.Show()
                            Login.Close()
                        ElseIf authorityid = 0 Then
                            'Login Normal User
                            MainCP.Show()
                            Login.Close()
                        ElseIf authorityid > 1 Then
                            MessageBox.Show("Error in Database User Information", "Visual WMS", MessageBoxButtons.OK, MessageBoxIcon.Warning)
                            Return
                        Else
                            Login.txtUsername.Text = ""
                            Login.txtPassword.Text = ""
                            Login.txtUsername.Focus()
                        End If
                    End If
                End With
            Catch ex As Exception
                MsgBox(ex.Message)
            Finally
                Reader.Close()
                Adapter.Dispose()
                cmd.Dispose()
                If conn IsNot Nothing Then
                    conn.Close()
                End If
            End Try
        End If
    End Sub

Also i changed it not to use Timer now i try create it like this:

Private Sub DisplayUsers()
        Try
            With cmd
                conn.Open()
                .Connection = conn
                .CommandText = "Select Count(*) From Accounts order by ID"
                'How to create it after i cant find information
                Dim Resulta = cmd.ExecuteScalar()
                If Convert.ToInt32(Resulta) > 0 Then
                    Guna2CircleProgressBar1.Value += Resulta  ' I think this is correct fo increase number
                Else
                    cmd.Dispose()
                    If conn IsNot Nothing Then
                        conn.Close()
                    End If
                    Return
                End If
            End With
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            cmd.Dispose()
            If conn IsNot Nothing Then
                conn.Close()
            End If
        End Try
    End Sub

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

I try check login function, seems everything is closing, cant figure it out when MainCP form is opened why gives "Connection is not closed" error message seems something is not correct

[jmcilhinney]

You're making things far more complicated than they need to be. When using ADO.NET, unless you have a good reason to do otherwise, you should create, use and destroy your data access objects where you need them. I would suggest that you follow the CodeBank link in my signature below and check out my thread on Retrieving & Saving Data but, in the case of a call to ExecuteScalar, I'd suggest this sort of thing:

Using connection As New OleDbConnection(connectionString),
      command As New OleDbCommand("SELECT COUNT(*) FROM Accounts", connection)
    Try
        connection.Open()
 
        Dim accountCount = CInt(command.ExecuteScalar())
 
        'Use accountCount here.
    Catch ex As Exception
        'Act on exception here.
    End Try
End Using

There can be no issues with connections being open or not being open if you don't keep using the same connection and possibly doing so incorrectly. ADO.NET is designed such that connections are lightweight and they are supposed to be used and discarded this way. By creating it with a Using statement, you alleviate the need to explicitly close/dispose the object when you're done with it.

[luckydead]

i try with your method and shows me "Conversion from string "Select COUNT(*) from Accounts" to type integer is not valid

[jmcilhinney]

No, you didn't.

[luckydead]

with your total copy it works, but i try to figure out why from my point was not working and giving this error "Conversion from string "Select COUNT(*) from Accounts" to type integer is not valid.
I want to find why this happends, what is exactly the problem so i can uderstand a little

[jmcilhinney]

How about you post the code you used and then we can see exactly what the problem is? Do you think we're psychic or something? If you code doesn't work then your code is wrong. If you keep that code a secret then how do you expect us to know what's wrong with it? This is just common sense. My guess would be that you used the CommandText property where you should have used the ExecuteScalar method, because that would result in that error message. I shouldn't have to guess though. Just post the code, as you should have done in the first place, and I can see for myself without guessing.

[luckydead]

Private Sub DisplayUsers()
        Try
            With cmd
                conn.Open()
                .Connection = conn
                .CommandText = "SELECT COUNT(*) FROM Accounts"
                'How to create it after i cant find information
                Dim accountCount = CInt(cmd.ExecuteScalar())
                Guna2CircleProgressBar1.Value += accountCount .ToString()
            End With
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            cmd.Dispose()
            If conn IsNot Nothing Then
                conn.Close()
            End If
        End Try
    End Sub

[si_the_geek]

                Guna2CircleProgressBar1.Value += accountCount .ToString()

That .ToString does not belong there at all, and it is causing the issue.

The .Value property wants a number, and accountCount is a number... you shouldn't be converting anything to/from String.

[luckydead]

even if i remove it is the same error