increment or loop, How? help me Good Sirs.

[mokushirokuxen]

     i have 2 database listview1(from tbl_storage) listview2(fromsales) so i want my listview items and subitems to be increment or be loop correct me if im wrong with that.
     sellingprice and Qty.(Quantity)

     ex.

itemno | name | qty| price | total |

    1      |   a      | 1 | 100  | 100 |

    1      |   a      | 1 | 100  | 100 |

it should be looking like this

    1      |   a      | 2 | 100 | 200 |

i want to loop it so when i add the same item it wont duplicate in listview2
     here's my whole code:

     Public Class FormSales
        Dim cls As New Class1
        Dim item As ListViewItem
        Private Sub FormitemregST_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            cls.setconnection("localhost", "root", "root", "a9dbase")
            cls.sqlcmd.CommandText = "SELECT * FROM tblinventory"
            cls.mycon.Open()
            cls.mydr = cls.sqlcmd.ExecuteReader
            Me.ListView3.Items.Clear()
            While cls.mydr.Read
                item = Me.ListView3.Items.Add(cls.mydr.GetInt32("itemno"))
                Me.item.SubItems.Add(cls.mydr.GetString("itemcategory"))
                Me.item.SubItems.Add(cls.mydr.GetString("itemname"))
                Me.item.SubItems.Add(cls.mydr.GetInt32("sellingprice"))
                Me.item.SubItems.Add(cls.mydr.GetInt32("stocks"))
                Me.item.SubItems.Add(cls.mydr.GetString("unit"))
                btnhandler("100000")
                txtboxlock()
            End While
            cls.mycon.Close()
        End Sub

        Public Sub getrecords()
            cls.sqlcmd.CommandText = "SELECT * FROM tblinventory"
            cls.mycon.Open()
            cls.mydr = cls.sqlcmd.ExecuteReader
            Me.ListView3.Items.Clear()
            While cls.mydr.Read
                item = Me.ListView3.Items.Add(cls.mydr.GetInt32("itemno"))
                Me.item.SubItems.Add(cls.mydr.GetString("itemcategory"))
                Me.item.SubItems.Add(cls.mydr.GetString("itemname"))
                Me.item.SubItems.Add(cls.mydr.GetInt32("sellingprice"))
                Me.item.SubItems.Add(cls.mydr.GetInt32("stocks"))
            End While
            cls.mycon.Close()
        End Sub
    #Region "BUTTON HANDLER"
        Public Sub btnhandler(ByVal status As String)
            btnneworder.Enabled = IIf(status.Substring(0, 1) = "1", True, False)
            btnadd.Enabled = IIf(status.Substring(1, 1) = "1", True, False)
            btnremove.Enabled = IIf(status.Substring(2, 1) = "1", True, False)
            btncancel.Enabled = IIf(status.Substring(3, 1) = "1", True, False)
            btnsave.Enabled = IIf(status.Substring(4, 1) = "1", True, False)
            btnpurchase.Enabled = IIf(status.Substring(5, 1) = "1", True, False)
        End Sub
    #End Region

        Private Sub btnneworder_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnneworder.Click
            btnhandler("011100")
            txtboxunlock()
        End Sub

        Private Sub btnadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnadd.Click
            Dim temp As Integer
            temp = listview2.Items.Count()
            If temp = 0 Then
                Dim i As Integer
                Dim lst As New ListViewItem(i)
                item = listview2.Items.Add(ListView3.FocusedItem.Text)
                item.SubItems.Add(ListView3.FocusedItem.SubItems(1).Text)
                item.SubItems.Add(ListView3.FocusedItem.SubItems(2).Text)
                item.SubItems.Add(ListView3.FocusedItem.SubItems(3).Text)
                item.SubItems.Add("1")
                i = i + 1
            End If
            ListView3.FocusedItem.SubItems(4).Text = Val(ListView3.FocusedItem.SubItems(4).Text) - 1

            cls.sqlcmd.CommandText = "update tblinventory set " & _
         "stocks='" & ListView3.FocusedItem.SubItems(4).Text & "' where itemno='" & (ListView3.FocusedItem.Text) & "';"

            cls.mycon.Open()
            cls.sqlcmd.ExecuteReader()
            cls.mycon.Close()

            btnhandler("011111")
        End Sub

        Private Sub btnremove_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnremove.Click
            'update stocks
            cls.mycon.Open()
            Dim stocks As Integer
            cls.sqlcmd.CommandText = "Select * from tblinventory where itemno='" & (listview2.FocusedItem.Text) & "';"
            cls.mydr = cls.sqlcmd.ExecuteReader
            cls.mydr.Read()
            If cls.mydr.HasRows = True Then
                stocks = cls.mydr.GetInt32("stocks")
            End If
            cls.mycon.Close()

            stocks = Val(stocks) + 1
            cls.sqlcmd.CommandText = "update tblinventory set " & _
       "stocks='" & stocks & "' where itemno='" & (listview2.FocusedItem.Text) & "';"
            cls.mycon.Open()
            cls.sqlcmd.ExecuteReader()
            cls.mycon.Close()
            listview2.FocusedItem.Remove()
            MsgBox("Item has Been Remove", MsgBoxStyle.Information, "Messaged")
            getrecords()
            btnhandler("011100")

            Exit Sub
        End Sub

        Private Sub btnpurchase_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnpurchase.Click
            Dim response As MsgBoxResult
            response = MsgBox("Do you want to Continue Purchase?", MsgBoxStyle.Question + MsgBoxStyle.YesNo, "Confirm")
            If response = MsgBoxResult.Yes Then
                MsgBox("Transaction Successful", MsgBoxStyle.Information, "Messaged")
            ElseIf response = MsgBoxResult.No Then
                Return
            End If
            btnhandler("100101")


        End Sub

        Private Sub btnsearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsearch.Click
            cls.sqlcmd.CommandText = "SELECT * FROM tblinventory where itemno='" & Me.txtsearch.Text & "';"
            cls.mycon.Open()
            cls.mydr = cls.sqlcmd.ExecuteReader
            Me.ListView3.Items.Clear()
            While cls.mydr.Read
                item = Me.ListView3.Items.Add(cls.mydr.GetInt32("itemno"))
                Me.item.SubItems.Add(cls.mydr.GetString("itemcategory"))
                Me.item.SubItems.Add(cls.mydr.GetString("itemname"))
                Me.item.SubItems.Add(cls.mydr.GetInt32("sellingprice"))
                Me.item.SubItems.Add(cls.mydr.GetInt32("stocks"))
            End While
            cls.mycon.Close()
        End Sub

        Private Sub btnrefresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnrefresh.Click
            getrecords()
            txtsearch.Clear()
        End Sub

        Private Sub btncancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btncancel.Click
            listview2.Items.Clear()
            MsgBox("Transaction Canceled", MsgBoxStyle.Information)
            btnhandler("100000")
            txtboxlock()
        End Sub

        Public Sub txtboxlock()
            txtrecieptno.Enabled = False
            txtsoldto.Enabled = False
            txtpayment.Enabled = False
            txttrans.Enabled = False
            txtdatepurchased.Enabled = False
        End Sub

        Public Sub txtboxunlock()
            txtrecieptno.Enabled = True
            txtsoldto.Enabled = True
            txtpayment.Enabled = True
            txttrans.Enabled = True
            txtdatepurchased.Enabled = True
        End Sub

        Private Sub btnsave_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click
            If Len(Trim(txtrecieptno.Text)) = 0 Then
                MessageBox.Show("Reciept No. Missing!", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                txtrecieptno.Focus()
                Exit Sub
            End If
            If Len(Trim(txtsoldto.Text)) = 0 Then
                MessageBox.Show("Name of Buyer Missing!", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                txtsoldto.Focus()
                Exit Sub
            End If
            If Len(Trim(txtpayment.Text)) = 0 Then
                MessageBox.Show("Payment Method Missing!", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                txtpayment.Focus()
                Exit Sub
            End If
            If Len(Trim(txttrans.Text)) = 0 Then
                MessageBox.Show("Transaction Method Missing!", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                txttrans.Focus()
                Exit Sub
            End If
            If Len(Trim(txtdatepurchased.Text)) = 0 Then
                MessageBox.Show("Purchased Date Missing!", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                txtdatepurchased.Focus()
                Exit Sub
            End If

            For x = 0 To listview2.Items.Count - 1
                cls.sqlcmd.CommandText = "insert into tbl_sales(itemno, itemcateg, itemname, sellingprice, quantity, recieptno, soldto, payment, transaction, datepurchased) values('" & _
              listview2.Items(x).SubItems(0).Text & "'," & _
           "'" & listview2.Items(x).SubItems(1).Text & "'," & _
           "'" & listview2.Items(x).SubItems(2).Text & "'," & _
           "'" & listview2.Items(x).SubItems(3).Text & "'," & _
           "'" & listview2.Items(x).SubItems(4).Text & "'," & _
            "'" & txtrecieptno.Text & "'," & _
            "'" & txtsoldto.Text & "'," & _
            "'" & txtpayment.Text & "'," & _
            "'" & txttrans.Text & "'," & _
            "'" & txtdatepurchased.Text & "');"

                cls.mycon.Open()
                cls.sqlcmd.ExecuteNonQuery()
                cls.mycon.Close()
            Next
            MessageBox.Show("Transaction Save!", "Message", MessageBoxButtons.OK)
            btnhandler("100001")
        End Sub
    End Class

[G_Waddell]

Wow! Thats a lot of Code to go through!

So I'll just go back to your question... You have a list of items and for each item you want to give total in stock and cash value.

OK there are two ways to do this:

1.Through a SQL query using SUM on the quantity and on the total and grouping by the other item fields : SELECT itemno, name, SUM(qty) As qty, price, SUM(Total) as Total FROM tblInventory GROUP BY itemno, name, price
2. Or as you said by looping through the records:

dim itemNo, CurrentItem as integer
dim ItemName as string
dim UnitPrice as decimal
dim TotalPrice as decimal
dim InStock, Qty as Integer
dim item as ListItem

CurrentItem = 0
'.....
'Modify your Query to sort by ItemNo
cls.sqlcmd.CommandText = "SELECT * FROM tblinventory Order By itemno"
'....
While cls.mydr.Read
    itemNo = cls.mydr("itemno")
    'Is this a new item?
    if itemNo <> CurrentItem then
        'Is this the first item?
        if CurrentItem <> 0 then
            'add previous item
            item = ListView3.Items.Add(CurrentItem)
            item.SubItems.Add(ItemName)
            item.SubItems.Add(InStock)
            item.SubItems.Add(UnitPrice)
            item.SubItems.Add(TotalPrice)
        end if
        'start again
        CurrentItem = itemNo
        ItemName = cls.mydr("itemname")
        InStock = 0
        TotalPrice = 0
     end if
     UnitPrice = cls.mydr("sellingprice")
     'Increment
     TotalPrice += UnitPrice
     Qty = cls.mydr("stocks")
     'Increment
     InStock += Qty
While End
'Get Last item...
if CurrentItem <> 0 then
    item = ListView3.Items.Add(CurrentItem)
    item.SubItems.Add(ItemName)
    item.SubItems.Add(InStock)
    item.SubItems.Add(UnitPrice)
    item.SubItems.Add(TotalPrice)
end if

[mokushirokuxen]

i will try this Sir thank you :) i will let you know if it works. God bless.