FrmMerch库存管理

Imports System.Data
Imports System.Data.SqlClient
Public Class FrmMerch
 

    Private Sub Frmload()
        Try
            Dim conn As New SqlConnection
            conn.ConnectionString = "data source=127.0.0.1;initial catalog=Supermarket;user=text;password=text"
            Dim sql As String
            sql = "select barcode as 条形码 ,merchname as 商品名称,quantity as 数量,unitprice as 单价,unitprice*quantity as 总额,allowsell as 是否允许销售 ," _
            & " supplierid as 供应商ID,jgsl as 警告数量,cxj as 促销价,cxksrq as 促价开始日期,cxjsrq as 促销结束日期 " _
            & "from merchs 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")
        Catch ex As Exception
            ' MsgBox(ex.ToString)
            Exit Sub
        End Try
    End Sub
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Try
            Dim str As Integer
            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
                If Me.ComboBox1.Text = "是" Then
                    str = 1
                ElseIf Me.ComboBox1.Text = "否" Then
                    str = 0
                Else
                    Me.ComboBox1.Text = ""
                End If
                sql = "update merchs set merchName='" & Me.TxtProductName.Text & "',unitprice=" & Me.TxtUnitPrice.Text & ",quantity='" & Me.TxtQuantity.Text & "',"
                sql &= "jgsl='" & Me.Txtjgsl.Text & "',cxj='" & Me.Txtcxj.Text & "',cxksrq='" & Me.Txtcxksrq.Text & "',"
                sql &= "cxjsrq='" & Me.Txtcxjsrq.Text & "',allowsell='" & str & "',supplierid='" & Me.TxtSupplierID.Text & " 'where barcode='" & Me.TxtBarcode.Text & "'"
                Dim mydata As New SqlDataAdapter(sql, conn)
                mydata.Fill(myset, "a")
                DataGrid1.DataSource = myset.Tables("a")
                Frmload()
            Else
                Frmload()
            End If
        Catch ex As Exception
            MsgBox(ex.ToString)
            Exit Sub
        End Try
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.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 = "delete from merchs where barcode='" & Me.TxtBarcode.Text & "'"
                clear()
                Dim mydata As New SqlDataAdapter(sql, conn)
                mydata.Fill(myset, "a")
                Me.DataGrid1.DataSource = myset.Tables("a")
                Frmload()
            ElseIf yn = 7 Then
                Frmload()
            End If
        Catch ex As Exception
            Exit Sub
        End Try
    End Sub
    Private Sub clear()
        Me.ComboBox1.Text = ""
        Me.TxtBarcode.Text = ""
        Me.Txtcxj.Text = ""
        Me.Txtcxjsrq.Text = ""
        Me.Txtcxksrq.Text = ""
        Me.Txtjgsl.Text = ""
        Me.TxtProductName.Text = ""
        Me.TxtQuantity.Text = ""
        Me.TxtSupplierID.Text = ""
        Me.TxtUnitPrice.Text = ""
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            Dim mytable As New DataTable
            Dim conn As New SqlConnection
            conn.ConnectionString = "data source=127.0.0.1;initial catalog=Supermarket;user=text;password=text"
            Dim sql As String
            If Me.TxtBarcode.Text = "" Then
                clear()
                Frmload()
            Else
                sql = "select barcode as 条形码 ,merchname as 商品名称,quantity as 数量,unitprice as 单价,unitprice*quantity as 总额,allowsell as 是否允许销售 ," _
                & " supplierid as 供应商ID,jgsl as 警告数量,cxj as 促销价,cxksrq as 促价开始日期,cxjsrq as 促销结束日期 " _
                & "from merchs  where barcode like" & "'" & 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 DataGrid1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataGrid1.Click
        Try
            Dim allow As String
            Me.TxtBarcode.Text = DataGrid1.Item(DataGrid1.CurrentCell.RowNumber, 0) & ""
            Me.TxtProductName.Text = DataGrid1.Item(DataGrid1.CurrentCell.RowNumber, 1).ToString
            Me.TxtQuantity.Text = DataGrid1.Item(DataGrid1.CurrentCell.RowNumber, 2).ToString
            Me.TxtUnitPrice.Text = DataGrid1.Item(DataGrid1.CurrentCell.RowNumber, 3).ToString
            allow = DataGrid1.Item(DataGrid1.CurrentRowIndex, 5).ToString()
            Me.TxtSupplierID.Text = DataGrid1.Item(DataGrid1.CurrentCell.RowNumber, 6) & ""
            Me.Txtjgsl.Text = DataGrid1.Item(DataGrid1.CurrentCell.RowNumber, 7) & ""
            Me.Txtcxj.Text = DataGrid1.Item(DataGrid1.CurrentCell.RowNumber, 8) & ""
            Me.Txtcxksrq.Text = DataGrid1.Item(DataGrid1.CurrentCell.RowNumber, 9) & ""
            Me.Txtcxjsrq.Text = DataGrid1.Item(DataGrid1.CurrentCell.RowNumber, 10) & ""
            If allow = "1" Then
                Me.ComboBox1.Text = "是"
            ElseIf allow = "0" Then
                Me.ComboBox1.Text = " 否"
            Else
                Me.ComboBox1.Text = ""
            End If
        Catch ex As Exception

            Exit Sub
        End Try
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Me.Close()
    End Sub

    Private Sub FrmMerch_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Try

            Dim conn As New SqlConnection
            conn.ConnectionString = "data source=127.0.0.1;initial catalog=Supermarket;user=text;password=text"
            conn.Open()
            Dim sql As String
            sql = "select barcode as 条形码 ,merchname as 商品名称,quantity as 数量,unitprice as 单价,unitprice*quantity as 总额,allowsell as 是否允许销售 ," _
            & " supplierid as 供应商ID,jgsl as 警告数量,cxj as 促销价,cxksrq as 促价开始日期,cxjsrq as 促销结束日期 " _
            & "from merchs where quantity<jgsl "
            Dim cmd As New SqlCommand(sql, conn)
            Dim dr As SqlDataReader
            dr = cmd.ExecuteReader
            If dr.Read Then
                Dim chaxun As New ChaXun
                chaxun.ShowDialog()
            End If
            conn.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
            Exit Sub
        Finally

        End Try
    End Sub
End Class