Sub Createicstockbill(sSheetName As String)
Dim cnZW As New ADODB.Connection
Dim rst As New ADODB.Recordset, rst1 As New ADODB.Recordset
Dim sFInterID As Integer
Dim sFDate As Date
Dim sBillNo As Integer, sFDCStockID As Integer
Dim sFItemID As String, sFAmount As String
Dim sFtranType As Integer
cnZW.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;password=" + Range("B3") + ";Initial Catalog=" + Range("B2") + ";Data Source=" + Range("B1")
On Error GoTo 0
Set rst = Nothing
'开始事务
cnZW.BeginTrans
i = 2
rst.Open "select * from icstockbillentry", cnZW, adOpenStatic, adLockOptimistic
Do
sFItemID = Sheets(sSheetName).Cells(i, 1)
sFAmount = Sheets(sSheetName).Cells(i, 4)
Sheets("参数").Select
sFInterID = Range("B8")
With rst
.AddNew
.Fields("FItemID") = sFItemID
.Fields("FAmount") = sFAmount
.Fields("FEntryID") = i - 1
.Fields("FInterID") = sFInterID
.Fields("FBrNo") = 0
.Fields("FQtyMust") = 0
.Fields("FQty") = 0
.Fields("FPrice") = 0
.Fields("FUnitID") = 0
.Fields("FAuxPrice") = 0
.Fields("FQtyActual") = 0
.Fields("FPlanPrice") = 0
.Fields("FAuxQtyActual") = 0
.Fields("FAuxPlanPrice") = 0
.Fields("FSourceEntryID") = 0
.Fields("FCommitQty") = 0
.Fields("FAuxCommitQty") = 0
.Fields("FKFPeriod") = 0
.Fields("FDCSPID") = 0
.Fields("FOrgBillEntryID") = 0
.Fields("FOperID") = 0
.Update
End With
i = i + 1
Loop Until Sheets(sSheetName).Cells(i, 1) = ""
rst.Close
cnZW.CommitTrans
rst.Open "select * from icstockbill", cnZW, adOpenStatic, adLockOptimistic
Do
Sheets("参数").Select
sFDate = Range("B4")
sFBillNo = Range("B5")
sFDCStockID = Range("B6")
sFtranType = Range("B7")
sFInterID = Range("B8")
With rst
.AddNew
.Fields("FInterID") = sFInterID
.Fields("FDCStockID") = sFDCStockID
.Fields("FDate") = Date
.Fields("FtranType") = sFtranType
.Fields("FBillNo") = sFBillNo
.Fields("FBrNo") = 0
.Fields("FBillerID") = 16394
.Fields("FHookInterID") = 0
.Fields("FPosted") = 0
.Fields("FCheckSelect") = 0
.Fields("FROB") = 1
.Fields("FStatus") = 0
.Fields("FUpStockWhenSave") = 0
.Fields("FCostOBJID") = 0
.Fields("FCancellation") = 0
.Fields("FOrgBillInterID") = 0
.Fields("FBackFlushed") = 0
.Fields("FWBinterID") = 0
.Fields("FPurposeID") = 0
.Fields("FCPInStockInterID") = 0
.Fields("FPayBillID") = 0
.Fields("FRelationTranType") = 0
.Fields("FRelateInvoiceID") = 0
.Update
End With
Loop
rst.Close
cnZW1.CommitTrans
MsgBox "已成功生成金额调整单!", vbOKOnly + vbInformation, "提示信息"
err:
Exit Sub
ConnErr:
MsgBox "数据源连接错误!"
Exit Sub
prgerr:
MsgBox "用户操作失误,系统出错!"
End Sub
运行时错误‘-2147217873 (80040e2f)
违反了PRIMARY KEY约束'PK_ICStockbillEntry'.不能在对象'ICStockbillEntry'中插入重复键。 调试 .update显黄色
请各位帮我看看啊。谢谢了~!
[此贴子已经被作者于2003-12-29 18:59:27编辑过] |