Imports System.Data.SqlClient
Public Class FrmSearch
    Private Sub fill()
        With lvRec
            .Columns.Add("ID", 30, HorizontalAlignment.Left)
            .Columns.Add("Category/Name", 110, HorizontalAlignment.Left)
            .Columns.Add("Description", 110, HorizontalAlignment.Left)
            .Columns.Add("Qty", 110, HorizontalAlignment.Left)
            .Columns.Add("Price", 110, HorizontalAlignment.Left)
            .Columns.Add("Supplier", 110, HorizontalAlignment.Left)
            .Columns.Add("Date-Purchased", 110, HorizontalAlignment.Left)
            .Columns.Add("Date-Recieved", 110, HorizontalAlignment.Left)
            .Activation = ItemActivation.OneClick
            .FullRowSelect = True
            .GridLines = True
            .HideSelection = False
            .HoverSelection = False
            .MultiSelect = True
            .View = View.Details
            .HotTracking = False
            .Font = New Font(lvRec.Font.FontFamily, 11, FontStyle.Bold, GraphicsUnit.Pixel)
            .BackColor = Color.White
        End With

    End Sub
    Private Sub FrmSearch_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Call fill()
        Call fillcombo()
    End Sub
    Public Sub fillcombo()
        Try
            Call opencon()
            Dim da As New SqlDataAdapter
            With da
                .SelectCommand = New SqlCommand
                .SelectCommand.CommandText = "SELECT DISTINCT category FROM stock"
                .SelectCommand.Connection = con
            End With
            Dim dr As SqlDataReader = da.SelectCommand.ExecuteReader
            Dim dt As New DataTable
            dt.Load(dr)
            For i As Integer = 0 To dt.Rows.Count - 1
                cbocatt.Items.Add(dt.Rows(i)("category"))
            Next
            Call closecon()
        Catch ex As Exception
            MsgBox(ex.Message)
            Call closecon()
        End Try
    End Sub
    Public Sub ConSelect()
        Me.lvRec.Items.Clear()
        Try
            Dim myd As New SqlDataAdapter
            With myd
                .SelectCommand = New SqlCommand
                .SelectCommand.CommandText = "SELECT * FROM stock WHERE category LIKE '%%" & Me.cbocatt.Text & "%%'"
                .SelectCommand.Connection = con
            End With
            Dim dr As SqlDataReader
            dr = myd.SelectCommand.ExecuteReader
            Do While dr.Read
                Dim item As New ListViewItem
                item.Text = IIf(dr.IsDBNull(0), " ", dr.Item(0)).ToString
                Dim c As Integer
                For c = 1 To dr.FieldCount - 1
                    If Not dr.IsDBNull(c) Then
                        item.SubItems.Add(dr.Item(c)).ToString()
                    Else
                        item.SubItems.Add(" ")
                    End If
                Next
                Me.lvRec.Items.Add(item)
            Loop

        Catch ex As Exception
            MsgBox(ex.Message)

        End Try
    End Sub

    Private Sub btn_view_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_view.Click
        Call opencon()
        Call ConSelect()
        Call closecon()
    End Sub

    Private Sub btn_Refresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_Refresh.Click
        Me.lvRec.Items.Clear()
    End Sub

    Private Sub btn_close_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_close.Click
        Me.Close()
    End Sub

    Private Sub cbocatt_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbocatt.SelectedIndexChanged

    End Sub
End Class