- Foxtable(狐表) (http://foxtable.net/bbs/index.asp)
-- 专家坐堂 (http://foxtable.net/bbs/list.asp?boardid=2)
---- [求助]插入SQL Server数据问题 (http://foxtable.net/bbs/dispbbs.asp?boardid=2&id=189659)
-- 发布时间：2023/12/14 20:43:00
-- [求助]插入SQL Server数据问题
此主题相关图片如下：显示各增加了两行.png
此主题相关图片如下：需要导入数据的excel表格.png
老师：
我在进行Excel导入时发现每导入一道Excel数据，在SQL Server中变成了两次插入，不知道什么原因，我把代码贴出来了，请老师指点一下：
Dim e As RequestEventArgs = args(0)
Dim flpn, dm_st, username, dm, cols, djr, djrzw, djrq, ID, xmmc, xmbh, tb, rybh, zw, lczt, css_1(), css(), vss(), cs, vs, bh As String
Dim i_sql As Integer = 0
Dim dr_sp As DataRow
Dim arr As New JArray
Dim obj As New JObject
If e.PostValues.ContainsKey("登记人") AndAlso e.PostValues.ContainsKey("代码") AndAlso e.Cookies.ContainsKey("user") AndAlso e.Cookies("user") <> "" AndAlso e.Files.Keys(0) <> "" Then
djr = e.PostValues("登记人")
djrzw = e.PostValues("登记人职务")
djrq = e.PostValues("登记日期")
ID = e.PostValues("ID")
dm = e.PostValues("代码")
xmmc = e.PostValues("项目名称")
xmbh = e.PostValues("项目编号")
tb = e.PostValues("tablename")
cols = e.PostValues("cols")
dr_sp = DataTables("超级用户表").sqlfind("name = \'" & djr & "\' and ID = \'" & ID & "\'")
If dr_sp IsNot Nothing Then
lczt = "流程结束"
Else
lczt = "草稿"
End If
Dim key As String = e.Files.Keys(0)
Dim fln As String = e.Files(key)(0)
flpn = ProjectPath & "HttpTempFiles\\" & fln \'msgbox("e.Files.Keys(0): " & e.Files.Keys(0) & " | e.files(key(0))" & e.Files(key)(0))
e.SaveFile(key, fln, flpn)
Dim dr_xmry As DataRow
Dim Book As New XLS.Book(flpn)
Dim Sheet As XLS.Sheet = Book.Sheets(0)
If dm = "LWKQ" Or dm = "YGKQ" Then
css_1 = {"工日", "加班小时", "年份", "月份", "金额", "日工资单价", "小时工资单价", "工日", "加班小时", "日工资合计", "加班工资合计", "工资合计"} \'列出数据类型的列
Dim bh_ls As String
For n As Integer = 1 To Sheet.Rows.Count - 1
Dim xm, nf, yf, gr, jbxs, bz As String
For i As Integer = 0 To sheet.cols.count - 1
If sheet(0, i).Text = "姓名" Then
xm = sheet(n, i).Text
ElseIf sheet(0, i).Text = "年份" Then
nf = sheet(n, i).Text
ElseIf sheet(0, i).Text = "月份" Then
yf = sheet(n, i).Text
ElseIf sheet(0, i).Text = "工日" Then
gr = sheet(n, i).Text
ElseIf sheet(0, i).Text = "加班小时" Then
jbxs = sheet(n, i).Text
ElseIf sheet(0, i).Text = "备注" Then
bz = sheet(n, i).Text
End If
Next
If xm = "" AndAlso nf = "" AndAlso yf = "" Then
If FileSys.FileExists(flpn) Then \'如果指定的文件存在
FileSys.DeleteFile(flpn, 2, 2) \'则彻底删除之
End If
If i_sql > 0 Then
e.WriteString("已成功导入了" & i_sql & "条记录，请刷新!")
End If
Return False
End If
Dim excel_1() As String = {xm, nf, yf}
Dim excel_2() As String = {"姓名", "年份", "月份"}
Dim str_1 As String
For i_ex As Integer = 0 To excel_1.count - 1
If excel_1(i_ex) = "" Then
str_1 = str_1 & "," & excel_2(i_ex)
End If
Next
If str_1 <> "" Then
e.WriteString("需导入的表中【" & str_1.TrimStart(",") & "】栏没有数据,请检查重新导入!")
If FileSys.FileExists(flpn) Then \'如果指定的文件存在
FileSys.DeleteFile(flpn, 2, 2) \'则彻底删除之
End If
Return False
End If
If gr = "" AndAlso jbxs = "" Or gr = 0 AndAlso jbxs = 0 Then
e.WriteString("工日或加班记录不能同时为空!")
If FileSys.FileExists(flpn) Then \'如果指定的文件存在
FileSys.DeleteFile(flpn, 2, 2) \'则彻底删除之
End If
Return False
End If
If dm.Contains("LW") Then
dm_st = "XMLW"
rybh = "劳务编号"
zw = "工种"
ElseIf dm.Contains("YG") Then
dm_st = "XMYG"
rybh = "员工编号"
zw = "职务"
End If
dr_xmry = _dt.sqlfind("name = \'" & xm & "\' and 代码 = \'" & dm_st & "\' and ID = \'" & ID & "\' And 项目名称 = \'" & xmmc & "\'") \'msgbox("name = \'" & xm & "\' and 代码 = \'" & dm_st & "\' and ID = \'" & ID & "\' And 项目名称 = \'" & xmmc & "\'")
If dr_xmry Is Nothing Then
If FileSys.FileExists(flpn) Then \'如果指定的文件存在
FileSys.DeleteFile(flpn, 2, 2) \'则彻底删除之
End If
e.WriteString("姓名为:【" & xm & "】的人员未登记在本项目中!")
Return False
End If
Dim drx As DataRow = DataTables("全表").sqlFind("name = \'" & xm & "\' and 年份 = \'" & nf & "\' and 月份 = \'" & yf & "\' and 工日 = \'" & gr & "\' and 加班小时 = \'" & jbxs & "\'")
If drx IsNot Nothing Then \'如果不存在相同的记录
e.WriteString("姓名为:【" & xm & "】的人员存在相同的记录，请检查!")
Return False
End If
Dim rgzhj As Integer = dr_xmry("日工资单价") * gr
Dim jbhj As Integer = dr_xmry("小时工资单价") * jbxs
Dim gzhj As Integer = jbhj + rgzhj
bh = Functions.Execute("编号", tb , dm)
css = {"name", "年份", "月份", "工日", "加班小时", "备注", "代码", zw, rybh, "项目名称", "项目编号", "字母", "类别", "日工资单价", "小时工资单价", "日工资合计", "加班工资合计", "工资合计", "编号", "流程状态", "登记人", "登记人职务", "登记日期", "ID"}
vss = {xm, nf, yf, gr, jbxs, bz, dm, dr_xmry(zw), dr_xmry(rybh), xmmc, xmbh, dr_xmry("字母"), dr_xmry("类别"), dr_xmry("日工资单价"), dr_xmry("小时工资单价"), rgzhj, jbhj, gzhj, bh, lczt, djr, djrzw, djrq, ID}
Dim st1, st2 As String
cs = ""
vs = ""
For ii As Integer = 0 To css.count - 1 \'对cs、vs字符串进行去空格，重新拼接成规范的字符串
If vss(ii) > "" Then
cs = cs & "," & css(ii)
If css_1.Contains(css(ii)) Then \'筛选含数据类的列
vs = vs & "," & vss(ii)
Else
vs = vs & ",\'" & vss(ii).replace("\'", "\'\'") & "\'"
End If
End If
Next
Dim cmd As New SQLCommand
cmd.C
cmd.CommandText = "insert into " & tb & "(" & cs.TrimStart(",") & ") values(" & vs.TrimStart(",") & ")"
cmd.ExecuteNonQuery
If cmd.ExecuteNonQuery > 0 Then
i_sql = i_sql + 1
bh_ls = bh_ls & ",\'" & bh.replace("\'", "\'\'") & "\'"
End If
Next
End if
End If
[此贴子已经被作者于2023/12/14 20:51:12编辑过]
-- 发布时间：2023/12/14 21:14:00
--
sql执行了2次啊
cmd.ExecuteNonQuery 第1次
If cmd.ExecuteNonQuery > 0 Then 第2次
改为
dim c as integer = cmd.ExecuteNonQuery
If c > 0 Then
-- 发布时间：2023/12/15 0:32:00
--
此主题相关图片如下：微信截图_20231215003019.png
老师，试过还是两次，帮忙看看是不是其它原因
[此贴子已经被作者于2023/12/15 0:32:49编辑过]
-- 发布时间：2023/12/15 8:31:00
--
无语，还是2次啊
此主题相关图片如下：1.png
-- 发布时间：2023/12/15 8:57:00
--
你是对的，是我搞错了，应该这样的
Dim cmd As New SQLCommand
cmd.C"> cmd.CommandText = "insert into " & tb & "(" & cs.TrimStart(",") & ") values(" & vs.TrimStart(",") & ")" \'添加行
If cmd.ExecuteNonQuery > 0 Then
i_sql = i_sql + 1
bh_ls = bh_ls & ",\'" & bh.replace("\'", "\'\'") & "\'"
End If