vb.net 教程 20

教程 20"/>

vb.net 教程 20

版权声明：本文为博主原创文章，转载请在显著位置标明本文出处以及作者网名，未经作者允许不得用于商业目的。

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

    入库单中，如果修改了货物种类、数量，应该对前后货物情况进行组合，获得货物增删情况，并最终对应修改在货物信息表的库存量字段中的数据。例如下面
    原入库单中入库的三种货物的ID和数量： 1,10   2,20  3,30
    修改后入库单中三种货物的ID和数量：2,25  3,20  4,15
    先进行组合，然后再修改货物信息表的库存量
    组合后的货物ID和数量： 1,10  2,-5  3,10  4,-15
    在代码中使用 Dictionary(Of Integer, Integer) 保存旧、新入库单中货物ID和数量，再使用editGoodsCount()方法对数据进行组合。关于Dictionary，请参看教程第4.2.3节《键值对集合》。
    全部代码如下：
 

Imports System.ComponentModel
Imports System.Data.OleDbPublic Class FormStorageInOrder'是否新增标志，如果是，设置为True；否则FalseDim isAdd As Boolean'传入的入库单ID号，也是判断新增还是修改的依据Dim orderId As IntegerDim connection As OleDbConnection'修改入库清单中的物品时设置标记Dim isEditGoods As Boolean = FalseStructure GoodsTypeDim TypeId As IntegerDim TypeInfo As StringEnd StructureDim 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 = OrderIdIf Me.orderId = 0 Then isAdd = True Else isAdd = FalseEnd SubPrivate Sub FormStorageInOrder_Load(sender As Object, e As EventArgs) Handles MyBase.LoaddtpOrder.Value = NowdtpEnter.Value = NowarrExpress = 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 和 cbGoodsTypePrivate Sub fillControls()'新建OleDbCommand对象实例Dim command As New OleDbCommand()'==========填充货物种类选择框==================='要执行的SQL查询command.CommandText = "select * from 货物类别"'设置OleDbCommand的数据连接为OleDbConnectioncommand.Connection = connection'声明OleDbDataReader对象Dim odReader As OleDbDataReader'通过OleDbCommand的ExecuteReader方法获得OleDbDataReader对象实例。odReader = command.ExecuteReader()Dim gt As GoodsType'如果OleDbDataReader中包含数据If odReader.HasRows Then'循环读取每一行数据，直到Read方法返回FalseDo While odReader.Readgt.TypeId = odReader.GetValue(0)cbGoodsType.Items.Add(odReader.GetValue(1))gt.TypeInfo = odReader.GetValue(2)arrGoodsType.Add(gt)LoopEnd If'关闭数据读取器odReader.Close()End Sub'向控件中填充数据'如果是新增，那么保持控件原状'如果是修改，那么需要读取数据库中的数据再填充Private Sub drawControls()If isAdd = True ThencbGoodsType.SelectedIndex = 0Call EnabledControls()Else'如果是修改数据，那么填充所有控件中的数据'新建OleDbCommand对象实例Dim command As New OleDbCommand()'=========填充lvGoodsType=================='要执行的SQL查询command.CommandText = "select 操作员ID,订购日期,入库日期 from 入库单 where 入库单ID=" & orderId'设置OleDbCommand的数据连接为OleDbConnectioncommand.Connection = connection'声明OleDbDataReader对象Dim odReader As OleDbDataReader'通过OleDbCommand的ExecuteReader方法获得OleDbDataReader对象实例。odReader = command.ExecuteReader(CommandBehavior.SingleRow)odReader.Read()'省略了检查数据记录是否有效Dim recordUserID As Integer = odReader.GetValue(0)dtpOrder.Value = odReader.GetValue(1).ToStringdtpEnter.Value = odReader.GetValue(2).ToStringcbGoodsType.SelectedIndex = 0odReader.Close()'=========填充 lvEnterInfo =================='要执行的SQL查询command.CommandText = "SELECT 入库单明细.产品ID, 货物信息.产品名称, 入库单明细.单价, 入库单明细.数量 " &"FROM 货物信息 INNER JOIN 入库单明细 ON 货物信息.产品ID = 入库单明细.产品ID " &"where 入库单明细.入库单ID=" & orderId'设置OleDbCommand的数据连接为OleDbConnectioncommand.Connection = connectionodReader = command.ExecuteReader()Dim lvItem As ListViewItem'如果OleDbDataReader中包含数据If odReader.HasRows Then'循环读取每一行数据，直到Read方法返回FalseDo While odReader.ReadlvItem = 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)lvEnterInfo.Items.Add(lvItem)dicGoodsInfoOld.Add(odReader.GetValue(0), odReader.GetValue(3))LoopEnd IfodReader.Close()Call UnabledControls()If recordUserID <> loginId ThenbtnSave.Enabled = FalsebtnEdit.Enabled = FalseEnd IfEnd IfEnd Sub'如果是新建，则允许控件操作Private Sub EnabledControls()dtpOrder.Enabled = TruedtpEnter.Enabled = TruelvGoods.Enabled = TruebtnGoodsAdd.Enabled = TruebtnDelete.Enabled = TruetxtGoodsPrice.Enabled = TruenudGoodsCount.Enabled = TruelvEnterInfo.Enabled = TruebtnSave.Enabled = TruebtnEdit.Enabled = FalsebtnClose.Enabled = TrueEnd Sub'如果是修改，初始不允许控件操作Private Sub UnabledControls()dtpOrder.Enabled = FalsedtpEnter.Enabled = FalsecbGoodsType.Enabled = FalselvGoods.Enabled = FalsebtnGoodsAdd.Enabled = FalsebtnDelete.Enabled = FalsetxtGoodsPrice.Enabled = FalsenudGoodsCount.Enabled = FalselvEnterInfo.Enabled = FalsebtnSave.Enabled = FalsebtnEdit.Enabled = TruebtnClose.Enabled = TrueEnd Sub'入库物类别选中Private Sub cbGoodsType_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cbGoodsType.SelectedIndexChangedDim selIndex As Integer = cbGoodsType.SelectedIndexlblGoodsTypeInfo.Text = arrGoodsType(selIndex).TypeInfolvGoods.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的数据连接为OleDbConnectioncommand.Connection = connection'声明OleDbDataReader对象Dim odReader As OleDbDataReader'通过OleDbCommand的ExecuteReader方法获得OleDbDataReader对象实例。odReader = command.ExecuteReader()Dim lvItem As ListViewItem'如果OleDbDataReader中包含数据If odReader.HasRows Then'循环读取每一行数据，直到Read方法返回FalseDo While odReader.ReadlvItem = 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)LoopEnd IfodReader.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'新建OleDbCommand对象实例Dim command As New OleDbCommand()'设置OleDbCommand的数据连接为OleDbConnectioncommand.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 OleDbDataReaderodReader = command.ExecuteReader(CommandBehavior.SingleResult)odReader.Read()'记录刚建的入库单ID号Dim newOrderID As Integer = odReader.GetInt32(0)odReader.Close()'3、向出库单明细中加入出库物品Call addGoodsDetailed(newOrderID)'4、修改货物信息表中对应的数量Call editGoodsCount()'当前出库单IDorderId = newOrderID'设置标志为修改isAdd = FalseElse'修改的SQL语句command.CommandText = getEditSql()'不管是新增还是修改，都不用返回值，所以使用ExecuteNonQuery。command.ExecuteNonQuery()'如果修改了出库物品，那么If isEditGoods = True Then'1、删除出库单明细中原有出库物品command.CommandText = "delete * from 出库单明细 where 订单ID=" & orderIdcommand.ExecuteNonQuery()'2、重新向出库单明细中加入出库物品Call addGoodsDetailed(orderId)'3、修改货物信息表中对应的数量Call editGoodsCount()End IfEnd If'刷新货物信息 lvGoodsDim selIndex As Integer = cbGoodsType.SelectedIndexlblGoodsTypeInfo.Text = arrGoodsType(selIndex).TypeInfolvGoods.Items.Clear()Call fillLvGoods(arrGoodsType(selIndex).TypeId)'保存之后禁止编辑数据Call UnabledControls()End Sub'新增时候插入入库单详细使用的sql语句Private Function getAddSql() As String'订购日期时间Dim orderTime As String = dtpOrder.Text'入库日期时间Dim enterTime As String = dtpEnter.TextDim sqlString As StringsqlString = "insert into 入库单(操作员ID,订购日期,入库日期,是否删除) " &"values(" & loginId & ",'" & orderTime & "','" & enterTime & "','否')"Return sqlStringEnd Function'修改时候入库单详细使用的sql语句Private Function getEditSql() As String'订购日期时间Dim orderTime As String = dtpOrder.Text'入库日期时间Dim enterTime As String = dtpEnter.TextDim sqlString As StringsqlString = "update 入库单 set 订购日期='" & orderTime & "',入库日期='" & enterTime & "' where 入库单ID=" & orderIdReturn sqlStringEnd Function'增加入货单明细内容Private Sub addGoodsDetailed(ByVal id As Integer)'新建OleDbCommand对象实例Dim command As New OleDbCommand()'设置OleDbCommand的数据连接为OleDbConnectioncommand.Connection = connectionDim lvi As ListViewItemDim GoodsID As IntegerDim GoodsPrice As SingleDim GoodsCount As Integer'购买清单中的货物逐项加入数据表 出库单明细For i As Integer = 0 To lvEnterInfo.Items.Count - 1lvi = lvEnterInfo.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))NextEnd 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'修改原 DictionaryFor i As Integer = 0 To dicGoodsInfoNew.Count - 1Dim singleGoodsNew As KeyValuePair(Of Integer, Integer) = dicGoodsInfoNew.ElementAt(i)If dicGoodsInfoOld.ContainsKey(singleGoodsNew.Key) = True ThenConsole.WriteLine("输出 ： {0} {1}", dicGoodsInfoOld(singleGoodsNew.Key), singleGoodsNew.Value)dicGoodsInfoOld(singleGoodsNew.Key) = dicGoodsInfoOld(singleGoodsNew.Key) - singleGoodsNew.ValueElsedicGoodsInfoOld.Add(singleGoodsNew.Key, 0 - singleGoodsNew.Value)End IfNext'修改数据库Dim command As New OleDbCommand()command.Connection = connection'购买清单中的货物逐项加入数据表 出库单明细For Each singleGoodsNew As KeyValuePair(Of Integer, Integer) In dicGoodsInfoOld'只需要对数量有变化的货物进行修改If singleGoodsNew.Value <> 0 Thencommand.CommandText = "update 货物信息 set 库存量=库存量-(" & singleGoodsNew.Value & ") where 产品ID=" & singleGoodsNew.Key'Console.WriteLine("value:" & singleGoodsNew.Value)'Console.WriteLine(command.CommandText)command.ExecuteNonQuery()End IfNext'数据库中修改后，将原Dictionary的值修改为 新Dictionary的值dicGoodsInfoOld = dicGoodsInfoNewdicGoodsInfoNew.Clear()End Sub'按下关闭按钮Private Sub btnClose_Click(sender As Object, e As EventArgs) Handles btnClose.ClickIf btnSave.Enabled = True ThenIf MessageBox.Show("数据未保存，是否退出？", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning) = DialogResult.OK ThenMe.Close()End IfElseMe.Close()End IfEnd Sub'点击lvGoods中的项目，将数据添加到对应位置Private Sub lvGoods_MouseClick(sender As Object, e As MouseEventArgs) Handles lvGoods.MouseClickDim lvhti As ListViewHitTestInfo = lvGoods.HitTest(e.X, e.Y)If IsNothing(lvhti.Item) ThenExit SubEnd IflblGoodsID.Text = lvhti.Item.SubItems(0).TextlblGoodsName.Text = lvhti.Item.SubItems(1).TexttxtGoodsPrice.Text = lvhti.Item.SubItems(3).TextEnd Sub'购买的货物信息增加到lvBuyInfoPrivate Sub btnGoodsAdd_Click(sender As Object, e As EventArgs) Handles btnGoodsAdd.ClickDim GoodsID As String = lblGoodsID.TextDim GoodsName As String = lblGoodsName.TextDim GoodsPrice As SingleIf Single.TryParse(txtGoodsPrice.Text, GoodsPrice) = False ThenMessageBox.Show("不是有效的货物价格。")Exit SubEnd IfDim GoodsCount As Integer = nudGoodsCount.ValueFor i As Integer = 0 To lvEnterInfo.Items.Count - 1If GoodsID = lvEnterInfo.Items(i).Text ThenMessageBox.Show("该货物已经添加，请先删除再添加。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning)Exit SubEnd IfNext'添加到lvEnterInfoDim lvi As New ListViewItem(GoodsID)lvi.SubItems.Add(GoodsName)lvi.SubItems.Add(GoodsPrice)lvi.SubItems.Add(GoodsCount)lvEnterInfo.Items.Add(lvi)'标记修改了出库物品isEditGoods = TrueEnd Sub'删除增加的出库物品Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.ClickIf lvEnterInfo.SelectedItems.Count < 1 ThenMessageBox.Show("未选择需要删除的数据")Exit SubEnd IflvEnterInfo.Items.Remove(lvEnterInfo.SelectedItems(0))'标记修改了出库物品isEditGoods = TrueEnd SubPrivate Sub FormStorageInOrder_Closing(sender As Object, e As CancelEventArgs) Handles Me.Closingconnection.Close()End Sub
End Class

由于平台下C#和vb.NET很相似，本文也可以为C#爱好者提供的参考。

学习更多vb知识，请参看 vb 教程 目录