你加一个事务就可以了
BEGIN TRAN
--生成WS中业务员汇总销售
SELECT CREATE_BY,
SUM(QTY*CAST(PRICE AS MONEY)*CUR_RATE/(1+TAX_RATE)) AS SIAMOUNT,
SUM(QTY*CAST(AVG_COST AS MONEY)) AS SICOST,
(SUM(QTY*CAST(PRICE AS MONEY)*CUR_RATE/(1+TAX_RATE)) -SUM(QTY*CAST(AVG_COST AS MONEY))) AS SINP
FROM SI AS H ,SI_DETAIL AS D
WHERE H.SI_NO=D.SI_NO AND DATE_CREATE BETWEEN @BEGINDATE AND @ENDDATE
GROUP BY CREATE_BY
--生成WSCH中业务员汇总销售
SELECT CREATE_BY,
SUM(QTY*CAST(PRICE AS MONEY)*CUR_RATE) AS SIAMOUNT,
SUM(QTY*CAST(AVG_COST AS MONEY)) AS SICOST,
(SUM(QTY*CAST(PRICE AS MONEY)*CUR_RATE) -SUM(QTY*CAST(AVG_COST AS MONEY))) AS SINP
FROM WSCH.DBO.SI AS H ,WSCH.DBO.SI_DETAIL AS D
WHERE H.SI_NO=D.SI_NO AND DATE_CREATE BETWEEN @BEGINDATE AND @ENDDATE
GROUP BY CREATE_BY
COMMIT TRANSACTION
.addnew
.fields(0).value=text1.text
.................
.update
end with
for i=0 to txt.count-1
with rsdetail
.addnew
.fields(i).value=txt(i).text
...........
.update
end with
next
cn.CommitTrans
exit sub
InsertErr:
cn.RollbackTrans
msgbox "ERROR",vbinformation,"AAA"
end sub
第三:(大型数据库SQL Server)
private sub TSave()
dim sqlstr as string
sqlstr="Begin trans insert into table(field1,....fieldn) values(value1,value2.....valuen) comm trans"
cn.execute(sqlstr)
end sub
第一:
private sub TSave()
cn.begintrans
on error goto InsertErr
cn.execute(SQL语句)
cn.CommitTrans
exit sub
InsertErr:
cn.RollbackTrans
msgbox "ERROR",vbinformation,"AAA"
end sub
第二:
private sub TSave()
dim rsMain as new adodb.recordset‘主表
dim rsDetail as new adodb.recordset’明细表
连接自己写
cn.begintrans
on error goto InsertErr
with rsmain
给你一个例子.数据库有两个:分别是WS,WSCH;此存储过程在WS中
--生成WS中业务员汇总销售
SELECT CREATE_BY,
SUM(QTY*CAST(PRICE AS MONEY)*CUR_RATE/(1+TAX_RATE)) AS SIAMOUNT,
SUM(QTY*CAST(AVG_COST AS MONEY)) AS SICOST,
(SUM(QTY*CAST(PRICE AS MONEY)*CUR_RATE/(1+TAX_RATE)) -SUM(QTY*CAST(AVG_COST AS MONEY))) AS SINP
FROM SI AS H ,SI_DETAIL AS D
WHERE H.SI_NO=D.SI_NO AND DATE_CREATE BETWEEN @BEGINDATE AND @ENDDATE
GROUP BY CREATE_BY
--生成WSCH中业务员汇总销售
SELECT CREATE_BY,
SUM(QTY*CAST(PRICE AS MONEY)*CUR_RATE) AS SIAMOUNT,
SUM(QTY*CAST(AVG_COST AS MONEY)) AS SICOST,
(SUM(QTY*CAST(PRICE AS MONEY)*CUR_RATE) -SUM(QTY*CAST(AVG_COST AS MONEY))) AS SINP
FROM WSCH.DBO.SI AS H ,WSCH.DBO.SI_DETAIL AS D
WHERE H.SI_NO=D.SI_NO AND DATE_CREATE BETWEEN @BEGINDATE AND @ENDDATE
GROUP BY CREATE_BY
请注意看"FROM WSCH.DBO.SI AS H ,WSCH.DBO.SI_DETAIL AS D"这一句.
你可以把存储过程改成VB程序代码.这个不用我教你了吧???
:)