版权声明：本文为博主原创文章，转载请在显著位置标明本文出处以及作者网名，未经作者允许不得用于商业目的。
关于《Visual Basic.Net 循序渐进》请到百度网盘下载，具体下载地址：
链接：https://pan.baidu.com/s/1IfaLvlklx-nT4KK4VKZuIw
提取码：ip5n
由于出库单所包含的信息比入库单多得多，因此，代码难度主要在不遗漏项目。
全部代码如下：
Imports System.ComponentModel
Imports System.Data.OleDb
Public Class FormStorageOutOrder
'是否新增标志，如果是，设置为True；否则False
Dim isAdd As Boolean
'传入的出库单ID号，也是判断新增还是修改的依据
Dim orderId As Integer
Dim connection As OleDbConnection
'修改出库清单中的物品时设置标记
Dim isEditGoods As Boolean = False
Structure GoodsType
Dim TypeId As Integer
Dim TypeInfo As String
End Structure
Dim arrExpress As List(Of Integer)
Dim arrGoodsType As List(Of GoodsType)
'如果是修改原出库单
'键值对分别保存 货物ID和对应数量 ，需要在货物信息表（库存）中进行增删
'原出库单中的
Dim dicGoodsInfoOld As Dictionary(Of Integer, Integer)
'修改后出库单中的
Dim dicGoodsInfoNew As Dictionary(Of Integer, Integer)
'根据传入的订单号进行确认操作
'如果订单号为0，那么新增
'否则，修改
Public Sub New(ByVal OrderId As Integer)
' 此调用是设计器所必需的。
InitializeComponent()
' 在 InitializeComponent() 调用之后添加任何初始化。
Me.orderId = OrderId
If Me.orderId = 0 Then isAdd = True Else isAdd = False
End Sub
Private Sub FormStorageOutOrder_Load(sender As Object, e As EventArgs) Handles MyBase.Load
dtpOrder.Value = Now
dtpSend.Value = Now
arrExpress = New List(Of Integer)
arrGoodsType = New List(Of GoodsType)
dicGoodsInfoOld = New Dictionary(Of Integer, Integer)
dicGoodsInfoNew = New Dictionary(Of Integer, Integer)
connection = New OleDbConnection(databaseConnString)
'打开数据连接
connection.Open()
Call fillControls()
Call drawControls()
End Sub
'填充数据选项，主要是 cbExpress 和 cbGoodsType
Private Sub fillControls()
'新建OleDbCommand对象实例
Dim command As New OleDbCommand()
'=========填充快递公司选择框==================
'要执行的SQL查询
command.CommandText = "select * from 快递公司"
'设置OleDbCommand的数据连接为OleDbConnection
command.Connection = connection
'声明OleDbDataReader对象
Dim odReader As OleDbDataReader
'通过OleDbCommand的ExecuteReader方法获得OleDbDataReader对象实例。
odReader = command.ExecuteReader()
'如果OleDbDataReader中包含数据
If odReader.HasRows Then
'循环读取每一行数据，直到Read方法返回False
Do While odReader.Read
arrExpress.Add(odReader.GetValue(0))
cbExpress.Items.Add(odReader.GetValue(1))
Loop
End If
odReader.Close()
'==========填充货物种类选择框===================
'要执行的SQL查询
command.CommandText = "select * from 货物类别"
'通过OleDbCommand的ExecuteReader方法获得OleDbDataReader对象实例。
odReader = command.ExecuteReader()
Dim gt As GoodsType
'如果OleDbDataReader中包含数据
If odReader.HasRows Then
'循环读取每一行数据，直到Read方法返回False
Do While odReader.Read
gt.TypeId = odReader.GetValue(0)
cbGoodsType.Items.Add(odReader.GetValue(1))
gt.TypeInfo = odReader.GetValue(2)
arrGoodsType.Add(gt)
Loop
End If
'关闭数据读取器
odReader.Close()
End Sub
'向控件中填充数据
'如果是新增，那么保持控件原状
'如果是修改，那么需要读取数据库中的数据再填充
Private Sub drawControls()
If isAdd = True Then
cbExpress.SelectedIndex = 0
cbGoodsType.SelectedIndex = 0
Call EnabledControls()
Else
'如果是修改数据，那么填充所有控件中的数据
'新建OleDbCommand对象实例
Dim command As New OleDbCommand()
'=========填充lvGoodsType==================
'要执行的SQL查询
command.CommandText = "select 雇员ID,订购日期,发货日期,快递公司,快递费,快递单号,买家姓名,买家手机号码,买家地址 from 出库单 where 订单ID=" & orderId
'设置OleDbCommand的数据连接为OleDbConnection
command.Connection = connection
'声明OleDbDataReader对象
Dim odReader As OleDbDataReader
'通过OleDbCommand的ExecuteReader方法获得OleDbDataReader对象实例。
odReader = command.ExecuteReader(CommandBehavior.SingleRow)
odReader.Read()
'省略了检查数据记录是否有效
txtName.Text = odReader.GetValue(6).ToString
txtTel.Text = odReader.GetValue(7).ToString
txtAddr.Text = odReader.GetValue(8).ToString
dtpOrder.Value = odReader.GetValue(1).ToString
dtpSend.Value = odReader.GetValue(2).ToString
Dim express As Integer = CType(odReader.GetValue(3), Integer)
For i As Integer = 0 To arrExpress.Count - 1
If express = arrExpress(i) Then
cbExpress.SelectedIndex = i
Exit For
End If
Next
txtExpressPrice.Text = odReader.GetValue(4).ToString
txtExpressID.Text = odReader.GetValue(5).ToString
cbGoodsType.SelectedIndex = 0
Dim recordUserID As Integer = odReader.GetValue(0)
odReader.Close()
'=========填充 lvBuyInfo ==================
'要执行的SQL查询
command.CommandText = "SELECT 出库单明细.产品ID,货物信息.产品名称,出库单明细.单价,出库单明细.数量 " &
"FROM 货物信息 INNER JOIN 出库单明细 ON 货物信息.产品ID = 出库单明细.产品ID " &
"where 出库单明细.订单ID=" & orderId
'设置OleDbCommand的数据连接为OleDbConnection
command.Connection = connection
odReader = command.ExecuteReader()
Dim lvItem As ListViewItem
'如果OleDbDataReader中包含数据
If odReader.HasRows Then
'循环读取每一行数据，直到Read方法返回False
Do While odReader.Read
lvItem = New ListViewItem(odReader.GetValue(0).ToString)
lvItem.SubItems.Add(odReader.GetValue(1).ToString)
lvItem.SubItems.Add(odReader.GetValue(2).ToString)
lvItem.SubItems.Add(odReader.GetValue(3).ToString)
lvBuyInfo.Items.Add(lvItem)
dicGoodsInfoOld.Add(odReader.GetValue(0), odReader.GetValue(3))
Loop
End If
odReader.Close()
Call UnabledControls()
If recordUserID <> loginId Then
btnSave.Enabled = False
btnEdit.Enabled = False
End If
End If
End Sub
'如果是新建，则允许控件操作
Private Sub EnabledControls()
txtName.Enabled = True
txtTel.Enabled = True
txtAddr.Enabled = True
dtpOrder.Enabled = True
dtpSend.Enabled = True
cbExpress.Enabled = True
txtExpressPrice.Enabled = True
txtExpressID.Enabled = True
cbGoodsType.Enabled = True
lvGoods.Enabled = True
btnGoodsAdd.Enabled = True
btnDelete.Enabled = True
txtGoodsPrice.Enabled = True
nudGoodsCount.Enabled = True
lvBuyInfo.Enabled = True
btnSave.Enabled = True
btnEdit.Enabled = False
btnClose.Enabled = True
End Sub
'如果是修改，初始不允许控件操作
Private Sub UnabledControls()
txtName.Enabled = False
txtTel.Enabled = False
txtAddr.Enabled = False
dtpOrder.Enabled = False
dtpSend.Enabled = False
cbExpress.Enabled = False
txtExpressPrice.Enabled = False
txtExpressID.Enabled = False
cbGoodsType.Enabled = False
lvGoods.Enabled = False
btnGoodsAdd.Enabled = False
btnDelete.Enabled = False
txtGoodsPrice.Enabled = False
nudGoodsCount.Enabled = False
lvBuyInfo.Enabled = False
btnSave.Enabled = False
btnEdit.Enabled = True
btnClose.Enabled = True
End Sub
'出库物类别选中
Private Sub cbType_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cbGoodsType.SelectedIndexChanged
Dim selIndex As Integer = cbGoodsType.SelectedIndex
lblGoodsTypeInfo.Text = arrGoodsType(selIndex).TypeInfo
lvGoods.Items.Clear()
Call fillLvGoods(arrGoodsType(selIndex).TypeId)
End Sub
'当选择货物种类时，填充lvGoodsType数据
Private Sub fillLvGoods(ByVal TypeId As Integer)
'新建OleDbCommand对象实例
Dim command As New OleDbCommand()
'=========填充lvGoodsType==================
'要执行的SQL查询
command.CommandText = "Select 产品ID,产品名称,采购价格,销售价格,库存量 from 货物信息 where 类别ID=" & TypeId
'设置OleDbCommand的数据连接为OleDbConnection
command.Connection = connection
'声明OleDbDataReader对象
Dim odReader As OleDbDataReader
'通过OleDbCommand的ExecuteReader方法获得OleDbDataReader对象实例。
odReader = command.ExecuteReader()
Dim lvItem As ListViewItem
'如果OleDbDataReader中包含数据
If odReader.HasRows Then
'循环读取每一行数据，直到Read方法返回False
Do While odReader.Read
lvItem = New ListViewItem(odReader.GetValue(0).ToString)
lvItem.SubItems.Add(odReader.GetValue(1).ToString)
lvItem.SubItems.Add(odReader.GetValue(2).ToString)
lvItem.SubItems.Add(odReader.GetValue(3).ToString)
lvItem.SubItems.Add(odReader.GetValue(4).ToString)
lvGoods.Items.Add(lvItem)
Loop
End If
odReader.Close()
End Sub
'修改数据
Private Sub btnEdit_Click(sender As Object, e As EventArgs) Handles btnEdit.Click
'按下后允许修改数据
Call EnabledControls()
End Sub
'保存数据
Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
Dim errMsg As String = checkData()
If errMsg <> "" Then
MessageBox.Show(errMsg)
Exit Sub
End If
'新建OleDbCommand对象实例
Dim command As New OleDbCommand()
'设置OleDbCommand的数据连接为OleDbConnection
command.Connection = connection
'保存数据，分两种情况
'新增或修改
If isAdd = True Then
'1、将出库单添加到数据库中
'新增的SQL语句
command.CommandText = getAddSql()
'不管是新增还是修改，都不用返回值，所以使用ExecuteNonQuery。
command.ExecuteNonQuery()
'2、新增需要返回此次出库单对应的编号
command.CommandText = "Select top 1 订单ID from 出库单 order by 订单ID desc"
Dim odReader As OleDbDataReader
odReader = command.ExecuteReader(CommandBehavior.SingleResult)
odReader.Read()
'记录刚建的出库单ID号
Dim newOrderID As Integer = odReader.GetInt32(0)
odReader.Close()
'3、向出库单明细中加入出库物品
Call addGoodsDetailed(newOrderID)
'4、修改货物信息表中对应的数量
Call editGoodsCount()
'当前出库单ID
orderId = newOrderID
'设置标志为修改
isAdd = False
Else
'修改的SQL语句
command.CommandText = getEditSql()
'不管是新增还是修改，都不用返回值，所以使用ExecuteNonQuery。
command.ExecuteNonQuery()
'如果修改了出库物品，那么
If isEditGoods = True Then
'1、删除出库单明细中原有出库物品
command.CommandText = "delete * from 出库单明细 where 订单ID=" & orderId
command.ExecuteNonQuery()
'2、重新向出库单明细中加入出库物品
Call addGoodsDetailed(orderId)
'3、修改货物信息表中对应的数量
Call editGoodsCount()
End If
End If
'刷新货物信息 lvGoods
Dim selIndex As Integer = cbGoodsType.SelectedIndex
lblGoodsTypeInfo.Text = arrGoodsType(selIndex).TypeInfo
lvGoods.Items.Clear()
Call fillLvGoods(arrGoodsType(selIndex).TypeId)
'保存之后禁止编辑数据
Call UnabledControls()
End Sub
'检查数据合法性
Private Function checkData() As String
If txtName.Text.Trim = "" Then
Return "买家姓名不能为空"
End If
If txtTel.Text.Trim.Length <> 11 Then
Return "手机长度应为11位"
End If
If txtAddr.Text.Trim = "" Then
Return "买家地址不能为空"
End If
If txtExpressPrice.Text.Trim = "" Then
Return "运费不能为空"
End If
Dim expressprice As Single
If Single.TryParse(txtExpressPrice.Text, expressprice) = False Then
Return "运费不是有效的数字"
End If
If txtExpressID.Text.Trim = "" Then
Return "快递单号不能为空"
End If
If lvBuyInfo.Items.Count < 1 Then
Return "出库货物不能为空"
End If
Return ""
End Function
'新增时候插入出库单详细使用的sql语句
Private Function getAddSql() As String
'买家姓名
Dim customerName As String = txtName.Text.Trim
'联系电话
Dim customerTel As String = txtTel.Text
'送货地址
Dim customerAddr As String = txtAddr.Text
'运费
Dim expressPrice As String = txtExpressPrice.Text
'运单号
Dim expressID As String = txtExpressID.Text
'订购日期时间
Dim orderTime As String = dtpOrder.Text
'发货日期时间
Dim sendTime As String = dtpSend.Text
'物流公司ID
Dim express As Integer = arrExpress(cbExpress.SelectedIndex)
Dim sqlString As String
sqlString = "insert into 出库单(雇员ID,订购日期,发货日期,快递公司,快递费,快递单号,买家姓名,买家手机号码,买家地址,是否删除) " &
"values(" & loginId & ",'" & orderTime & "','" & sendTime & "'," & express & "," & expressPrice & ",'" &
expressID & "','" & customerName & "','" & customerTel & "','" & customerAddr & "','否')"
Return sqlString
End Function
'修改时候出库单详细使用的sql语句
Private Function getEditSql() As String
'买家姓名
Dim customerName As String = txtName.Text.Trim
'联系电话
Dim customerTel As String = txtTel.Text
'送货地址
Dim customerAddr As String = txtAddr.Text
'运费
Dim expressPrice As String = txtExpressPrice.Text
'运单号
Dim expressID As String = txtExpressID.Text
'订购日期时间
Dim orderTime As String = dtpOrder.Text
'发货日期时间
Dim sendTime As String = dtpSend.Text
'物流公司ID
Dim express As Integer = arrExpress(cbExpress.SelectedIndex)
Dim sqlString As String
sqlString = "update 出库单 set 订购日期='" & orderTime & "',发货日期='" & sendTime & "',快递公司=" & express & ",快递费=" & expressPrice &
",快递单号='" & expressID & "',买家姓名='" & customerName & "',买家手机号码='" & customerTel & "',买家地址='" & customerAddr &
"' where 订单ID=" & orderId
Return sqlString
End Function
'增加出货单明细内容
Private Sub addGoodsDetailed(ByVal id As Integer)
'新建OleDbCommand对象实例
Dim command As New OleDbCommand()
'设置OleDbCommand的数据连接为OleDbConnection
command.Connection = connection
Dim lvi As ListViewItem
Dim GoodsID As Integer
Dim GoodsPrice As Single
Dim GoodsCount As Integer
'购买清单中的货物逐项加入数据表 出库单明细
For i As Integer = 0 To lvBuyInfo.Items.Count - 1
lvi = lvBuyInfo.Items(i)
GoodsID = CType(lvi.SubItems(0).Text, Integer)
GoodsPrice = CType(lvi.SubItems(2).Text, Single)
GoodsCount = CType(lvi.SubItems(3).Text, Integer)
command.CommandText = "insert into 出库单明细 values(" & id & "," & GoodsID & "," & GoodsPrice & "," & GoodsCount & ")"
command.ExecuteNonQuery()
dicGoodsInfoNew.Add(CType(lvi.SubItems(0).Text, Integer), CType(lvi.SubItems(3).Text, Integer))
Next
End Sub
'修改货物信息表中的数量
Private Sub editGoodsCount()
'将前后两个Dictionary组合在一起，获得修改后货物的增减量
'A B 组合后
'1,10 2,25 1,10
'2,20 3,20 2,-5
'3,30 4,15 3,10
' 4,-15
'修改原 Dictionary
For i As Integer = 0 To dicGoodsInfoNew.Count - 1
Dim singleGoodsNew As KeyValuePair(Of Integer, Integer) = dicGoodsInfoNew.ElementAt(i)
If dicGoodsInfoOld.ContainsKey(singleGoodsNew.Key) = True Then
'Console.WriteLine("输出 ： {0} {1}", dicGoodsInfoOld(singleGoodsNew.Key), singleGoodsNew.Value)
dicGoodsInfoOld(singleGoodsNew.Key) = dicGoodsInfoOld(singleGoodsNew.Key) - singleGoodsNew.Value
Else
dicGoodsInfoOld.Add(singleGoodsNew.Key, 0 - singleGoodsNew.Value)
End If
Next
'修改数据库
Dim command As New OleDbCommand()
command.Connection = connection
'Dim GoodsID As Integer
'Dim GoodsCount As Integer
'购买清单中的货物逐项加入数据表 出库单明细
For Each singleGoodsNew As KeyValuePair(Of Integer, Integer) In dicGoodsInfoOld
'只需要对数量有变化的货物进行修改
If singleGoodsNew.Value <> 0 Then
command.CommandText = "update 货物信息 set 库存量=库存量+(" & singleGoodsNew.Value & ") where 产品ID=" & singleGoodsNew.Key
'Console.WriteLine("value:" & singleGoodsNew.Value)
'Console.WriteLine(command.CommandText)
command.ExecuteNonQuery()
End If
Next
'数据库中修改后，将原Dictionary的值修改为 新Dictionary的值
dicGoodsInfoOld = dicGoodsInfoNew
dicGoodsInfoNew.Clear()
End Sub
'按下关闭按钮
Private Sub btnClose_Click(sender As Object, e As EventArgs) Handles btnClose.Click
If btnSave.Enabled = True Then
If MessageBox.Show("数据未保存，是否退出？", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning) = DialogResult.OK Then
Me.Close()
End If
Else
Me.Close()
End If
End Sub
'点击lvGoods中的项目，将数据添加到对应位置
Private Sub lvGoods_MouseClick(sender As Object, e As MouseEventArgs) Handles lvGoods.MouseClick
Dim lvhti As ListViewHitTestInfo = lvGoods.HitTest(e.X, e.Y)
If IsNothing(lvhti.Item) Then
Exit Sub
End If
lblGoodsID.Text = lvhti.Item.SubItems(0).Text
lblGoodsName.Text = lvhti.Item.SubItems(1).Text
txtGoodsPrice.Text = lvhti.Item.SubItems(3).Text
End Sub
'购买的货物信息增加到lvBuyInfo
Private Sub btnGoodsAdd_Click(sender As Object, e As EventArgs) Handles btnGoodsAdd.Click
Dim GoodsID As String = lblGoodsID.Text
Dim GoodsName As String = lblGoodsName.Text
Dim GoodsPrice As Single
If Single.TryParse(txtGoodsPrice.Text, GoodsPrice) = False Then
MessageBox.Show("不是有效的货物价格。")
Exit Sub
End If
Dim GoodsCount As Integer = nudGoodsCount.Value
Dim lvItem As ListViewItem
'找到对应lvGoods中的项目
For i As Integer = 0 To lvGoods.Items.Count - 1
lvItem = lvGoods.Items(i)
If lvItem.Text = GoodsID Then
'检查售价是否低于进价
If GoodsPrice < CType(lvItem.SubItems(3).Text, Single) Then
If MessageBox.Show("货物售价低于成本价，是否继续出库？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = DialogResult.No Then
Exit Sub
End If
End If
If GoodsCount > CType(lvItem.SubItems(4).Text, Integer) Then
MessageBox.Show("货物库存量不足", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning)
Exit Sub
End If
Exit For
End If
Next
For i As Integer = 0 To lvBuyInfo.Items.Count - 1
If GoodsID = lvBuyInfo.Items(i).Text Then
MessageBox.Show("该货物已经添加，请先删除再添加。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning)
Exit Sub
End If
Next
'添加到lvbuyinfo
Dim lvi As New ListViewItem(GoodsID)
lvi.SubItems.Add(GoodsName)
lvi.SubItems.Add(GoodsPrice)
lvi.SubItems.Add(GoodsCount)
lvBuyInfo.Items.Add(lvi)
'标记修改了出库物品
isEditGoods = True
End Sub
'删除增加的出库物品
Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click
If lvBuyInfo.SelectedItems.Count < 1 Then
MessageBox.Show("未选择需要删除的数据")
Exit Sub
End If
lvBuyInfo.Items.Remove(lvBuyInfo.SelectedItems(0))
'标记修改了出库物品
isEditGoods = True
End Sub
Private Sub FormStorageOutOrder_Closing(sender As Object, e As CancelEventArgs) Handles Me.Closing
connection.Close()
End Sub
End Class由于.net平台下C#和vb.NET很相似，本文也可以为C#爱好者提供的参考。
学习更多vb.net知识，请参看 vb.net 教程 目录
扫一扫
409
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
