有5张表，表1(HS编码,料件名称,数量,金额,原产地,手册号,供应商,日期,料件序号,判定ID),表2(HS编码, 料件名称, 料件序号, 数量,金额,原产地,手册号,供应商,日期,流水号,分配ID),表3(部品番号,数量,金额,原产地,是否汇总),表4(部品番号, 数量,金额,原产地,日期,流水号,历史ID),表5(手册号,料件序号,料件料号,税则号,料件名称,规格型号,备案数量,实扣数量,预扣数量,可进量,备注),窗体上有流水号文本框,DateTimePicker1为日期，点击按钮,将表1的记录逐条插入到表2,其中分配ID为自定义增长字段,然后将表3的记录逐条插入到表4,最后将表5的预扣数量更新到实扣数量，VB.NET实现的代码如下：
imports System.Data.SqlClient
rivate Sub Button确认_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button确认.Click
If String.IsNullOrEmpty(TextBox1.Text) Then
MessageBox.Show("请先接单")
Return
End If
Dim 流水号 As String = TextBox1.Text.Trim()
Dim 日期 As DateTime = DateTimePicker1.Value
Using conn As New SqlConnection(DBconnString)
conn.Open()
Using tran As SqlTransaction = conn.BeginTransaction() '开启事务
Try
Dim dtTable1 As New DataTable()
Dim dtTable2 As New DataTable()
'步骤1,先将料件判定清单的记录保存至进口分配历史
Dim query料件判定清单 As String = "SELECt HS编码,料件名称,数量,净重,毛重,金额,币制,原产地,手册号,供应商,日期,料件序号,判定ID FROM 料件判定清单"
Using cmd料件判定清单 As New SqlCommand(query料件判定清单, conn, tran)
Using da料件判定清单 As New SqlDataAdapter(cmd料件判定清单)
da料件判定清单.Fill(dtTable1)
End Using
'先查找分配ID的最大值,+1作为新值;
Dim max分配ID As Integer = 0
Dim sqlGetMax分配ID As String = "SELECt ISNULL(MAX(分配ID),0) FROM 进口分配历史"
Using cmdMax进口分配历史 As New SqlCommand(sqlGetMax分配ID, conn, tran)
max分配ID = CInt(cmdMax进口分配历史.ExecuteScalar())
End Using
For Each row As DataRow In dtTable1.Rows
Dim cmdInsert进口分配历史 As New SqlCommand("INSERT INTO 进口分配历史(HS编码,料件名称,料件序号,数量,净重,毛重,金额,原产地,手册号,供应商,日期,流水号,分配ID)" _
& "VALUES(@HS编码,@料件名称,@料件序号,@数量,@净重,@毛重,@金额,@原产地,@手册号,@供应商,@日期,@流水号,@分配ID)", conn, tran)
cmdInsert进口分配历史.Parameters.AddWithValue("@HS编码", row("HS编码"))
cmdInsert进口分配历史.Parameters.AddWithValue("@料件名称", row("料件名称"))
cmdInsert进口分配历史.Parameters.AddWithValue("@料件序号", row("料件序号"))
cmdInsert进口分配历史.Parameters.AddWithValue("@数量", row("数量"))
cmdInsert进口分配历史.Parameters.AddWithValue("@净重", row("净重"))
cmdInsert进口分配历史.Parameters.AddWithValue("@毛重", row("毛重"))
cmdInsert进口分配历史.Parameters.AddWithValue("@金额", row("金额"))
cmdInsert进口分配历史.Parameters.AddWithValue("@原产地", row("原产地"))
cmdInsert进口分配历史.Parameters.AddWithValue("@手册号", row("手册号"))
cmdInsert进口分配历史.Parameters.AddWithValue("@供应商", row("供应商"))
cmdInsert进口分配历史.Parameters.AddWithValue("@日期", 日期)
cmdInsert进口分配历史.Parameters.AddWithValue("@流水号", 流水号)
max分配ID += 1
cmdInsert进口分配历史.Parameters.AddWithValue("@分配ID", max分配ID)
cmdInsert进口分配历史.ExecuteNonQuery()
Next
End Using
'步骤2,将进口清单的记录保存至进口清单历史
Dim query进口清单 As String = "SELECt 部品番号,数量,净重,毛重,金额,原产地,是否汇总 FROM 进口清单"
Using cmd进口清单 As New SqlCommand(query进口清单, conn, tran)
Using da进口清单 As New SqlDataAdapter(cmd进口清单)
da进口清单.Fill(dtTable2)
End Using
'先查找历史ID的最大值,+1作为新值
Dim max历史ID As Integer = 0
Dim sqlGetMax历史ID As String = "SELECt ISNULL(MAX(历史ID),0) FROM 进口清单历史 "
Using cmdMax进口清单历史 As New SqlCommand(sqlGetMax历史ID, conn, tran)
max历史ID = CInt(cmdMax进口清单历史.ExecuteScalar())
End Using
For Each row As DataRow In dtTable2.Rows
Dim cmdInsert进口清单历史 As New SqlCommand("INSERT INTO 进口清单历史(部品番号,数量,净重,毛重,金额,原产地,日期,流水号,历史ID)" _
& " VALUES(@部品番号,@数量,@净重,@毛重,@金额,@原产地,@日期,@流水号,@历史ID)", conn, tran)
cmdInsert进口清单历史.Parameters.AddWithValue("@部品番号", row("部品番号"))
cmdInsert进口清单历史.Parameters.AddWithValue("@数量", row("数量"))
cmdInsert进口清单历史.Parameters.AddWithValue("@净重", row("净重"))
cmdInsert进口清单历史.Parameters.AddWithValue("@毛重", row("毛重"))
cmdInsert进口清单历史.Parameters.AddWithValue("@金额", row("金额"))
cmdInsert进口清单历史.Parameters.AddWithValue("@原产地", row("原产地"))
cmdInsert进口清单历史.Parameters.AddWithValue("@日期", 日期)
cmdInsert进口清单历史.Parameters.AddWithValue("@流水号", 流水号)
max历史ID += 1
cmdInsert进口清单历史.Parameters.AddWithValue("@历史ID", max历史ID)
cmdInsert进口清单历史.ExecuteNonQuery()
Next
End Using
'步骤3,更新手册实扣数量
Dim sqlUpdate手册料件清单 As String = "UPDATE 手册料件清单 SET 实扣数量=实扣数量+预扣数量,预扣数量=0" _
& "WHERe 手册号 IN (SELECT 手册号 FROM 料件判定清单) AND 料件序号 IN (SELECt 料件序号 FROM 料件判定清单)"
Using cmdUpdate手册料件清单 As New SqlCommand(sqlUpdate手册料件清单, conn, tran)
cmdUpdate手册料件清单.ExecuteNonQuery()
End Using
'步骤4,删除料件判定清单记录,删除进口清单记录,删除进口料件汇总
Dim sqlDelete料件判定清单 As String = "DELETE FROM 料件判定清单"
Using cmdDel料件判定清单 As New SqlCommand(sqlDelete料件判定清单, conn, tran)
cmdDel料件判定清单.ExecuteNonQuery()
End Using
Dim sqlDelete进口清单 As String = "DELETE FROM 进口清单"
Using cmdDel进口清单 As New SqlCommand(sqlDelete进口清单, conn, tran)
cmdDel进口清单.ExecuteNonQuery()
End Using
Dim sqlDelete进口料件汇总 As String = "DELETE FROM 进口料件汇总"
Using cmdDel进口料件汇总 As New SqlCommand(sqlDelete进口料件汇总, conn, tran)
cmdDel进口料件汇总.ExecuteNonQuery()
End Using
tran.Commit()
'步骤5,DataGridView刷新
Dim dt1 As New DataTable()
Dim sql1 As String = "SELECT * FROM 进口清单"
Dim da1 As New SqlDataAdapter(sql1, conn)
da1.Fill(dt1)
DataGridView1.DataSource = dt1
Dim dt2 As New DataTable()
Dim sql2 As String = "SELECt * FROM 进口料件汇总"
Dim da2 As New SqlDataAdapter(sql2, conn)
da2.Fill(dt2)
DataGridView2.DataSource = dt2
Dim dt3 As New DataTable()
Dim sql3 As String = "SELECt * FROM 料件判定清单"
Dim da3 As New SqlDataAdapter(sql3, conn)
da3.Fill(dt3)
DataGridView3.DataSource = dt3
MessageBox.Show("提交成功")
Catch ex As Exception
tran.Rollback()
MessageBox.Show("提交失败" & ex.Message)
Finally
conn.Close()
End Try
End Using
End Using
End Sub
