VB.NET调用存储过程循环DATAGRIDVIEW插入数据数据库中途错误回滚的问题
Dim trans As SqlClient.SqlTransaction
trans = SqlConNetLink.SqlCon.sqlcon.BeginTransaction
For i As Integer = 0 To MyDataGridView1.RowCount - 1
If MyDataGridView1.Rows(i).Cells("制度金额").Value <> "" Then
PArr = {New SqlParameter("@Query", "ZJ"), New SqlParameter("@BL001", Date.Now.ToString("yyyyMMddHHmmssfff")), New SqlParameter("@BL002",
MyDataGridView1.Rows(i).Cells("主持人编号").Value), New SqlParameter("@BL003", MyDataGridView1.Rows(i).Cells("主持人姓名").Value), New SqlParameter("@BL004",
MyDataGridView1.Rows(i).Cells("Column2").Value), New SqlParameter("@BL005", "否"), New SqlParameter("@BL006", MyDataGridView1.Rows(i).Cells("制度金额").Value), New SqlParameter
("@BL007", "Y"), New SqlParameter("@BL008", Date.Now.Date.ToString), New SqlParameter("@BL009", "黄票"), New SqlParameter("@BL010", MyDataGridView1.Rows(i).Cells("制度金
额").Value), New SqlParameter("@BL011", USEAMS.UserInfo.UserName), New SqlParameter("@BL012", Date.Now.ToString), New SqlParameter("@BL013", "MAMAMS"), New SqlParameter
("@BL014", "否")}
If SIDU("I", PArr, "Pgz_scx_deletemx") = True Then ‘调用一个存储过程进行插入动作
Else
trans.Rollback()
Common.Debug.myMsg("票据开据失败！")
Exit For
End If
End If
Next
trans.Commit()
循环一个DATAGRIDVIEW表，然后插入数据到数据库中。因为数据很多，我怕中途插入失败，如果失败就回滚到最开始状态。但是我不知道我这样写能行不，如果是程序中才用SQL语句形式的肯定没问题了
。我的存储过程是
begin
set @SUBSYSTEM='IIFAMA'
set @USR_GROUP='admin'
set @FLAG=1
set @CREATE_DATE=(select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':',''))
set @MODI_DATE=(select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':',''))
Select @sql1='INSERT INTO IIFAMA(SUBSYSTEM,CREATOR,USR_GROUP,CREATE_DATE,MODIFIER,MODI_DATE,FLAG,MA001,MA002,MA003,MA004,MA005) VALUES
('''+@SUBSYSTEM+''','''+@CREATOR+''','''+@USR_GROUP+''','''+@CREATE_DATE+''','''+@MODIFIER+''','''+@MODI_DATE+''','''+cast(@FLAG as varchar)
+''','''+@BL001+''','''+@BL002+''','''+@BL003+''','''+@BL004+''','''+@BL005+''')'
if @@ERROR<>0
Rollback Tran
End
大家帮忙看下哈。
[解决办法]
不错哦,用vb.net+T-sql储存过程
