-
vb.net 教程 20-4 库存管理系统3.6 库存信息(FormStorageInfo)
版权声明:本文为博主原创文章,转载请在显著位置标明本文出处以及作者网名,未经作者允许不得用于商业目的。
关于《Visual Basic.Net 循序渐进》请到百度网盘下载,具体下载地址:
链接:https://pan.baidu.com/s/1IfaLvlklx-nT4KK4VKZuIw
提取码:ip5n
操作员权限可查看、查询库存货物信息,管理员权限可以增加、修改、删除。
如果库存货物较多,一次性显示可能会影响性能和交互体验,因此在显示时对数据进行了分页。关于如何分页显示数据,请参看教程第19.5.3.7节《分页显示数据》。
通常情况下,sql查询分为四个部分:select、from、where、order by,因此每一次sql查询,实际就是这四部分的组合,在本节代码以及后面几节代码中将频繁地使用以下四个变量(对不同的查询有不同变量名称),分别表示sql查询的四部分。
Dim sqlOrderSelect As String
Dim sqlOrderFrom As String
Dim sqlOrderOrder As String
Dim sqlOrderWhere As String
同时,定义变量 sqlFlag 来标识当前应该使用的sql查询语句。
另外,为了方便用户操作,提供了双击datagridview中某行,直接跳出对应货物ID号的库存货物操作(FormStorageInfoTable)窗口,代码中是dgv.MouseDoubleClick。
全部代码如下:
Imports System.ComponentModel
Imports System.Data.OleDb
Public Class FormStorageInfo
Dim connection As OleDbConnection
Dim ds As DataSet
Dim adapter As OleDbDataAdapter
Dim dt As DataTable
'每页显示记录数
Const pagesize As Integer = 50
'总的页数
Dim maxPage As Integer
'当前页数
Dim currentPage As Integer
Dim F_Main As FormMain
'以库存物品查询相关sql语句
Dim sqlOrderSelect As String
Dim sqlOrderFrom As String
Dim sqlOrderOrder As String
Dim sqlOrderWhere As String
'以库存物自定义查询相关语句
Dim sqlQuerySelect As String
Dim sqlQueryFrom As String
Dim sqlQueryOrder As String
Dim sqlQueryWhere As String
'当前应该使用的sql查询语句
Dim sqlFlag As String
Private Sub FormStorageInfo_Load(sender As Object, e As EventArgs) Handles Me.Load
'管理员权限才能使用 新增、修改、删除 按钮
If permissions = 0 Then
tsbAdd.Visible = True
tsbEdit.Visible = True
tsbDelete.Visible = True
Else
tsbAdd.Visible = False
tsbEdit.Visible = False
tsbDelete.Visible = False
End If
Me.WindowState = FormWindowState.Maximized
connection = New OleDbConnection(databaseConnString)
connection.Open()
F_Main = CType(Me.MdiParent, FormMain)
sqlOrderSelect = "SELECT 货物信息.产品ID,货物信息.产品名称,货物信息.库存量,货物类别.类别名称 as 类别,货物信息.单位数量,货物信息.采购价格,货物信息.销售价格,货物信息.产品编号,供应商.公司名称 as 供应商 "
sqlOrderFrom = "FROM (货物信息 INNER JOIN 供应商 ON 货物信息.供应商ID = 供应商.供应商ID) INNER JOIN 货物类别 ON 货物信息.类别ID = 货物类别.类别ID "
sqlOrderWhere = "where 货物信息.是否删除='否' "
sqlOrderOrder = "order by 货物信息.产品ID"
sqlQuerySelect = "SELECT 货物信息.产品ID,货物信息.产品名称,货物信息.库存量,货物类别.类别名称 as 类别,货物信息.单位数量,货物信息.采购价格,货物信息.销售价格,货物信息.产品编号,供应商.公司名称 as 供应商 "
sqlQueryFrom = "FROM (货物信息 INNER JOIN 供应商 ON 货物信息.供应商ID = 供应商.供应商ID) INNER JOIN 货物类别 ON 货物信息.类别ID = 货物类别.类别ID "
sqlQueryWhere = ""
sqlQueryOrder = "order by 货物信息.产品ID"
sqlFlag = "库存信息"
Call showGrid(sqlFlag)
End Sub
Private Sub tsbStockInOrder_Click(sender As Object, e As EventArgs) Handles tsbStockInOrder.Click
sqlFlag = "库存信息"
Call showGrid(sqlFlag)
'在查询入库物品单时,才允许增改删
tsbAdd.Enabled = True
tsbEdit.Enabled = True
tsbDelete.Enabled = True
End Sub
'显示查询的数据信息
Public Sub showGrid(ByVal tableFlag As String)
ds = New DataSet(tableFlag)
'建立OleDbCommand,传入Sql语句求要查询记录的总数
Dim command As New OleDbCommand()
Dim sqlSelect As String = ""
Dim sqlFrom As String = ""
Dim sqlWhere As String = ""
Dim sqlOrder As String = ""
Select Case tableFlag
Case "库存信息"
sqlSelect = sqlOrderSelect
sqlFrom = sqlOrderFrom
sqlWhere = sqlOrderWhere
sqlOrder = sqlOrderOrder
Case "自定义"
sqlSelect = sqlQuerySelect
sqlFrom = sqlQueryFrom
sqlWhere = sqlQueryWhere
sqlOrder = sqlQueryOrder
End Select
command.CommandText = "select count(*) " & sqlFrom & sqlWhere
command.Connection = connection
Dim count As Integer = command.ExecuteScalar
F_Main.tsslInfo.Text = "数据总数:" & count & " 条"
If count < pagesize Then
tsbFirstPage.Enabled = False
tsbPrevPage.Enabled = False
tsbNextPage.Enabled = False
tsbLastPage.Enabled = False
Else
tsbFirstPage.Enabled = True
tsbPrevPage.Enabled = True
tsbNextPage.Enabled = True
tsbLastPage.Enabled = True
End If
command.CommandText = sqlSelect &
sqlFrom &
sqlWhere &
sqlOrder
adapter = New OleDbDataAdapter(command)
Console.WriteLine(command.CommandText)
'根据记录总数得到总的页数,其中Math.Ceiling返回大于或等于指定的数的最小整数值
maxPage = Math.Ceiling(count / pagesize)
currentPage = 0
adapter.Fill(ds, currentPage * pagesize, pagesize, tableFlag)
'防止列顺序与查询顺序不一致,需要加入以下一行代码
dgv.DataSource = Nothing
dgv.DataSource = ds.Tables(tableFlag)
tslRecord.Text = (currentPage + 1).ToString & "/" & maxPage.ToString
End Sub
'第一页数据
Private Sub tsbFirstPage_Click(sender As Object, e As EventArgs) Handles tsbFirstPage.Click
currentPage = 0
'先清除已经填入的表,否则会将数据追加到已有表
ds.Tables(sqlFlag).Clear()
adapter.Fill(ds, currentPage * pagesize, pagesize, sqlFlag)
dgv.DataSource = ds.Tables(sqlFlag)
tslRecord.Text = (1).ToString & "/" & maxPage.ToString
End Sub
'上一页数据
Private Sub tsbPrevPage_Click(sender As Object, e As EventArgs) Handles tsbPrevPage.Click
If currentPage = 0 Then
MessageBox.Show("已经是第一页了")
Exit Sub
End If
currentPage -= 1
ds.Tables(sqlFlag).Clear()
adapter.Fill(ds, currentPage * pagesize, pagesize, sqlFlag)
dgv.DataSource = ds.Tables(sqlFlag)
tslRecord.Text = (currentPage + 1).ToString & "/" & maxPage.ToString
End Sub
'下一页数据
Private Sub tsbNextPage_Click(sender As Object, e As EventArgs) Handles tsbNextPage.Click
If currentPage = maxPage - 1 Then
MessageBox.Show("已经是最后一页了")
Exit Sub
End If
currentPage += 1
ds.Tables(sqlFlag).Clear()
Dim recordcount As Integer = adapter.Fill(ds, currentPage * pagesize, pagesize, sqlFlag)
dgv.DataSource = ds.Tables(sqlFlag)
tslRecord.Text = (currentPage + 1).ToString & "/" & maxPage.ToString
End Sub
'最后一页数据
Private Sub tsbLastPage_Click(sender As Object, e As EventArgs) Handles tsbLastPage.Click
currentPage = maxPage - 1
ds.Tables(sqlFlag).Clear()
adapter.Fill(ds, currentPage * pagesize, pagesize, sqlFlag)
dgv.DataSource = ds.Tables(sqlFlag)
tslRecord.Text = (currentPage + 1).ToString & "/" & maxPage.ToString
End Sub
'增加货物信息
Private Sub tsbAdd_Click(sender As Object, e As EventArgs) Handles tsbAdd.Click
Dim F_StorageInfoTable As New FormStorageInfoTable(0)
F_StorageInfoTable.ShowDialog()
End Sub
'查看或修改货物信息
Private Sub tsbEdit_Click(sender As Object, e As EventArgs) Handles tsbEdit.Click
If dgv.SelectedRows.Count = 0 Then
MessageBox.Show("请先选择货物")
Exit Sub
End If
Dim orderid As Integer
orderid = CType(dgv.SelectedRows(0).Cells(0).Value, Integer)
Dim F_StorageInfoTable As New FormStorageInfoTable(orderid)
F_StorageInfoTable.ShowDialog()
End Sub
'删除某件货物信息。。
Private Sub tsbDelete_Click(sender As Object, e As EventArgs) Handles tsbDelete.Click
If dgv.SelectedRows.Count = 0 Then
MessageBox.Show("请先选择货物")
Exit Sub
End If
Dim orderid As Integer
orderid = CType(dgv.SelectedRows(0).Cells(0).Value, Integer)
Dim command As New OleDbCommand()
command.Connection = connection
If MessageBox.Show("确定要删除此货物?", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning) = DialogResult.Cancel Then
Exit Sub
End If
'将该入库单标记为删除(实际未删除)
command.CommandText = "update 货物信息 set 是否删除='是' where 产品ID=" & orderid
'删除不用返回值,直接使用ExecuteNonQuery。
command.ExecuteNonQuery()
'从dgv中删除该行
dgv.Rows.Remove(dgv.SelectedRows(0))
End Sub
Private Sub FormStorageInfo_Closing(sender As Object, e As CancelEventArgs) Handles Me.Closing
connection.Close()
End Sub
'在dgv中双击某行
Private Sub dgv_MouseDoubleClick(sender As Object, e As MouseEventArgs) Handles dgv.MouseDoubleClick
Dim dgvhti As DataGridView.HitTestInfo = dgv.HitTest(e.X, e.Y)
Dim selectedIndex As Integer
If dgvhti.Type = DataGridViewHitTestType.Cell Or dgvhti.Type = DataGridViewHitTestType.RowHeader Then
selectedIndex = dgvhti.RowIndex
Dim orderid As Integer
orderid = CType(dgv.Rows(selectedIndex).Cells(0).Value, Integer)
'当在 入库单、自定义2 时,双击同选中某行后按下“修改”按钮的效果
Dim F_StorageInfoTable As New FormStorageInfoTable(orderid)
F_StorageInfoTable.ShowDialog()
Else
Exit Sub
End If
End Sub
'自定义查询,需要从FormStockOutQuery返回查询条件
Public customWhere As String
'自定义查询
Private Sub tsbQuery_Click(sender As Object, e As EventArgs) Handles tsbQuery.Click
Dim F_StorageInfoQuery As New FormStorageInfoQuery
F_StorageInfoQuery.Owner = Me
F_StorageInfoQuery.ShowDialog()
If customWhere = "" Then
Exit Sub
End If
sqlQueryWhere = "where " & customWhere & " and (货物信息.是否删除='否') "
sqlFlag = "自定义"
Call showGrid(sqlFlag)
End Sub
Private Sub tsbHome_Click(sender As Object, e As EventArgs) Handles tsbHome.Click
Me.Close()
End Sub
End Class
由于.net平台下C#和vb.NET很相似,本文也可以为C#爱好者提供的参考。
————————————————
版权声明:本文为CSDN博主「VB.Net」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/UruseiBest/article/details/114224330
栏目列表
最新更新
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跳出多重循环实例