add 2 data table values based on id

[vijaycare]

Hi All,

I have 2 data table in my application and want to create a new table from those 2 tables.

  Dim sql As String = "Select ID, DESC, QTY from Purchases Where PDate >= @StartDt and PDate <=@EndDt order by ID"
    Dim da As New OleDb.OleDbDataAdapter(Sql, Con.conn)
    da.selectCommand.Parameters.AddwithValue ("@StartDt", me.dtpStart.value.Date)
    da.selectCommand.Parameters.AddwithValue ("@EndDt", me.dtpEnd.value.Date)
    Dim dt As New DataTable
    dt.clear()
    da.fill (dt)

The table1 contains data like
500
0
700
640
0
1100
500

The tabl2 contains data like

ID DESC QTY
1 ASD 2000
2 BNS 1500
3 DSC 200
4 QSR 300
5 RSJ 4500
6 JVS 1800
7 LKS 1450

And I want to create a table from those 2 tables like:

ID DESC QTY
1 ASD 2500
2 BNS 1500
3 DSC 900
4 QSR 940
5 RSJ 4500
6 JVS 2900
7 LKS 1950

how can i achieve this. Any suggestion would be appreciated. Thanks

[Reverend Jim]

First of all you have to have some way of relating table1 to table2. You have given no name to the column in table1 (I assume from context that it represents a quantity). You'd have to include an ID column to associate the quantity to an ID.

Also, you should not use DESC as a column name. In some database systems this is a reserved word (indicates a descending order for sorting). Once you have two tables set up as

Temp
   ID
   QTY

Purchases
    ID
    DESCR
    QTY

You can add Temp to Purchases in a query by

select p1.ID, p1.DESCR, QTY=p1.QTY+p2.QTY
  from Purchases p1, Temp p2
 where p1.ID=p2.ID

This will return the results you want in a recordset for display. Note - this does not update the Purchases database. To create a new table from the results do

select p1.ID, p1.DESCR, QTY=p1.QTY+p2.QTY
  into NewPurchases
  from Purchases p1, Temp p2
 where p1.ID=p2.ID

[vijaycare]

Thanks Jim,

from the above example how can i use this result in a datagridview?

[Reverend Jim]

I have no experience using DataSets, DataAdapters, DataTables, etc so I can't offer any help on how to bind a DataGridView to a database table. I've only ever used ADO and ADO.NET for database operations. I don't like all the layers of abstraction.