VB: Fill multiple textboxes with data from MySQL DB

Question

So, I have this program which has to store a lot of different info from users and shows it through Textboxes, Numerics, etc when the Form loads. At first I thought it would be easy, but as soon as I started writing the code I figured that if I would do the easy way (the way I know) I would have to write hundreds of Subs, each one with a MySQL query and then assign, one by one, the values to their respective textboxes, comboboxes, etc.

So, how can I pull data from multiple rows from a MySQL DB and then assign the data from each one of those rows to textboxes?

This is what I have now, which works fine, but only for getting one single value from the DB:

Imports MySql.Data.MySqlClient

Public Class GetInfo


    Public Shared Sub Run()

        Dim reader As MySqlDataReader
        Dim result As String

        Dim Query_Read As String = "Select Nome FROM dk_db_sql_yog." & Username
        Dim Cmd_Read_Name As New MySqlCommand(Query_Read)
        Cmd_Read_Name.Connection = Connect
        reader = Cmd_Read_Name.ExecuteReader()
        If reader.Read() Then
            If reader.IsDBNull(0) Then
                result = ""
            Else
                result = reader.GetString(0)
            End If
        End If

        Form1.Name_Textbox.Text = result
        reader.Close()

     End Sub

End Class

Could you show a few more iterations? And how many TextBoxes are there? Hundreds??? — Bjørn-Roger Kringsjå
– Bjørn-Roger Kringsjå, Commented Dec 21, 2013 at 20:16
@Bjørn-RogerKringsjå Well there are probably something like ~150 boxes to be filled. And sorry, I didn't understand what you asked me to show more (not native english here :x) — andre_ss6
– andre_ss6, Commented Dec 21, 2013 at 20:52
Do you want to loop through a collection of users? Something like For each user in myTableOfUsers What is the name of the next two TextBox controls? — Bjørn-Roger Kringsjå
– Bjørn-Roger Kringsjå, Commented Dec 21, 2013 at 21:00
@Bjørn-RogerKringsjå No. The Form will open and then Run that Sub I posted. That Sub only search trough a table specified by the user (the '& username' part in the code). That table contains all the user's info (like Name, Age, resources and etc). Then I need to get the data from each row in that table and then assign them to the program's textboxes. — andre_ss6
– andre_ss6, Commented Dec 21, 2013 at 21:09
I've added an answer. But i strongly suggest that you consider merging all tables into one users table. In my opinion, and I might be wrong, this is bad database structuring. — Bjørn-Roger Kringsjå
– Bjørn-Roger Kringsjå, Commented Dec 21, 2013 at 21:28

Ruben_PH · Accepted Answer · 2013-12-22 02:19:20Z

1

As I understand your question, you may use a datatable:

Dim reader As MySqlDataReader
Dim result As New Datatable

Dim Query_Read As String = "Select Nome, Nome1, Nome2 FROM dk_db_sql_yog." & Username
Dim Cmd_Read_Name As New MySqlCommand(Query_Read)
Cmd_Read_Name.Connection = Connect
result.Load(Cmd_Read_Name.ExecuteReader)

With Form1
  For Each dtrow AS DataRow in result.rows
     .Name_Textbox1.Text = dtrow(0)
     .Name_Textbox2.Text = dtrow(1)
     .Name_Textbox3.Text = dtrow(2)
  Next
End With

edited Dec 22, 2013 at 2:19

answered Dec 21, 2013 at 21:17

Ruben_PH

1,8248 gold badges27 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

andre_ss6 Over a year ago

It says that Value of type MySqlDataReader cannot be converted to a Datatable in result = Cmd_Read_Name.ExecuteReader

Bjørn-Roger Kringsjå Over a year ago

@AndreSilva Correct, you can not. ExecuteReader returns a reader, not a datatable, thus the error.

Jeff Over a year ago

you just need to change result = Cmd_Read_Name.ExecuteReader to result.Load(Cmd_Read_Name.ExecuteReader) and then you can loop through the DataTable

andre_ss6 Over a year ago

@Jeff Oh that worked! Thanks! I only had to change result As Datatable to result As New Datatable so it wouldn't result in an exception.

Collectives™ on Stack Overflow

VB: Fill multiple textboxes with data from MySQL DB

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related