The first query works. But I wonder how can I make another query getting the sum ? ?
VB.NET:
cmd.Connection = conn
conn.Open()
Dim ds As New DataSet
Dim ds2 As New DataSet
cmd.CommandText = "SELECT PadFile.date, PadFile.apn, PadFile.store, PadFile.soh, PadFile.ordered, PadFile.sales_replen, Product.packsize FROM PadFile INNER JOIN Product ON PadFile.apn = Product.apn WHERE date = '071231'"
da.Fill(ds, "data")
conn.Close()
For Each dt As DataTable In ds.Tables
For Each dr As DataRow In dt.Rows
cmd.Connection = conn
conn.Open()
cmd.CommandText = "SELECT sum(ordered) as ordered, sum(delivered) as delivered FROM EDIFile WHERE date = @date AND apn = @apn AND store = @store"
cmd.Parameters.Clear()
cmd.Parameters.AddWithValue("@date", "080204")
cmd.Parameters.AddWithValue("@apn", Trim(dr.Item("apn")))
cmd.Parameters.AddWithValue("@store", Trim(dr.Item("store")))
da2.Fill(ds2, "data")
conn.Close()
For Each dt2 As DataTable In ds2.Tables
For Each dr2 As DataRow In dt2.Rows
MsgBox(Trim(dr2.Item("ordered")))
Next dr2
Next dt2
Next dr
Next dt
MsgBox("update completed")