SQL Server Error: Divide by zero error encountered.The statement has been terminated

Last Reply 2 months ago By dharmendr

Posted 2 months ago

How to insert data into the database?

Controller.cs :

        [HttpPost]
        [ActionName("AddInvoiceDetails")]
        public void insertInvoiceDetails(InvoiceUnchecked invdtls)
        {
            SqlConnection myConnection = new SqlConnection();
            myConnection.ConnectionString = ConfigurationManager.ConnectionStrings["ERPConnectionString"].ConnectionString;
            SqlCommand sqlCmd = new SqlCommand();
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "INSERT_WAREHOUSE";
            sqlCmd.Connection = myConnection;
            sqlCmd.Parameters.AddWithValue("@InvoiceId", invdtls.InvoiceId);
            myConnection.Open();
            int rowInserted = sqlCmd.ExecuteNonQuery();            
            myConnection.Close();          
        }

Sp :

USE [ERPT]
GO
/****** Object:  StoredProcedure [dbo].[INSERT_WAREHOUSE]    Script Date: 6/19/2019 12:43:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--exec INSERT_WAREHOUSE @invoiceid = 33
ALTER PROCEDURE [dbo].[INSERT_WAREHOUSE]
        --  @receiptdate datetime=null     
        @invoiceid int=null
	,@createdby varchar(50)=null                            
AS
BEGIN

declare @receiptdate datetime

set  @receiptdate = getdate()

DECLARE 
	@WMID INT =NULL
	,@invoiceno varchar(50)=null  
	,@dispatchid int=null
	,@partid int=null
        ,@customerid int=null
        ,@invoicevalue decimal(18,2)=null
        ,@unitvalue decimal(18,2)=null
        ,@invoiceqty int=null     
        ,@noofpallets int=null  
	,@invoicedate datetime=null  

DECLARE @STARTDATE DATETIME,@ENDDATE DATETIME

SELECT @STARTDATE = DATEADD(MONTH,-7,STARTDATE),@ENDDATE = ENDDATE FROM FINANCIALYEAR where getdate() between startdate and enddate
SELECT @CUSTOMERID=customerid,@invoiceno=invoiceno,@invoicedate=createddate FROM DISPATCH WHERE INVOICEID = @invoiceid 

SELECT @WMID = wmid FROM WAREHOUSE_CUSTOMER WHERE customerid = @customerid

INSERT INTO warehouse
SELECT
    @WMID as 'wmid',
	DD.DISPATCHID, 
	@invoiceid 'invid',
	@invoiceno 'invno',
	DD.PARTID,
	DD.CUSTOMERID,
        --DC.INRAMOUNT,    
	EncryptByPassPhrase('ENCAMT', cast(DC.INRAMOUNT as varchar(15))) 'InrAmt',
	EncryptByPassPhrase('ENCUNITAMT', cast(CAST((Dc.INRAMOUNT/DD.NUMBERS) AS DECIMAL(18,2)) as varchar(15))) 'UnitAmt',
        --CAST((Dc.INRAMOUNT/DD.NUMBERS) AS DECIMAL(18,2)) 'UnitAmt',       
    DD.NUMBERS,      
    0, -- issue qty      
    DD.NUMBERS, -- stock    
    @invoicedate,
    @receiptdate 'Receiptdate', -- receiptdate 
    getdate() 'createddate',       
    @createdby 'createdby',       
    DD.NUMBERS/PALLETQTY 'NoofPallet', 
	'',
	'',
	'',
	'',
	'',   
    3 -- Ware house  'Status'		
 FROM DISPATCH_DAILY DD
 INNER JOIN DISPATCH_CURRENCY DC ON DC.DISPATCHID = DD.DISPATCHID
 INNER JOIN PART ON PART.PARTID = DD.PARTID 
 LEFT  JOIN part_pallet_master PPM ON PPM.PARTID = DD.PARTID AND PPM.CUSTOMERID = DD.CUSTOMERID
 LEFT  JOIN DISPATCH ON DISPATCH.INVOICEno = DD.INVOICEno AND DISPATCH.CREATEDDATE >= @STARTDATE
 WHERE DD.INVOICENO = @INVOICENO AND DISPATCHTIME >= @STARTDATE AND DISPATCHTIME <= @ENDDATE
 
 
 --SET IDENTITY_INSERT warehouse_details ON
 
 --SELECT * FROM WAREHOUSE_ENTRYTYPE
 INSERT INTO warehouse_details
            (
       [wmid]
      ,[receivedfromid]
      ,[issuedtoid]
      ,[customerid]
      ,[dispatchid]
      ,[partid]
      ,[invoiceid]
      ,[entrytypeid]
      ,[qty]
      ,[value]
      ,[noofpallets]
      ,[packinglistno]
      ,[issueddate]
      ,[createddate]
      ,[createdby]
      ,[c1]
      ,[c2]
      ,[c3]
      ,[c4]
			)
 SELECT
		@WMID as 'wmid',
		'' as 'receitedfromid',
		'' as 'issuedtoid',
		DD.CUSTOMERID,
		DD.DISPATCHID,
		DD.PARTID,
		@invoiceid 'invoiceid',
		'1' 'Entrytype',
		DD.NUMBERS 'Qty',
		0 as value,
		DD.NUMBERS/PALLETQTY 'NoofPallet',
		'' 'packinglistno',
		'' 'issueddate',
		getdate() 'createddate',
		@createdby 'createdby',
		'' 'c1',
		'' 'c2',
		'' 'c3',
		'' 'c4'
  FROM DISPATCH_DAILY DD
 INNER JOIN DISPATCH_CURRENCY DC ON DC.DISPATCHID = DD.DISPATCHID
 INNER JOIN PART ON PART.PARTID = DD.PARTID 
 LEFT  JOIN part_pallet_master PPM ON PPM.PARTID = DD.PARTID AND PPM.CUSTOMERID = DD.CUSTOMERID
 LEFT  JOIN DISPATCH ON DISPATCH.INVOICEno = DD.INVOICEno AND DISPATCH.CREATEDDATE >= @STARTDATE
 WHERE DD.INVOICENO = @INVOICENO AND DISPATCHTIME >= @STARTDATE AND DISPATCHTIME <= @ENDDATE


 UPDATE DISPATCH SET STATUS = 3 where invoiceid = @invoiceid

 --SELECT * FROM WAREHOUSE_INVOICE_STATUS
      
END

Here I have an issue the data is not getting inserted.

When I execute the stored procedure, I get an error.

exec INSERT_WAREHOUSE @invoiceid = 33

Msg 8134, Level 16, State 1, Procedure INSERT_WAREHOUSE, Line 32 [Batch Start Line 7]Divide by zero error encountered.The statement has been terminated.Msg 8134, Level 16, State 1, Procedure INSERT_WAREHOUSE, Line 69 [Batch Start Line 7]Divide by zero error encountered.The statement has been terminated. (1 row affected)

You are viewing reply posted by: dharmendr 2 months ago.