sql server200 insert problem with upd lock

• • Reply

  13 years ago

  by avinash5

  avi pawar

  Pune, India

  Joined 14 years ago

  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)

  Report abuse