adding item from textbox to listbox when data source is set

[vb_newbie]

Hi, I have a combobox, textbox, listbox, and a save button. Combobox is filled with illness names stored from database. Every illness that will be selected from the combobox, its symptoms will be listed in the listbox. When a symptom is typed in the textbox and save button is clicked, the selected value from the combobox and the new symptom typed from the textbox will be saved in the database and the newly typed symptom should be added in the listbox..but I can't add the item from textbox to listbox. I already tried lstIllness.Items.Add(textbox1.text) but got an error, it says 'Items collection cannot be modified when the data source property is set'. Please help me. Thanks

[jassicasim]

SQL to relate them.

[QVeen72]

Hi,

Dont directly bind to the ListBox control..
Populate items to it, using SQL Reader..
and then you can add listitems, as and when required..

Regards
Veena

[HctiMitcH]

Use this tutorial for adding listbox items.

http://www.fryan0911.com/2009/06/vbnet-listview-tutorial-add-edit-and.html

[vb_newbie]

@HctiMitcH: Thanks for that but it's not applicable on my situation because the given example don't have database.
@QVeen72: I already tried that one but still got an error. Here's how I did it:

With Me
            Dim dt As New DataTable("myTable")
            Dim cmd As New MySqlCommand
            Dim dtr As MySqlDataReader
            Dim str As String
            Call Connect()
            lstProd.HorizontalScrollbar = True
            str = "Select prod from myTable where item = @item"
            cmd.Parameters.AddWithValue("item", cmbRecord.Text)
            cmd.Connection = myConn
            cmd.CommandText = str
            dtr = cmd.ExecuteReader

            While dtr.Read()
                lstProd.Items.Add(dtr("prod"))
            End While
            lstProd.DataSource = dt

            txtItem.Clear()
            dtr = Nothing
            cmd = Nothing
            myConn.Close()

        End With
        Call Disconnect()

This code is under the cmbRecord_SelectedIndexChanged event. Here's on the btnAdd_click event:

        Call Connect()
            STRSQL = "insert into ill_cause values ('', @new, @prod)"
            Using myCmd = New MySqlCommand(STRSQL, myConn)
                'myConn.Open()
                myCmd.Parameters.AddWithValue("new", cmbRecord.Text)
                myCmd.Parameters.AddWithValue("prod", txtProd.Text)
                myCmd.ExecuteNonQuery()
            End Using
            MsgBox("Product Added")
            lstProd.Items.Add(txtProd.Text)
            myConn.Close()
            txtProd.Clear()
            Call Disconnect()

But it gives me error 'Items collection cannot be modified when the data source property is set'. Can anyone please correct it? My apologies, I'm quite in a hurry now to finish this. Please help me.

[QVeen72]

Hi,

Remove Line Number 17.. dont set the dataSource for lstProd

Regards
Veena

[adam_k]

Just requery your db, instead of adding it manually.

[vb_newbie]

@Veena: I tried it but it didn't work :(
@adam: I don't really get how to do that sir, you see I'm just starting to learn..

Anyway, I tried something but not really sure if this is appropriate since it's still not working properly:
Private product As New List(Of String)
this is the frmAdd_Load event:

            Call Connect()

            STRSQL = "select item from Record"
            Dim cmd As New MySqlCommand(STRSQL, myConn)
            Dim da As New MySqlDataAdapter(cmd)
            Dim dt As New DataTable("Record")
            da.Fill(dt)

            If dt.Rows.Count > 0 Then
                cmbRecord.DataSource = dt
                cmbRecord.DisplayMember = "item"
            End If
            lstProd.DataSource = product
            dt = Nothing
            myConn.Close()
            Call Disconnect()

this is the cmbRecord_SelectedIndexChanged event:

                Dim cmd As New MySqlCommand
                Dim dtr As MySqlDataReader
                Dim str As String
                Call Connect()

                str = "Select prod from myTable where item = @item"
                cmd.Parameters.AddWithValue("item", cmbRecord.Text)
                cmd.Connection = myConn
                cmd.CommandText = str
                dtr = cmd.ExecuteReader

                Dim prods As New List(Of String)
                While dtr.Read()
                    prods.Add(dtr("prod"))
                End While
                lstProd.DataSource = prods
                txtProd.Clear()
                dtr = Nothing
                cmd = Nothing
                myConn.Close()

                Call Disconnect()

and this is the btnAdd_Click event:

                    Call Connect()
                    STRSQL = "insert into myTable values ('', @item, @newProd)"
                    Using myCmd = New MySqlCommand(STRSQL, myConn)
                        'myConn.Open()
                        myCmd.Parameters.AddWithValue("item", cmbRecord.Text)
                        myCmd.Parameters.AddWithValue("newProd", txtProd.Text)
                        myCmd.ExecuteNonQuery()
                    End Using
                    MsgBox("Product Added")
                    lstProd.Items.Add(txtProd.Text)
                    lstProd.DataSource = Nothing
                    myConn.Close()
                    txtProd.Clear()
                    Call Disconnect()

it now adds the product I typed in the txtProd to the lstProd, but it clears first the all the products in the list and then the newly added product will be displayed. When I select another item from the cmbRecord it displays the products belonging to it,then when I select again the item where I added a new product, it will show all the products belonging to it and the recenly product that I added to it is now displayed.(I think this is confusing) For example: I select pants from cmbRecord, then lstProd will display pants1,pants2..when I typed pants3 in txtProd and click btnAdd, the lstProd will be cleared and pants3 only will be displayed. When I select dress from cmbRecord, lstProd will show dress1,dress2..then when I select again pants from cmbRecord,it will now display pants1,pants2,pants3..that shows that pants3 is already in the list whith item pants. But its not working properly right?,because it clears first the products on the list and have to change the selected item from the cmbRecord then back to the previous selected item to view its products with the just added product. Can you please help me fix this? thanks again