asked on

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

ASKER CERTIFIED SOLUTION

Nasir Razzaq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jack_Jones

ASKER

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.

Brad Brett

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

Nasir Razzaq

Are you sure there are rows for the given condition?

Jack_Jones

ASKER

yes there are rows, that would be if there are 30 accounts out of 60 accounts online, it should grab that online = 30 correct?

team2005

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

Jack_Jones

ASKER

team2005, that would work if i were using odbc, but im using a different method to connect.

Jack_Jones

ASKER

only error is counterval is not declared.

team2005

Hi!

Sorry,...

Try

Dim int counterval = command.ExecuteScalar()

Jack_Jones

ASKER

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

team2005

Hi!

Strange ?

Do you get any error message ?

team2005

Glad i can help :)

Nasir Razzaq

Jack,
Is the code in selected solution not a copy of my comment http:#35497023?

Jack_Jones

ASKER

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.

Jack_Jones

ASKER

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.