ListView Question...

[imarc]

Hello all! I have hit a small bump in my development of a program I am working on. Basically what I need to happen is this; for each item in the listview it executes a SQL query to input values into a database. In this case it pulls the quantity from the listview for each entry, it then (by means of a SQL query) adds the quantity of the entry in the listview to the existing quantity of that entry in the database. Here is the pseudocode I am working on to do this. Make corrections and suggestions as needed as this isn't yet solved.

    Private Sub Button5_Click(sender As System.Object, e As System.EventArgs) Handles Button5.Click
        Dim lvi As ListViewItem
        For Each lvi In ListView1.Items
            Call Back2Inv()
        Next
    End Sub

    Private Sub Back2Inv()
        ds = New DataSet
        da = New MySqlDataAdapter("update inventory set qty='" & ListView1.SelectedItems(0).SubItems(1).Text & "'where id='" & ListView1.SelectedItems(0).SubItems(0).Text & "'", mysqlconn)
        da.Fill(ds, "inventory")
    End Sub

[Maligui]

That is quite a toll on the database. I would build the entire dataset first, the apply the updates after the dataset is made/updated.

DataSet:

• Id
• Quantity

Code:

Dim ds As New DataSet ' Fill this from the database.


Private Sub Button5_Click(sender As System.Object, e As System.EventArgs) Handles Button5.Click
    Dim lvi As ListViewItem         
    For i = 0 To ListView1.Items.Count -1
        Dim lvi As ListViewItem = ListView1(i)

        ' add each item to the dataset.
        Dim Id as Integer
        Dim Quantity As Integer

        ' we need to convert the text to an integer
        Integer.Parse(lvi.Text, Integer)
        Integer.Parse(lvi.SubItems(0).Text, Quantity)

        ' add the quantity in the list view to the quantities in the database
        ds.Add(Id,Quantity)

    Next

    ' update the database
    ds.Update()
End Sub 

[imarc]

I like this idea, however I'm having difficulty translating it into workable MySQL functions. I tried the code you supplied but was generated a few errors that I am stuck on. I will highlight below.

Private Sub Button5_Click(sender As System.Object, e As System.EventArgs) Handles Button5.Click
        Dim lvi As ListViewItem
        For i = 0 To SalesView.Items.Count - 1
            Dim lvi As ListViewItem = SalesView(i)

            ' add each item to the dataset.
            Dim Id As Integer
            Dim Quantity As Integer

            ' we need to convert the text to an integer
            Integer.Parse(lvi.Text, Integer)
            Integer.Parse(lvi.SubItems(0).Text, Quantity)

            ' add the quantity in the list view to the quantities in the database
            ds.Add(Id, Quantity)

         Next()

        ' update the database
        ds.Update()
    End Sub

I am getting errors at "Dim lvi" and also the "=SalesView(i)"
Also at Integer.Parse(lvi.Text, Integer)
And at ds.Add and ds.Update with the error "is not a member of System.Data.DataSet

Some assistance would be helpful!

Thanks!

[Maligui]

You have lvi declared before the loop, remove that.

Here is what I came up with

' Dataset
' Table Name:Quantities
'   Id:         Integer
'   Quantity:   Integer

' Declare the new dataset
Dim ds As New DataSet1
' Declare a data adapter for the dataset
Dim da As New OleDbDataAdapter("SQL", "Connection")

For i = 0 To SalesView.Items.Count - 1
    Dim lvi As ListViewItem = SalesView(i)

    ' add each item to the dataset.
    Dim Id As Integer
    Dim Quantity As Integer

    ' we need to convert the text to an integer
    Id = Integer.Parse(lvi.Text)
    Quantity = Integer.Parse(lvi.SubItems(0).Text)

    ' create a new row
    Dim row As New DataSet1.QuantitiesRow(8, 15)

    ' you can use this way too
    Dim anotherrow = ds.Quantities.NewQuantitiesRow
    anotherrow.Id = Id
    anotherrow.Quantity = Quantity

    ' add the new row to the dataset
    ds.Quantities.AddQuantitiesRow(row)
Next


' Accept changes made to the dataset
' this is good if the dataset is already filled, 
' and the data value changes as well.
ds.AcceptChanges()

' update the database
da.Update(ds)

[imarc]

I'm not like most users that just leave when they figure it out. Here is basically what I done to make sure that my program works the way that I need it to do. My initial goal was to make my program basically "read" the quantities of the items in the list view and match their UPC's to put the quantity that was checked out of the database back in.

First, let me outline the data structure. I have 3 tables, sales, salesticket, and inventory. A new sale is created with a button and saved in the database. Then when one clicks the add items buttons they select an item from the inventory, enter how many to put on the ticket, and click okay. The data is then transferred to a sales ticket that is unique to the order that was created prior. The qty that was checked out is then updated to the database to reflect the changes.

Howerver, and this is where this thread was going, if a customer decided to cancel the order they could cancel it and then all inventory be placed back into the database and all sales ticket items and the initial sale is deleted from the database. Do keep in mind I am doing all this using MySQL as the SQL provider. So the code is going to reflect that.

    Private Sub Button5_Click(sender As System.Object, e As System.EventArgs) Handles Button5.Click
        Try
            SalesView.Items(0).Selected = True
            SalesView.Select()
            Dim lvi As ListViewItem
            For Each lvi In SalesView.Items
                Call Back2Inv()
            Next
            'Deletes the sale from the database
            ds = New DataSet
            da = New MySqlDataAdapter("delete from sales where id = '" & TextBox1.Text & "'", mysqlconn)
            da.Fill(ds, "sales")
            'deletes the contents of the sale from the database
            ds = New DataSet
            da = New MySqlDataAdapter("delete from saleticket where forsaleid = '" & TextBox1.Text & "'", mysqlconn)
            da.Fill(ds, "salesticket")
            'clears all the textboxes that display sale info
            TextBox1.Text = ""
            TextBox2.Text = ""
            TextBox3.Text = ""
            TextBox4.Text = ""
            TextBox5.Text = ""
            TextBox6.Text = ""
            TextBox7.Text = "$0.00"
            'displays a message letting the user know that it has been canceled
            MessageBox.Show("The order has been canceled.", "*** | Sales", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Catch ex As Exception
            MessageBox.Show("There is no active order to cancel.", "*** | Sales", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End Try

    End Sub

    Public Sub Back2Inv()
        SalesView.Items(0).Selected = True
        SalesView.Select()
        ds = New DataSet
        da = New MySqlDataAdapter("update inventory set qty=`qty`+'" & SalesView.Items(0).SubItems(1).Text & "' where upc='" & SalesView.Items(0).SubItems(4).Text & "'", mysqlconn)
        da.Fill(ds, "inventory")
        SalesView.Items(0).Remove()

    End Sub

A big thanks to Maligui that got me heading in the right direction!