listbox mysql

[Talk2Tom11]

I am reading information from a mysql database into a listbox. The query that i have should only return one column from the database. But when displayed in the listbox that same column is written 4 times. this is my code for the listbox...

 

While myReader.Read()
           'ProgressBar1.Value = ProgressBar1.Value + 5
           ListBox2.Items.Add(myReader.GetString(0) + ":  " + myReader.GetString(1) + ", " + myReader.GetString(2))

       End While

 

Does anyone know why this would display the same thing 4 times???

 

When i run the same query through mysql directly... it comes out right but only when doing it through VB is multiplies

[PlausiblyDamp]

Could you show the code you are using to create the reader?

[Talk2Tom11]

locationd = My.Settings.location.ToString
       password = My.Settings.password.ToString
       Dim myconnectionString As String
       myconnectionString = "Database=test;Data Source=" + locationd + ";User Id=root;Password=" + password
       Dim mySelectQuery As String = "SELECT guest.id, guest.lastname, guest.firstname FROM guest, student, trans WHERE trans.stid =" + txtBoxID.Text
       Dim myConnection As New MySqlConnection(myconnectionString)
       Dim myCommand As New MySqlCommand(mySelectQuery, myConnection)
       myConnection.Open()
       Dim myReader As MySqlDataReader
       myReader = myCommand.ExecuteReader()
       ' Always call Read before accessing data.
       While myReader.Read()
           'ProgressBar1.Value = ProgressBar1.Value + 5
           ListBox1.Items.Add(myReader.GetString(0) + ":  " + myReader.GetString(1) + ", " + myReader.GetString(2))

       End While
       ' always call Close when done reading.
       myReader.Close()
       ' Close the connection when done with it.
       myConnection.Close()

[PlausiblyDamp]

If you cut and paste the contents of the mySelectQuery into your MySQL query tool and run it do you get the correct results?

 

Out of interest what data type is trans.stid in the database?

 

As a final point you could try using a parameterised query rather than string concatenation as it will removed issues due to data type conversions as well as being a more robust / secure solution.

[Talk2Tom11]

When i use the query in mySQL directly it produces the right solution... it shows one column.

 

stid is of type integer.

 

How would i go about make that change that you mentioned last?