Click here to Skip to main content
15,347,733 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am facing
"Transaction (Process ID 426) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."
also some time get this error
"Violation of UNIQUE KEY constraint 'UK_UserTotalEntry_VoucherNo'. Cannot insert duplicate key in object 'dbo.UserTotalEntry'. The duplicate key value is (89295/37029)"
this error some time comes and not. Also I am not able to generate this error again I have tried lots of times. I am trying to insert data in bulk with the help of datatable and created a Typed Table in db. I am sharing my codes below and request for help and also I am using Go daddy Shared Hosting plan in which i can not alter anything in my db. Thanks in advance.

What I have tried:

vb.net code
VB.NET
Public Function SaveUpdate(ByVal Action As String)
    Dim Msg As String = Nothing

    Try
        Dim dt_bulk As New DataTable
        dt_bulk.Columns.Add("VoucherNo")
        dt_bulk.Columns("VoucherNo").Expression = "'" & lbl_vno.Text & "'"
        dt_bulk.AcceptChanges()

        Dim dt_Update As New DataTable
        dt_Update = dgv_manualentryedit.DataSource

        dt_bulk.Merge(dt_Update)

        If cn.State = ConnectionState.Closed Then cn.Open()

        Dim ds As New DataSet()
        cmd = New SqlCommand("Proc_dml_SaveUpdate")

        cmd.Parameters.AddWithValue("@NumberEntry", dt_bulk)
        cmd.Parameters.AddWithValue("@VoucherNo", lbl_vno.Text)
        cmd.Parameters.AddWithValue("@date", CustomDate(dtp_Date.Value))
        cmd.Parameters.AddWithValue("@GameMasterId", GameMasterID)
        cmd.Parameters.AddWithValue("@ProfitID", ProfitIDNo)
        cmd.Parameters.AddWithValue("@TotalAmt", lbl_mamount.Text)
        cmd.Parameters.AddWithValue("@CreatedBy", LoginID)
        cmd.Parameters.AddWithValue("@Action", Action)
        cmd.Parameters.AddWithValue("@DeviceID", "2")

        cmd.Connection = cn
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandTimeout = 9000
        adapter = New SqlDataAdapter(cmd)
        adapter.Fill(ds)

        If DsJantri.Tables.Count = 1 Then
            ds.Tables(0).TableName = "Status"
        Else
            ds.Tables(0).TableName = "PartyDetail"
            ds.Tables(1).TableName = "Status"
        End If



    Catch ex As Exception
        ShowErrorMsg(ex, Me.Name, MethodInfo.GetCurrentMethod().Name)
        Return False
    End Try

    Return True
End Function

--my Stored Procedure
SQL
ALTER PROCEDURE [dbo].[Proc_dml_SaveUpdate]
    @NumberEntry NumberEntry READONLY,
    @VoucherNo varchar(50),
    @date varchar(10),
    @GameMasterId int,
    @ProfitID int,
    @TotalAmt int,
    @CreatedBy int,
    @Action Varchar(50),
    @DeviceID varchar(2)
AS
BEGIN

  Begin Tran
  SET NOCOUNT ON;	  
  BEGIN TRY 
declare  @CrDateTime datetime =CAST(SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30') AS datetime) 
if @Action = 'Save'
begin 
 insert into UserTotalEntry (VoucherNo,date,GameMasterId,ProfitID,TotalAmt,CreatedBy,CreatedOn,DeviceID)
 values  (@VoucherNo , convert(date,('' + @date + ''),103),@GameMasterId, @ProfitId ,@TotalAmt,@CreatedBy,@CrDateTime,@DeviceID)
end   
else if @Action='Update'
begin
 update UserTotalEntry set TotalAmt=@TotalAmt,ModifyOn= @CrDateTime,ModifyBy=@CreatedBy  where VoucherNo=@VoucherNo
 Delete from numberentry where VoucherNo=@VoucherNo
end		 
  INSERT INTO NumberEntry (VoucherNo,Number,Amount,ID)
  SELECT VoucherNo,Number,Amount,ID FROM @NumberEntry

  update  numberentry  set number='0'+number where len(number)=1 and voucherno=@VoucherNo
  
  update numberentry set id = 'BB' where len(number)= 3  and number <> 100  and isnull(id,'') ='' and voucherno=@VoucherNo


  If (@@Error <> 0)  
 Begin          
    rollback transaction       
 End 
 else 
 commit transaction 
		

 select ut.voucherno as'VoucherNo' ,g.GameMasterName ,u.UserName as 'AccountName',
 ut.TotalAmt from usertotalentry  ut  
 left join ProfitnLossAc p on p.profitid =ut.profitid 	
 left join users u on u.UserId =p.UserId 
 left join gamemaster g on g.GameMasterId= ut.GameMasterId
 where ut.voucherno=@VoucherNo

  select 'Entry Saved' as 'Action'


END TRY  


  BEGIN CATCH  

IF @@TRANCOUNT > 0
  ROLLBACK TRANSACTION 

 insert into DbErrorLogs     
 SELECT  
    ERROR_NUMBER() AS ErrorNumber  
    ,ERROR_SEVERITY() AS ErrorSeverity  
    ,ERROR_STATE() AS ErrorState  
    ,ERROR_PROCEDURE() AS ErrorProcedure  
    ,ERROR_LINE() AS ErrorLine  
    ,ERROR_MESSAGE() AS ErrorMessage 
    ,@CrDateTime as 'Datetime'
	,@CreatedBy as LoginID

	
	  select 'Entry Failed' as 'Action'
	  			                          
    END CATCH;


END
Posted
Updated 10-Sep-20 21:44pm
v4
Comments
Gerry Schmitz 10-Sep-20 16:12pm
   
Don't "insert data in bulk" when other processes are updating the the database. A transaction involves a few records; not a "bulk".
Prashant Sharma 11-Sep-20 3:51am
   
sir can please help me any alternate way because i am taking data from a datagrid and than passing to a datatable, if i use loop than it will take much time.
Gerry Schmitz 11-Sep-20 14:57pm
   
That would require a knowledge of run times and operating schedules. A redesign would require a knowledge of the business rules.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900