clientsolutions.vb

这是一个VBA开发的项目实例

Imports System.Data.OleDb
Public Class ClientSolutions
    Inherits System.Windows.Forms.Form
    Private varCompanyName As New StartApplication.ShareClass()
    Private ourConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\EasySelling\EasySelling.mdb;Persist Security Info=False"
    Private clientConnection As OleDbConnection = New OleDbConnection(ourConn)
    Private clientCommand As OleDbCommand
    Private clientDataReader As OleDbDataReader
    Private strSqlStatement As String
    Private varClientID As Long
#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub
    Friend WithEvents Button1 As System.Windows.Forms.Button
    Friend WithEvents btnUpdate As System.Windows.Forms.Button
    Friend WithEvents ColumnHeader1 As System.Windows.Forms.ColumnHeader
    Friend WithEvents lblLabelFormDes As System.Windows.Forms.Label
    Friend WithEvents lstvSolutions As System.Windows.Forms.ListView

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.Button1 = New System.Windows.Forms.Button()
        Me.btnUpdate = New System.Windows.Forms.Button()
        Me.lstvSolutions = New System.Windows.Forms.ListView()
        Me.ColumnHeader1 = New System.Windows.Forms.ColumnHeader()
        Me.lblLabelFormDes = New System.Windows.Forms.Label()
        Me.SuspendLayout()
        '
        'Button1
        '
        Me.Button1.Font = New System.Drawing.Font("Tahoma", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.Button1.Location = New System.Drawing.Point(422, 410)
        Me.Button1.Name = "Button1"
        Me.Button1.Size = New System.Drawing.Size(88, 24)
        Me.Button1.TabIndex = 80
        Me.Button1.Text = "&Close"
        '
        'btnUpdate
        '
        Me.btnUpdate.Font = New System.Drawing.Font("Tahoma", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.btnUpdate.Location = New System.Drawing.Point(326, 410)
        Me.btnUpdate.Name = "btnUpdate"
        Me.btnUpdate.Size = New System.Drawing.Size(88, 24)
        Me.btnUpdate.TabIndex = 79
        Me.btnUpdate.Text = "&Save"
        '
        'lstvSolutions
        '
        Me.lstvSolutions.BackColor = System.Drawing.Color.PowderBlue
        Me.lstvSolutions.CheckBoxes = True
        Me.lstvSolutions.Columns.AddRange(New System.Windows.Forms.ColumnHeader() {Me.ColumnHeader1})
        Me.lstvSolutions.Font = New System.Drawing.Font("Tahoma", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.lstvSolutions.GridLines = True
        Me.lstvSolutions.Location = New System.Drawing.Point(55, 79)
        Me.lstvSolutions.Name = "lstvSolutions"
        Me.lstvSolutions.Size = New System.Drawing.Size(456, 320)
        Me.lstvSolutions.TabIndex = 78
        Me.lstvSolutions.View = System.Windows.Forms.View.Details
        '
        'ColumnHeader1
        '
        Me.ColumnHeader1.Text = "Solutions"
        Me.ColumnHeader1.Width = 452
        '
        'lblLabelFormDes
        '
        Me.lblLabelFormDes.Font = New System.Drawing.Font("Tahoma", 9.0!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.lblLabelFormDes.Location = New System.Drawing.Point(54, 15)
        Me.lblLabelFormDes.Name = "lblLabelFormDes"
        Me.lblLabelFormDes.Size = New System.Drawing.Size(456, 56)
        Me.lblLabelFormDes.TabIndex = 77
        Me.lblLabelFormDes.Text = "Customers will need some Solutions for Issues being faced by them. Suggest releva" & _
        "nt Solutions by selecting them from the List below:"
        Me.lblLabelFormDes.TextAlign = System.Drawing.ContentAlignment.MiddleLeft
        '
        'ClientSolutions
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.BackColor = System.Drawing.Color.PowderBlue
        Me.ClientSize = New System.Drawing.Size(565, 448)
        Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.Button1, Me.btnUpdate, Me.lstvSolutions, Me.lblLabelFormDes})
        Me.FormBorderStyle = System.Windows.Forms.FormBorderStyle.None
        Me.Name = "ClientSolutions"
        Me.Text = "ClientSolutions"
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim NewMDIParent As New MainMDI()
        NewMDIParent.Show()
        NewMDIParent.Hide()

        Me.Close()
    End Sub

    Private Sub ClientSolutions_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim issueCount As Integer
        issueCount = 0
        Try
            clientConnection.Open()

            strSqlStatement = "SELECT Client_ID FROM tblClients WHERE Client_Name = '" & varCompanyName.SharedValue & "'"
            clientCommand = New OleDbCommand(strSqlStatement, clientConnection)
            clientDataReader = clientCommand.ExecuteReader()
            While clientDataReader.Read
                varClientID = clientDataReader.Item(0)
            End While
            clientDataReader.Close()


            strSqlStatement = "SELECT solution,selected FROM tblClientSolutions WHERE Client_ID = " & varClientID
            clientCommand = New OleDbCommand(strSqlStatement, clientConnection)
            clientDataReader = clientCommand.ExecuteReader()
            While clientDataReader.Read
                lstvSolutions.Items().Add(clientDataReader.Item(0), issueCount)
                If clientDataReader.Item(1) = True Then
                    lstvSolutions.Items(issueCount).Checked = True
                End If
                issueCount = issueCount + 1
            End While
            clientDataReader.Close()
            clientConnection.Close()
        Catch
            MsgBox(Err.Description())
        End Try
    End Sub

    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        Dim varCollection As System.Windows.Forms.ListView.CheckedListViewItemCollection
        Dim varitem As ListViewItem
        Try
            varCollection = lstvSolutions.CheckedItems()
            clientConnection.Open()
            For Each varitem In varCollection
                strSqlStatement = "UPDATE tblClientSolutions SET Selected = True WHERE Client_ID=" & varClientID & " AND solution = '" & varitem.Text & "'"
                clientCommand = New OleDbCommand(strSqlStatement, clientConnection)
                clientCommand.ExecuteNonQuery()
            Next
            clientConnection.Close()
            MsgBox("Record Saved Successfully.", MsgBoxStyle.Information, "Record Saved")

        Catch
            MsgBox(Err.Description())
        End Try
    End Sub
End Class