vb.net 教程 20-4 库存管理系统3.10 入库单查询（FormStorageInQuery）_vba入库汇总表利用窗体输入日期范围及种类查询实例下载-优快云博客

关于《Visual Basic.Net 循序渐进》请到百度网盘下载，具体下载地址：
链接：https://pan.baidu.com/s/1IfaLvlklx-nT4KK4VKZuIw
提取码：ip5n

窗体中使用了DateTimePicker控件来设置日期时间，DateTimePicker的属性CustomFormat设置为：yyyy-MM-dd HH:mm:ss，同时Format属性必须设置为：Custom。关于DateTimePicker控件的使用，请参看教程第8.1.14节《DateTimePicker（日期选择）控件》。
入库单查询日期时，建议sql语句使用between。
如查询2000年11月1日当天的入库单：
"(入库单.订购日期 between #2000-11-01 0:00:00# and #2000-11-01 23:59:59#)"
注意，日期时间用#（井号）包围。
查询2000年11月1日至2000年11月10日间的入库单：
"(入库单.订购日期 between #2000-11-01 0:00:00# and #2000-11-010 23:59:59#)"
全部代码如下：

Imports System.ComponentModel
Imports System.Data.OleDb

Public Class FormStorageInQuery

    Dim connection As OleDbConnection
    Dim arrRecordUser As List(Of Integer)
    Dim arrGoodsType As List(Of Integer)
    Dim arrGoodsID As List(Of Integer)

    Private Sub FormStorageInQuery_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        arrRecordUser = New List(Of Integer)
        arrGoodsType = New List(Of Integer)
        arrGoodsID = New List(Of Integer)

        connection = New OleDbConnection(databaseConnString)
        '打开数据连接
        connection.Open()

        '新建OleDbCommand对象实例
        Dim command As New OleDbCommand()

        '=========填充操作员选择框==================
        '要执行的SQL查询
        command.CommandText = "select ID,姓名 from 操作员"
        '设置OleDbCommand的数据连接为OleDbConnection
        command.Connection = connection

        '声明OleDbDataReader对象
        Dim odReader As OleDbDataReader
        odReader = command.ExecuteReader()

        '如果OleDbDataReader中包含数据
        If odReader.HasRows Then
            '循环读取每一行数据，直到Read方法返回False
            Do While odReader.Read
                arrRecordUser.Add(odReader.GetValue(0))
                cbRecordUser.Items.Add(odReader.GetValue(1))
            Loop
        End If
        odReader.Close()

        '=========填充货物类型选择框==================
        '要执行的SQL查询
        command.CommandText = "select 类别ID,类别名称 from 货物类别"

        odReader = command.ExecuteReader()
        '如果OleDbDataReader中包含数据
        If odReader.HasRows Then
            '循环读取每一行数据，直到Read方法返回False
            Do While odReader.Read
                arrGoodsType.Add(odReader.GetValue(0))
                cbGoodsType.Items.Add(odReader.GetValue(1))
            Loop
        End If
        odReader.Close()

        If cbRecordUser.Items.Count > 0 Then
            cbRecordUser.SelectedIndex = 0
        End If
        If cbGoodsType.Items.Count > 0 Then
            cbGoodsType.SelectedIndex = 0
        End If
        cbOrderCondition.SelectedIndex = 0
        cbEnterCondition.SelectedIndex = 0
        If cbRecordUser.Items.Count > 0 Then cbRecordUser.SelectedIndex = 0
    End Sub

    Private Sub FormStorageInQuery_Closing(sender As Object, e As CancelEventArgs) Handles Me.Closing
        connection.Close()
    End Sub

    Private Sub btnOk_Click(sender As Object, e As EventArgs) Handles btnOk.Click
        Dim SqlWhere As String = ""
        Dim subSqlWhere As String = ""

        '判断是否勾选了查询条件
        Dim checkCondition As Boolean = False

        '构建查询的where语句
        '1、入库单号
        If ckOrderID.Checked = True Then
            checkCondition = True
            If txtOrderID.Text.Trim = "" Then
                MessageBox.Show("订单编号不能为空值")
                Exit Sub
            End If
            SqlWhere = getSqlOrderID()
        End If

        '2、操作人员
        If ckRecordUser.Checked = True Then
            checkCondition = True
            If cbRecordUser.Text = "" Then
                MessageBox.Show("操作人员姓名不能为空值")
                Exit Sub
            End If
            subSqlWhere = getRecordUser()
            SqlWhere = IIf(SqlWhere = "", subSqlWhere, SqlWhere & " And " & subSqlWhere)
        End If

        '3、订购日期
        If ckOrder.Checked = True Then
            checkCondition = True
            subSqlWhere = getOrder()
            SqlWhere = IIf(SqlWhere = "", subSqlWhere, SqlWhere & " And " & subSqlWhere)
        End If

        '4、发货日期
        If ckEnter.Checked = True Then
            checkCondition = True
            subSqlWhere = getEnter()
            SqlWhere = IIf(SqlWhere = "", subSqlWhere, SqlWhere & " And " & subSqlWhere)
        End If

        '5、货物名称
        If ckName.Checked = True Then
            checkCondition = True
            If cbGoods.Text = "" Then
                MessageBox.Show("货物名称不能为空值")
                Exit Sub
            End If
            subSqlWhere = getGoodsName()
            SqlWhere = IIf(SqlWhere = "", subSqlWhere, SqlWhere & " And " & subSqlWhere)
        End If

        If checkCondition = False Then
            MessageBox.Show("必须勾选一个查询条件")
            Exit Sub
        End If

        '关闭此窗口
        Dim F_StorageOut As FormStorageIn
        F_StorageOut = Me.Owner
        F_StorageOut.customWhere = SqlWhere
        Me.Close()
    End Sub

#Region "设置各个条件下的查询语句"

    '注意：查询条件在使用like时
    '在Access中，应该使用*
    '在vb中，应该使用%
    Private Function getSqlOrderID() As String
        Return "(入库单.入库单ID like '%" & txtOrderID.Text.Trim & "%')"
    End Function

    Private Function getRecordUser() As String
        Return "(入库单.操作员ID=" & arrRecordUser(cbRecordUser.SelectedIndex) & ")"
    End Function

    Private Function getOrder() As String
        If cbOrderCondition.Text = "等于" Then
            Return "(入库单.订购日期 between #" & dtpOrderStart.Text & " 0:00:00# and #" & dtpOrderStart.Text & " 23:59:59#)"
        Else
            Return "(入库单.订购日期 between #" & dtpOrderStart.Text & " 0:00:00# and #" & dtpOrderEnd.Text & " 23:59:59#)"
        End If
    End Function

    Private Function getEnter() As String
        If cbEnterCondition.Text = "等于" Then
            Return "(入库单.入库日期 between #" & dtpEnterStart.Text & " 0:00:00# and #" & dtpEnterStart.Text & " 23:59:59#)"
        Else
            Return "(入库单.入库日期 between #" & dtpEnterStart.Text & " 0:00:00# and #" & dtpEnterEnd.Text & " 23:59:59#)"
        End If
    End Function

    Private Function getGoodsName() As String
        Return "(入库单明细.产品ID=" & arrGoodsID(cbGoods.SelectedIndex) & ")"
    End Function


#End Region

    Private Sub btnCancel_Click(sender As Object, e As EventArgs) Handles btnCancel.Click
        Dim F_StorageIn As FormStorageIn
        F_StorageIn = Me.Owner
        F_StorageIn.customWhere = ""
        Me.Close()
    End Sub

    Private Sub cbGoodsType_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cbGoodsType.SelectedIndexChanged
        Dim goodsTypeID As Integer
        goodsTypeID = arrGoodsType(cbGoodsType.SelectedIndex)
        arrGoodsID.Clear()
        cbGoods.Items.Clear()

        '新建OleDbCommand对象实例
        Dim command As New OleDbCommand()

        '=========填充操作员选择框==================
        '要执行的SQL查询
        command.CommandText = "select 产品ID,产品名称 from 货物信息 where 类别ID=" & goodsTypeID
        '设置OleDbCommand的数据连接为OleDbConnection
        command.Connection = connection

        '声明OleDbDataReader对象
        Dim odReader As OleDbDataReader
        odReader = command.ExecuteReader()

        '如果OleDbDataReader中包含数据
        If odReader.HasRows Then
            '循环读取每一行数据，直到Read方法返回False
            Do While odReader.Read
                arrGoodsID.Add(odReader.GetValue(0))
                cbGoods.Items.Add(odReader.GetValue(1))
            Loop
        End If
        odReader.Close()

        cbGoods.SelectedIndex = 0
    End Sub
End Class

由于.net平台下C#和vb.NET很相似，本文也可以为C#爱好者提供的参考。

学习更多vb.net知识，请参看 vb.net 教程目录