# Using linq to do math equations... I think..lol

Page 1 of 1

## 0 Replies - 832 Views - Last Post: 20 April 2013 - 11:16 AMRate Topic: //<![CDATA[ rating = new ipb.rating( 'topic_rate_', { url: 'http://www.dreamincode.net/forums/index.php?app=forums&module=ajax&section=topics&do=rateTopic&t=319079&amp;s=7407eb694c5b951087d87c7b2cafeed4&md5check=' + ipb.vars['secure_hash'], cur_rating: 0, rated: 0, allow_rate: 0, multi_rate: 1, show_rate_text: true } ); //]]>

### #1 jscottcarson

Reputation: 0
• Posts: 3
• Joined: 10-April 13

# Using linq to do math equations... I think..lol

Posted 20 April 2013 - 11:16 AM

I am in school and this is for a class. I am not looking for the code necessarily. I am truly trying to learn this. The assignment is to join 2 tables of an access database (inventory and orders) on a key using linq and subtract the orders quantity column from the inventory quantity column to generate products ( by itemID columns ) that have a 0 (or less) quantity. The orders table has columns labeled custID, ItemID, quantity. The Inventory table has itemID, description, price, quantity.

I am able to write the linq that does the join. My question is how I should be thinking to subtract the orders.quantity column from the inventory.quantity and return the results? Specifically, the items that end up with a 0 value or less?

My code is below. If I use orders.quantity - inventory.quantity it returns the difference per field. The issue is that the orders are listed using multiple instances of the same itemID. So item 2 is listed 3 times once with a quantity of 100, once with 200, once with 75. So if the beginning quantity in inventory for item 2 is 500, it is returning 3 field with quantities of 400, 300, 425, when I need it to return one quantity per item id, or, 125 for item 2. I hope this makes sense. I'm just not sure how to perform the math after the linq join given these conditions. Do I handle the math in the linq statement or in a for each after the linq somehow? Not sure how to approach it. Any guidance is appreciated.

```

Imports System.Data
Imports System.Data.OleDb

Public Class Microland

Dim objcon As New OleDbConnection("provider= microsoft.ace.oledb.12.0;Data Source = C:\Users\Administrator\Documents\Visual Studio 2012\Projects\Prohect 9\Prohect 9\bin\Debug\MICROLAND.accdb; Persist Security Info=False;")

Private Sub btnOos_Click(sender As Object, e As EventArgs) Handles btnOos.Click

Dim ConnectionString As String = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source = C:\Users\Administrator\Documents\Visual Studio 2012\Projects\Prohect 9\Prohect 9\bin\Debug\MICROLAND.accdb"
Dim AccessConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)

Dim dt As New DataTable
Using da As New OleDbDataAdapter("SELECT custID, itemID, quantity FROM Orders", AccessConnection)
da.Fill(dt)
End Using

Dim dt1 As New DataTable
Using da1 As New OleDbDataAdapter("SELECT itemID, description, price, quantity FROM Inventory", AccessConnection)
da1.Fill(dt1)
End Using

ListBox1.Items.Add("Here are the items that are out of")

Dim query = From number In MICROLANDDataSet.Inventory
Join othernumber In MICROLANDDataSet.Orders
On number.itemID Equals othernumber.itemID
Select number.itemID, othernumber

ListBox1.DataSource = query.ToList
End Sub

'TODO: This line of code loads data into the 'MICROLANDDataSet.Orders' table. You can move, or remove it, as needed.
'TODO: This line of code loads data into the 'MICROLANDDataSet.Inventory' table. You can move, or remove it, as needed.
'TODO: This line of code loads data into the 'MICROLANDDataSet.Orders' table. You can move, or remove it, as needed.
'TODO: This line of code loads data into the 'MICROLANDDataSet.Inventory' table. You can move, or remove it, as needed.
'TODO: This line of code loads data into the 'MICROLANDDataSet.Customers' table. You can move, or remove it, as needed.

End Sub

Private Sub btnBills_Click(sender As Object, e As EventArgs) Handles btnBills.Click
Dim ConnectionString As String = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source = C:\Users\Administrator\Documents\Visual Studio 2012\Projects\Prohect 9\Prohect 9\bin\Debug\MICROLAND.accdb"
Dim AccessConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)

Dim dt2 As New DataTable
Using da As New OleDbDataAdapter("SELECT custID, name, street, city, amtPurchases FROM Customers", AccessConnection)
da.Fill(dt2)
End Using

For Each row In dt2.Rows
Next

End Sub

End Class

```

Is This A Good Question/Topic? 0

Page 1 of 1

 .related ul{list-style-type:circle;font-size:12px;font-weight:bold;}.related li{margin-bottom:5px;background-position:left 7px!important;margin-left:-35px;}.related h2{font-size:18px;font-weight:bold;}.related a{color:blue;}