frmsearch.frm

this code helps u to understand the basic thing to connect visual basic with sqlserver. this ll be v

VERSION 5.00
Object = "{831FDD16-0C5C-11D2-A9FC-0000F8754DA1}#2.0#0"; "MSCOMCTL.OCX"
Begin VB.Form frmSearch 
   BorderStyle     =   1  'Fixed Single
   Caption         =   "Search"
   ClientHeight    =   4590
   ClientLeft      =   45
   ClientTop       =   330
   ClientWidth     =   6375
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MinButton       =   0   'False
   Picture         =   "frmSearch.frx":0000
   ScaleHeight     =   4590
   ScaleWidth      =   6375
   StartUpPosition =   2  'CenterScreen
   Begin VB.CommandButton cmdCancel 
      Caption         =   "&Cancel"
      Height          =   375
      Left            =   5160
      TabIndex        =   4
      Top             =   4080
      Width           =   975
   End
   Begin VB.CommandButton cmdView 
      Caption         =   "&View All"
      Height          =   375
      Left            =   240
      TabIndex        =   3
      Top             =   4080
      Width           =   975
   End
   Begin VB.TextBox txtEntry 
      BackColor       =   &H00C0E0FF&
      Height          =   285
      Left            =   2640
      TabIndex        =   2
      Top             =   480
      Width           =   3375
   End
   Begin VB.ComboBox cboSelection 
      BackColor       =   &H00C0E0FF&
      Height          =   315
      Left            =   240
      Style           =   2  'Dropdown List
      TabIndex        =   1
      Top             =   480
      Width           =   2055
   End
   Begin MSComctlLib.ListView lvwList 
      Height          =   3015
      Left            =   240
      TabIndex        =   0
      Top             =   960
      Width           =   5895
      _ExtentX        =   10398
      _ExtentY        =   5318
      View            =   3
      LabelWrap       =   -1  'True
      HideSelection   =   -1  'True
      FullRowSelect   =   -1  'True
      GridLines       =   -1  'True
      _Version        =   393217
      ForeColor       =   -2147483640
      BackColor       =   8438015
      BorderStyle     =   1
      Appearance      =   1
      NumItems        =   0
   End
   Begin VB.Label Label2 
      Caption         =   "Enter Entry"
      Height          =   255
      Left            =   2640
      TabIndex        =   6
      Top             =   120
      Width           =   1575
   End
   Begin VB.Label Label1 
      Alignment       =   2  'Center
      Caption         =   "Search Criteria"
      Height          =   255
      Left            =   480
      TabIndex        =   5
      Top             =   120
      Width           =   1455
   End
End
Attribute VB_Name = "frmSearch"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit
'info = "ccc" if used in search form in the listview for sales
'info = "bbb" if used in sales form in the listview for products
'info = "aaa" if used in search form in the listview for products
' i hope u all get what am i talking bout here.... hehehe.....



Private Sub cmdCancel_Click()
Unload Me
End Sub

Private Sub cmdProcess_Click()
Call open_conn
If info = "ccc" Then
    Select Case cboSelection.Text
        Case Is = "Invoice"
            Set rs = cn.Execute("Select * from tblsales where invoice = '" + Trim(txtEntry.Text) + "'")
            Call list_inventory
        Case Is = "Date Sold"
            Set rs = cn.Execute("select * from tblsales where dates like '" + Trim(txtEntry.Text) + "%';")
            Call list_inventory
        Case Is = "Product Code"
            Set rs = cn.Execute("select * from tblsales where pcode like '" + Trim(txtEntry.Text) + "%';")
            Call list_inventory
        Case Is = "Quantity"
            Set rs = cn.Execute("select * from tblsales where quant like '" + Trim(txtEntry.Text) + "%';")
            Call list_inventory
        Case Is = "Unit Price"
            Set rs = cn.Execute("select * from tblsales where unitp like '" + Trim(txtEntry.Text) + "%';")
            Call list_inventory
        Case Is = "Total"
            Set rs = cn.Execute("select * from tblsales where total like '" + Trim(txtEntry.Text) + "%';")
            Call list_inventory
    End Select
Else
    Select Case cboSelection.Text
        Case Is = "Product Code"
            Set rs = cn.Execute("Select * from tblproduct where pcode = '" + Trim(txtEntry.Text) + "'")
            Call list_inventory
        Case Is = "Description"
            Set rs = cn.Execute("select * from tblproduct where descrr like '" + Trim(txtEntry.Text) + "%';")
            Call list_inventory
        Case Is = "Brand"
            Set rs = cn.Execute("select * from tblproduct where brand like '" + Trim(txtEntry.Text) + "%';")
            Call list_inventory
        Case Is = "Unit"
            Set rs = cn.Execute("select * from tblproduct where unit like '" + Trim(txtEntry.Text) + "%';")
            Call list_inventory
    End Select
End If
Call close_conn
End Sub


Private Sub cmdView_Click()
Form_Load
txtEntry.Text = ""
End Sub

Private Sub Form_Load()
If info = "ccc" Then
    With lvwList ' naming all the columnheaders of the listview(lvwList)
        .ColumnHeaders.Clear
        .ColumnHeaders.Add.Text = "Invoice"
        .ColumnHeaders.Add.Text = "Date Sold"
        .ColumnHeaders.Add.Text = "Product Code"
        .ColumnHeaders.Add.Text = "Quantity"
        .ColumnHeaders.Add.Text = "Unit Price"
        .ColumnHeaders.Add.Text = "Total"
    End With
    cboSelection.Clear ' entering values for the combo box(cboSelection)
    cboSelection.AddItem "Invoice"
    cboSelection.AddItem "Date Sold"
    cboSelection.AddItem "Product Code"
    cboSelection.AddItem "Quantity"
    cboSelection.AddItem "Unit Price"
    cboSelection.AddItem "Total"
    Call open_conn
    Set rs = cn.Execute("select * from tblsales")
    Call list_inventory
    Call close_conn
Else
    With lvwList ' naming all the columnheaders of the listview(lvwList)
        .ColumnHeaders.Clear
        .ColumnHeaders.Add.Text = "Product Code"
        .ColumnHeaders.Add.Text = "Description"
        .ColumnHeaders.Add.Text = "Brand"
        .ColumnHeaders.Add.Text = "Unit"
    End With
    cboSelection.Clear ' entering values for the combo box(cboSelection)
    cboSelection.AddItem "Product Code"
    cboSelection.AddItem "Description"
    cboSelection.AddItem "Brand"
    cboSelection.AddItem "Unit"
    Call open_conn
    Set rs = cn.Execute("select * from tblproduct")
    Call list_inventory
    Call close_conn
End If
End Sub

Private Sub lvwList_ColumnClick(ByVal ColumnHeader As MSComctlLib.ColumnHeader)
n1 = ColumnHeader.Index ' sorting again!!!
If info = "ccc" Then
    If n1 <> 2 Then Call SortNum(lvwList, ColumnHeader)
Else
    If n1 = 1 Then Call SortNum(lvwList, ColumnHeader) Else _
    Call SortAlpha(lvwList, ColumnHeader)
End If
End Sub

Private Sub lvwList_DblClick()
If info = "ccc" Then
    If lvwList.ListItems.Count <> 0 Then
        Call open_conn
'''''''''''''''''''''transfering whole invoice # to the frmsales.lvwlist''''''''''''''''''''
        Set rs = cn.Execute("select * from tblsales where invoice='" + Trim(lvwList.SelectedItem) + "'")
        If Not rs.EOF Then
        frmSales.lvwList.ListItems.Clear
        rs.MoveFirst
        Do While Not rs.EOF
            Set itmx = frmSales.lvwList.ListItems.Add(, , rs!invoice)
                itmx.SubItems(1) = rs!dates
               itmx.SubItems(2) = rs!pcode
                itmx.SubItems(3) = rs!quant
                itmx.SubItems(4) = rs!unitp
                itmx.SubItems(5) = rs!total
                rs.MoveNext
        Loop
        Else
            frmSales.lvwList.ListItems.Clear
        End If
''''''''''''''''''''''end of transfer'''''''''''''''''''''''''''
        Call close_conn
    End If
    With frmSales
        .lblInvoice.Caption = ""
        .DTPicker1.Value = Date
        .lblPcode.Caption = ""
        .txtDescrr.Text = ""
        .txtBrand.Text = ""
        .txtUnit.Text = ""
        .txtQuant.Text = ""
        .txtUnitP.Text = ""
        .txtTotal.Text = ""
        .txtGrand.Text = ""
        .cmdEdit.Enabled = True
    End With
    Unload Me
Else
    If lvwList.ListItems.Count <> 0 Then
        Call open_conn
        Set rs = cn.Execute("Select * from tblproduct where pcode='" + Trim(lvwList.SelectedItem) + "'")
        If Not rs.EOF Then
            If info = "aaa" Then
                With frmProduct
                    .lblPcode.Caption = rs!pcode
                    .txtDescrr.Text = rs!descrr
                    .txtBrand.Text = rs!brand
                    .txtUnit.Text = rs!unit
                End With
            Else
''''''''''''''''''''''verify first if the product selected is already used to avoid repetition'''''''''''''''''
                n1 = 1
                Do While n1 <= frmSales.lvwList.ListItems.Count
                frmSales.lvwList.ListItems(n1).Selected = True
                If frmSales.lvwList.SelectedItem.SubItems(2) = rs!pcode Then
                    MsgBox "The product is already in use!", vbCritical + vbInformation, "User Info"
                    Exit Sub
                End If
                n1 = n1 + 1
                Loop
''''''''''''''''''''''''end of verification'''''''''''''''''''''''''''''''''''''
                With frmSales
                    .lblPcode.Caption = rs!pcode
                    .txtDescrr.Text = rs!descrr
                    .txtBrand.Text = rs!brand
                    .txtUnit.Text = rs!unit
                End With
            End If
        End If
        Call close_conn
        Unload Me
    End If
End If
End Sub

Private Sub txtEntry_Change()
cmdProcess_Click
End Sub

Public Sub list_inventory()
If info = "ccc" Then ' transfer of the datas to the listview
    If Not rs.EOF Then
        lvwList.ListItems.Clear
        rs.MoveFirst
        Do While Not rs.EOF
            Set itmx = lvwList.ListItems.Add(, , rs!invoice)
                itmx.SubItems(1) = rs!dates
                itmx.SubItems(2) = rs!pcode
                itmx.SubItems(3) = rs!quant
                itmx.SubItems(4) = rs!unitp
                itmx.SubItems(5) = rs!total
                rs.MoveNext
        Loop
    Else
        lvwList.ListItems.Clear
    End If
Else
    If Not rs.EOF Then
        lvwList.ListItems.Clear
        rs.MoveFirst
        Do While Not rs.EOF
            Set itmx = lvwList.ListItems.Add(, , rs!pcode)
                itmx.SubItems(1) = rs!descrr
                itmx.SubItems(2) = rs!brand
                itmx.SubItems(3) = rs!unit
                rs.MoveNext
        Loop
    Else
        lvwList.ListItems.Clear
    End If
End If
End Sub