我的程序是一个loop,里面call2个SP
while
SP1
SP2
loop每次都是call SP1 或者 SP2导致VB 整个Crash了。
已经尝试了2天,用各种不同的方法,定义全局变量recordset 和 command,包括2个SP使用不同的connection都不能解决这个问题。求救
while
SP1
SP2
loop每次都是call SP1 或者 SP2导致VB 整个Crash了。
已经尝试了2天,用各种不同的方法,定义全局变量recordset 和 command,包括2个SP使用不同的connection都不能解决这个问题。求救
解决方案 »
- 求助,如何将txt文本中的数据点提取出来,并保存到数组里
- VB中ActiveX exe, ActiveX DLL, Active Document DLL, Active Document EXE有什么区别阿
- 什么地方有王国荣 VB6 数据庫程式設計 下载
- 不好意思,初学vb,我想通过点击一个按钮来弹出一个窗口,然后通过此窗口返回值,请列出具体步骤,在线结分,先谢了
- 请教关于mschart?
- VB版面的朋友们,问大家一个问题:大家发现好帖子的时候怎么保存到硬盘的?
- 水晶报表里有个sql 公式,不知道怎么用的
- 动态菜单的问题!急急急!!!!!!!!
- vb向库添加记录问题
- 急急!!!!
- 简体与繁体系统的环境变量或是系统变量区别在哪
- 大家帮忙看下代码?
问下:
1,什么性质的sp,是只读,还是有insert/update。
2,你说的crash是没反应还是崩溃,error num?
3,如果是有insert/update,你的tran是怎么设立的?
4,你是怎么连接数据库的?
Faulting application vb6.exe, version 6.0.81.76, faulting module ntdll.dll, version 5.1.2600.5755, fault address 0x000369da.while loop里面就是简单得call SP1, call SP2,里面分别insert tbl1 和 tbl2谢谢各位拉
感觉上是windows的问题,什么os,sp?
我只是节省时间,请不要把重心放在while loop里面,肯定不是死循环
现在问题基本定位了,我详细说一下,希望各位能帮忙
while SP1 ..... parameter output @a
SP2 ..... use @a as a input parametermove next
loop现在问题出在,在第一循环执行SP2时候的execute的时候,或者在第二循环时执行SP1的execute的时候,就会整个crash. 估计是Sybase在处理利用 output parameter as input parameter的局限性。我做了一个测试,用SQL server写同样的2个SP1, SP2,一切正常。
再次谢谢各位
必须各自创建独立的对象,相互之间通过 Value 属性赋值。
我的coding大概是这样的
while
dim a, b, c, d
sp1 byval a input, byval b input, byref c output, byref d output
sp2 byval c input, byval d input
move next
loop
每次的错误都是在cmd.execute里面,数据库的操作已经完成了,因为SP1是insert TBL1, SP2 insert TBL2,出错的时候TBL里面已经成功有东西了
错误是 ntdll.dll的话,应该是资源的释放问题,这个真得很棘手啊,谢谢各位肋
ByVal sCompCode As String, ByVal sPolno As String, ByVal sDocID As String, ByVal dtTranDate As Date, ByVal sClmPayRef As String, _
ByVal sBatchID As String, ByVal sAgybudcd As String, ByVal sStatusCD As String, ByVal sHandleCD As String, ByVal sPrintCD As String, _
ByVal sPayType As String, ByVal dtPaidDate As Date, ByVal sCheqCur As String, ByVal nCheqAmt As Double, ByVal sPayee1 As String, _
ByVal sPayee2 As String, ByVal sAddress1 As String, ByVal sAddress2 As String, ByVal sAddress3 As String, ByVal sAddress4 As String, _
ByVal sAddress5 As String, ByVal sPostalCode As String, ByVal sParCode As String, ByVal sAgent1 As String, ByVal sAgent2 As String, _
ByVal sCreatedBy As String, ByRef sTranID As String, ByRef nErrCode As Long, ByRef sErrDesc As String)
Dim SYBcmd As ADODB.Command
Set SYBcmd = New ADODB.Command
Dim oOutput1 As ADODB.Parameter
Dim oOutput2 As ADODB.Parameter
Dim oOutput3 As ADODB.Parameter
Dim oOutput4 As ADODB.Parameter
Dim oOutput5 As ADODB.Parameter
Dim oOutput6 As ADODB.Parameter
Dim oOutput7 As ADODB.Parameter
Dim oOutput8 As ADODB.Parameter
Dim oOutput9 As ADODB.Parameter
Dim oOutput10 As ADODB.Parameter
Dim oOutput11 As ADODB.Parameter
Dim oOutput12 As ADODB.Parameter
Dim oOutput13 As ADODB.Parameter
Dim oOutput14 As ADODB.Parameter
Dim oOutput15 As ADODB.Parameter
Dim oOutput16 As ADODB.Parameter
Dim oOutput17 As ADODB.Parameter
Dim oOutput18 As ADODB.Parameter
Dim oOutput19 As ADODB.Parameter
Dim oOutput20 As ADODB.Parameter
Dim oOutput21 As ADODB.Parameter
Dim oOutput22 As ADODB.Parameter
Dim oOutput23 As ADODB.Parameter
Dim oOutput24 As ADODB.Parameter
Dim oOutput25 As ADODB.Parameter
Dim oOutput26 As ADODB.Parameter
Dim oOutput27 As ADODB.Parameter
Dim oOutput28 As ADODB.Parameter
Dim oOutput29 As ADODB.Parameter
Dim oOutput30 As ADODB.Parameter
ExecSYBMasterSP = False
On Error GoTo ErrHandle
With SYBcmd
.ActiveConnection = gSYBCnn
.CommandType = adCmdStoredProc
.CommandTimeout = 1000 .CommandText = SYB_MASTER
'.Parameters.Append .CreateParameter("txt_Int_ID", adVarChar, adParamInput, 20, sHandleID)
Set oOutput1 = .CreateParameter("txt_Int_ID", adVarChar, adParamInput, 20, sHandleID)
.Parameters.Append oOutput1
' .Parameters.Append .CreateParameter("txt_Comp_No", adVarChar, adParamInput, 3, sCompCode)
Set oOutput2 = .CreateParameter("txt_Comp_No", adVarChar, adParamInput, 20, sCompCode)
.Parameters.Append oOutput2
' .Parameters.Append .CreateParameter("txt_Policy_No", adVarChar, adParamInput, 10, sPolno)
Set oOutput3 = .CreateParameter("txt_Policy_No", adVarChar, adParamInput, 10, sPolno)
.Parameters.Append oOutput3
' .Parameters.Append .CreateParameter("txt_Doc_ID", adVarChar, adParamInput, 10, sDocID)
Set oOutput4 = .CreateParameter("txt_Doc_ID", adVarChar, adParamInput, 10, sDocID)
.Parameters.Append oOutput4
' .Parameters.Append .CreateParameter("dte_Doc_Date", adDBTimeStamp, adParamInput, 0, dtTranDate)
Set oOutput5 = .CreateParameter("dte_Doc_Date", adDBTimeStamp, adParamInput, 0, dtTranDate)
.Parameters.Append oOutput5
'
' .Parameters.Append .CreateParameter("txt_Ref_No", adVarChar, adParamInput, 15, sClmPayRef)
Set oOutput6 = .CreateParameter("txt_Ref_No", adVarChar, adParamInput, 15, sClmPayRef)
.Parameters.Append oOutput6
' .Parameters.Append .CreateParameter("txt_DPDSK", adVarChar, adParamInput, 5, sBatchID)
Set oOutput7 = .CreateParameter("txt_DPDSK", adVarChar, adParamInput, 5, sBatchID)
.Parameters.Append oOutput7
' .Parameters.Append .CreateParameter("txt_FSC_Loc", adVarChar, adParamInput, 2, sAgybudcd)
Set oOutput8 = .CreateParameter("txt_FSC_Loc", adVarChar, adParamInput, 2, sAgybudcd)
.Parameters.Append oOutput8
' .Parameters.Append .CreateParameter("txt_Status_Cod", adVarChar, adParamInput, 2, sStatusCD)
Set oOutput9 = .CreateParameter("txt_Status_Cod", adVarChar, adParamInput, 2, sStatusCD)
.Parameters.Append oOutput9
' .Parameters.Append .CreateParameter("txt_Handling_C", adVarChar, adParamInput, 2, sHandleCD)
Set oOutput10 = .CreateParameter("txt_Handling_C", adVarChar, adParamInput, 2, sHandleCD)
.Parameters.Append oOutput10
'
' .Parameters.Append .CreateParameter("txt_Printing_C", adVarChar, adParamInput, 2, sPrintCD)
Set oOutput11 = .CreateParameter("txt_Printing_C", adVarChar, adParamInput, 2, sPrintCD)
.Parameters.Append oOutput11
' .Parameters.Append .CreateParameter("txt_Pay_Type", adVarChar, adParamInput, 4, sPayType)
Set oOutput12 = .CreateParameter("txt_Pay_Type", adVarChar, adParamInput, 4, sPayType)
.Parameters.Append oOutput12
' .Parameters.Append .CreateParameter("dte_Pay_Date", adDBTimeStamp, adParamInput, 0, dtPaidDate)
Set oOutput13 = .CreateParameter("dte_Pay_Date", adDBTimeStamp, adParamInput, 0, dtPaidDate)
.Parameters.Append oOutput13
' .Parameters.Append .CreateParameter("txt_Pay_Curren", adVarChar, adParamInput, 3, sCheqCur)
Set oOutput14 = .CreateParameter("txt_Pay_Curren", adVarChar, adParamInput, 3, sCheqCur)
.Parameters.Append oOutput14
' .Parameters.Append .CreateParameter("num_Pay_Amount", adDouble, adParamInput, 17, nCheqAmt)
Set oOutput15 = .CreateParameter("num_Pay_Amount", adDouble, adParamInput, 17, nCheqAmt)
.Parameters.Append oOutput15
'
' .Parameters.Append .CreateParameter("txt_Payee_Name1", adVarChar, adParamInput, 100, sPayee1)
Set oOutput16 = .CreateParameter("txt_Payee_Name1", adVarChar, adParamInput, 100, sPayee1)
.Parameters.Append oOutput16
' .Parameters.Append .CreateParameter("txt_Payee_Name2", adVarChar, adParamInput, 100, sPayee2)
Set oOutput17 = .CreateParameter("txt_Payee_Name2", adVarChar, adParamInput, 100, sPayee2)
.Parameters.Append oOutput17
' .Parameters.Append .CreateParameter("txt_Addr_Line1", adVarChar, adParamInput, 50, sAddress1)
Set oOutput18 = .CreateParameter("txt_Addr_Line1", adVarChar, adParamInput, 50, sAddress1)
.Parameters.Append oOutput18
' .Parameters.Append .CreateParameter("txt_Addr_Line2", adVarChar, adParamInput, 50, sAddress2)
Set oOutput19 = .CreateParameter("txt_Addr_Line2", adVarChar, adParamInput, 50, sAddress2)
.Parameters.Append oOutput19
' .Parameters.Append .CreateParameter("txt_Addr_Line3", adVarChar, adParamInput, 50, sAddress3)
Set oOutput20 = .CreateParameter("txt_Addr_Line3", adVarChar, adParamInput, 50, sAddress3)
.Parameters.Append oOutput20
'
' .Parameters.Append .CreateParameter("txt_Addr_Line4", adVarChar, adParamInput, 50, sAddress4)
Set oOutput21 = .CreateParameter("txt_Addr_Line4", adVarChar, adParamInput, 50, sAddress4)
.Parameters.Append oOutput21
' .Parameters.Append .CreateParameter("txt_Addr_Line5", adVarChar, adParamInput, 50, sAddress5)
Set oOutput22 = .CreateParameter("txt_Addr_Line5", adVarChar, adParamInput, 50, sAddress5)
.Parameters.Append oOutput22
' .Parameters.Append .CreateParameter("txt_Postal_Cod", adVarChar, adParamInput, 10, sPostalCode)
Set oOutput23 = .CreateParameter("txt_Postal_Cod", adVarChar, adParamInput, 10, sPostalCode)
.Parameters.Append oOutput23
' .Parameters.Append .CreateParameter("txt_PAR_Code", adVarChar, adParamInput, 2, sParCode)
Set oOutput24 = .CreateParameter("txt_PAR_Code", adVarChar, adParamInput, 2, sParCode)
.Parameters.Append oOutput24
' .Parameters.Append .CreateParameter("txt_Agent_Code1", adVarChar, adParamInput, 10, sAgent1)
Set oOutput25 = .CreateParameter("txt_Agent_Code1", adVarChar, adParamInput, 10, sAgent1)
.Parameters.Append oOutput25
'
' .Parameters.Append .CreateParameter("txt_Agent_Code2", adVarChar, adParamInput, 10, sAgent2)
Set oOutput26 = .CreateParameter("txt_Agent_Code2", adVarChar, adParamInput, 10, sAgent2)
.Parameters.Append oOutput26
' .Parameters.Append .CreateParameter("txt_Created_By", adVarChar, adParamInput, 100, sCreatedBy)
Set oOutput27 = .CreateParameter("txt_Created_By", adVarChar, adParamInput, 100, sCreatedBy)
.Parameters.Append oOutput27
'.Parameters.Append .CreateParameter("txtTxnID", adVarChar, adParamOutput, 20)
Set oOutput28 = .CreateParameter("txtTxnID", adVarChar, adParamOutput, 20)
.Parameters.Append oOutput28
'.Parameters.Append .CreateParameter("intErrCode", adInteger, adParamOutput, 4)
Set oOutput29 = .CreateParameter("intErrCode", adInteger, adParamOutput, 4)
.Parameters.Append oOutput29
'.Parameters.Append .CreateParameter("txtErrDesc", adVarChar, adParamOutput, 255)
Set oOutput30 = .CreateParameter("txtErrDesc", adVarChar, adParamOutput, 255)
.Parameters.Append oOutput30
.Execute
End With
Dim s As String * 20
s = Trim(GetString(oOutput28.Value)) 'Trim(GetString(SYBcmd.Parameters("txtTxnID").Value))
sTranID = Trim(s)
Debug.Print "sTranID = " & sTranID
'nErrCode = GetNumber(SYBcmd.Parameters("intErrCode").Value)
'sErrDesc = Trim(GetString(SYBcmd.Parameters("txtErrDesc").Value)) ExecSYBMasterSP = True
'Set SYBcmd.ActiveConnection = Nothing
Do While SYBcmd.Parameters.Count > 0
SYBcmd.Parameters.Delete (0)
Loop
Set oOutput1 = Nothing
Set oOutput2 = Nothing
Set oOutput3 = Nothing
Set oOutput4 = Nothing
Set oOutput5 = Nothing
Set oOutput6 = Nothing
Set oOutput7 = Nothing
Set oOutput8 = Nothing
Set oOutput9 = Nothing
Set oOutput10 = Nothing
Set oOutput11 = Nothing
Set oOutput12 = Nothing
Set oOutput13 = Nothing
Set oOutput14 = Nothing
Set oOutput15 = Nothing
Set oOutput16 = Nothing
Set oOutput17 = Nothing
Set oOutput18 = Nothing
Set oOutput19 = Nothing
Set oOutput20 = Nothing
Set oOutput21 = Nothing
Set oOutput22 = Nothing
Set oOutput23 = Nothing
Set oOutput24 = Nothing
Set oOutput25 = Nothing
Set oOutput26 = Nothing
Set oOutput27 = Nothing
Set oOutput28 = Nothing
Set oOutput29 = Nothing
Set oOutput30 = Nothing
Set SYBcmd = Nothing
Exit FunctionErrHandle:
sErrMsg = "Error in ExecSYBMasterSP" & vbCrLf & "Error Number: " & CStr(Err.Number) & vbCrLf & "Error Description: " & Err.Description
RecordLog sErrMsg
On Error GoTo 0
End Function
Public Function ExecSYBDetailSP(ByRef sErrMsg As String, ByVal sTranID1 As String, ByVal sDocID As String, ByVal sDocPath As String, _
ByVal sSeqNo As String, ByVal sCreatedBy As String, ByRef sDetailIDDtl As String, ByRef nErrCodeDtl As Long, ByRef sErrDescDtl As String)
Dim SYBcmd As ADODB.Command
Set SYBcmd = New ADODB.Command
Dim oOutput1 As ADODB.Parameter
Dim oOutput2 As ADODB.Parameter
Dim oOutput3 As ADODB.Parameter
Dim oOutput4 As ADODB.Parameter
Dim oOutput5 As ADODB.Parameter
Dim oOutput6 As ADODB.Parameter
Dim oOutput7 As ADODB.Parameter
Dim oOutput8 As ADODB.Parameter
ExecSYBDetailSP = False
On Error GoTo ErrHandle
With SYBcmd
.ActiveConnection = gSYBCnn
.CommandType = adCmdStoredProc
.CommandTimeout = 1000
.CommandText = SYB_DETAIL
'.Parameters.Append .CreateParameter("txt_Txn_ID", adVarChar, adParamInput, 20, sTranID1)
Set oOutput1 = .CreateParameter("txt_Txn_ID", adVarChar, adParamInput, 20, sTranID1)
.Parameters.Append oOutput1
'.Parameters.Append .CreateParameter("txt_Doc_ID", adVarChar, adParamInput, 10, sDocID)
Set oOutput2 = .CreateParameter("txt_Doc_ID", adVarChar, adParamInput, 10, sDocID)
.Parameters.Append oOutput2
'.Parameters.Append .CreateParameter("txt_Doc_Path", adVarChar, adParamInput, 100, sDocPath)
Set oOutput3 = .CreateParameter("txt_Doc_Path", adVarChar, adParamInput, 100, sDocPath)
.Parameters.Append oOutput3
'.Parameters.Append .CreateParameter("txt_Seq_No", adVarChar, adParamInput, 2, sSeqNo)
Set oOutput4 = .CreateParameter("txt_Seq_No", adVarChar, adParamInput, 2, sSeqNo)
.Parameters.Append oOutput4
'.Parameters.Append .CreateParameter("txt_Created_By", adVarChar, adParamInput, 100, sCreatedBy)
Set oOutput5 = .CreateParameter("txt_Created_By", adVarChar, adParamInput, 100, sCreatedBy)
.Parameters.Append oOutput5
'.Parameters.Append .CreateParameter("txtDetailID", adVarChar, adParamOutput, 15)
Set oOutput6 = .CreateParameter("txtDetailID", adVarChar, adParamOutput, 15)
.Parameters.Append oOutput6
'.Parameters.Append .CreateParameter("intErrCode", adInteger, adParamOutput, 4)
Set oOutput7 = .CreateParameter("intErrCode", adInteger, adParamOutput, 4)
.Parameters.Append oOutput7
'.Parameters.Append .CreateParameter("txtErrDesc", adVarChar, adParamOutput, 255)
Set oOutput8 = .CreateParameter("txtErrDesc", adVarChar, adParamOutput, 255)
.Parameters.Append oOutput8
.Execute
End With
Dim s As String * 15
s = Trim(GetString(oOutput6.Value)) 'Trim(GetString(SYBcmd.Parameters("txtDetailID").Value))
sDetailIDDtl = Trim(s)
'nErrCodeDtl = GetNumber(SYBcmd.Parameters("intErrCode").Value)
'sErrDescDtl = Trim(GetString(SYBcmd.Parameters("txtErrDesc").Value))
'Set SYBcmd.ActiveConnection = Nothing
Do While SYBcmd.Parameters.Count > 0
SYBcmd.Parameters.Delete (0)
Loop
Set oOutput1 = Nothing
Set oOutput2 = Nothing
Set oOutput3 = Nothing
Set oOutput4 = Nothing
Set oOutput5 = Nothing
Set oOutput6 = Nothing
Set oOutput7 = Nothing
Set oOutput8 = Nothing Set SYBcmd = Nothing
ExecSYBDetailSP = True
Exit Function
ErrHandle:
sErrMsg = "Error in ExecSYBDetailSP" & vbCrLf & "Error Number: " & CStr(Err.Number) & vbCrLf & "Error Description: " & Err.Description
RecordLog sErrMsg
On Error GoTo 0
End Function
定义一个就可以了,set 用同一个.
再说明次,已经很明显是资源释放的问题,就是在释放 Set SYBcmd = Nothing 出错,应该是sTranIDOutput没有释放成功。症状是死机,只能从事件管理器找到错误。错误就是:Faulting application vb6.exe, version 6.0.81.76, faulting module ntdll.dll, version 5.1.2600.5755, fault address 0x000369da. 这里贴一下调用2个SP的functionnErrCode = 2
sErrDesc = "Initial"
sTranIDOutput = "Initial"
If Not ExecSYBMasterSP(sErrMsg, sHandleID, sCompCode, sPolno, sDocID, _
dtTranDate, sRefNo, sBatchID, sAgybudcd, sStatusCD, _
sHandleCD, sPrintCD, sPayType, dtPaidDate, sCheqCur, _
nCheqAmt, sPayee1, sPayee2, sAddress1, sAddress2, _
sAddress3, sAddress4, sAddress5, sPostalCode, _
sParCode, sAgent1, sAgent2, sCreatedBy, sTranIDOutput, nErrCode, sErrDesc) Then
RecordLog ("Fail! ExecSYBMasterSP fails at " & Now)
End If
nErrCodeDtl = 2
sErrDescDtl = "Initial"
sDetailIDDtl = "Initial"
sTranIDInput = sTranIDOutput
If Not ExecSYBDetailSP(sErrMsg, sTranIDInput, CS_GCSEOB, msTemp, "1", CS_COMPASS, sDetailIDDtl, nErrCodeDtl, sErrDescDtl) Then
RecordLog ("Fail! ExecSYBDetailSP fail at " & Now)
End If
fo_rs.MoveNext
Public Function GetString(ByRef vValue As Variant) As String
If Not IsNull(vValue) Then
GetString = CStr(vValue)
Else
GetString = MCS_BLANK
End If
End Function
然后将你的 SP 参数逐个加到样例提供的 SP 中,看看是哪个参数引起错误。