need help with mysql data to label

[djrico8]

Hello there, iam new to this forum but iam also new with retrieving data from a mysql database to an label in vb.net.
Iam using vb 2010 and my mysql is hosted on a server.

Here is my code:

Imports MySql.Data.MySqlClient
Imports System.Data.Sql
Imports System.Data.SqlClient

Public Class Gamefrm
    Dim con As New MySqlConnection
    Dim cmd As New MySqlCommand
    Dim reader As MySqlDataReader


    Private Sub gamefrm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            con = New MySqlConnection("Server==********;; User Id=********; Password=********; Database==********;")
            Dim cmd As New MySqlCommand()
            cmd.CommandText = "SELECT health,strength FROM playerstats WHERE playerid='" & playeridlbl.Text & "'"
            cmd.CommandType = CommandType.Text
            cmd.Connection = con
            con.Open()
            reader = cmd.ExecuteReader()
            While reader.Read = True
                healthlbl.Text = reader.Item(1).ToString()
                strengthlbl.Text = reader.Item(2).ToString()
            End While
        Catch ex As Exception
            MsgBox(ex.Message)
            If con.State <> ConnectionState.Closed Then
                con.Close()
            End If
            con.Close()
        End Try


        Try
            con = New MySqlConnection("Server==********;; User Id=********; Password=********; Database==********;")
            Dim cmd As New MySqlCommand()
            cmd.CommandText = "SELECT money,weapon,ammunition FROM playerinventory WHERE playerid='" & playeridlbl.Text & "'"
            cmd.CommandType = CommandType.Text
            cmd.Connection = con
            con.Open()
            reader = cmd.ExecuteReader()
            While reader.Read = True
                moneylbl.Text = reader.Item(1).ToString()
                weaponlbl.Text = reader.Item(2).ToString()
                ammunitionlbl.Text = reader.Item(3).ToString()
            End While
        Catch ex As Exception
            MsgBox(ex.Message)
            If con.State <> ConnectionState.Closed Then
                con.Close()
            End If
            con.Close()
        End Try
    End Sub

    Private Sub Gamefrm_FormClosing(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles MyBase.FormClosing
        Mainfrm.Show()
        Loginfrm.UsernameTextBox.Text = ""
        Loginfrm.PasswordTextBox.Text = ""
    End Sub

End Class

I masked the username and password for the database, but i hope someone can help me :).

[Reverend Jim]

Fields are zero-relative. You could index them by (using the correct syntax)

healthlbl.Text = reader(0)
strengthlbl.Text = reader(1)

but this is bad practice because if you change the query you might brreak the code. You should, instead, index using the field name as in

healthlbl.Text = reader("health")
strengthlbl.Text = reader("strength")

Note that the second form works whether the query does any of the following:

SELECT health, strength
SELECT strength, health
SELECT *

Because the fields health and strength are already strings you do not need to do ToString.

[djrico8]

Thank you for the reply Revered Jim, but when i use the second option it isn't populating the labels.

Also no warning or error is coming up.

[Reverend Jim]

Do you have SQL Management Studio (SQLMS) installed? It's free from Microsoft and is really useful for developing data base based apps. If not then I suggest a breakpoint at the first line of

reader = cmd.ExecuteReader()
While reader.Read = True
    healthlbl.Text = reader.Item(1).ToString()
    strengthlbl.Text = reader.Item(2).ToString()
End While

Check the exact query text and make sure your query is actually returning a record with values. With SQLMS you could execute the query in an ad hoc window and check the results. I strongly suggest you install it.

[djrico8]

It returns that the reader = nothing, so i think the query is wrong then?

cmd.CommandText = "SELECT health,strength FROM playerstats WHERE playerid='" & playeridlbl.Text & "'"

if i read this it has to be right?

This is a picture of the table in the mysql database.

[Reverend Jim]

That looks right, however because your playerid is numeric you should leave out the single quotes and use

cmd.CommandText = "SELECT health,strength FROM playerstats " &
                  " WHERE playerid=" & playeridlbl.Text

You were comparing a numeric field 1 with the string '1'. At least I am assuming playeridlbl.Text had the value "1".

Again, you will find working with databases (and composing queries) much easier if you install SQL Management Studio.

[djrico8]

Dear Revered Jim thank you for the reply, when i remove the single qoutes i finally get an error.

[Reverend Jim]

Try

cmd.CommandText = "SELECT health,strength FROM playerstats " &
                  " WHERE playerid=" & playeridlbl.Text
Debug.WriteLine cmd.CommandText

and post the output here.

[djrico8]

I have made a screenshot for you.

After this message i get another message wich is: Unknown column 'playeridlbl' in 'where clause'

[Reverend Jim]

I don't see any output from Debug.Writeline. Comment out the Try/Catch/End Try.

[Santanu.Das]

There ia an extra space in your sql statement you made find from your uploaded picture. The sqlstatement should be

"SELECT health,strength FROM playerstats WHERE playerid= " & playeridlbl.Text 

Before reading the reader, please check it produces any record or it is blank. A blank reader can produce an exception if you try to read it.

If reader.HasRows() then

    'make a loop here to read reader

End If

Hope it can help you.

[djrico8]

It resulted in the same error as before

This is what i have at the moment:

con = New MySqlConnection("Server=*******; User Id==*******;; Password==*******;; Database=ricarye112_whoopiegame")
            Dim cmd As New MySqlCommand()
            cmd.CommandText = cmd.CommandText = "SELECT health,strength FROM playerstats WHERE playerid= " & playeridlbl.Text
            Debug.WriteLine(cmd.CommandText)
            cmd.CommandType = CommandType.Text
            cmd.Connection = con
            con.Open()
            reader = cmd.ExecuteReader()
            If reader.HasRows() Then

            Else
                Do While reader.Read = True
                    healthlbl.Text = reader("health")
                    strengthlbl.Text = reader("strength")
                Loop
            End If


 Catch ex As Exception
            MsgBox(ex.Message)
            If con.State <> ConnectionState.Closed Then
                con.Close()
            End If
            con.Close()

I can not see any problems with the coding....

[Reverend Jim]

In this case it doesn't matter what your code is. The error is in the query and showing us

cmd.CommandText = cmd.CommandText = "SELECT health,strength FROM playerstats WHERE playerid= " & playeridlbl.Text

doesn't tell us what the query is because we don't know the value of playeridlbl.Text. If you won't post the actual query (ie the value of cmd.CommandText) then we can't help you.

[djrico8]

The value in playeridlbl.text = 1 (this has been filled in by selecting playerid where username is Djrico8).

So what it has to do now, is getting the health and strength wich comes with that player.

so the query to run is: get health and strength where playerid is 1 (the playerid for playername Djrico8).

[Reverend Jim]

OK. Then the obvious next question is - what is the type of the database field named playerid. I had assumed it was numeric based on the single example. Please note that I have asked you three times to post the actual query (the value of cmd.CommandText) and you still haven't done that.

[djrico8]

The type of the field is numeric (integer).
The value of cmd.CommandText = 'playeridlbl' this is the value when i debug the program.

Iam sorry that i didn't give the answer (maybe i have read it wrong).

[djrico8]

The problem is fixxed, i inserted a code to get the playerid again and fill in the playeridlbl with the newly obtained playerid, now it is working.

This is the new code i entered.

Try
            con = New MySqlConnection("Server=*****; User Id=*****; Password=*****; Database=ricarye112_whoopiegame")
            Dim cmd As New MySqlCommand()
            cmd.CommandText = "SELECT playerid FROM players WHERE username= '" & userstatsbox.Text & "'"
            Debug.WriteLine(cmd.CommandText)
            cmd.CommandType = CommandType.Text
            cmd.Connection = con
            con.Open()
            reader = cmd.ExecuteReader()
            While reader.Read = True
                playeridlbl.Text = reader(0)
            End While
        Catch ex As Exception
            MsgBox(ex.Message)
            If con.State <> ConnectionState.Closed Then
                con.Close()
            End If
            con.Close()
        End Try


Try
            con = New MySqlConnection("Server=*****; User Id=*****; Password=*****; Database=ricarye112_whoopiegame")
            Dim cmd As New MySqlCommand()
            cmd.CommandText = "SELECT health,strength FROM playerstats WHERE playerid= " & playeridlbl.Text
            cmd.CommandType = CommandType.Text
            cmd.Connection = con
            con.Open()
            reader = cmd.ExecuteReader()
            While reader.Read = True
                healthlbl.Text = reader("health")
                strengthlbl.Text = reader("strength")
            End While
        Catch ex As Exception
            MsgBox(ex.Message)
            If con.State <> ConnectionState.Closed Then
                con.Close()
            End If
            con.Close()
        End Try


Try
            con = New MySqlConnection("Server=*****; User Id=*****; Password=*****; Database=ricarye112_whoopiegame")
            Dim cmd As New MySqlCommand()
            cmd.CommandText = "SELECT money,weapon,ammunition FROM playerinventory WHERE playerid= " & playeridlbl.Text
            cmd.CommandType = CommandType.Text
            cmd.Connection = con
            con.Open()
            reader = cmd.ExecuteReader()
            While reader.Read = True
                moneylbl.Text = reader("money")
                weaponlbl.Text = reader("weapon")
                ammunitionlbl.Text = reader("ammunition")
            End While
        Catch ex As Exception
            MsgBox(ex.Message)
            If con.State <> ConnectionState.Closed Then
                con.Close()
            End If
            con.Close()
        End Try

Thank you for all the help, you may see the newly code to see where it might got wrong.