dcsimg
Results 1 to 17 of 17

Thread: VB.Net WF subtotal from listbox with data from access via SQL

  1. #1

    Thread Starter
    Member
    Join Date
    Jun 2018
    Posts
    40

    Post VB.Net WF subtotal from listbox with data from access via SQL

    Hello,
    Could anyone help with attempting to calculate and display a subtotal from a listbox which contains items with multiple pieces of data. The data arrives into the listbox from access via SQL. Each record contains multiple pieces of information including the prices which i need a subtotal for all of the prices of the items in the listbox. I would like the subtotal to be displayed in a separate textbox.
    Many thanks

  2. #2
    Member
    Join Date
    Jun 2018
    Posts
    33

    Re: VB.Net WF subtotal from listbox with data from access via SQL

    Option 1:
    Each record contains multiple pieces of information including the prices which i need a subtotal for all of the prices of the items in the listbox.
    Depending on how those pieces of information are separated (comma, spaces, ect...) you could split the data and retrieve only the price.

    For that solution you'll need to work with "indexOf()" and "substring()" or something similar.

    You should find everything you need here: https://msdn.microsoft.com/fr-fr/lib...v=vs.110).aspx

    (Recommended) Option 2:

    You might simply not be using the proper control for the job. You say there are multiple values per item therefore why not use "DataGridView".

    https://msdn.microsoft.com/en-us/lib...v=vs.110).aspx

    DataGridView (Or DataGrid in WPF) is closer to an excel sheet than a list and a safer bet then searching through text.

    Hope this helps you a bit on how to get your list of prices to work with,

    Good day to you,

    KBConsole

  3. #3

    Thread Starter
    Member
    Join Date
    Jun 2018
    Posts
    40

    Re: VB.Net WF subtotal from listbox with data from access via SQL

    Quote Originally Posted by KBConsole View Post
    Option 1:


    Depending on how those pieces of information are separated (comma, spaces, ect...) you could split the data and retrieve only the price.

    For that solution you'll need to work with "indexOf()" and "substring()" or something similar.

    You should find everything you need here: https://msdn.microsoft.com/fr-fr/lib...v=vs.110).aspx


    KBConsole
    My program acts as a transaction form where i use SQL to find the item in a database and bring it to the listbox with item name, type and price which is why i use the listbox.
    The items are separated using spaces. Any chance you could demostrate how to use the method 1 to split the data and retrieve the price.
    thanks

  4. #4
    Member
    Join Date
    Jun 2018
    Posts
    33

    Re: VB.Net WF subtotal from listbox with data from access via SQL

    Here is a piece of code so you have something to start with:

    Code:
     For Each ListedItem As Object In ListBox1.Items
          Dim StringArray() As String
          StringArray = ListedItem.Split(" "c)
                For Each s As String In StringArray
                    MessageBox.Show(s)
                Next
     Next
    Notice: It is in no way complete since multiple spaces would cause problems, just a sample.

    Wish you luck,

    KBConsole
    Last edited by KBConsole; Jun 14th, 2018 at 02:17 PM.

  5. #5
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    3,792

    Re: VB.Net WF subtotal from listbox with data from access via SQL

    It seems the me that your using the wrong control. A DataGridView is much better suited for what you want to do. The data would be displayed in columns and then all you need to do is sum the price column.

    Code:
                Dim tot As Decimal = 0
    
                For Each row As DataGridViewRow In Me.DataGridView1.Rows
                    tot += CDec(row.Cells("price").Value)
                Next
                MessageBox.Show(tot.ToString)
    Or if your using a DataTable as the datasource for the DGV,

    Code:
                For Each row As DataRow In yourdataTable.Rows
                    tot += CDec(row("Price"))
                Next
                MessageBox.Show(tot.ToString)
    Or you could retrieve the total from the Database using a SQL command "Select Sum(Price) as subtotal From yourTable Where itemName = 'someValue'"

    You really haven't shown use any of your code so I can't really say which is the best method for you.

  6. #6

    Thread Starter
    Member
    Join Date
    Jun 2018
    Posts
    40

    Re: VB.Net WF subtotal from listbox with data from access via SQL

    Quote Originally Posted by wes4dbt View Post
    It seems the me that your using the wrong control. A DataGridView is much better suited for what you want to do. The data would be displayed in columns and then all you need to do is sum the price column.

    Code:
                Dim tot As Decimal = 0
    
                For Each row As DataGridViewRow In Me.DataGridView1.Rows
                    tot += CDec(row.Cells("price").Value)
                Next
                MessageBox.Show(tot.ToString)
    Or if your using a DataTable as the datasource for the DGV,

    Code:
                For Each row As DataRow In yourdataTable.Rows
                    tot += CDec(row("Price"))
                Next
                MessageBox.Show(tot.ToString)
    Or you could retrieve the total from the Database using a SQL command "Select Sum(Price) as subtotal From yourTable Where itemName = 'someValue'"

    You really haven't shown use any of your code so I can't really say which is the best method for you.
    My apologies
    Upon the button being pressed I call upon a procedure which grabs the record via sql and stores it into four different listboxes which is my temporary workaround to calculating a subtotal.
    here is the code


    "Private Sub Button7_Click(sender As Object, e As EventArgs) Handles Button7.Click
    If IsNumeric(txtsearch.Text) Then
    AddItem(txtsearch.Text)
    End If
    End Sub"


    "Private Sub AddItem(Number As String)
    If Dbconnect() Then
    Dim SQLCmd As New OleDbCommand
    With SQLCmd
    .Connection = cn
    .CommandText = "Select * From Items Where ItemID = @SearchID"
    .Parameters.AddWithValue("@SearchID", txtsearch.Text)
    Dim rs As OleDbDataReader = .ExecuteReader()
    While rs.Read
    Dim Displayvalue As String = rs("ItemName")
    Dim Displayvalue2 As String = rs("ItemType")
    Dim displayvalue1 As String = rs("ItemPrice")
    Dim Displayvalue3 As String = rs("ItemID")
    ListBox1.Items.Add(Displayvalue)
    ListBox2.Items.Add(displayvalue1)
    ListBox3.Items.Add(Displayvalue2)
    ListBox4.Items.Add(Displayvalue3)
    End While
    End With
    End If
    End Sub"

  7. #7
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,222

    Re: VB.Net WF subtotal from listbox with data from access via SQL

    Why not simply calculate the total when you retrieve the data?

    eg:
    Code:
    Private Sub AddItem(Number As String)
            If Dbconnect() Then
                Dim SQLCmd As New OleDbCommand
                With SQLCmd
                    .Connection = cn
                    .CommandText = "Select * From Items Where ItemID = @SearchID"
                    .Parameters.AddWithValue("@SearchID", txtsearch.Text)
                    Dim rs As OleDbDataReader = .ExecuteReader()
                    Dim totalPrice as Decimal = 0D
                    While rs.Read
                        Dim Displayvalue As String = rs("ItemName")
                        Dim Displayvalue2 As String = rs("ItemType")
                        Dim displayvalue1 As String = rs("ItemPrice")
                        Dim Displayvalue3 As String = rs("ItemID")
                        totalPrice += rs("ItemPrice")
                        ListBox1.Items.Add(Displayvalue)
                        ListBox2.Items.Add(displayvalue1)
                        ListBox3.Items.Add(Displayvalue2)
                        ListBox4.Items.Add(Displayvalue3)
                    End While
                    MessageBox.Show(totalPrice.ToString)
                End With
            End If
        End Sub
    By the way, I don't understand why you are choosing to use multiple ListBoxes (separate controls, with one column each) rather than a DataGrid (one control, multiple columns)

  8. #8
    Member
    Join Date
    Jun 2018
    Posts
    33

    Re: VB.Net WF subtotal from listbox with data from access via SQL

    Dim Displayvalue As String = rs("ItemName")
    Dim Displayvalue2 As String = rs("ItemType")
    Dim displayvalue1 As String = rs("ItemPrice")
    Dim Displayvalue3 As String = rs("ItemID")
    ListBox1.Items.Add(Displayvalue)
    ListBox2.Items.Add(displayvalue1)
    ListBox3.Items.Add(Displayvalue2)
    ListBox4.Items.Add(Displayvalue3)
    Is that the order you want them in ? So: Name/Price/Type/ID ?

    Change Listbox for DataGridView and replace your code with this:

    Code:
    Private Sub AddItem(Number As String)
    If Dbconnect() Then
                Dim SQLCmd As New OleDbCommand
                With SQLCmd
                    .Connection = cn
                    .CommandText = "Select * From Items Where ItemID = @SearchID"
                    .Parameters.AddWithValue("@SearchID", txtsearch.Text)
                    Dim rs As OleDbDataReader = .ExecuteReader()
                    While rs.Read
                        Dim Displayvalue As String = rs("ItemName")
                        Dim Displayvalue1 As String = rs("ItemPrice")
                        Dim Displayvalue2 As String = rs("ItemType")
                        Dim Displayvalue3 As String = rs("ItemID")
                        DataGridView1.ColumnCount = 4
                        DataGridView1.Columns(0).Name = "Product Name"
                        DataGridView1.Columns(1).Name = "Product Type"
                        DataGridView1.Columns(2).Name = "Product Price"
                        DataGridView1.Columns(3).Name = "Product ID"
    
                        Dim NewRow As String() = New String() {Displayvalue, Displayvalue1, Displayvalue2, Displayvalue3}
                        DataGridView1.Rows.Add(NewRow)
                    End While
                End With
            End If
    End Sub
    Or the shorter version:

    Code:
    Private Sub AddItem(Number As String)
    If Dbconnect() Then
                Dim SQLCmd As New OleDbCommand
                With SQLCmd
                    .Connection = cn
                    .CommandText = "Select * From Items Where ItemID = @SearchID"
                    .Parameters.AddWithValue("@SearchID", txtsearch.Text)
                    Dim rs As OleDbDataReader = .ExecuteReader()
                    While rs.Read
                        DataGridView1.ColumnCount = 4
                        DataGridView1.Columns(0).Name = "Product Name"
                        DataGridView1.Columns(1).Name = "Product Type"
                        DataGridView1.Columns(2).Name = "Product Price"
                        DataGridView1.Columns(3).Name = "Product ID"
    
                        Dim NewRow As String() = New String() {rs("ItemName"), rs("ItemPrice"), rs("ItemType"), rs("ItemID")}
                        DataGridView1.Rows.Add(NewRow)
                    End While
                End With
            End If
    End Sub
    Use wes4dbt's example to calculate subtotal:

    Dim tot As Decimal = 0

    For Each row As DataGridViewRow In Me.DataGridView1.Rows
    tot += CDec(row.Cells("price").Value)
    Next

    MessageBox.Show(tot.ToString)
    Of course change "tot" for the var you are using and "price" for the appropriate column ect..

    Here's a useful link for a different approach:

    http://vb.net-informations.com/datag...ew_binding.htm

    Hope this helps,

    KBConsole

  9. #9

    Thread Starter
    Member
    Join Date
    Jun 2018
    Posts
    40

    Re: VB.Net WF subtotal from listbox with data from access via SQL

    Quote Originally Posted by KBConsole View Post
    Is that the order you want them in ? So: Name/Price/Type/ID ?

    Change Listbox for DataGridView and replace your code with this:

    Code:
    Private Sub AddItem(Number As String)
    If Dbconnect() Then
                Dim SQLCmd As New OleDbCommand
                With SQLCmd
                    .Connection = cn
                    .CommandText = "Select * From Items Where ItemID = @SearchID"
                    .Parameters.AddWithValue("@SearchID", txtsearch.Text)
                    Dim rs As OleDbDataReader = .ExecuteReader()
                    While rs.Read
                        Dim Displayvalue As String = rs("ItemName")
                        Dim Displayvalue1 As String = rs("ItemPrice")
                        Dim Displayvalue2 As String = rs("ItemType")
                        Dim Displayvalue3 As String = rs("ItemID")
                        DataGridView1.ColumnCount = 4
                        DataGridView1.Columns(0).Name = "Product Name"
                        DataGridView1.Columns(1).Name = "Product Type"
                        DataGridView1.Columns(2).Name = "Product Price"
                        DataGridView1.Columns(3).Name = "Product ID"
    
                        Dim NewRow As String() = New String() {Displayvalue, Displayvalue1, Displayvalue2, Displayvalue3}
                        DataGridView1.Rows.Add(NewRow)
                    End While
                End With
            End If
    End Sub
    Or the shorter version:

    Code:
    Private Sub AddItem(Number As String)
    If Dbconnect() Then
                Dim SQLCmd As New OleDbCommand
                With SQLCmd
                    .Connection = cn
                    .CommandText = "Select * From Items Where ItemID = @SearchID"
                    .Parameters.AddWithValue("@SearchID", txtsearch.Text)
                    Dim rs As OleDbDataReader = .ExecuteReader()
                    While rs.Read
                        DataGridView1.ColumnCount = 4
                        DataGridView1.Columns(0).Name = "Product Name"
                        DataGridView1.Columns(1).Name = "Product Type"
                        DataGridView1.Columns(2).Name = "Product Price"
                        DataGridView1.Columns(3).Name = "Product ID"
    
                        Dim NewRow As String() = New String() {rs("ItemName"), rs("ItemPrice"), rs("ItemType"), rs("ItemID")}
                        DataGridView1.Rows.Add(NewRow)
                    End While
                End With
            End If
    End Sub
    Use wes4dbt's example to calculate subtotal:



    Of course change "tot" for the var you are using and "price" for the appropriate column ect..

    Here's a useful link for a different approach:

    http://vb.net-informations.com/datag...ew_binding.htm

    Hope this helps,

    KBConsole
    Hi,
    i used a listbox as i had never used datagridview before but now i have with your method and experienced it being much easier so a big thanks! Just one more thing would be how would i be able to pass the datagridview data from one datagrid to another in a different form.
    Thanks

  10. #10
    Member
    Join Date
    Jun 2018
    Posts
    33

    Re: VB.Net WF subtotal from listbox with data from access via SQL

    *EDIT*

    Previously posted code was not tested and does not work, sorry for misleading you.

    See below for proper example.

    Sincerely,

    KBConsole
    Last edited by KBConsole; Jun 15th, 2018 at 07:25 AM.

  11. #11

    Thread Starter
    Member
    Join Date
    Jun 2018
    Posts
    40

    Re: VB.Net WF subtotal from listbox with data from access via SQL

    Quote Originally Posted by KBConsole View Post
    In your second form:

    Code:
    NewDGV.DataSource = Form1.DataGridView1
    "NewDGV" being the name you gave to the datagrid in your second form so feel free to change it.

    Glad that helped you out and good luck with the next steps,

    KBConsole
    I tried this in the second form with no luck. I did rename the datagrid. Any suggestions?

  12. #12
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    3,792

    Re: VB.Net WF subtotal from listbox with data from access via SQL

    Code:
    Just one more thing would be how would i be able to pass the datagridview data from one datagrid to another in a different form.
    Yeah I was a little surprised at KBConsole suggestion. This is a different subject and it would be best to start a new thread. But before you do try Googling "visual basic pass data between forms". There's lots of information on this subject.

    If you need more help then make sure to post your current code that creates the data and how the second form is called.

  13. #13
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,063

    Re: VB.Net WF subtotal from listbox with data from access via SQL

    For future reference, WF is officially Windows Workflow Foundation in the .NET world. If you're actually referring to Windows Forms then you should say "Windows Forms" or "WinForms". I avoided this thread specifically because I thought that it was a Workflow question.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  14. #14
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,063

    Re: VB.Net WF subtotal from listbox with data from access via SQL

    Quote Originally Posted by wes4dbt View Post
    But before you do try Googling "visual basic pass data between forms". There's lots of information on this subject.
    E.g. the Data Among Multiple Forms link in my signature below.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  15. #15
    Member
    Join Date
    Jun 2018
    Posts
    33

    Re: VB.Net WF subtotal from listbox with data from access via SQL

    Tested this code from: https://stackoverflow.com/questions/...r-datagridview

    Code:
    'References to source and target grid.
    
            Dim sourceGrid As DataGridView = Form1.DataGridView1
            Dim targetGrid As DataGridView = Me.DataGridView1
    
            'Copy all rows and cells.
    
            Dim targetRows = New List(Of DataGridViewRow)
    
            For Each sourceRow As DataGridViewRow In sourceGrid.Rows
    
                If (Not sourceRow.IsNewRow) Then
    
                    Dim targetRow = CType(sourceRow.Clone(), DataGridViewRow)
    
                    'The Clone method do not copy the cell values so we must do this manually.
                    'See: https://msdn.microsoft.com/en-us/library/system.windows.forms.datagridviewrow.clone(v=vs.110).aspx
    
                    For Each cell As DataGridViewCell In sourceRow.Cells
                        targetRow.Cells(cell.ColumnIndex).Value = cell.Value
                    Next
    
                    targetRows.Add(targetRow)
    
                End If
    
            Next
    
            'Clear target columns and then clone all source columns.
    
            targetGrid.Columns.Clear()
    
            For Each column As DataGridViewColumn In sourceGrid.Columns
                targetGrid.Columns.Add(CType(column.Clone(), DataGridViewColumn))
            Next
    
            'It's recommended to use the AddRange method (if available)
            'when adding multiple items to a collection.
    
            targetGrid.Rows.AddRange(targetRows.ToArray())
    Works like a charm for cloning the whole table.

    If you only want specific values I use the following with loops:

    Code:
    MyValue = (MyDataGridView.Rows(MyRowNumber).Cells(MyColumnNumber).Value)
    Note: Depending on the usage this may be slow, anyone is welcome to suggest better (this should however be a separate thread i believe)

    KBConsole

  16. #16

    Thread Starter
    Member
    Join Date
    Jun 2018
    Posts
    40

    Re: VB.Net WF subtotal from listbox with data from access via SQL

    Quote Originally Posted by KBConsole View Post
    Tested this code from: https://stackoverflow.com/questions/...r-datagridview

    Code:
    'References to source and target grid.
    
            Dim sourceGrid As DataGridView = Form1.DataGridView1
            Dim targetGrid As DataGridView = Me.DataGridView1
    
            'Copy all rows and cells.
    
            Dim targetRows = New List(Of DataGridViewRow)
    
            For Each sourceRow As DataGridViewRow In sourceGrid.Rows
    
                If (Not sourceRow.IsNewRow) Then
    
                    Dim targetRow = CType(sourceRow.Clone(), DataGridViewRow)
    
                    'The Clone method do not copy the cell values so we must do this manually.
                    'See: https://msdn.microsoft.com/en-us/library/system.windows.forms.datagridviewrow.clone(v=vs.110).aspx
    
                    For Each cell As DataGridViewCell In sourceRow.Cells
                        targetRow.Cells(cell.ColumnIndex).Value = cell.Value
                    Next
    
                    targetRows.Add(targetRow)
    
                End If
    
            Next
    
            'Clear target columns and then clone all source columns.
    
            targetGrid.Columns.Clear()
    
            For Each column As DataGridViewColumn In sourceGrid.Columns
                targetGrid.Columns.Add(CType(column.Clone(), DataGridViewColumn))
            Next
    
            'It's recommended to use the AddRange method (if available)
            'when adding multiple items to a collection.
    
            targetGrid.Rows.AddRange(targetRows.ToArray())
    Works like a charm for cloning the whole table.

    If you only want specific values I use the following with loops:

    Code:
    MyValue = (MyDataGridView.Rows(MyRowNumber).Cells(MyColumnNumber).Value)
    Note: Depending on the usage this may be slow, anyone is welcome to suggest better (this should however be a separate thread i believe)

    KBConsole
    *EDIT*
    My mistake i incorrectly entered the code. Works like a charm thank you.
    Last edited by snoopyd; Jun 16th, 2018 at 06:16 AM.

  17. #17
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,222

    Re: VB.Net WF subtotal from listbox with data from access via SQL

    It belongs in the second form, but the Load event is a dubious place to put code (in some circumstances it can hide error messages from you), so it is better to use the Shown event instead.

    Moving the code there might show you an explanation of why it isn't behaving as expected.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width