Question How to insert(merge) new query result to existing datatable using datareader

[demetron]

What i was trying to do is to generate a list of products with all details added by user into the datagridview but as i am using datatable.load(datareader) it always reset the previous value of the datatable,i also tried datatable.load(datareader,upinsert) but same thing is happening again.

If e.KeyCode = Keys.Return Then
            cn = connectdb("MSMS.mdb", "")
            Dim cmd2 As New OleDbCommand("SELECT Code,Product,Pack,Batch,Expiration,Free,MRP,Rate,CST,VAT,CD,SD FROM product WHERE Product ='" + DataGridView2.SelectedRows(0).Cells(0).Value.ToString + "'", cn)
            Dim dr2 As OleDbDataReader
            Dim dt As New DataTable
            dr2 = cmd2.ExecuteReader()
            dt.Load(dr2, LoadOption.Upsert)
            DataGridView3.DataSource = dt

i want to insert into datatable every time when Enter is pressed along with the last result?

 

[jmcilhinney]

Does your data have a primary key? It sounds like what you're asking for is to be able add multiple rows for one PK value. Can you clarify?

 

[demetron]

thanks for the reply

Yes it has a primary key and it's "Batch no." in this case,i am working on a counter sales kind of project and the products have to add to cart,lets say it has a textbox1 where the operator type the initials of the product name to search from entire product database showing below in datagridview1,and as operator types it will automatically sorted out in a datagridview1(it has select_row_once and multiple selection = false),so basically the product is auto selected in datagridview1 and when he press enter(the code i posted) all the columns for the product selected in datagridview1 is loaded in datagridview2. (it was simple and working)

But i am stuck .......after 1st run it auto focus textbox1 and same procedure is repeated ...but instead of adding new product to datagridview2 it simply reset and only load columns of last product selected.

hope you will understand my problem ... range: otherwise i'll post some screenshot to explain ...thanks.

 

[demetron]

I am adding some pics to explain my problem.....