Dynamically add values into DataGridView Cell From ListBox VB2010

[nadiam]

Hi. I have a form with list box : lst_product, datagridview : grd_order and button: btn_addline. lst_product has a list of product ids selected from database (MS Acess 2013) , grd_order is by default empty except for 2 headers and btn_addline adds rows to grd_order.

btn_addline :

Private Sub btn_addline_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_addline.Click
   grd_order.RowCount = grd_order.RowCount + 1
End Sub

everything to do with lst_product:

lst_product.DataSource = run_sql_query("SELECT * FROM TBL_PRODUCTS_A154287 ORDER BY FLD_PRODUCT_ID ASC")
lst_product.DisplayMember = "FLD_PRODUCT_ID"

**//This code snippet lets user click on a product id in the list and populates the id and its corresponding price into the column cell**

Private Sub product_list(ByVal pid As String)

   Dim getPID As String = "SELECT * FROM TBL_PRODUCTS_A154287 WHERE FLD_PRODUCT_ID='" & pid & "'"

   Dim thePTable As New DataTable

   Dim reader As New OleDb.OleDbDataAdapter(getPID, myconnection)

   reader.Fill(thePTable)

   For i As Integer = 0 To grd_order.RowCount - 1
       grd_order(0, i).Value = thePTable.Rows(0).Item("FLD_PRODUCT_ID")
       grd_order(1, i).Value = thePTable.Rows(0).Item("FLD_PRICE")
   Next
End Sub

The btn_addline does work and adds a new row(Row1) to the datagrid the problem is when another row(Row2) is added.

When new row(Row2) is added after btn_addline and a new product id is selected from lst_product the data in Row1 changes along with the selected id. so instead of Row1 : ID0001 | RM12, Row2 : ID0002 | RM45. I get Row1 : ID0002 | RM45, Row2 : ID0002 | RM45

How do i code so that each row/cell is unique?

[nadiam]

So is what im asking not possible to do? is there another way to accompolish what i want?

[ddanbe]

Line 17 and 18 instead of Rows(0), should that not be Rows(i)?

[nadiam]

@ddanbe i did change it and i got IndexOutOfRangeException was handled error : There is no row at position 1.

[nadiam]

hmm i think i asked the wrong question. its not how to add the values because i can do that, its more of making each row and cell unique from each other. oh well. please if anyone can help. thank you.

[tinstaafl]

The Fill function of the DataAdapter will merge data to an existing table. By making thePTable global, you can use it as the datasource for grd-order. Each time you add any records to the thPTable it will update grd-order:

Dim thePTable As New DataTable
Public Sub New()
    ' This call is required by the designer.
    InitializeComponent()

    ' Add any initialization after the InitializeComponent() call.
    grd_order.DataSource = thePTable
End Sub

Private Sub product_list(ByVal pid As String)
    Dim getPID As String = "SELECT * FROM TBL_PRODUCTS_A154287 WHERE FLD_PRODUCT_ID='" & pid & "'"
    Dim reader As New OleDb.OleDbDataAdapter(getPID, myconnection)
    reader.Fill(thePTable)
End Sub

When you want to start over you can call the thePTable.Clear() sub routine to empty the data table.

[nadiam]

@tinstaafl thank you! So based on the select query, when a product id is selected from listbox, new row that is populated with data is added to the table.

how do i change the column name? coz dt follows the column name from database like FLD_PRODUCT_ID etc, want to change it to PRODUCT ID etc

[nadiam]

code for the listbox - datagridview interaction

At the top of the code so its public

Dim dt As New DataTable

in private sub form load

grd_order.DataSource = dt

in private sub for product list

Private Sub product_list(ByVal pid As String)
    Dim getproduct As String = "SELECT FLD_PRODUCT_ID,FLD_PRICE FROM TBL_PRODUCTS_A154287 WHERE FLD_PRODUCT_ID='" & pid & "'"
    Dim reader As New OleDb.OleDbDataAdapter(getproduct, myconnection)
    Dim getcolumns As String = "SELECT FLD_QTY,FLD_SUBTOTAL FROM TBL_ORDERITEM_A154287 WHERE FLD_PRODUCT_ID='" & pid & "'"
    Dim reader2 As New OleDb.OleDbDataAdapter(getcolumns, myconnection)
    reader.Fill(dt)
    reader2.Fill(dt)

End Sub

http://imgur.com/a/TKu0K

Thats my table after multiple product ids are selected from list box. i want to change the name of the Header Text. i thought of using this dt.Columns(0).HeaderText = "" but nope. its the databinding thing.

I was also wondering how to calculate the subtotal? because user can key in the quantity of the product they want. like in the picture : CK005 | 850 | , quantity and subtotal cells are empty. how would i get the value from cell Price and cell Quantity? to calculate the subtotal : valueFromQtyCell * valueFromPriceCell for each row.

[tinstaafl]

You should be able to do something like this:

"SELECT FLD_PRODUCT_ID AS `PRODUCT ID`,FLD_PRICE FROM TBL_PRODUCTS_A154287 WHERE FLD_PRODUCT_ID='" & pid & "'"

The delimiters around PRODUCT ID are ticks(top right button by No. 1).

Adding columns and adjusting the data is better put into another question.

[nadiam]

Thank you, tinstaafl! Okay will open a new topic for my other question.