Cmd.ExecuteReader()在for next中第二循环时提示超时，sos

m0_37971119 2017-05-25 02:30:10

SQ1 = "insert into mf_IC(IC_NO,IC_DD,FIX_CST,USR,CHK_MAN,PRT_SW,CLS_DATE,DEP,SAL_NO,EFF_DD,RECORD_DD,WH1,WH2)"
SQ1 = SQ1 & " VALUES( '" & TextBox2.Text & "','" & sqdate1 & "',left('" & ComboBox2.Text & "',1),'" & Usr & "','" & Usr & "','N','" & sqdate1 & "','CK','A0009','" & sqdate1 & "','" & sqdate1 & "','" & ComboBox3.Text & "','" & ComboBox1.Text & "')"

Dim Sqlconn As New SqlConnection(Sql1) 'insert into mf_pss,tf_pss,tf_pss_z
Sqlconn.Open()
Dim cmd1 As New SqlCommand
cmd1.Connection = Sqlconn
cmd1.CommandTimeout = 60
Dim trans As SqlTransaction
trans = Sqlconn.BeginTransaction
cmd1.Transaction = trans
Try

cmd1.CommandText = SQ1 'insert into Mf_IC
cmd1.ExecuteNonQuery()

Dim dt2 As New DataTable
Dim dt3 As New DataTable
Dim dr2 As SqlDataReader
Dim dr3 As SqlDataReader
For i = 0 To dgv1.RowCount - 1 '追行insert into tf_IC
dt2.Clear()
dt3.Clear()

cmd1.CommandText = "insert into tf_IC(IC_NO,ITM,IC_DD,PRD_NO,PRD_MARK,UNIT,QTY,QTY1,WH1,WH2,CST,FIX_CST,CST_STD,PRE_ITM,UP,UP_CST,UP_CST_UT,EFF_DD,REM) values('" & TextBox2.Text & "','" & dgv1.Rows(i).Cells("ITM").Value & "','" & dgv1.Rows(i).Cells("IC_DD1").Value & "','" & dgv1.Rows(i).Cells("PRD_NO").Value & "','" & dgv1.Rows(i).Cells("PRD_MARK").Value & "',1,'" & dgv1.Rows(i).Cells("QTY").Value & "','" & dgv1.Rows(i).Cells("QTY1").Value & "','" & dgv1.Rows(i).Cells("WH1").Value & "','" & dgv1.Rows(i).Cells("WH2").Value & "','" & dgv1.Rows(i).Cells("CST").Value & "','" & Microsoft.VisualBasic.Left(ComboBox2.Text, 1) & "',0,'" & dgv1.Rows(i).Cells("ITM").Value & "','" & dgv1.Rows(i).Cells("UP").Value & "','" & dgv1.Rows(i).Cells("UP").Value & "','" & dgv1.Rows(i).Cells("UP").Value & "','" & sqdate1 & "','" & dgv1.Rows(i).Cells("SEQ_NO").Value & "')"
cmd1.ExecuteNonQuery()
'--------------------------------------------------------更新库存prdt1
Dim Sqlconn4 As New SqlConnection(Sql1)
Sqlconn4.Open()

Dim Cmd4 As New SqlCommand("SELECT * FROM prdt1 WHERE PRD_NO='" & dgv1.Rows(i).Cells("PRD_NO").Value & "' AND PRD_MARK='" & dgv1.Rows(i).Cells("PRD_MARK").Value & "' AND WH='" & dgv1.Rows(i).Cells("WH1").Value & "' ", Sqlconn4)

'Dim dr2 As SqlDataReader
dr2 = Cmd4.ExecuteReader()

'Dim dt2 As New DataTable
dt2.Load(dr2)
If dt2.Rows.Count = 1 Then
If dt2.Rows(0).Item("QTY") >= dgv1.Rows(i).Cells("QTY").Value Then '在库数有记录且在库数不少于二维码数时出单

cmd1.CommandText = "UPDATE PRDT1 SET QTY=QTY-" & dgv1.Rows(i).Cells("QTY").Value & ",QTY1=QTY1-" & dgv1.Rows(i).Cells("QTY1").Value & ",AMT_CST=AMT_CST-" & dgv1.Rows(i).Cells("CST").Value & " WHERE PRD_NO='" & dgv1.Rows(i).Cells("PRD_NO").Value & "' AND PRD_MARK='" & dgv1.Rows(i).Cells("PRD_MARK").Value & "' AND WH='" & dgv1.Rows(i).Cells("WH1").Value & "' "
cmd1.ExecuteNonQuery()
'------------------------------------------
cmd1.CommandText = "UPDATE TBRSYS.DBO.RMB_SEQ SET WH='" & dgv1.Rows(i).Cells("WH2").Value & "' WHERE SEQ_NO='" & dgv1.Rows(i).Cells("SEQ_NO").Value & "' "
cmd1.ExecuteNonQuery()

'-----------------------------------在进仓内新增数据
Dim Sqlconn5 As New SqlConnection(Sql1)
Sqlconn5.Open()

Dim Cmd5 As New SqlCommand("SELECT * FROM prdt1 WHERE PRD_NO='" & dgv1.Rows(i).Cells("PRD_NO").Value & "' AND PRD_MARK='" & dgv1.Rows(i).Cells("PRD_MARK").Value & "' AND WH='" & dgv1.Rows(i).Cells("WH2").Value & "' ", Sqlconn5)

' Dim dr3 As SqlDataReader
dr3 = Cmd5.ExecuteReader()
'Dim dt3 As New DataTable
dt3.Load(dr3)
If dt3.Rows.Count = 1 Then '有数据时更新
cmd1.CommandText = "UPDATE PRDT1 SET QTY=QTY+" & dgv1.Rows(i).Cells("QTY").Value & ",QTY1=QTY1+" & dgv1.Rows(i).Cells("QTY1").Value & ",AMT_CST=AMT_CST+" & dgv1.Rows(i).Cells("CST").Value & " WHERE PRD_NO='" & dgv1.Rows(i).Cells("PRD_NO").Value & "' AND PRD_MARK='" & dgv1.Rows(i).Cells("PRD_MARK").Value & "' AND WH='" & dgv1.Rows(i).Cells("WH2").Value & "' "
cmd1.ExecuteNonQuery()
'--------------------
Else '没数据时insert into
cmd1.CommandText = "insert into PRDT1(WH,PRD_NO,PRD_MARK,QTY,QTY1,AMT_CST,LST_IND,LST_SFD) VALUES('" & dgv1.Rows(i).Cells("WH2").Value & "','" & dgv1.Rows(i).Cells("PRD_NO").Value & "','" & dgv1.Rows(i).Cells("PRD_MARK").Value & "','" & dgv1.Rows(i).Cells("QTY").Value & "','" & dgv1.Rows(i).Cells("QTY1").Value & "','" & dgv1.Rows(i).Cells("CST").Value & "','" & sqdate1 & "','" & sqdate1 & "')"
cmd1.ExecuteNonQuery()
End If
Sqlconn5.Close()
Cmd5.Connection.Close()

Else
MsgBox("二维码重量大于现在库数，不能出库")
End If
Sqlconn4.Close()
Cmd4.Connection.Close()

Else
MsgBox("在库数不足，不能出库", vbOKOnly)
cmd1.CommandText = "UPDATE PRDT44441 SET QTY=QTY-convert(decimal(10,8)' " '--------
cmd1.ExecuteNonQuery()
Sqlconn4.Close()
Cmd4.Connection.Close()

End If

Next

...全文