frmgoods.vb

这是一个关于图书仓库管理系统的程序源代码。是我的毕业设计的作品

            Dim STRSQL = "SELECT * FROM GOODS WHERE GoodsName='" & txtGoodsName.Text.Trim() & "' and warehouseid='" & cboWarehouseID.Text.Trim() & "'"
            myDataObj.ExecuteSQL(STRSQL, drSqlServer)
            If drSqlServer.Read() Then
                MsgBox("此种图书信息已存在", MsgBoxStyle.Exclamation)

                txtGoodsID.Text = ""
                txtGoodsName.Text = ""
                cboTypeName.Text = ""
                txtPrice.Text = ""
                txtStockNum.Text = ""
                txtUnit.Text = ""
                txtNumLow.Text = ""
                txtNumHigh.Text = ""
                cboWarehouseName.Text = ""

                drSqlServer.Close()
            Else
                drSqlServer.Close()

                Dim Params() As SqlParameter = _
                            {New SqlParameter("@GoodsName", SqlDbType.VarChar), _
                             New SqlParameter("@TypeID", SqlDbType.Int), _
                             New SqlParameter("@Price", SqlDbType.Money), _
                             New SqlParameter("@StockNum", SqlDbType.Int), _
                             New SqlParameter("@Unit", SqlDbType.VarChar), _
                             New SqlParameter("@NumLow", SqlDbType.Int), _
                             New SqlParameter("@NumHigh", SqlDbType.Int), _
                             New SqlParameter("@WarehouseID", SqlDbType.Int)}

                Params(0).Value = txtGoodsName.Text.Trim
                Params(1).Value = Val(cboTypeID.Text)
                Params(2).Value = Val(txtPrice.Text)
                Params(3).Value = Val(txtStockNum.Text)
                Params(4).Value = txtUnit.Text.Trim
                Params(5).Value = Val(txtNumLow.Text)
                Params(6).Value = Val(txtNumHigh.Text)
                Params(7).Value = Val(cboWarehouseID.Text)

                myDataObj.ExecuteSP("sp_InsertGoods", Params)

                MsgBox("添加完毕", MsgBoxStyle.Information)
            End If

        ElseIf strCommand = "update" Then
                Dim Params() As SqlParameter = _
                                       {New SqlParameter("@GoodsID", SqlDbType.Int), _
                                        New SqlParameter("@GoodsName", SqlDbType.VarChar), _
                                        New SqlParameter("@TypeID", SqlDbType.Int), _
                                        New SqlParameter("@Price", SqlDbType.Money), _
                                        New SqlParameter("@StockNum", SqlDbType.Int), _
                                        New SqlParameter("@Unit", SqlDbType.VarChar), _
                                        New SqlParameter("@NumLow", SqlDbType.Int), _
                                        New SqlParameter("@NumHigh", SqlDbType.Int), _
                                        New SqlParameter("@WarehouseID", SqlDbType.Int)}

                params(0).Value = Val(txtGoodsID.Text)
                Params(1).Value = txtGoodsName.Text.Trim
                Params(2).Value = Val(cboTypeID.Text)
                Params(3).Value = Val(txtPrice.Text)
                Params(4).Value = Val(txtStockNum.Text)
                Params(5).Value = txtUnit.Text.Trim
                Params(6).Value = Val(txtNumLow.Text.Trim)
                Params(7).Value = Val(txtNumHigh.Text.Trim)
                Params(8).Value = Val(cboWarehouseID.Text)

                myDataObj.ExecuteSP("sp_UpdateGoods", Params)

                MsgBox("更新完毕", MsgBoxStyle.Information)
            Else
                Me.Close()
            End If
        STRSQL = "SELECT g.GoodsID, g.GoodsName, c.TypeName,g.TypeID,  g.Price, g.StockNum, g.Unit, g.NumLow, g.NumHigh,  w.WarehouseName, g.WarehouseID " & _
                         "FROM Goods g " & _
                         "INNER JOIN Category c ON g.TypeID=c.TypeID " & _
                         "INNER JOIN Warehouse w ON g.WarehouseID=w.WarehouseID"
            freshData()
            strCommand = ""
            btnAdd.Enabled = True
            btnUpdate.Enabled = True
            btnDel.Enabled = True
            GroupBox2.Enabled = False

    End Sub

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        If strCommand = "add" Then
            txtGoodsID.Text = ""
            txtGoodsName.Text = ""
            cboTypeName.SelectedIndex = 0
            txtPrice.Text = ""
            txtStockNum.Text = ""
            txtUnit.Text = "本"
            txtNumLow.Text = ""
            txtNumHigh.Text = ""
            cboWarehouseName.SelectedIndex = 0
            GroupBox2.Enabled = False
            btnAdd.Enabled = True
            btnUpdate.Enabled = True
            btnDel.Enabled = True

            strCommand = ""
        ElseIf strCommand = "update" Then
            GroupBox2.Enabled = False
            btnAdd.Enabled = True
            btnUpdate.Enabled = True
            btnDel.Enabled = True
            strCommand = ""
        Else
            Me.Close()
        End If

    End Sub


    Private Sub dgGoods_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles dgGoods.Click
        showData()
    End Sub

    Private Sub cboTypeName_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboTypeName.SelectedIndexChanged
        cboTypeID.SelectedIndex = cboTypeName.SelectedIndex
    End Sub

    Private Sub cboWarehouseName_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboWarehouseName.SelectedIndexChanged
        cboWarehouseID.SelectedIndex = cboWarehouseName.SelectedIndex
    End Sub


    Private Sub cboOrderbyType_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboOrderbyType.SelectedIndexChanged
        If cboOrderbyType.SelectedIndex = 0 Then
            strSQL = "SELECT g.GoodsID, g.GoodsName, c.TypeName,g.TypeID,  g.Price, g.StockNum, g.Unit, g.NumLow, g.NumHigh,  w.WarehouseName, g.WarehouseID " & _
                     "FROM Goods g " & _
                     "INNER JOIN Category c ON g.TypeID=c.TypeID " & _
                     "INNER JOIN Warehouse w ON g.WarehouseID=w.WarehouseID"
        Else
            strSQL = "SELECT g.GoodsID, g.GoodsName, c.TypeName,g.TypeID,  g.Price, g.StockNum, g.Unit, g.NumLow, g.NumHigh,  w.WarehouseName, g.WarehouseID " & _
                     "FROM Goods g " & _
                     "INNER JOIN Category c ON g.TypeID=c.TypeID " & _
                     "INNER JOIN Warehouse w ON g.WarehouseID=w.WarehouseID " & _
                     "WHERE c.TypeName='" & cboOrderbyType.Text & "'"
        End If
        freshData()
    End Sub

    Private Sub cboOrderByWarehouse_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboOrderByWarehouse.SelectedIndexChanged
        If cboOrderByWarehouse.SelectedIndex = 0 Then
            strSQL = "SELECT g.GoodsID, g.GoodsName, c.TypeName,g.TypeID,  g.Price, g.StockNum, g.Unit, g.NumLow, g.NumHigh,  w.WarehouseName, g.WarehouseID " & _
                     "FROM Goods g " & _
                     "INNER JOIN Category c ON g.TypeID=c.TypeID " & _
                     "INNER JOIN Warehouse w ON g.WarehouseID=w.WarehouseID"

        Else
            strSQL = "SELECT g.GoodsID, g.GoodsName, c.TypeName,g.TypeID,  g.Price, g.StockNum, g.Unit, g.NumLow, g.NumHigh,  w.WarehouseName, g.WarehouseID " & _
                     "FROM Goods g " & _
                     "INNER JOIN Warehouse w ON g.WarehouseID=w.WarehouseID " & _
                     "INNER JOIN Category c ON g.TypeID=c.TypeID " & _
                     "WHERE w.WarehouseName='" & cboOrderByWarehouse.Text & "'"

        End If
        freshData()
    End Sub

    Private Sub cboOrderbyNum_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboOrderbyNum.SelectedIndexChanged
        If cboOrderbyNum.SelectedIndex = 0 Then '所有
            strSQL = "SELECT g.GoodsID, g.GoodsName, c.TypeName,g.TypeID,  g.Price, g.StockNum, g.Unit, g.NumLow, g.NumHigh,  w.WarehouseName, g.WarehouseID " & _
                     "FROM Goods g " & _
                     "INNER JOIN Category c ON g.TypeID=c.TypeID " & _
                     "INNER JOIN Warehouse w ON g.WarehouseID=w.WarehouseID"

        ElseIf cboOrderbyNum.SelectedIndex = 1 Then '正常
            strSQL = "SELECT g.GoodsID, g.GoodsName, c.TypeName,g.TypeID,  g.Price, g.StockNum, g.Unit, g.NumLow, g.NumHigh,  w.WarehouseName, g.WarehouseID " & _
                     "FROM Goods g " & _
                     "INNER JOIN Warehouse w ON g.WarehouseID=w.WarehouseID " & _
                     "INNER JOIN Category c ON g.TypeID=c.TypeID " & _
                     "WHERE  g.StockNum BETWEEN g.NumLow AND g.NUMHIGH"

        ElseIf cboOrderbyNum.SelectedIndex = 2 Then '过低
            strSQL = "SELECT g.GoodsID, g.GoodsName, c.TypeName,g.TypeID,  g.Price, g.StockNum, g.Unit, g.NumLow, g.NumHigh,  w.WarehouseName, g.WarehouseID " & _
                     "FROM Goods g " & _
                     "INNER JOIN Warehouse w ON g.WarehouseID=w.WarehouseID " & _
                     "INNER JOIN Category c ON g.TypeID=c.TypeID " & _
                     "WHERE g.StockNum<g.NumLow"

        ElseIf cboOrderbyNum.SelectedIndex = 3 Then '过高
            strSQL = "SELECT g.GoodsID, g.GoodsName, c.TypeName,g.TypeID,  g.Price, g.StockNum, g.Unit, g.NumLow, g.NumHigh,  w.WarehouseName, g.WarehouseID " & _
                     "FROM Goods g " & _
                     "INNER JOIN Warehouse w ON g.WarehouseID=w.WarehouseID " & _
                     "INNER JOIN Category c ON g.TypeID=c.TypeID " & _
                     "WHERE g.StockNum>g.NumHigh"
        End If
        freshData()
    End Sub

    Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        strSQL = "SELECT g.GoodsID, g.GoodsName, c.TypeName,g.TypeID,  g.Price, g.StockNum, g.Unit, g.NumLow, g.NumHigh,  w.WarehouseName, g.WarehouseID " & _
                 "FROM Goods g " & _
                 "INNER JOIN Warehouse w ON g.WarehouseID=w.WarehouseID " & _
                 "INNER JOIN Category c ON g.TypeID=c.TypeID " & _
                 "WHERE g.GoodsName='" & txtOrderValue.Text.Trim() & "'"
        freshData()
    End Sub

    Private Sub btnDel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDel.Click
        If MsgBox("确定要删除记录:" & txtGoodsName.Text & "?", MsgBoxStyle.Exclamation + MsgBoxStyle.OKCancel) = MsgBoxResult.OK Then
            Dim Params() As SqlParameter = {New SqlParameter("@GoodsID", SqlDbType.VarChar)}
            Params(0).Value = txtGoodsID.Text

            myDataObj.ExecuteSP("sp_DelGoods", Params)

            Dim strSQL = "SELECT * FROM GOODS WHERE GOODSID='" & Params(0).Value & "'"
            myDataObj.ExecuteSQL(strSQL, drSqlServer)

            If drSqlServer.Read() Then
                MsgBox("此图书信息已在其它地方存在记录,不可删除!", MsgBoxStyle.Information)

            Else

                MsgBox("删除完毕", MsgBoxStyle.Information)
            End If
            drSqlServer.Close()


            freshData()
        End If
    End Sub


    Private Sub dgGoods_Navigate(ByVal sender As System.Object, ByVal ne As System.Windows.Forms.NavigateEventArgs) Handles dgGoods.Navigate

    End Sub


    Private Sub txtPrice_LostFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtPrice.LostFocus
        Dim i As Integer
        txtPrice.Text = Trim(txtPrice.Text)
        If txtPrice.Text = "" Then
            Exit Sub
        End If
        Try
            i = CInt(txtPrice.Text)
        Catch
            MsgBox("只能是0-9之间的数字组成", MsgBoxStyle.Information)
            txtPrice.Text = ""
            txtPrice.Focus()

            Exit Sub

        End Try
        If Val(txtPrice.Text) < 0 Then

            MsgBox("单价不能为负值,请重新输入", MsgBoxStyle.Information)
            txtPrice.Text = ""
            txtPrice.Focus()

        End If
       
    End Sub

    Private Sub txtNumLow_LostFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtNumLow.LostFocus
        Dim i As Integer
        txtNumLow.Text = Trim(txtNumLow.Text)
        If txtNumLow.Text = "" Then
            Exit Sub

        End If
        Try
            i = CInt(txtNumLow.Text)
        Catch
            MsgBox("只能是0-9之间的数字组成", MsgBoxStyle.Information)
            txtNumLow.Text = ""
            txtNumLow.Focus()

            Exit Sub

        End Try

        If Val(txtNumLow.Text) < 0 Or Val(txtNumLow.Text) <> i Then

            MsgBox("输入错误,请输入非负整数", MsgBoxStyle.Information)
            txtNumLow.Text = ""
            txtNumLow.Focus()


        End If
    End Sub

    Private Sub txtNumHigh_LostFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtNumHigh.LostFocus
        Dim i As Integer
        txtNumHigh.Text = Trim(txtNumHigh.Text)
        If txtNumHigh.Text = "" Then
            Exit Sub

        End If
        Try
            i = CInt(txtNumHigh.Text)
        Catch
            MsgBox("只能是0-9之间的数字组成", MsgBoxStyle.Information)
            txtNumHigh.Text = ""
            txtNumHigh.Focus()

            Exit Sub

        End Try
        If Val(txtNumHigh.Text) < Val(txtNumLow.Text) Or Val(txtNumHigh.Text) <> i Then

            MsgBox("输入错误,请输大于下限值的正整数", MsgBoxStyle.Information)
            txtNumHigh.Text = ""
            txtNumHigh.Focus()
        End If
    End Sub

    Private Sub txtStockNum_LostFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtStockNum.LostFocus
        Dim i As Integer
        txtStockNum.Text = Trim(txtStockNum.Text)
        If txtStockNum.Text = "" Then
            Exit Sub

        End If
        Try
            i = CInt(txtStockNum.Text)
        Catch
            MsgBox("只能是0-9之间的数字组成", MsgBoxStyle.Information)
            txtStockNum.Text = ""
            txtStockNum.Focus()

            Exit Sub

        End Try
        If Val(txtStockNum.Text) < 0 Or Val(txtStockNum.Text) <> i Then

            MsgBox("输入错误,请输入非负整数", MsgBoxStyle.Information)
            txtStockNum.Text = ""
            txtStockNum.Focus()

        End If
    End Sub

    Private Sub txtStockNum_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtStockNum.TextChanged

    End Sub

    Private Sub txtPrice_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtPrice.TextChanged

    End Sub
End Class