Hi Team

Iv taken a look over the forum and I cant quiet find what I am after, I also apologize as what I am about to ask assistance for is for something I have little to no previous experience with (Its been like 17 years since I have worked in VB). I have tried to find a good basic tutorial online as well but they are not what I want. So this might be a bit long winded as I will try to explain all the things I'm trying to do, and if anyone is willing to assist then I would be very grateful for your time.

To give a little background on what I am trying to do: I have a program that I made built on VBA in Excel it works 100% the way I want and expect it to. I would now like to migrate this over to VB.NET so that the computers it can be used on wont require Excel to be installed on them.

The program is an inventory program that reads from a MS Access Database, it shows Stock On Hand, Product Codes etc... the program then goes a step further and preforms maths, like Retail Sell and Cost to work out Profit margins etc. In Excel this is easy to do, just use formulas and return the sheet into a listview.

In VB.net this is all a bit new to me, I have so far managed to import the entire Inventory File into the Listview as I understand it I have moved the MS DB into a DATASET and from the DATASET it is now in the ListView.

this is the code im so far using:

Code:
Imports System.Data.OleDb
Public Class Form1


    'this will serve as an open connection to our data source. 
    Public con As OleDb.OleDbConnection = jokendb()
    'this is some set of commands and a database connection that are use to fill later our datatable
    Dim da As New OleDb.OleDbDataAdapter
    'this will represent as our table in memory
    Dim dt As New DataTable
    'this holds our SQL Statements
    Dim sql As String

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles Me.Load
        Try
            sql = "SELECT * FROM INVXLS"
            con.Open()
            da = New OleDb.OleDbDataAdapter(sql, Con)
            'fill the data to datatable
            da.Fill(dt)
            'declare a variable as DataRow which is represent a row of data
            Dim newrow As DataRow
            'loop from each row based on the number of rows stored in dt
            For Each newrow In dt.Rows
                'display the results in listview
                ListView1.Items.Add(newrow.Item(18)) 'Brand
                ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(newrow.Item(2)) 'Model Name
                ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(newrow.Item(3)) 'Description
                ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(newrow.Item(0)) 'Product Code
                ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(newrow.Item(29)) 'Stock On Hand QTY
'this is not every column that i will need but just trying to work with this small amount for now to get it working. 


            Next

        Catch ex As Exception
            MsgBox(ex.Message)

        End Try
    End Sub

    Public Function jokendb() As OleDb.OleDbConnection
        Return New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\MyName\Desktop\INVXLS_8860.mdb")
    End Function


End Class
i now need to build on top of this so that i can preform the following functions,

  • Filter the list by a search term that someone types into a textbox, searching will search a particular column of the database. For example search Product Codes, and return results that match, Search Model Numbers and return results that match. (preferably with LIKE matches)
  • While the listview is being populated, it would need the preform maths on some columns specifically the Cost, and Retail Sell, and then add a column to the list view that will show the Profit based on the maths done. I have a vague idea how this would be done but would like to confirm that such a thing is possible?


  • For better Understanding, the MS DATABASE is called INVXLS_8862.mdb, the TABLE in it is called INVXLS. it has over 10,000 rows.
  • While the TABLE has lots of columns (about 58) to it i don't need to show all of them in the ListView, just the important ones.
  • I don't ever need to Write To or Modify the Database in anyway, the program only reads the data in it and then displays it in a more useful way. The end result also must not leave the MS .mdb open and in use.


If your wondering why I want to use a ListView and not a DataGridView is that I would like to use the formatting options that a listview has, like setting some rows to Red Text, some to Green, etc depending on its values.

Once i get a few of these basics coded, then i would be able to work out the rest of the programming to complete the program. So if anyone has experience with this sort of coding your help would be greatly appreciated. Or if you know of some great tutorials that are aimed at this sort of thing again that would be helpful as i understand what i have just asked is not a simple question to answer and would require some time to fully develop.

Thank you in Advance.