How do I fill my form with MySQL data based on Combobox?

[kismetgerald]

Hey guys/gals,

So with the help of fellow members @ stackoverflow.com, I've been able to retrieve some data from my MySQL database and bind the results to a Combobox (ComboBoxCard) using the code below.

Now I would like to display the results of my query in some controls on my form (5 Textboxes, 1 Combobox, and 2 DateTimePickers) based on the selected value of ComboBoxCard. How do I do this?

The primary goal of this particular form in my application is to allow the user to update a customer's credit card information. Sometimes, a customer may have more than 1 card on file - which is why I needed a mechanism to display all cards on file and allow the user to use the Combobox to select which card to update. So, if there's a better way to do this please do not hesitate to suggest.

Thanks.

HERE'S MY CODE:

[code2=vbnet]Private Sub RetrieveMySQLdata()

Try
'FOR MySQL DATABASE USE
Dim dbQuery As String = ""
Dim dbCmd As New MySqlCommand
Dim dbAdapter As New MySqlDataAdapter
Dim dbTable As New DataTable
If dbConn.State = ConnectionState.Closed Then
dbConn.ConnectionString = String.Format("Server={0};Port={1};Uid={2};Password={3};Database=accounting", FormLogin.ComboBoxServerIP.SelectedItem, My.Settings.DB_Port, My.Settings.DB_UserID, My.Settings.DB_Password)
dbConn.Open()
End If

dbQuery = "SELECT *" & _
"FROM cc_master INNER JOIN customer ON customer.accountNumber = cc_master.customer_accountNumber " & _
"WHERE customer.accountNumber = '" & TextBoxAccount.Text & "'"
With dbCmd
.CommandText = dbQuery
.Connection = dbConn
End With
With dbAdapter
.SelectCommand = dbCmd
.Fill(dbtable)
End With
ComboBoxCard.DataSource = dbTable
ComboBoxCard.ValueMember = "ccNumber"
Catch ex As Exception
MessageBox.Show("A DATABASE ERROR HAS OCCURED" & vbCrLf & vbCrLf & ex.Message & vbCrLf & _
vbCrLf + "Please report this to the IT/Systems Helpdesk at Ext 131.")
Finally
dbConn.Close()
End Try

End Sub[/code2]

[brandonio21]

So with the help of fellow members @ stackoverflow.com

Ah, I love that website. Such a great community.


So, I think what you are going to want to do is make dbTable a global variable, meaning that it can be accessed by all methods.

Then, on the ComboBox SelectedItemChanged event, simply make it so you retrieve information from the dbTable variable. However, this may not work depending on how your database is arranged. Could you give us a layout depicting how your database is arranged?

[kismetgerald]

Thanks for your response. Not sure how you want me to illustrate the database; would you prefer screen shots, or an ERD (created in MySQL Workbench)?

Hope you're well.

[brandonio21]

You can simply do something like this:

CustomerInfo (Database Name)
---Customers (Table Name)
------ID (Field Name)
------Name
------Age
------Email
------Phone Number
---CreditCards
------CustomerID
------CardType
------CardNumber

Or you can take a screenshot. Whatever you prefer!

[kismetgerald]

Hey bro, I'll do even better. Here's a pdf version of the ERD I printed using MySQL WorkBench.

https://dl.dropbox.com/u/55174425/ERD.pdf

Hope it's helpful.

[kismetgerald]

Any luck on my request?

[brandonio21]

Thanks for the bump, I completely forgot about this post.

Anyway, So, since the combobox contains a list of all of the credit card number that belong to a certain user, we are going to want to do something when the combobox's selected index is changed.

First, we are going to get the credit card's number and save it.

Then, we need to select all of the records from the cc_master table and fill in all of the controls. This is pretty simple to do, we just need to use a new MySQL Query and fill in the data
[code2=vbnet]Private Sub ComboBoxCard_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles ComboBoxCard.SelectedIndexChanged
Dim creditCardNumber As String = ComboBoxCard.SelectedItem.ToString

'Here we use a new MySQL call to fill in fields using the existing dbConn
'variable
Dim dbQuery As String = ""
Dim dbCmd As New MySqlCommand
Dim dbAdapter As New MySqlDataAdapter
Dim data As MySqlDataReader
If dbConn.State = ConnectionState.Closed Then
dbConn.ConnectionString = String.Format("Server={0};Port={1};Uid={2};Password={3};Database=accounting", FormLogin.ComboBoxServerIP.SelectedItem, My.Settings.DB_Port, My.Settings.DB_UserID, My.Settings.DB_Password)
dbConn.Open()
End If

dbQuery = "SELECT * FROM cc_master WHERE ccNumber='" & creditCardNumber & "'"

With dbCmd
.CommandText = dbQuery
.Connection = dbConn
End With
With dbAdapter
.SelectCommand = dbCmd
End With
data = dbCmd.ExecuteReader()

While data.Read()
'Now we can deal with each individual data entry and you can put it into
'various controls
txtExpireMonth.Text = data(2).ToString
txtExpireYear.Text = data(3).ToString
txtCode.Text = data(4).ToString
txtZipCode.Text = data(5).ToString
txtType.Text = data(6).ToString
txtAuthorizedUseStart.Text = data(7).ToString
txtAuthorizedUseEnd.Text = data(8).ToString
End While

'Now we close are vars to save memory
data.Close()


End Sub[/code2]

If you have any questions or this doesn't work, feel free to ask.

[kismetgerald]

Thanks Brandon - thought you forgot about me for a minute there. Anyway, in the silence I managed to get some help using a Listview control instead and it's working.

So for now, I think I'll abandon the idea of using the Combobox. But I'll experiment with it to see where else in my application I can use it.

I know you're busy, so I'm very grateful for taking the time out of your day to help me out. Thanks again, and God bless you.

[brandonio21]

No problem! I'm glad that I could help! Sorry once again for the slow response time.