select an item in a combo/dropdown box from an incoming database return

[mrlizter]

hi all
im writing some software at the moment, i have a mysql database backing it all and i have a page called "edit items". its basically a datagrid which displays all the rows from the sql database in the products table.

now i identified a problem which is that if i leave the "category" area just a text box the user will be able to insert any number of different spellings and mis-spellings of the one word. SO to combat this i decided to change the text box style (basic default datagrid) to a drop down box which will load all the available category types (which the user defines earlier in the program set up)

but....when i return the string from the database with all the items....the combo box dies. and i've no idea for to get the combo box to show the correct text for that item.
the working logical order is this:

• load itemedit
• combo box.items = (load category's from database) (*up to here it works - i have rigged a button to do just this and it does load the category list and function properly)
• query database with "select * from products"
• datagrid populate
• add new row's till done (null from database)
• close connection to database.

thats how it should work....but it comes up with...

"The following exception occurred in the datagridview:
System.arguementException: Datagridviewcomboboxcell value is not valid.
To replace this default dialogue handle the dataerror event.:

so im kind of stuck, ive tried every possible combination of "combobox.text, .items .selected .display " that i could think of or looked like it might work.

below is my load items into the combo box code and then the load items code.

thanks


'this is the code that inserts the available category's into the combo box
'combo box design name is 'cat'

Public Sub load_cat_list()

        Dim myConnectionString As String = "Database=james;Data Source=localhost;User Id=root;Password=password"

        Dim myConnection As New MySql.Data.MySqlClient.MySqlConnection(myConnectionString)
        Dim myInsertQuery As String = "select catname from category"
        Dim myCommand As New MySql.Data.MySqlClient.MySqlCommand(myInsertQuery, myConnection)

        myConnection.Open()
        Dim myReader As MySql.Data.MySqlClient.MySqlDataReader
        myReader = myCommand.ExecuteReader()
        ' Always call Read before accessing data.
        While myReader.Read()
            Cat.Items.Add(myReader.GetString(0))

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



    End Sub

'this is the load items code, that populates the datagrid
'string 3 of the myreader.getstring is / should be the combo box column

Public Sub LoadItems()

        DataGridView1.Rows.Clear()

        Dim myConnectionString As String = "Database=james;Data Source=localhost;User Id=root;Password=password"

        Dim myConnection As New MySql.Data.MySqlClient.MySqlConnection(myConnectionString)
        Dim myInsertQuery As String = "select name, price, size, category from product"
        Dim myCommand As New MySql.Data.MySqlClient.MySqlCommand(myInsertQuery, myConnection)

        myConnection.Open()
        Dim myReader As MySql.Data.MySqlClient.MySqlDataReader
        myReader = myCommand.ExecuteReader()
        ' Always call Read before accessing data.
        While myReader.Read()
            DataGridView1.Rows.Add(New Object() {myReader.GetString(0), myReader.GetString(1), myReader.GetString(2), myReader.GetString(3))}


        End While
        myReader.Close()
        myConnection.Close()

    End Sub

hope someone can help

thanks muchly

mrlizter

 

[Bernie]

Where in your code are you getting the exception? Is it when the first value tries to load or somewhere after a few values have loaded? If it's after a few have loaded, have you been able to capture the "bad" values?

Bernie

 

[mrlizter]

first i call the list of available items into the combo box, this works beautifully.
then the next step is populating the datagrid with the data from the mysql database. its at this point when the datagrid tries to insert a value(to display) into the combo box that it throws a wobbler... PROBABLY because i don't know the correct method of inserting something into a combo box.... .text doesn't work... etc...

thanks

mrlizter

 

[cjard]

use a datatable as the datasource for the combo, set the displaymember to the display column and the valuememeber to the value column, set the datapropertyname to the name in your foreign table that contains the values the combo has to decode (valuemember -> displaymember)

For more info, read the MSDN article:
DataGridViewComboBoxColumn Class (System.Windows.Forms)


If youre a newbie, MySQL will cause you more time-costly problems than the low monetary-cost negates. I'd reccommend using OracleXE or SQLServerExpress (both free) to learn your data access, then go to MySQL if you really have to.