原创 vb.net 教程 20-4 库存管理系统3.12 出库管理（FormStorageOut）

当前位置:

首页 > VB.net教程 >

原创 vb.net 教程 20-4 库存管理系统3.12 出库管理（FormStorageOut）

关于《Visual Basic.Net 循序渐进》请到百度网盘下载，具体下载地址：

链接：https://pan.baidu.com/s/1IfaLvlklx-nT4KK4VKZuIw

提取码：ip5n

同入库管理，仍然存在一个出库单对应多个出库物的情况，因此提供了两种显示方式。“出库单”按钮按下显示的是出库单信息，“出库货物”按钮按下显示的是出库货物信息。datagridview在显示出库单时，双击弹出出库单ID对应的出库单操作窗口；datagridview在显示出库货物信息时，双击转到显示该出库货物对应的出库单。

全部代码如下：

Imports System.ComponentModel

Imports System.Data.OleDb

Public Class FormStorageOut

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

'出库货物查询相关sql语句

Dim sqlFoodsSelect As String

Dim sqlFoodsFrom As String

Dim sqlFoodsOrder As String

Dim sqlFoodsWhere As String

'自定义查询相关语句

Dim sqlQuerySelect As String

Dim sqlQueryFrom As String

Dim sqlQueryOrder As String

Dim sqlQueryWhere As String

Dim sqlFlag As String

'Public Event RefreshData()

Private Sub FormStorageOut_Load(sender As Object, e As EventArgs) Handles MyBase.Load

Me.WindowState = FormWindowState.Maximized

connection = New OleDbConnection(databaseConnString)

connection.Open()

F_Main = CType(Me.MdiParent, FormMain)

'sqlOrderSelect = "SELECT 出库单.订单ID, 出库单.雇员ID as 操作员编号,操作员.姓名 AS 操作员, 出库单.订购日期, 出库单.发货日期, 快递公司.公司名称 as 快递公司, 出库单.快递费, 出库单.买家姓名, 出库单.买家手机号码, 出库单.买家地址 "

sqlOrderSelect = "SELECT 出库单.订单ID, 操作员.姓名 AS 操作员, 出库单.订购日期, 出库单.发货日期, 快递公司.公司名称 as 快递公司, 出库单.快递费, 出库单.买家姓名, 出库单.买家手机号码, 出库单.买家地址 "

sqlOrderFrom = "FROM 快递公司 INNER JOIN (出库单 INNER JOIN 操作员 ON 出库单.雇员ID = 操作员.ID) ON 快递公司.运货商ID = 出库单.快递公司 "

sqlOrderWhere = "where 出库单.是否删除='否'"

sqlOrderOrder = "order by 出库单.订单ID"

sqlFoodsSelect = "select 出库单明细.订单ID,货物信息.产品名称,出库单明细.单价,出库单明细.数量,操作员.姓名 as 操作员,出库单.订购日期,出库单.发货日期,出库单.买家姓名,出库单.买家手机号码,出库单.买家地址 "

sqlFoodsFrom = "FROM 货物信息 INNER JOIN (操作员 INNER JOIN (出库单 INNER JOIN 出库单明细 ON 出库单.订单ID = 出库单明细.订单ID) ON 操作员.ID = 出库单.雇员ID) ON 货物信息.产品ID = 出库单明细.产品ID "

sqlFoodsWhere = "where 出库单.是否删除='否'"

sqlFoodsOrder = "order by 出库单明细.订单ID"

sqlQuerySelect = "SELECT 出库单.订单ID, 操作员.姓名 AS 操作员, 出库单.订购日期, 出库单.发货日期, 快递公司.公司名称 as 快递公司, 出库单.快递费, 出库单.买家姓名, 出库单.买家手机号码, 出库单.买家地址 "

sqlQueryFrom = "FROM 快递公司 INNER JOIN (出库单 INNER JOIN 操作员 ON 出库单.雇员ID = 操作员.ID) ON 快递公司.运货商ID = 出库单.快递公司 "

sqlQueryWhere = ""

sqlQueryOrder = "order by 出库单.订单ID"

sqlFlag = "出库单"

Call showGrid(sqlFlag)

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 = sqlFoodsSelect

sqlFrom = sqlFoodsFrom

sqlWhere = sqlFoodsWhere

sqlOrder = sqlFoodsOrder

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 tsbStockOutOrder_Click(sender As Object, e As EventArgs) Handles tsbStockOutOrder.Click

sqlFlag = "出库单"

Call showGrid(sqlFlag)

'在查询出库物品单时，才允许增改删

tsbAdd.Enabled = True

tsbEdit.Enabled = True

tsbDelete.Enabled = True

End Sub

Private Sub tsbStockOutFoods_Click(sender As Object, e As EventArgs) Handles tsbStockOutFoods.Click

sqlFlag = "出库物"

Call showGrid(sqlFlag)

'在查询出库物品时，禁止增改删

tsbAdd.Enabled = False

tsbEdit.Enabled = False

tsbDelete.Enabled = False

End Sub

'增加出库单

Private Sub tsbAdd_Click(sender As Object, e As EventArgs) Handles tsbAdd.Click

Dim F_StorageOutOrder As New FormStorageOutOrder(0)

F_StorageOutOrder.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_StorageOutOrder As New FormStorageOutOrder(orderid)

F_StorageOutOrder.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

'先查询该出库单记录是否当前操作员保存

command.CommandText = "select 雇员ID from 出库单 where 订单ID=" & orderid

Dim odReader As OleDbDataReader

odReader = command.ExecuteReader(CommandBehavior.SingleResult)

odReader.Read()

Dim recordUserID As Integer = odReader.GetInt32(0)

If recordUserID <> loginId Then

MessageBox.Show("不允许删除其它操作员的出库单")

Exit Sub

End If

odReader.Close()

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 FormStorageOut_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)

If sqlFlag = "出库物" Then

'当在出库物表格时，双击转到查询对应的订单

sqlQueryWhere = "where (出库单.订单ID=" & orderid & ") and (出库单.是否删除='否')"

sqlFlag = "自定义"

Call showGrid(sqlFlag)

'在查询出库物品单时，才允许增改删

tsbAdd.Enabled = True

tsbEdit.Enabled = True

tsbDelete.Enabled = True

Else

'当在出库单、自定义时，双击同选中某行后按下“修改”按钮的效果

Dim F_StorageOutOrder As New FormStorageOutOrder(orderid)

F_StorageOutOrder.ShowDialog()

End If

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_StockOutQuery As New FormStorageOutQuery

F_StockOutQuery.Owner = Me

F_StockOutQuery.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/114224615

栏目列表