客户订单管理系统，所有代码和SqlServer数据库源文件，数据库设

客户订单管理系统功能简介：
某公司为企业客户提供食品、蔬菜、肉制品等商品采购配送服务。为此公司需要保存企业客户的基本信息，其基本信息包括企业名称、客户简称、企业法人、企业地址、邮政编码、开户银行、账号以及企业客户的联系人，联系人信息包括联系人姓名、联系人电话、手机、电子信箱。        一个企业客户可以有多个联系人。

公司为企业客户提供的商品信息描述包括商品名称、包装方式、计量单位、商品产地、商品保质期、商品特征描述、售价。

企业客户中的联系人根据企业的实际需求和公司提供的商品信息下订单，订单信息包括订单日期、要求到货日期时间、送货地址、特殊说明、下订单人以及订购的商品详细信息，订购商品详细信息包括商品名称、购买数量、单价、金额。         一个订单中可以有多种商品。

公司为了能够保质、保量、按时地位客户服务，要求客户在要求到货日期的前一天下订单且每天只能下一个订单。每天早上公司根据客户订单确定采购的商品和总量形成采购单，采购员根据采购单采购，采购回来后保存采购回来的商品数量、计量单位及采购价格。

详细设计要求：
1）设计E-R图。
2）根据E-R图进行数据库的逻辑设计和物理设计
3）在数据库上建库、基本表以及依据数据库设计方案建立的视图
4）创建系统总体功能菜单
5）实现对商品信息、客户信息、订单信息、采购信息的管理（包括数据的插入、删除和修改功能）
6）实现各种基本表中数据的查询功能（执行模糊查询）
7）实现按商品分类查询和统计。
8）设计使用该系统的用户有三种：系统管理员、采购员、客服。
采购员能够根据执行采购单生成功能、根据采购结果实现对采购回来的商品的维护功能。客服在接到客户的需求后能够生成客户订单、查询客户信息、维护商品信息。系统管理员能够执行系统全部功能。
9）将所开发出的功能分配给三种不同的用户，各类用户在登录系统时需要输入帐号、密码，并进入自己的功能菜单，进行相应的操作。

<无>

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim name As String = TextBox1.Text
        Dim password As String = TextBox2.Text
        If password = "123456" Then
            If name = "purchaser" Then
                Form2.Button1.Enabled = False
                Form2.Button2.Enabled = False
                Form2.Button3.Enabled = False

                Me.Hide()
                Form2.Show()

            ElseIf name = "waiter" Then
    
                Form2.Button5.Enabled = False
                Me.Hide()
                Form2.Show()

            ElseIf name = "admin" Then
                Form2.Show()
                Me.Hide()
            Else
                MsgBox("不存在此用户名")
            End If
        Else
            MsgBox("密码错误")
        End If
    End Sub

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Form2.Button1.Enabled = True
        Form2.Button2.Enabled = True
        Form2.Button3.Enabled = True

        Form2.Button5.Enabled = True
    End Sub
End Class

Public Class Form2

    Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Me.Hide()
        Form3.Show()

    End Sub

    Private Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
        Me.Hide()
        Form4.Show()
    End Sub

    Private Sub Button3_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button3.Click
        Me.Hide()
        Form5.Show()
    End Sub


    Private Sub Button5_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button5.Click
        Me.Hide()
        Form7.Show()
    End Sub
End Class

Imports System.Data.SqlClient

Public Class Form3

    Dim connstr As String = "data source=localhost;initial catalog=Database project design;integrated security=SSPI"
    Dim conn As SqlConnection = New SqlConnection(connstr)
    Private Sub Form3_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        fresh()
        fresh1()
    End Sub


    Sub fresh()
        Dim myadpt As SqlDataAdapter = New SqlDataAdapter("select * from 企业客户信息表", conn)
        Dim data As DataTable = New DataTable
        myadpt.Fill(data)
        DataGridView1.DataSource = data
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        If MsgBox("您确认要更改此企业信息吗？（若您需要更改企业名称，请在下方输入信息后进行更改）", MsgBoxStyle.OkCancel) = MsgBoxResult.Ok Then
            Try
                Dim data As DataTable
                data = DataGridView1.DataSource
                Dim myadpt As SqlDataAdapter = New SqlDataAdapter("select * from 企业客户信息表", conn)
                Dim cmdb As SqlCommandBuilder = New SqlCommandBuilder(myadpt)
                Dim changedData As DataTable = data.GetChanges()

                If Not changedData Is Nothing Then
                    myadpt.Update(changedData)
                    data.AcceptChanges()
                End If
                MsgBox("修改企业信息成功！")
                fresh()
                fresh1()

            Catch ex As Exception
                MsgBox("暂时无法在信息框内修改企业名称！请您在下方修改企业名称!")
            End Try
        End If
    End Sub


    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        fresh()
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

        Dim myadpt As SqlDataAdapter = New SqlDataAdapter("select * from 企业客户信息表 where 企业名称='" & Trim(TextBox1.Text) & "'", conn)
        Dim data As DataTable = New DataTable
        myadpt.Fill(data)
        DataGridView1.DataSource = data

    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        If MsgBox("您确认要删除此企业吗？（将会删除此企业所有联系人及所有订单）", MsgBoxStyle.OkCancel) = MsgBoxResult.Ok Then
            Try
                Dim myadpt As SqlDataAdapter = New SqlDataAdapter("delete  from 企业客户信息表  where 企业名称='" & Trim(TextBox2.Text) & "' ", conn)
                Dim data As DataTable = New DataTable
                myadpt.Fill(data)
                DataGridView1.DataSource = data
                Dim myadpt1 As SqlDataAdapter = New SqlDataAdapter("delete  from 客户订购信息表  where 企业名称='" & Trim(TextBox2.Text) & "' ", conn)
                Dim data1 As DataTable = New DataTable
                myadpt1.Fill(data1)
                DataGridView1.DataSource = data1

                fresh()
                fresh1()
            Catch ex As Exception
                MsgBox("不存在该企业名称！")
            End Try


        End If
        fresh()
    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        Dim str() As String = {TextBox3.Text, TextBox4.Text, TextBox5.Text, TextBox6.Text, TextBox7.Text}

        Dim mycmd As String = "insert into 联系人信息表(联系人姓名,电话号,手机号,企业名称,邮箱) values('"
        For i% = 0 To 3
            mycmd = mycmd & Trim(str(i)) & "','"
        Next
        mycmd = mycmd & str(4) & "')"
        Dim conn As SqlConnection = New SqlConnection(connstr)
        conn.Open()
        Dim cmd As SqlCommand = New SqlCommand(mycmd, conn)
        Try
            If cmd.ExecuteNonQuery = 1 Then
                MsgBox("添加联系人成功")
            Else
                MsgBox("添加联系人失败")
            End If
            conn.Close()
            fresh1()
        Catch ex As Exception
            MsgBox("添加了重复的联系人姓名！或是 不存在该企业名称！")
        End Try
    End Sub

    Sub fresh1()
        Try
            Dim myadpt As SqlDataAdapter = New SqlDataAdapter("select * from 联系人信息表,企业客户信息表 where 联系人信息表.企业名称=企业客户信息表.企业名称", conn)
            Dim mydt As DataTable = New DataTable
            myadpt.Fill(mydt)
            DataGridView2.DataSource = mydt
        Catch ex As Exception
            MsgBox("不存在该企业名称！")
        End Try
    End Sub

    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
        If MsgBox("您确认要更改此联系人姓名吗？请与客户确认是否更换联系人！（将会更改订单中所有相应的联系人姓名！）", MsgBoxStyle.OkCancel) = MsgBoxResult.Ok Then
            Dim data As DataTable
            data = DataGridView2.DataSource
            Dim myadpt As SqlDataAdapter = New SqlDataAdapter("select * from 联系人信息表", conn)
            Dim cmdb As SqlCommandBuilder = New SqlCommandBuilder(myadpt)
            Dim changedData As DataTable = data.GetChanges()
            Try
                If Not changedData Is Nothing Then
                    myadpt.Update(changedData)
                    data.AcceptChanges()
                End If
                MsgBox("修改联系人信息成功！")
                fresh1()
            Catch ex As Exception
                MsgBox("添加的联系人姓名重复！")
            End Try
        End If
    End Sub

    Private Sub Button8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button8.Click
        fresh1()
    End Sub

    Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click
        If MsgBox("您确认要删除此联系人吗？请与企业客户联系确认！（将会删除该联系人下的所有订单信息！）", MsgBoxStyle.OkCancel) = MsgBoxResult.Ok Then
            Try
                Dim myadpt As SqlDataAdapter = New SqlDataAdapter("delete  from 联系人信息表  where 联系人姓名='" & Trim(TextBox8.Text) & "' ", conn)
                Dim data As DataTable = New DataTable
                myadpt.Fill(data)
                DataGridView2.DataSource = data
                fresh1()
            Catch ex As Exception
                MsgBox("不存在该联系人姓名！")
            End Try
        End If
        fresh1()
    End Sub

    Private Sub Button9_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button9.Click
        Me.Hide()
        Form2.Show()
    End Sub


    Private Sub Button10_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button10.Click
        If MsgBox("您确认要修改此企业名称吗？请与企业客户联系确认！（将会修改该联系人信息和订单信息中的企业名称！）", MsgBoxStyle.OkCancel) = MsgBoxResult.Ok Then
            Dim enterprisename As String = Trim(TextBox10.Text)
            Dim name1 As String = Trim(TextBox9.Text)
            Try
                Dim cmd1 As SqlCommand = New SqlCommand("update 企业客户信息表 set 企业名称 = '" & name1 & "'where 企业名称='" & enterprisename & "'", conn)
                conn.Open()
                Try
                    If cmd1.ExecuteNonQuery = 1 Then
                        Dim cmd2 As SqlCommand = New SqlCommand("update 客户订购信息表 set 企业名称 = '" & name1 & "'where 企业名称='" & enterprisename & "'", conn)
                        cmd2.ExecuteNonQuery()
                        MsgBox("修改企业名称成功")
                        fresh()
                    Else
                        MsgBox("修改企业名称失败")
                        fresh()
                    End If
                Catch ex As DuplicateNameException
                    MsgBox("已存在该企业名称")
                End Try
                conn.Close()
            Catch ex As Exception
                MsgBox("修改企业名称失败,要修改的企业名称不存在")
            End Try
        End If
    End Sub

    Private Sub Button11_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button11.Click
        Try
            Dim myadpt As SqlDataAdapter = New SqlDataAdapter("select * from 联系人信息表 where 企业名称= '" & Trim(TextBox11.Text) & "'", conn)
            Dim mydt As DataTable = New DataTable
            myadpt.Fill(mydt)
            DataGridView2.DataSource = mydt
        Catch ex As Exception
            MsgBox("不存在该企业名称！")
        End Try
    End Sub
End Class

Imports System.Data.SqlClient

Public Class Form4
    Dim connstr As String = "data source=localhost;initial catalog=Database project design;integrated security=SSPI"
    Dim conn As SqlConnection = New SqlConnection(connstr)
    Private Sub Form4_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
       
        fresh()
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        If MsgBox("您确认要更改此商品信息吗？（若您更改商品名称，所有订购商品和采购商品的名称都将进行更改！）", MsgBoxStyle.OkCancel) = MsgBoxResult.Ok Then
            Dim data As DataTable
            data = DataGridView1.DataSource
            Dim myadpt As SqlDataAdapter = New SqlDataAdapter("select * from 商品信息表", conn)
            Dim cmdb As SqlCommandBuilder = New SqlCommandBuilder(myadpt)
            Dim changedData As DataTable = data.GetChanges()
            Try
                If Not changedData Is Nothing Then
                    myadpt.Update(changedData)
                    data.AcceptChanges()
                End If
                MsgBox("修改商品信息成功！")
                fresh()
            Catch ex As DuplicateNameException
                MsgBox("添加的商品名称重复！")
            Catch ex As Exception
                MsgBox("修改商品名称失败！")
            End Try
        End If
    End Sub


    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        fresh()
    End Sub
    Sub fresh()
        Dim myadpt As SqlDataAdapter = New SqlDataAdapter("select * from 商品信息表", conn)
        Dim data As DataTable = New DataTable
        myadpt.Fill(data)
        DataGridView1.DataSource = data
    End Sub

    Private Sub DateTimePicker1_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DateTimePicker1.ValueChanged
        Dim myadpt As SqlDataAdapter = New SqlDataAdapter("select * from 商品信息表 where 出产日期 between '" & Trim(Format(DateTimePicker1.Value, "yyyy-MM-dd").ToString()) & "'  and  '" & Trim(Format(DateTimePicker2.Value, "yyyy-MM-dd").ToString()) & "'", conn)
        Dim mydt As DataTable = New DataTable
        myadpt.Fill(mydt)
        DataGridView1.DataSource = mydt
    End Sub

    Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
        Dim myadpt As SqlDataAdapter = New SqlDataAdapter("select * from 商品信息表 where 商品产地= '" & Trim(ComboBox1.Text) & "' ", conn)
        Dim mydt As DataTable = New DataTable
        myadpt.Fill(mydt)
        DataGridView1.DataSource = mydt
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Dim myadpt As SqlDataAdapter = New SqlDataAdapter("select * from 商品信息表 where 商品单价 between '" & Val(TextBox1.Text) & "' and  '" & Val(TextBox2.Text) & "'  ", conn)
        Dim mydt As DataTable = New DataTable
        myadpt.Fill(mydt)
        DataGridView1.DataSource = mydt
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim myadpt As SqlDataAdapter = New SqlDataAdapter("select * from 商品信息表 where 商品名称= '" & Trim(TextBox3.Text) & "'  ", conn)
        Dim mydt As DataTable = New DataTable
        myadpt.Fill(mydt)
        DataGridView1.DataSource = mydt
    End Sub

    Private Sub DateTimePicker2_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DateTimePicker2.ValueChanged
        Dim myadpt As SqlDataAdapter = New SqlDataAdapter("select * from 商品信息表 where 出产日期 between '" & Trim(Format(DateTimePicker1.Value, "yyyy-MM-dd").ToString()) & "'  and  '" & Trim(Format(DateTimePicker2.Value, "yyyy-MM-dd").ToString()) & "'", conn)
        Dim mydt As DataTable = New DataTable
        myadpt.Fill(mydt)
        DataGridView1.DataSource = mydt
    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        If MsgBox("您确认要删除此商品吗？（将会删除订单中所有相应的订购商品！）", MsgBoxStyle.OkCancel) = MsgBoxResult.Ok Then
            Try
                Dim myadpt As SqlDataAdapter = New SqlDataAdapter("delete  from 商品信息表  where 商品名称='" & Trim(TextBox4.Text) & "' ", conn)
                Dim data As DataTable = New DataTable
                myadpt.Fill(data)
                DataGridView1.DataSource = data
                fresh()
            Catch ex As Exception
                MsgBox("不存在该商品名称！")
            End Try
        End If
        fresh()
    End Sub

    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
        Dim myadpt As SqlDataAdapter = New SqlDataAdapter("select * from 商品信息表 order by 商品单价 DESC ", conn)
        Dim mydt As DataTable = New DataTable
        myadpt.Fill(mydt)
        DataGridView1.DataSource = mydt
    End Sub

    Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click
        Me.Hide()
        Form2.Show()
    End Sub


    Private Sub Button8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button8.Click

        Dim BeforeGoodsName As String = Trim(TextBox5.Text)
        Dim AfterGoodsName As String = Trim(TextBox6.Text)
        Try
            Dim cmd1 As SqlCommand = New SqlCommand("update 商品信息表 set 商品名称 = '" & AfterGoodsName & "'where 商品名称='" & BeforeGoodsName & "'", conn)
            conn.Open()
            Try
                If cmd1.ExecuteNonQuery = 1 Then

                    Dim cmd2 As SqlCommand = New SqlCommand("update 所有订购商品信息表 set 商品名称 = '" & AfterGoodsName & "'where 商品名称='" & BeforeGoodsName & "'", conn)
                    cmd2.ExecuteNonQuery()
                    MsgBox("修改商品名称成功")
                    fresh()

                Else
                    MsgBox("修改商品名称失败")
                    fresh()

                End If
            Catch ex As DuplicateNameException
                MsgBox("已有此商品名称")
            End Try
            conn.Close()
        Catch ex As Exception
            MsgBox("修改商品名称失败")
        End Try
    End Sub

    Private Sub Button9_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button9.Click
        daochu(DataGridView1, "商品信息")
    End Sub
    Public Function daochu(ByVal x As DataGridView, ByVal namestr As String) As Boolean '导出到Excel函数
        Try
            If x.Rows.Count <= 0 Then '判断记录数,如果没有记录就退出
                MessageBox.Show("没有记录可以导出", "没有可以导出的项目", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Return False
            Else '如果有记录就导出到Excel
                Dim xx As Object : Dim yy As Object
                xx = CreateObject("Excel.Application") '创建Excel对象
                yy = xx.workbooks.add()
                Dim i As Integer, u As Integer = 0, v As Integer = 0 '定义循环变量,行列变量
                For i = 1 To x.Columns.Count '把表头写入Excel
                    yy.worksheets(1).cells(1, i) = x.Columns(i - 1).HeaderCell.Value
                Next
                Dim str(x.Rows.Count - 1, x.Columns.Count - 1) '定义一个二维数组
                For u = 1 To x.Rows.Count '行循环
                    For v = 1 To x.Columns.Count '列循环
                        If x.Item(v - 1, u - 1).ToString <> "System.Guid" Then
                            str(u - 1, v - 1) = x.Item(v - 1, u - 1).Value
                        Else
                            str(u - 1, v - 1) = x.Item(v - 1, u - 1).Value.ToString
                        End If
                    Next
                Next
                yy.worksheets(1).range("A2").Resize(x.Rows.Count, x.Columns.Count).Value = str '把数组一起写入Excel
                yy.worksheets(1).Cells.EntireColumn.AutoFit() '自动调整Excel列
                yy.worksheets(1).name = namestr  '表标题写入作为Excel工作表名称
                xx.visible = True '设置Excel可见
                yy = Nothing '销毁组建释放资源
                xx = Nothing '销毁组建释放资源
            End If
            Return True
        Catch ex As Exception '错误处理
            MessageBox.Show(Err.Description.ToString, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error) '出错提示
            Return False
        End Try
    End Function
End Class

Imports System.Data.SqlClient

Public Class Form5
    Dim connstr As String = "data source=localhost;initial catalog=Database project design;integrated security=SSPI"
    Dim conn As SqlConnection = New SqlConnection(connstr)
    Private Sub Form5_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        fresh1()
        fresh2()
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim data As DataTable
        data = DataGridView1.DataSource
        Dim myadpt As SqlDataAdapter = New SqlDataAdapter("select * from 客户订购信息表 ", conn)
        Dim cmdb As SqlCommandBuilder = New SqlCommandBuilder(myadpt)
        Dim changedData As DataTable = data.GetChanges()
        Try
            If Not changedData Is Nothing Then
                myadpt.Update(changedData)
                data.AcceptChanges()
            End If
            fresh1()
            MsgBox("修改订单信息成功")
        Catch ex As Exception
            MsgBox("修改订单信息失败，请检查是否存在该企业名称和联系人名称！")
            fresh1()
        End Try
    End Sub
    Sub fresh1()
        Try
            Dim myadpt As SqlDataAdapter = New SqlDataAdapter("select * from 客户订购信息表,企业客户信息表,联系人信息表 where 客户订购信息表.企业名称 = 企业客户信息表.企业名称 and 客户订购信息表.联系人姓名 = 联系人信息表.联系人姓名  ", conn)
            Dim data As DataTable = New DataTable
            myadpt.Fill(data)
            DataGridView1.DataSource = data
        Catch ex As Exception
            MsgBox("企业名称不存在！ 或是  联系人姓名不存在！")
        End Try
    End Sub
    Sub fresh2()
        Try
            Dim myadpt As SqlDataAdapter = New SqlDataAdapter("select * from 所有订购商品信息表,客户订购信息表,商品信息表 where 所有订购商品信息表.订单号=客户订购信息表.订单号 and 所有订购商品信息表.商品名称=商品信息表.商品名称", conn)
            Dim data As DataTable = New DataTable
            myadpt.Fill(data)
            DataGridView2.DataSource = data
        Catch ex As Exception
            MsgBox("订单号不存在！ 或是  商品名称不存在！")
        End Try
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim str() As String = {TextBox1.Text, TextBox2.Text, Format(DateAdd("d", 1, DateTimePicker1.Value), "yyyy-MM-dd").ToString(),
                               Format(DateTimePicker2.Value, "yyyy-MM-dd-hh:mm:ss").ToString(), TextBox3.Text, TextBox4.Text, TextBox12.Text}

        Dim mycmd As String = "insert into 客户订购信息表(企业名称,订单号,下单日期,到货日期,送货地址,特殊说明,联系人姓名) values('"
        For i% = 0 To 5
            mycmd = mycmd & Trim(str(i)) & "','"
        Next
        mycmd = mycmd & str(6) & "')"
        Dim conn As SqlConnection = New SqlConnection(connstr)
        conn.Open()
        Dim cmd As SqlCommand = New SqlCommand(mycmd, conn)
        Try
            If cmd.ExecuteNonQuery = 1 Then
                MsgBox("添加订单成功")
            Else
                MsgBox("添加订单失败")
            End If
            conn.Close()
            fresh1()
        Catch ex As Exception
            MsgBox("企业客户不存在！或是 联系人不存在！或是 添加了重复的客户名称和订单号或联系人！")
        End Try
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        If MsgBox("您确认要删除此订单吗？请与客户沟通后确认其是否删除订单？（将会删除订单下所有订购商品）", MsgBoxStyle.OkCancel) = MsgBoxResult.Ok Then
            Try
                Dim myadpt As SqlDataAdapter = New SqlDataAdapter("delete  from 客户订购信息表 where 订单号='" & Trim(TextBox5.Text) & "'", conn)
                Dim data As DataTable = New DataTable
                myadpt.Fill(data)
                DataGridView1.DataSource = data
                MsgBox("删除订单成功！")
                fresh1()
            Catch ex As Exception
                MsgBox("删除订单失败！")
            End Try
        End If
    End Sub

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

        Try
            If MsgBox("点击‘是’选择直接删除该企业客户（包括其所有联系人和订单信息)，点击‘否’选择仅删除该企业下所有订单", MsgBoxStyle.YesNoCancel) = MsgBoxResult.No Then
                Dim myadpt As SqlDataAdapter = New SqlDataAdapter("delete  from 客户订购信息表 where 企业名称='" & Trim(TextBox6.Text) & "'", conn)
                Dim data As DataTable = New DataTable
                myadpt.Fill(data)
                DataGridView1.DataSource = data
                MsgBox("删除企业成功")
                fresh1()
            ElseIf MsgBox("点击‘是’选择直接删除该企业客户（包括其所有联系人和订单信息)，点击‘否’选择仅删除该企业下所有订单", MsgBoxStyle.YesNoCancel) = MsgBoxResult.Yes Then
                Dim myadpt2 As SqlDataAdapter = New SqlDataAdapter("delete  from 客户订购信息表 where 企业名称='" & Trim(TextBox6.Text) & "'", conn)
                Dim data2 As DataTable = New DataTable
                myadpt2.Fill(data2)
                DataGridView1.DataSource = data2
                Dim myadpt1 As SqlDataAdapter = New SqlDataAdapter("delete  from 企业客户信息表 where 企业名称='" & Trim(TextBox6.Text) & "'", conn)
                Dim data1 As DataTable = New DataTable
                myadpt1.Fill(data1)
                DataGridView1.DataSource = data1
                MsgBox("删除企业成功")
                fresh1()
            Else
                fresh1()
            End If
        Catch ex As Exception
            MsgBox("删除企业失败")
        End Try
    End Sub

    Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click
        fresh1()
    End Sub

    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
        fresh2()
    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        Dim data As DataTable
        data = DataGridView2.DataSource
        Try
            Dim myadpt As SqlDataAdapter = New SqlDataAdapter("select * from 所有订购商品信息表 ", conn)
            Dim cmdb As SqlCommandBuilder = New SqlCommandBuilder(myadpt)
            Dim changedData As DataTable = data.GetChanges()

            If Not changedData Is Nothing Then
                myadpt.Update(changedData)
                data.AcceptChanges()
            End If
            fresh2()
            MsgBox("修改订单商品信息成功")
        Catch ex As Exception
            MsgBox("修改订单商品信息失败，请查看是否存在该订单号或商品名称！")

            fresh1()

        End Try
    End Sub

    Private Sub Button8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button8.Click
        Dim a, b, c As Integer
        a = Val(Trim(TextBox8.Text))
        b = Val(Trim(TextBox9.Text))
        c = Val(Trim(TextBox10.Text))
        Try
            Dim mycmd As String = "insert into 所有订购商品信息表(商品名称,订购数量,商品单价,订购全额,订单号)values('" & Trim(TextBox7.Text) & "','" & a & "','" & b & "','" & c & "','" & Trim(TextBox11.Text) & "')"
            Dim conn As SqlConnection = New SqlConnection(connstr)
            conn.Open()
            Dim cmd As SqlCommand = New SqlCommand(mycmd, conn)

            If cmd.ExecuteNonQuery = 1 Then
                MsgBox("添加订单商品成功")
            Else
                MsgBox("添加订单商品失败")
            End If
            conn.Close()
            fresh2()
        Catch ex As Exception
            MsgBox("添加了重复的商品！ 或 添加了不存在的商品！或是 添加了不存在的订单号！")
        End Try

    End Sub


    Private Sub Button9_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button9.Click
        Me.Hide()
        Form2.Show()
    End Sub
   
    Private Sub Button10_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button10.Click
        Try
            Dim myadpt As SqlDataAdapter = New SqlDataAdapter("select * from 客户订购信息表 where 企业名称 = '" & Trim(TextBox13.Text) & "'", conn)
            Dim data As DataTable = New DataTable
            myadpt.Fill(data)
            DataGridView1.DataSource = data
        Catch ex As Exception
            MsgBox("企业名称不存在！")
        End Try
    End Sub

    Private Sub Button11_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button11.Click
        Try
            Dim myadpt As SqlDataAdapter = New SqlDataAdapter("select * from 所有订购商品信息表 where 订单号 = '" & Trim(TextBox14.Text) & "'", conn)
            Dim data As DataTable = New DataTable
            myadpt.Fill(data)
            DataGridView2.DataSource = data
        Catch ex As Exception
            MsgBox("订单号不存在！")
        End Try
    End Sub
End Class

Imports System.Data.SqlClient

Public Class Form7
    Dim connstr As String = "data source=localhost;initial catalog=Database project design;integrated security=SSPI"
    Dim conn As SqlConnection = New SqlConnection(connstr)
    Sub fresh()
        Dim myadpt As SqlDataAdapter = New SqlDataAdapter("select * from 采购信息表,商品信息表 where 采购信息表.商品名称=商品信息表.商品名称", connstr)
        Dim mydt As DataTable = New DataTable
        myadpt.Fill(mydt)
        DataGridView2.DataSource = mydt
    End Sub

    Private Sub Form7_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        fresh1()
        fresh()
    End Sub
    Sub fresh1()
        Dim myadpt As SqlDataAdapter = New SqlDataAdapter("select 所有订购商品信息表.商品名称,SUM(所有订购商品信息表.订购数量) from 客户订购信息表,所有订购商品信息表 where  客户订购信息表.订单号=所有订购商品信息表.订单号 GROUP BY 所有订购商品信息表.商品名称", connstr)
        Dim mydt As DataTable = New DataTable
        myadpt.Fill(mydt)
        DataGridView1.DataSource = mydt
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim num As Integer = Val(TextBox2.Text) * Val(TextBox4.Text)

        Dim cmd As String = "insert into 采购信息表(商品名称,采购数量,计量单位,采购价格,采购日期,总价) values('" & Trim(TextBox1.Text) & "','" & Trim(TextBox2.Text) & "','" & Trim(TextBox3.Text) & "','" & Trim(TextBox4.Text) & "','" & Trim(Format(DateTimePicker1.Value, "yyyy-MM-dd").ToString()) & "','" & num & "')"
        Dim conn As SqlConnection = New SqlConnection(connstr)
        conn.Open()
        Dim mycmd As SqlCommand = New SqlCommand(cmd, conn)
        Try
            If mycmd.ExecuteNonQuery = 1 Then
                fresh()
                MsgBox("添加采购回执信息成功！")

            Else
                MsgBox("添加采购回执信息失败！")
            End If
        Catch ex As Exception
            MsgBox("添加采购回执信息失败！")
        End Try
        conn.Close()

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        fresh1()
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        daochu(DataGridView1, "采购单" & Trim(Format(DateTimePicker1.Value, "yyyy-MM-dd").ToString()))
    End Sub
    Public Function daochu(ByVal x As DataGridView, ByVal namestr As String) As Boolean '导出到Excel函数
        Try
            If x.Rows.Count <= 0 Then '判断记录数,如果没有记录就退出
                MessageBox.Show("没有记录可以导出", "没有可以导出的项目", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Return False
            Else '如果有记录就导出到Excel
                Dim xx As Object : Dim yy As Object
                xx = CreateObject("Excel.Application") '创建Excel对象
                yy = xx.workbooks.add()
                Dim i As Integer, u As Integer = 0, v As Integer = 0 '定义循环变量,行列变量
                For i = 1 To x.Columns.Count '把表头写入Excel
                    yy.worksheets(1).cells(1, i) = x.Columns(i - 1).HeaderCell.Value
                Next
                Dim str(x.Rows.Count - 1, x.Columns.Count - 1) '定义一个二维数组
                For u = 1 To x.Rows.Count '行循环
                    For v = 1 To x.Columns.Count '列循环
                        If x.Item(v - 1, u - 1).ToString <> "System.Guid" Then
                            str(u - 1, v - 1) = x.Item(v - 1, u - 1).Value
                        Else
                            str(u - 1, v - 1) = x.Item(v - 1, u - 1).Value.ToString
                        End If
                    Next
                Next
                yy.worksheets(1).range("A2").Resize(x.Rows.Count, x.Columns.Count).Value = str '把数组一起写入Excel
                yy.worksheets(1).Cells.EntireColumn.AutoFit() '自动调整Excel列
                yy.worksheets(1).name = namestr  '表标题写入作为Excel工作表名称
                xx.visible = True '设置Excel可见
                yy = Nothing '销毁组建释放资源
                xx = Nothing '销毁组建释放资源
            End If
            Return True
        Catch ex As Exception '错误处理
            MessageBox.Show(Err.Description.ToString, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error) '出错提示
            Return False
        End Try
    End Function

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

    Private Sub DateTimePicker2_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DateTimePicker2.ValueChanged
        Dim myadpt As SqlDataAdapter = New SqlDataAdapter("select 所有订购商品信息表.商品名称,SUM(所有订购商品信息表.订购数量) from 客户订购信息表,所有订购商品信息表 where 下单日期='" & Trim(Format(DateAdd("d", -1, DateTimePicker2.Value), "yyyy-MM-dd").ToString()) & "' AND 客户订购信息表.订单号=所有订购商品信息表.订单号 GROUP BY 所有订购商品信息表.商品名称", connstr)
        Dim mydt As DataTable = New DataTable
        myadpt.Fill(mydt)
        DataGridView1.DataSource = mydt
    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        fresh()
    End Sub

    Private Sub DateTimePicker3_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DateTimePicker3.ValueChanged
        Dim myadpt As SqlDataAdapter = New SqlDataAdapter("select * from 采购信息表 where 采购日期='" & Trim(Format(DateAdd("d", -1, DateTimePicker3.Value))) & "'", connstr)
        Dim mydt As DataTable = New DataTable
        myadpt.Fill(mydt)
        DataGridView2.DataSource = mydt
    End Sub

End Class