my self avi i m developing application in vb6.0 and sql server 2000 which is running on lan almost on more than 50 computers while inserting in database i m using upd lock on table. initially it worked fine when there was very few data say 100 to 200 data but when there are now more than 500 records its not saving any record and also itsnot giving any error msg to me. when i clean feedback table on which i i used upd lock record get saved. below is insert procedure plz tell me any changes or any other method to do it.
-----insert procedure---
strSQL = " BEGIN TRANSACTION "
strSQL = strSQL & "Select TransId from FeedBack with(updlock) Declare @TransID numeric Declare @colTransID numeric"
If bEditFlag = False Then strSQL = strSQL & " select @TransId = (isnull(max(TransID),0) + 1) from FeedBack " strSQL = strSQL & " select @colTransID = (isnull(max(TransID),0) + 1) from CollectionDeposit "
Else
strSQL = strSQL & " Set @TransID = " & TransID & "" strSQL = strSQL & " Set @colTransID = " & cTransID & "" End If
strSQL = strSQL & " delete from FeedBack where TransID=@TransId"
strSQL = strSQL & " Select TransId from CollectionDeposit with(updlock)"
strSQL = strSQL & " delete from CollectionDeposit where FBTransID=@TransID"
For i = 0 To UBound(vGridArray, 2)
If FBType = "RMND" Then
ExecID = vGridArray(8, i)
ElseIf FBType = "LOAC" Then
ExecID = vGridArray(8, i)
ElseIf FBType = "BCKT" Then
ExecID = vGridArray(8, i) End If
strSQL = strSQL & " INSERT INTO FeedBack (TransID,FeedbackType,ExecName,FileID,FileSrno,rCode,Remark,ReminderDate,FeedBackDate,CompanyNAme,BankName,UserName,TransDate,TransTime,FBEntryDate,statusid,TowhomeMet)" strSQL = strSQL & " VALUES(@TransID,'" & FBType & "'," & ExecID & "," & vGridArray(0, i) & "," & vGridArray(1, i) & ",'" & vGridArray(2, i) & "','" & vGridArray(3, i) & "',convert(datetime,'" & vGridArray(4, i) & "',103),convert(datetime,'" & vGridArray(5, i) & "',103)," & g_compId & "," & g_BankId & "," & g_UserId & ",convert(datetime,'" & Date & "',103),'" & Time & "',convert(datetime,'" & FBEntryDate & "',103)," & vGridArray(6, i) & ",'" & vGridArray(7, i) & "')"
strsql1 = "select FileSrno from FBStatusDetails" strsql1 = strsql1 & " where FileID=" & vGridArray(0, i) & " and FileSrno=" & vGridArray(1, i) & " and CompanyNAme=" & g_compId & " and BankName=" & g_BankId & ""
Set rsGrd = clsDa.GetRecordSet(strsql1, "", QO_Usetext)
If rsGrd.RecordCount > 0 Then
strSQL = strSQL & " Update FBStatusDetails set TransID =@TransID,UserName=" & g_UserId & ",TransDate=convert(datetime,'" & Date & "',103),TransTime='" & Time & "',StatusID=" & vGridArray(6, i) & "" strSQL = strSQL & " where FileID=" & vGridArray(0, i) & " and FileSrno=" & vGridArray(1, i) & " and CompanyNAme=" & g_compId & " and BankName=" & g_BankId & ""
Else
strSQL = strSQL & " INSERT INTO FBStatusDetails (TransID,FileID,FileSrno,CompanyNAme,BankName,UserName,TransDate,TransTime,StatusID)"
strSQL = strSQL & " VALUES(@TransID," & vGridArray(0, i) & "," & vGridArray(1, i) & "," & g_compId & "," & g_BankId & "," & g_UserId & ",convert(datetime,'" & Date & "',103),'" & Time & "'," & vGridArray(6, i) & ")"
End If
rsGrd.Close
Set rsGrd = Nothing
If FBType = "EXEC" Then
RcptNo = Split(vGridArray(8, i), ",")
RcptDt = Split(vGridArray(9, i), ",")
RcptAmt = Split(vGridArray(10, i), ",")
RcptRBID = Split(vGridArray(11, i), ",")
RcptCBC = Split(vGridArray(13, i), ",")
Else
RcptNo = Split(vGridArray(9, i), ",")
RcptDt = Split(vGridArray(10, i), ",")
RcptAmt = Split(vGridArray(11, i), ",")
RcptRBID = Split(vGridArray(12, i), ",")
RcptCBC = Split(vGridArray(13, i), ",")
End If
For iRcpt = 0 To UBound(RcptNo)
Total = Val(RcptAmt(iRcpt)) + Val(RcptCBC(iRcpt))
strsql1 = "select PaymentMode,ChequeNo,ChqDate,ChqBankName from Collectiondeposit where Receiptno=" & RcptNo(iRcpt) & " and RBID=" & RcptRBID(iRcpt) & " and fileid=" & g_FileId & " and Execname=" & ExecID & " and Filesrno=" & vGridArray(1, i) & " and recondate=convert(datetime,'" & FBEntryDate & "',103)"
Set rsRcpt = clsDa.GetRecordSet(strsql1, "", QO_Usetext)
If rsRcpt.RecordCount > 0 Then
PMOde = rsRcpt(0)
chqNo = rsRcpt(1)
chqDt = rsRcpt(2)
ChqBnk = rsRcpt(3)
Else
PMOde = "CASH" chqNo = "" chqDt = "01/01/1900" ChqBnk = "."
End If
strSQL = strSQL & " INSERT INTO CollectionDeposit (RBId,ReceiptNo ,FileId,FileSrNo,ExecName,Bucket,Amount,CBC,TotalAmt,PaymentMode,ChequeNo,ChqDate,ChqBankName,CancelFlag,CancelRsn,CompanyNAme,BankName,UserName,TransDate,TransTime,VoucherDate,ReconDate,FBTransid)" strSQL = strSQL & " VALUES(" & RcptRBID(iRcpt) & "," & RcptNo(iRcpt) & "," & vGridArray(0, i) & "," & vGridArray(1, i) & "," & ExecID & ",0," & RcptAmt(iRcpt) & "," & RcptCBC(iRcpt) & "," & Total & ",'" & PMOde & "','" & chqNo & "',convert(datetime,'" & chqDt & "',103),'" & ChqBnk & "',0,''," & g_compId & "," & g_BankId & "," & g_UserId & ",convert(datetime,'" & Date & "',103),'" & Time & "',convert(datetime,'" & Format(RcptDt(iRcpt), "dd/mm/yyyy") & "',103),convert(datetime,'" & FBEntryDate & "',103),@TransId)"
Next
Next
strSQL = strSQL & " COMMIT TRANSACTION " Insert = clsDa.Execute(strSQL, "", QO_Usetext)
No one has replied yet! Why not be the first?
Sign in or Join us (it's free).