0 Replies - 551 Views - Last Post: 20 April 2013 - 11:16 AM Rate Topic: -----

#1 jscottcarson  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • 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")
        ListBox1.Items.Add("inventory or must be reordered.")
        ListBox1.Items.Add("")
        ListBox1.Items.Add("The numbers shown give the")
        ListBox1.Items.Add("minimum reorder quantity required.")
        ListBox1.Items.Add("")

        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

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'MICROLANDDataSet.Orders' table. You can move, or remove it, as needed.
        Me.OrdersTableAdapter.Fill(Me.MICROLANDDataSet.Orders)
        'TODO: This line of code loads data into the 'MICROLANDDataSet.Inventory' table. You can move, or remove it, as needed.
        Me.InventoryTableAdapter.Fill(Me.MICROLANDDataSet.Inventory)
        'TODO: This line of code loads data into the 'MICROLANDDataSet.Orders' table. You can move, or remove it, as needed.
        Me.OrdersTableAdapter.Fill(Me.MICROLANDDataSet.Orders)
        'TODO: This line of code loads data into the 'MICROLANDDataSet.Inventory' table. You can move, or remove it, as needed.
        Me.InventoryTableAdapter.Fill(Me.MICROLANDDataSet.Inventory)
        'TODO: This line of code loads data into the 'MICROLANDDataSet.Customers' table. You can move, or remove it, as needed.
        Me.CustomersTableAdapter.Fill(Me.MICROLANDDataSet.Customers)

    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
            ListBox1.Items.Add(row(1))
            ListBox1.Items.Add(row(2))
            ListBox1.Items.Add(row(3))
            ListBox1.Items.Add(" ")
        Next


    End Sub

   
End Class




Is This A Good Question/Topic? 0
  • +

Page 1 of 1