Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
How do I fill my form with MySQL data based on Combobox?
#1
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]
//Kismet
#2
kismetgerald Wrote: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?
My Blog | My Setup | My Videos | Have a wonderful day.
#3
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.
//Kismet
#4
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!
My Blog | My Setup | My Videos | Have a wonderful day.
#5
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.
//Kismet
#6
Any luck on my request?
//Kismet
#7
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.
My Blog | My Setup | My Videos | Have a wonderful day.
#8
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.
//Kismet
#9
No problem! I'm glad that I could help! Sorry once again for the slow response time.
My Blog | My Setup | My Videos | Have a wonderful day.


Possibly Related Threads…
Thread Author Replies Views Last Post
  How can I read a link from mysql databse??? mnxford 9 32,853 04-12-2013, 06:27 PM
Last Post: brandonio21
  I am getting problem with brandonio's registration form... mnxford 1 9,133 03-27-2013, 10:55 AM
Last Post: mnxford
  i am getting a problem with brandonio's login form mnxford 1 9,045 03-27-2013, 10:55 AM
Last Post: mnxford
  How do I use Listview to delete MySQL DB records kismetgerald 10 33,756 11-28-2012, 05:19 PM
Last Post: brandonio21
  [SOLVED] MySQL Update Query - What am I doing wrong? kismetgerald 11 43,025 10-18-2012, 07:16 PM
Last Post: brandonio21
  Loading DB data into Combobox then populate TextBox Controls kismetgerald 0 5,692 09-02-2012, 08:44 PM
Last Post: kismetgerald
  Help with creating an INSERT statement using MySQL kismetgerald 4 16,697 08-30-2012, 03:03 PM
Last Post: brandonio21
  MySQL Database Issue Moldraxian 7 28,948 08-13-2012, 08:58 PM
Last Post: brandonio21
  getting a file name form a save dialoge zmanalpha 3 14,051 08-04-2012, 03:15 PM
Last Post: brandonio21
  MySql Database Queries in VB.Net Moldraxian 6 22,250 07-26-2012, 03:44 PM
Last Post: Moldraxian

Forum Jump:


Users browsing this thread: 1 Guest(s)