FrmStock进货管理

Imports System.Data
Imports System.Data.SqlClient

Public Class FrmStock
    Private Sub FrmStock_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            Dim conn As New SqlConnection
            conn.ConnectionString = "data source=127.0.0.1;initial catalog=Supermarket;user id=text;password=text;"
            Dim sql As String
            sql = "select stockID as 进货编号,barcode as 条形码 ,merchname as 商品名称,quantity as 数量,unitprice as 单价,unitprice*quantity as 总额 " _
                       & ",stockdate as 进货日期,employeesid as 进货员编号,supplierid as 供应商ID " _
                       & "from stocks order by barcode"
            Dim adp As New SqlDataAdapter(sql, conn)
            Dim ds As New DataSet
            adp.Fill(ds, "a")
            Me.DataGrid1.DataSource = ds.Tables("a").DefaultView
        Catch ex As Exception
            'MsgBox(ex.ToString)
        End Try
    End Sub
    Private Sub ButQue_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButQue.Click
        Try
            Dim mytable As New DataTable
            Dim conn As New SqlConnection
            Dim sql As String
            conn.ConnectionString = "data source=127.0.0.1;initial catalog=Supermarket;user=text;password=text;"
            If Me.TxtBarcode.Text = "" Then
                MsgBox("请输入条件")
            Else
                sql = "select stockid as 进货编号,barcode as 条形码 ,merchname as 商品名称,quantity as 数量,unitprice as 单价, supplierid as 供应商ID,employeesid as 进货员工id,stockdate as 进货日期 " _
                & "from stocks  where  barcode ='" & Me.TxtBarcode.Text & "'"
                Dim adp As New SqlDataAdapter(sql, conn)
                Dim ds As New DataSet
                adp.Fill(ds, "a")
                Me.DataGrid1.DataSource = ds.Tables("a")
            End If
        Catch ex As Exception
            'MsgBox(ex.ToString)
        End Try
    End Sub

 

    Private Sub ButAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButAdd.Click
        Try
            If Me.TxtBarcode.Text = "" Or _
               Me.TxtName.Text = "" Or _
               Me.TxtUnitPrice.Text = "" Or _
               Me.TxtTotalPrice.Text = "" Or _
               Me.TxtSup.Text = "" Or _
               Me.TxtQuantity.Text = "" Or _
               Me.TxtEmployeeID.Text = "" Or _
               Me.TxtData.Text = "" Then
                MsgBox("进货信息不能为空！")
                Exit Sub
            Else
                Dim yn As Integer
                Dim conn As New SqlConnection
                conn.ConnectionString = "data source=  127.0.0.1  ;initial catalog=Supermarket;user id=text;password=text;"
                Dim sql As String
                sql = "select * from merchs where barcode='" & Me.TxtBarcode.Text & "'"
                Dim cmd As New SqlCommand(sql, conn)
                Dim dr As SqlDataReader
                Dim ds As New DataSet
                conn.Open()
                dr = cmd.ExecuteReader
                If dr.Read = True Then
                    yn = MsgBox("该商品在库存以有记录,要更新库存吗？", MsgBoxStyle.YesNo)
                    If yn = 6 Then
                        conn.Close()
                        Dim sql1 As String
                        sql1 = "update merchs set Quantity=Quantity + '" & Me.TxtQuantity.Text & "'"
                        Dim adp As New SqlDataAdapter(sql1, conn)
                        adp.Fill(ds, "a")
                        sql1 = "insert into stocks(barcode,merchname,quantity,unitprice,stockdate,employeesid,Supplierid)values("
                        sql1 &= "'" & Me.TxtBarcode.Text & "','" & Me.TxtName.Text & "'," & Me.TxtQuantity.Text & ",'" & Me.TxtUnitPrice.Text & "'"
                        sql1 &= ",'" & Me.TxtData.Text & "','" & Me.TxtEmployeeID.Text & "','" & Me.TxtSup.Text & "')"
                        Dim adp1 As New SqlDataAdapter(sql1, conn)
                        Dim ds1 As New DataSet
                        adp1.Fill(ds1, "a")
                    ElseIf yn = 7 Then
                        Exit Sub
                    End If
                Else
                    conn.Close()
                    yn = MsgBox("该商品在库存没有记录,要添加吗？", MsgBoxStyle.YesNo)
                    If yn = 6 Then
                        Dim str As String
                        str = "insert into merchs(barcode,merchname,unitprice,quantity,supplierid)values("
                        str &= "'" & Me.TxtBarcode.Text & "','" & Me.TxtName.Text & "'," & Me.TxtUnitPrice.Text & ",'" & Me.TxtQuantity.Text & "','" & Me.TxtSup.Text & "')"
                        Dim adp As New SqlDataAdapter(str, conn)
                        adp.Fill(ds, "a")
                        sql = "insert into stocks(barcode,merchname,quantity,unitprice,stockdate,employeesid,Supplierid)values("
                        sql &= "'" & Me.TxtBarcode.Text & "','" & Me.TxtName.Text & "'," & Me.TxtQuantity.Text & ",'" & Me.TxtUnitPrice.Text & "' "
                        sql &= ",'" & Me.TxtData.Text & "','" & Me.TxtEmployeeID.Text & "','" & Me.TxtSup.Text & "')"
                        Dim adp1 As New SqlDataAdapter(sql, conn)
                        Dim ds1 As New DataSet
                        adp1.Fill(ds1, "a")
                    ElseIf yn = 7 Then
                        Exit Sub
                    End If
                End If
            End If
        Catch ex As Exception
            MsgBox("没有此厂商的记录！")
            Exit Sub
        Finally
            FrmStock_Load(sender, e)
        End Try
    End Sub

    Private Sub ButEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButEdit.Click
        Try
            Dim yn As Integer
            yn = MsgBox("是否修改记录？", MsgBoxStyle.YesNo, "修改记录")
            If yn = 6 Then
                Dim conn As New SqlConnection
                conn.ConnectionString = "data source=127.0.0.1;initial catalog=Supermarket;user=text;password=text"
                Dim myset As New DataSet
                Dim mytable As New DataTable
                Dim sql As String
                sql = "update stocks set merchName='" & Me.TxtName.Text & "',unitprice=" & Me.TxtUnitPrice.Text & ",quantity='" & Me.TxtQuantity.Text & "'"
                sql &= " where barcode='" & Me.TxtBarcode.Text & "'"
                Dim mydata As New SqlDataAdapter(sql, conn)
                mydata.Fill(myset, "a")
                DataGrid1.DataSource = myset.Tables("a")
                FrmStock_Load(sender, e)
            End If
        Catch ex As Exception
            ' MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub BuDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BuDelete.Click
        Try
            Dim yn As Integer
            If Me.TxtBarcode.Text <> "" Then

            Else
                MsgBox("记录不能为空！")
                Exit Sub
            End If
            yn = MsgBox("是否删除记录？", MsgBoxStyle.YesNo, "删除记录")
            If yn = 6 Then
                Dim conn As New SqlConnection
                conn.ConnectionString = "data source=127.0.0.1;initial catalog=Supermarket;user=text;password=text"
                Dim ds As New DataSet
                Dim mytable As New DataTable
                Dim sql As String
                sql = "delete from stocks where barcode='" & Me.TxtBarcode.Text & "'"
                Dim adp As New SqlDataAdapter(sql, conn)
                adp.Fill(ds, "a")
                MsgBox("成功删除记录！")
                FrmStock_Load(sender, e)
            End If
        Catch ex As Exception

        End Try
    End Sub
    Private Sub DataGrid1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataGrid1.Click
        Try
            Me.TxtBarcode.Text = DataGrid1.Item(DataGrid1.CurrentCell.RowNumber, 1).ToString
            Me.TxtName.Text = DataGrid1.Item(DataGrid1.CurrentCell.RowNumber, 2).ToString
            Me.TxtQuantity.Text = DataGrid1.Item(DataGrid1.CurrentCell.RowNumber, 3).ToString
            Me.TxtUnitPrice.Text = DataGrid1.Item(DataGrid1.CurrentCell.RowNumber, 4).ToString
            Me.TxtSup.Text = DataGrid1.Item(DataGrid1.CurrentCell.RowNumber, 8).ToString
            Me.TxtEmployeeID.Text = DataGrid1.Item(DataGrid1.CurrentCell.RowNumber, 7).ToString
            Me.TxtData.Text = DataGrid1.Item(DataGrid1.CurrentCell.RowNumber, 6).ToString
            Me.TxtTotalPrice.Text = DataGrid1.Item(DataGrid1.CurrentCell.RowNumber, 5).ToString
        Catch ex As Exception

        End Try

    End Sub

End Class