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?

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

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

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

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.

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.

@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

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.

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.

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.