Question How do I go about populating a mysql table with data from a combo box?

[shabbaranks]

Hi there - first post so please be gentle with me....

Ive got a form which has two buttons "In" and "Out" a persons name ane then a combo box which is the result of whether they press "In" Im in the office or "Out" Im out the office. Ideally what I would like to do is populate the combo box info to a table within mysql - how difficult is it to do this?

I have setup a connections between the form and the mysql db which works - so am I half way there?

Thanks

 

[jmcilhinney]

Are you sure that you have that question the right way around? You have asked how to populate a database with data from a ComboBox. Do you actually want to know that or do you really want to know how to populate the ComboBox with data from the database?

 

[shabbaranks]

Well this is where my total nooob knowledge comes in. Ideally I would like a combo box to give the option to select a name from a table in a mysql database - does that make more sense?

Thanks

 

[jmcilhinney]

Yes, that's makes much more sense and doesn't bear much resemblance to the original question. We got there in the end though.

First up, if you haven't already, you should download and install Connector/Net from the MySQL web site. It is a MySQL-specific ADO.NET provider. Once install, you can use the members of the MySql.Data.MySqlClient namespace in the same way as you do the System.Data.SqlClient namespace for SQL Server. With that in mind, you can use the MySqlConnection.GetSchema method to get a DataTable containing various schema information, including about tables. You should check out the appropriate documentation for more info.

 

[shabbaranks]

Thats just gone right over my head. I have a working connection between the database and my form (tick). So I now need to link the combo to the specific table - havent a clue how to do that. Have you got an example please?

Thanks

 

[jmcilhinney]

use the MySqlConnection.GetSchema method

check out the appropriate documentation

What exactly do you not understand about that?

mysqlconnection.getschema - Google Search

 

[shabbaranks]

I think I am almost there.... But It doesnt populate the combo box - is there something I am missing?

Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
Dim con AsNewMySqlConnection
Dim constr AsString = "Database=Database_Item;" & _
"Data Source=database;" & _
"User Id=user;Password=password"
Try
con.ConnectionString = constr
con.Open()
Catch ex As SqlClient.SqlException
MsgBox(ex.Message)
EndTry
Dim sStmt AsString = "SELECT FirstName, Surname FROM Phone_Numbers"
Dim cmd AsNewMySqlCommand(sStmt, con)
Dim da AsMySqlDataAdapter = NewMySqlDataAdapter(cmd)
Dim dt AsNewDataTable("Phone_Numbers")
da.Fill(dt)
If dt.Rows.Count > 0 Then
ComboBox1.DataSource = dt
ComboBox1.DisplayMember = "SurName"
ComboBox1.ValueMember = "FirstName"
EndIf
EndSub

 

[jmcilhinney]

I can't recall whether DisplayMember is case-sensitive or not but it's the first thing I'd check:

Dim sStmt As String = "SELECT FirstName, Sur[B][U]n[/U][/B]ame FROM Phone_Numbers"

ComboBox1.DisplayMember = "Sur[B][U]N[/U][/B]ame"

Also, please don't try to colour your code snippets. Just use the [xcode] tag button in the full editor and specify "vb" for the option.

 

[shabbaranks]

Thanks will give it a go. I didnt colour code - I just copied and pasted from VB. But I will make sure its normal next time.