vb.net 教程 20-4 库存管理系统3.6 库存信息（FormStorageInfo）

当前位置:

首页 > VB.net教程 >

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

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

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

栏目列表