[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 252
  • Last Modified:

Visual Basic MySql get data.

Hello Experts,

 Is there away to modify my code to get all the active online users. But instead of displaying all the names I would like it to goto a label instead with just a count of online users. Thanks a ton, and hope to get this working.


The following code works to display the user in a listbox, but I can't figure out how to change it to do what I would like to acheive. Been up for 27hrs now so going to rest a bit to help clear the mind also.

conn = New MySqlConnection("server= ;Port= ; user id= ; password= ; database= ")
        Dim username As Boolean = True
        Dim sqlquery As String = "SELECT * FROM accounts WHERE online = 'Online'"
        Dim data As MySqlDataReader
        Dim adapter As New MySqlDataAdapter
        Dim command As New MySqlCommand
        conn.Open()
        command.CommandText = sqlquery
        command.Connection = conn
        adapter.SelectCommand = command
        data = command.ExecuteReader
        While data.Read()
           UsersOnline.Items.Add(data(1).ToString)
        End While

        data.Close()
        conn.Close()

Open in new window

0
Jack_Jones
Asked:
Jack_Jones
  • 7
  • 4
  • 3
  • +1
1 Solution
 
CodeCruiserCommented:
You can change the code as below



conn = New MySqlConnection("server= ;Port= ; user id= ; password= ; database= ")
Dim username As Boolean = True
Dim sqlquery As String = "SELECT Count(*) FROM accounts WHERE online = 'Online'"
Dim command As New MySqlCommand
conn.Open()
command.CommandText = sqlquery
command.Connection = conn
lblUserCount.Text = command.ExecuteScalar()
data.Close()
conn.Close()

Open in new window

0
 
Jack_JonesAuthor Commented:
Just sits no errors or anything but don't update the usersonline.txt using the:

usersonline.text = command.ExecuteScalar()

Removed the data.close() because it wasn't being used just gave a green line under it.
0
 
Mohamed AbowardaSoftware EngineerCommented:
You can use DataSet and count how many row in the DataSet according to the SQL query results:

Using DataSet:
http://vb.net-informations.com/dataset/dataset-oledb.htm

Count the Rows in a Dataset:
http://www.123aspx.com/ReadReviews.aspx?res=2826
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
CodeCruiserCommented:
Are you sure there are rows for the given condition?
0
 
Jack_JonesAuthor Commented:
yes there are rows, that would be if there are 30 accounts out of 60 accounts online, it should grab that online = 30 correct?
0
 
team2005Commented:
Hi!

Try this code:

Hope that helps
Dim antrec As Int64
  Dim strsql as string = "SELECT Count(*) FROM accounts WHERE online = 'Online';" 
  
  antrec = GETQUERYRES_B(sqlstr)
  
  lblUserCount.Text = str(antrec)


 Public Function GETQUERYRES_B(ByRef strsql As String) As Object
        Dim cn As System.Data.Odbc.OdbcConnection
        Dim cmd As System.Data.Odbc.OdbcCommand
        Try
            cn = New System.Data.Odbc.OdbcConnection(hmsTILPconnstr)
            cn.Open()
            cmd = New System.Data.Odbc.OdbcCommand(strsql, cn)
            GETQUERYRES_B = cmd.ExecuteScalar
            cn.Close()
        Catch ex As System.Exception
            MsgBox("Error " & ex.Message)
            GETQUERYRES_B = ""
        End Try
    End Function

Open in new window

0
 
Jack_JonesAuthor Commented:
team2005, that would work if i were using odbc, but im using a different method to connect.
0
 
Jack_JonesAuthor Commented:
only error is counterval is not declared.
0
 
team2005Commented:
Hi!

Sorry,...

Try

Dim int counterval = command.ExecuteScalar()

0
 
Jack_JonesAuthor Commented:
ok Dim counterval as integer fixed that, but data.close has blue line under it I just removed it because it has no reason to be there. Got it to load but not updating the usersactive.text
0
 
team2005Commented:
Hi!

Strange ?

Do you get any error message ?
0
 
team2005Commented:
Glad i can help :)
0
 
CodeCruiserCommented:
Jack,
Is the code in selected solution not a copy of my comment http:#35497023?
0
 
Jack_JonesAuthor Commented:
Seems I have to select this one as I don't want any abandon questions. I have everything working and still very thankful for the help received.
0
 
Jack_JonesAuthor Commented:
Also I only accepted the first solution because it worked for me and was able to retreive the data. The first one I was not able to get working, but after going back and playing around with it. I was able to get it working just as the same. So the selected answer here does work and thanks again.

@Team2005, I don't know what to say around the google thing, but you did help me out still.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 7
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now