vb.net 教程 20-4 库存管理系统3.10 入库单查询（FormStorageInQuery）

当前位置:

首页 > VB.net教程 >

vb.net 教程 20-4 库存管理系统3.10 入库单查询（FormStorageInQuery）

关于《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#爱好者提供的参考。

————————————————

原文链接：https://blog.csdn.net/UruseiBest/article/details/114224542

栏目列表