-
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#爱好者提供的参考。
————————————————
版权声明:本文为CSDN博主「VB.Net」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/UruseiBest/article/details/114224542
栏目列表
最新更新
Python获取微信好友数据
Python 的排序方法 sort 和 sorted 的区别
WinForm轻松实现自定义分页 (转载)
Mac系统下使用brew搭建PHP(LNMP/LAMP)开发环境
在win系统安装配置 Memcached for PHP 5.3 图文
箱图在数据预处理中的应用
Python WEB开发:用Tornado框架制作简易【表
自从学会了用python解析视频,都不用去找
Python代码阅读(第50篇):对列表间隔取
Python爬虫+数据分析+可视化展示,分析《
MongoDB常用命令(2)
MongoDB基本介绍与安装(1)
SQLServer触发器调用JavaWeb接口
SQL Server索引的原理深入解析
SqlServer2016模糊匹配的三种方式及效率问题
SQL中Truncate的用法
sqlserver 多表关联时在where语句中慎用tri
链接服务器读取Mysql---出现消息 7347,级别
SQL Server解惑——为什么你拼接的SQL语句换
MySQL视图了解一下
php 判断常量、变量和函数是否存在
php if..else 循环
PHP中foreach循环详解
php foreach用法和实例
php for的高级运用
php foreach循环用法介绍
PHP条件语句(if else/switch)语法与示例
PHP 循环语句基本语法结构笔记
PHP 条件语句基本语法结构
php break跳出多重循环实例