客户订单管理系统功能简介: 某公司为企业客户提供食品、蔬菜、肉制品等商品采购配送服务。为此公司需要保存企业客户的基本信息,其基本信息包括企业名称、客户简称、企业法人、企业地址、邮政编码、开户银行、账号以及企业客户的联系人,联系人信息包括联系
某公司为企业客户提供食品、蔬菜、肉制品等商品采购配送服务。为此公司需要保存企业客户的基本信息,其基本信息包括企业名称、客户简称、企业法人、企业地址、邮政编码、开户银行、账号以及企业客户的联系人,联系人信息包括联系人姓名、联系人电话、手机、电子信箱。 一个企业客户可以有多个联系人。
公司为企业客户提供的商品信息描述包括商品名称、包装方式、计量单位、商品产地、商品保质期、商品特征描述、售价。
企业客户中的联系人根据企业的实际需求和公司提供的商品信息下订单,订单信息包括订单日期、要求到货日期时间、送货地址、特殊说明、下订单人以及订购的商品详细信息,订购商品详细信息包括商品名称、购买数量、单价、金额。 一个订单中可以有多种商品。
公司为了能够保质、保量、按时地位客户服务,要求客户在要求到货日期的前一天下订单且每天只能下一个订单。每天早上公司根据客户订单确定采购的商品和总量形成采购单,采购员根据采购单采购,采购回来后保存采购回来的商品数量、计量单位及采购价格。
详细设计要求:
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
