public DataTable exceldataLife(string location) { try { CommonCLS.IBTBatches IBTBatch = new CommonCLS.IBTBatches(); String BatchNo = IBTBatch.GetBatchNo("BATCH_NO"); DateTime CurrentDate = DateTime.Now; DataTable ds = new DataTable(); OleDbCommand excelCommand = new OleDbCommand(); OleDbDataAdapter excelDataAdapter = new OleDbDataAdapter(); string excelConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + location + "';Extended Properties= 'Excel 8.0;HDR=Yes;IMEX=1'"; OleDbConnection excelConn = new OleDbConnection(excelConnStr); excelConn.Open(); DataTable dtPatterns = new DataTable(); //string usrName = "deshapriya.sooriyaTest";//Remove Please excelCommand = new OleDbCommand("SELECT `SERIAL`".ToString() + "as SERIAL,`TRANSACTION_CODE`".ToString() + " as TRANSACTION_CODE,`VOUCHER_DATE`".ToString() + " as VOUCHER_DATE,`PAYMENT_MODE`".ToString() + " as PAYMENT_MODE,`NARRATION`".ToString() + " as NARRATION,`POLICY_NUMBER`".ToString() + " as POLICY_NUMBER,`AMOUNT`".ToString() + " as AMOUNT,`POLICY_FEE`".ToString() + " as POLICY_FEE,`CASH_ACCOUNT`".ToString() + " as CASH_ACCOUNT,`POLICY_BRANCH`".ToString() + " as POLICY_BRANCH,`PAYING_PARTY`".ToString() + " as PAYING_PARTY,`INSTRUMENT_NUMBER`".ToString() + " as INSTRUMENT_NUMBER,`INSTRUMENT_DATE` ".ToString() + " as INSTRUMENT_DATE,`STATUS`".ToString() + " as STATUS,'" + usrName + "' as CREATEDBY,'" + CurrentDate + "' as CREATED_DATE, '" + BatchNo + "' as BATCH_NO, 'UPLOADED' as BATCH_STATUS, 'MANUAL' as BATCH_TYPE FROM [UPLOAD$]", excelConn); //excelCommand = new OleDbCommand("SELECT `SERIAL`".ToString() + "as SERIAL,`TRANSACTION_CODE`".ToString() + " as TRANSACTION_CODE,`VOUCHER_DATE`".ToString() + " as VOUCHER_DATE,`PAYMENT_MODE`".ToString() + " as PAYMENT_MODE,`NARRATION`".ToString() + " as NARRATION,`POLICY_NUMBER`".ToString() + " as POLICY_NUMBER,`AMOUNT`".ToString() + " as AMOUNT,`POLICY_FEE`".ToString() + " as POLICY_FEE,`CASH_ACCOUNT`".ToString() + " as CASH_ACCOUNT,`POLICY_BRANCH`".ToString() + " as POLICY_BRANCH,`PAYING_PARTY`".ToString() + " as PAYING_PARTY,`INSTRUMENT_NUMBER`".ToString() + " as INSTRUMENT_NUMBER,`INSTRUMENT_DATE` ".ToString() + " as INSTRUMENT_DATE,`STATUS`".ToString() + " as STATUS,'" + usrName + "' as CREATEDBY,'" + CurrentDate + "' as CREATED_DATE, '" + BatchNo + "' as BATCH_NO, 'UPLOADED' as BATCH_STATUS, 'MANUAL' as BATCH_TYPE FROM [UPLOAD$]", excelConn); excelDataAdapter.SelectCommand = excelCommand; excelDataAdapter.Fill(dtPatterns); return(dtPatterns); } catch (Exception) { throw; } }
public void UpdateMRPMCR(string Type)//----------------Update MRP MCR-------------------------------// { try { DataTable DtTemp = GetUnmatchedDetails(); String StrMRP = ""; String StrMCR = ""; ArrayList UnmatchedList = new ArrayList(); if (DtTemp.Rows.Count == 0) { return; } foreach (DataRow Dr in DtTemp.Rows) { if (Dr["DeptDesc"].ToString() == "MRP") { StrMRP = StrMRP + "'" + Dr["serial_NO"].ToString() + "',"; } if (Dr["DeptDesc"].ToString() == "MCR") { StrMCR = StrMCR + "'" + Dr["serial_NO"].ToString() + "',"; } } if (StrMCR != "") { StrMCR = StrMCR.Remove(StrMCR.Length - 1, 1); } else { StrMCR = "'xxxxxxxxxxxxxxxxx'"; } if (StrMRP != "") { StrMRP = StrMRP.Remove(StrMRP.Length - 1, 1); } else { StrMRP = "'xxxxxxxxxxxxxxxxx'"; } //------------------------------Get MRP/MCR Details--------------------------------- var dtMRP = new DataTable(); var dtMCR = new DataTable(); SqlConnection sqlconn = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlconnForMRP"].ConnectionString); SqlCommand sqlcmd = new SqlCommand(); sqlcmd.CommandType = System.Data.CommandType.Text; //sqlcmd.CommandText = " SELECT REPLACE(C.PropNo,'/','') AS PROPOSALNO,UPPER(C.Name) AS CLIENTNAME, " + // " C.Premiumpolicyfee AS PREMIUM,UPPER(SUBSTRING(C.Code,1,4)) AS HNBCODE, " + // " C.Ibtdate AS IBTDATE,UPPER(REPLACE(REPLACE(C.ACCOUNT,' ',''),'-','')) AS CASHACCOUNT," + // " 'MRP' AS PRODUCT,BAN.Branchname,C.BNKCode, 'Count' as Count FROM Chequedetail C,BANK BAN " + // " WHERE BAN.BNKCode = C.Code " + // " --AND Convert(datetime,C.ibtDate,103) >= '05/08/2016'" + // " --AND Convert(datetime,C.ibtDate,103) <= '11/08/2016' " + // " AND REPLACE(REPLACE(C.ACCOUNT,' ',''),'-','') IN (" + StrMRP + ") " + // " UNION " + // " SELECT REPLACE(C.PropNo,'/','') AS PROPOSALNO,UPPER(C.Name) AS CLIENTNAME," + // " C.Premiumpolicyfee AS PREMIUM,UPPER(SUBSTRING(C.Code,1,4)) AS HNBCODE, " + // " C.Ibtdate AS IBTDATE,UPPER(REPLACE(REPLACE(C.ACCOUNT,' ',''),'-','')) AS CASHACCOUNT, " + // " 'MCR' AS PRODUCT,BAN.Branchname,C.BNKCode, 'Count' as Count FROM ChequedetailMicro C,BANK BAN " + // " WHERE BAN.BNKCode = C.Code " + // " --AND Convert(datetime,C.ibtDate,103) >= '05/08/2016' " + // " --AND Convert(datetime,C.ibtDate,103) <= '11/08/2016' " + // " AND REPLACE(REPLACE(C.ACCOUNT,' ',''),'-','') IN (" + StrMCR + ") "; //25-09-2017 //sqlcmd.CommandText = " SELECT REPLACE(C.PropNo,'/','') AS PROPOSALNO,UPPER(C.Name) AS CLIENTNAME, " + // " C.Premium AS PREMIUM, C.Policyfee AS POLICYFEE, UPPER(SUBSTRING(C.Code,1,4)) AS HNBCODE, " + // " C.Ibtdate AS IBTDATE,C.ACCOUNT AS CASHACCOUNT," + // " 'MRP' AS PRODUCT,BAN.Branchname,C.BNKCode, 'Count' as Count FROM Chequedetail C,BANK BAN " + // " WHERE BAN.BNKCode = C.Code " + // " AND C.ACCOUNT IN (" + StrMRP + ") " + // " UNION " + // " SELECT REPLACE(C.PropNo,'/','') AS PROPOSALNO,UPPER(C.Name) AS CLIENTNAME," + // " C.Premium AS PREMIUM, C.Policyfee AS POLICYFEE, UPPER(SUBSTRING(C.Code,1,4)) AS HNBCODE, " + // " C.Ibtdate AS IBTDATE,C.ACCOUNT AS CASHACCOUNT, " + // " 'MCR' AS PRODUCT,BAN.Branchname,C.BNKCode, 'Count' as Count FROM ChequedetailMicro C,BANK BAN " + // " WHERE BAN.BNKCode = C.Code " + // " AND C.ACCOUNT IN (" + StrMCR + ") order by PRODUCT"; if (Type == "MRP") { sqlcmd.CommandText = " SELECT REPLACE(C.PropNo,'/','') AS PROPOSALNO,UPPER(C.Name) AS CLIENTNAME, " + " C.Premium AS PREMIUM, C.Policyfee AS POLICYFEE, UPPER(SUBSTRING(C.Code,1,4)) AS HNBCODE, " + " C.Ibtdate AS IBTDATE,C.ACCOUNT AS CASHACCOUNT," + " 'MRP' AS PRODUCT,BAN.Branchname,C.BNKCode, 'Count' as Count,Premiumpolicyfee FROM Chequedetail C,BANK BAN " + " WHERE BAN.BNKCode = C.Code " + " AND C.ACCOUNT IN (" + StrMRP + ") "; } if (Type == "MCR") { sqlcmd.CommandText = " SELECT REPLACE(C.PropNo,'/','') AS PROPOSALNO,UPPER(C.Name) AS CLIENTNAME," + " C.Premium AS PREMIUM, C.Policyfee AS POLICYFEE, UPPER(SUBSTRING(C.Code,1,4)) AS HNBCODE, " + " C.Ibtdate AS IBTDATE,C.ACCOUNT AS CASHACCOUNT, " + " 'MCR' AS PRODUCT,BAN.Branchname,C.BNKCode, 'Count' as Count,Premiumpolicyfee FROM ChequedetailMicro C,BANK BAN " + " WHERE BAN.BNKCode = C.Code " + " AND C.ACCOUNT IN (" + StrMCR + ") order by PRODUCT"; } sqlcmd.Connection = sqlconn; sqlconn.Open(); var dataReader = sqlcmd.ExecuteReader(); dtMRP.Load(dataReader); DataView dv = dtMRP.DefaultView; dv.Sort = "IBTDATE desc"; dtMRP = dv.ToTable(); sqlconn.Close(); sqlconn.Dispose(); dataReader.Close(); sqlcmd.Dispose(); DataTable DtRecCount = new DataTable(); DtRecCount.Columns.Add("SerialNo", typeof(string)); DtRecCount.Columns.Add("Count", typeof(int)); ArrayList arrRegular = new ArrayList(); ArrayList arrBulk = new ArrayList(); foreach (DataRow item in dtMRP.Rows) { String CASHACCOUNT = item["CASHACCOUNT"].ToString(); DataRow[] DrTemp1 = dtMRP.Select("CASHACCOUNT = '" + CASHACCOUNT + "'"); if (DrTemp1.Count() > 1) { if (!arrBulk.Contains(CASHACCOUNT)) { if (!(CASHACCOUNT == "" || CASHACCOUNT == " " || CASHACCOUNT == null)) { arrBulk.Add(CASHACCOUNT); } } } else { if (!(CASHACCOUNT == "" || CASHACCOUNT == " " || CASHACCOUNT == null)) { arrRegular.Add(CASHACCOUNT); } } } CommonCLS.IBTBatches IBTBatch = new CommonCLS.IBTBatches(); String BatchNo = IBTBatch.GetBatchNo("BATCH_NO"); //------------------------- MRP / MCR Receipts Updation--------------------- using (OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["ORAWF"].ToString())) { conn.Open(); OracleCommand command = conn.CreateCommand(); OracleTransaction transaction; transaction = conn.BeginTransaction(System.Data.IsolationLevel.Serializable); command.Transaction = transaction; foreach (var item in arrRegular) { String CASHACCOUNT = item.ToString(); DataRow Dr = dtMRP.Select("CASHACCOUNT = '" + CASHACCOUNT + "'").Single(); //-----------------------Regular Receipting------------------------------------- //OracleCommand cmd = new OracleCommand();//conn.CreateCommand(); //cmd.Connection = conn; //cmd.CommandText = "SP_FAS_UPDATE_MRPMCR_DTL"; //cmd.CommandType = CommandType.StoredProcedure; OracleCommand cmd = conn.CreateCommand(); cmd.CommandText = "SP_FAS_IBT_UPD_MRPMCR_DTL"; cmd.CommandType = CommandType.StoredProcedure; String SerialNo = Dr["CASHACCOUNT"].ToString(); String PolicyNo = Dr["PROPOSALNO"].ToString(); String Premium = Dr["PREMIUM"].ToString(); String PolicyFee = Dr["POLICYFEE"].ToString(); String MRPNarration = Dr["HNBCODE"].ToString() + '-' + Dr["CASHACCOUNT"].ToString() + ';' + PolicyNo + ':' + Dr["CLIENTNAME"].ToString(); String MRPPayingParty = Dr["BRANCHNAME"].ToString(); String Premiumpolicyfee = Dr["Premiumpolicyfee"].ToString(); cmd.Parameters.AddWithValue("vSerialNo", OracleType.VarChar).Value = SerialNo; cmd.Parameters.AddWithValue("vPolicyNo", OracleType.VarChar).Value = PolicyNo; cmd.Parameters.AddWithValue("vAmount", OracleType.Number).Value = Premium; cmd.Parameters.AddWithValue("vMRPNarration", OracleType.VarChar).Value = MRPNarration.Trim(); cmd.Parameters.AddWithValue("vMRPPayingParty", OracleType.VarChar).Value = "Hatton National Bank PLC-" + MRPPayingParty.Trim(); cmd.Parameters.AddWithValue("vMRPPolicyFee", OracleType.Number).Value = PolicyFee; cmd.Parameters.AddWithValue("vBatchNo", OracleType.Number).Value = BatchNo; //23-11-2017 cmd.Parameters.AddWithValue("vCreatedBy", OracleType.Number).Value = Type;// "MRP"; cmd.Parameters.AddWithValue("vPremiumpolicyfee", OracleType.Number).Value = Premiumpolicyfee; cmd.Parameters.Add("vResult", OracleType.VarChar, 100).Direction = ParameterDirection.Output; cmd.Transaction = transaction; cmd.ExecuteNonQuery(); string vResult = cmd.Parameters["vResult"].Value.ToString(); if (vResult != "Updated") { UnmatchedList.Add(vResult); } //UnmatchedList.Add(vResult); //------------------------------------------------------------------------------- } //------------------------Bulk Receipting---------------------------------------- Int16 SubNo = 1; foreach (var item in arrBulk) { String CASHACCOUNT = item.ToString(); DataRow[] Dr = dtMRP.Select("CASHACCOUNT = '" + CASHACCOUNT + "'"); DataTable dtTemp = Dr.CopyToDataTable(); //---------Validate MRP Divivion Entered Total With Statement Total-------- Double TempMRPTotal = 0; foreach (DataRow row in dtTemp.Rows) { TempMRPTotal = TempMRPTotal + (Convert.ToDouble(row["Premium"].ToString()) + Convert.ToDouble(row["POLICYFEE"].ToString())); } Boolean Temp = ValidateTotals(item.ToString(), TempMRPTotal); if (Temp != true) { UnmatchedList.Add(item.ToString()); continue; } //------------------------------------------------------------------------- Int16 Count = 1; foreach (DataRow row in dtTemp.Rows) { String SerialNo = row["cashaccount"].ToString(); String RefNumber = row["proposalno"].ToString(); String Amount = row["Premium"].ToString(); String PolicyFee = row["POLICYFEE"].ToString(); String MRPNarration = row["HNBCODE"].ToString() + '-' + row["CASHACCOUNT"].ToString() + ';' + RefNumber + ':' + row["CLIENTNAME"].ToString(); String MRPPayingParty = row["BRANCHNAME"].ToString(); OracleCommand cmdBulk = new OracleCommand();//conn.CreateCommand(); cmdBulk.Connection = conn; cmdBulk.CommandText = "SP_FAS_IBT_UPD_MRPMCR_DTL_BULK"; cmdBulk.CommandType = CommandType.StoredProcedure; //cmdBulk.Parameters.AddWithValue("vID", OracleType.Int32).Value = DtlID; cmdBulk.Parameters.AddWithValue("vSerialNo", OracleType.VarChar).Value = SerialNo; cmdBulk.Parameters.AddWithValue("vSubNo", OracleType.VarChar).Value = SubNo; cmdBulk.Parameters.AddWithValue("vRefNumber", OracleType.VarChar).Value = RefNumber; cmdBulk.Parameters.AddWithValue("vAmount", OracleType.Number).Value = Convert.ToDouble(Amount); cmdBulk.Parameters.AddWithValue("vCreatedBy", OracleType.Number).Value = Type;// "MRP"; cmdBulk.Parameters.AddWithValue("vCount", OracleType.Number).Value = Count; cmdBulk.Parameters.AddWithValue("vMRPNarration", OracleType.VarChar).Value = MRPNarration.Trim(); cmdBulk.Parameters.AddWithValue("vMRPPayingParty", OracleType.VarChar).Value = "Hatton National Bank PLC-" + MRPPayingParty.Trim(); cmdBulk.Parameters.AddWithValue("vMRPPolicyFee", OracleType.Number).Value = PolicyFee; cmdBulk.Parameters.AddWithValue("vBatchNo", OracleType.Number).Value = BatchNo; SubNo = Convert.ToInt16(SubNo + 1); cmdBulk.Transaction = transaction; cmdBulk.ExecuteNonQuery(); Count++; } SubNo = 1; } //------------------------------------------------------------------------------- transaction.Commit(); conn.Close(); //Emailling The Figure -- Unmatched List(Total check with uploaded record total if not tally reject) if (UnmatchedList.Count > 0) { CommonCLS.IBTEmails.EmailAlertCommon(9, UnmatchedList, ""); } } //---------------------------------------------------------------- } catch (Exception ex) { throw; } }
protected void cmdConfirm_Click(object sender, EventArgs e)//----------------------------------Records Confirm Click Event----------------------------------------// { //double dr_total = 0; if (page_result.Visible == true) { page_result.Visible = false; } get_LiveRecords_Total(); if (int.Parse(Session["TotalRecords_WhenPageLoad"].ToString()) == LiveRecord_Count) { //Check Totals------------- Double TempTot = 0; DataTable dtTemp = (DataTable)Session["TransList"]; if (dtTemp == null) { return; } foreach (DataRow item in dtTemp.Rows) { TempTot = TempTot + Convert.ToDouble(item["Amount"].ToString()); } //*****************************************-----New Fn-----**********************************// double real_total = double.Parse(txtTotal.Text); double calculation_differance = TempTot - real_total; if ((Math.Abs(calculation_differance) <= 0.1) || (calculation_differance == 0)) { if (calculation_differance > 0) { calculation_differance = (-1) * (calculation_differance); } else { calculation_differance = (1) * (calculation_differance); } } else { call_error_msg(false); lblResult.Text = "Calculation Differance Is Greater than 0.1 "; return; } //********************************************************************************************// CommonCLS.CommonFunctions clsCom = new CommonCLS.CommonFunctions(); DataTable Dt = (DataTable)Session["TransList"]; Int16 SubNo = 1; CommonCLS.IBTBatches IBTBatch = new CommonCLS.IBTBatches(); String BatchNo = IBTBatch.GetBatchNo("BATCH_NO"); using (OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["ORAWF"].ToString())) { conn.Open(); OracleCommand command = conn.CreateCommand(); OracleTransaction transaction; transaction = conn.BeginTransaction(System.Data.IsolationLevel.Serializable); // Assign transaction object for a pending local transaction command.Transaction = transaction; try { Int16 Count = 1; foreach (DataRow item in Dt.Rows) { if (item["Status"].ToString().Trim() != "New") { continue; } OracleCommand cmd = new OracleCommand();//conn.CreateCommand(); cmd.Connection = conn; cmd.CommandText = "sp_fas_ibt_BulkReceipt"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("vID", OracleType.Int32).Value = DtlID; cmd.Parameters.AddWithValue("vSubNo", OracleType.VarChar).Value = SubNo; cmd.Parameters.AddWithValue("vRefNumber", OracleType.VarChar).Value = item["Ref_no"].ToString(); //cmd.Parameters.AddWithValue("vAmount", OracleType.Number).Value = double.Parse(item["Amount"].ToString()); cmd.Parameters.AddWithValue("vAmount", OracleType.Number).Value = double.Parse(item["Amount"].ToString()) + (calculation_differance); calculation_differance = 0; cmd.Parameters.AddWithValue("vCreatedBy", OracleType.Number).Value = usrName; cmd.Parameters.AddWithValue("vCount", OracleType.Number).Value = Count; cmd.Parameters.AddWithValue("vBatchNo", OracleType.Number).Value = BatchNo; SubNo = Convert.ToInt16(SubNo + 1); cmd.Transaction = transaction; cmd.ExecuteNonQuery(); Count++; } arrRemoveListRV = (ArrayList)Session["arrRemoveListRV"]; if (arrRemoveListRV != null) { foreach (Object obj in arrRemoveListRV) { OracleCommand cmd = new OracleCommand();//conn.CreateCommand(); cmd.Connection = conn; cmd.CommandText = "sp_fas_ibt_bulk_rv_Remove"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("BulkRV_ID", OracleType.Int32).Value = Convert.ToInt32(obj); cmd.Transaction = transaction; cmd.ExecuteNonQuery(); } } transaction.Commit(); CommonCLS.CommonFunctions.Logger(System.Web.VirtualPathUtility.GetFileName(System.Web.HttpContext.Current.Request.Url.AbsolutePath), System.Reflection.MethodBase.GetCurrentMethod().Name, "----Bulk Receipt Confirmed!---- By: " + usrName + " Base Record ID :" + DtlID, Server.MapPath("~/IBTLogFiles/Log.txt")); conn.Close(); } catch (Exception ex) { CommonCLS.CommonFunctions.Logger(System.Web.VirtualPathUtility.GetFileName(System.Web.HttpContext.Current.Request.Url.AbsolutePath), System.Reflection.MethodBase.GetCurrentMethod().Name, ex.InnerException.ToString(), Server.MapPath("~/IBTLogFiles/Log.txt")); call_error_msg(false); lblResult.Text = ex.InnerException.ToString(); } } ClearControls(); running_count = 0; if (running_count == 0) { get_TotalRecords_WhenPageLoad(); running_count = running_count + 1;//New } call_error_msg(true); lblResult.Text = "Records Confirmed For Receipt...!"; } else { running_count = 0; call_error_msg(false); lblResult.Text = "Record Locked By Another User...! Please Refresh And Try Again!"; } }
private void ConfirmRecords(Boolean SelectAll)//-----------------------------------------------Confirm All Records------------------------------------------------// { try { //Int32 RecCount = GetUnreceiptedCount(); //if (RecCount > 499) //{ // call_error_msg(true); // lblResult.Text = "Please Run the relavant receipt batch and do the other confirmations...!"; // return; //} CommonCLS.IBTBatches IBTBatch = new CommonCLS.IBTBatches(); String BatchNo = IBTBatch.GetBatchNo("BATCH_NO"); if (GrdIBTConfirmation.Rows.Count > 0) { using (OracleConnection OraCon = new OracleConnection(ConfigurationManager.ConnectionStrings["ORAWF"].ToString())) { OraCon.Open(); OracleCommand command = OraCon.CreateCommand(); OracleTransaction transaction; // Start a local transaction transaction = OraCon.BeginTransaction(System.Data.IsolationLevel.ReadCommitted); // Assign transaction object for a pending local transaction command.Transaction = transaction; try { DataTable Dt = (DataTable)Session["ConfirmedData"]; //when user select all the records to be confirmed if (SelectAll) { Int16 Count = 0; foreach (DataRow item in Dt.Rows) { //if (Count == 500) //{ // call_error_msg(true); // lblResult.Text = "Please Run the relavant receipt batch and do the other confirmations...!"; // break; //} OracleCommand cmd = OraCon.CreateCommand(); cmd.CommandText = "SP_FAS_IBT_CONFIRMATION"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("vPolicyNo", OracleType.VarChar).Value = item["POLICY_NO"].ToString(); cmd.Parameters.Add("vProduct", OracleType.VarChar).Value = item["VALUE"].ToString(); cmd.Parameters.Add("vAmount", OracleType.Number).Value = item["CREDIT"].ToString(); cmd.Parameters.Add("vID", OracleType.Number).Value = Convert.ToInt64(item["ID"].ToString()); cmd.Parameters.Add("vCreatedBy", OracleType.VarChar).Value = usrName; cmd.Parameters.Add("vBatchNo", OracleType.VarChar).Value = BatchNo; cmd.Transaction = transaction; cmd.ExecuteNonQuery(); //***********************************************************************************************Test Count++; } } else { foreach (GridViewRow row in GrdIBTConfirmation.Rows)//Selected Records { CheckBox chk = (row.Cells[0].FindControl("chkboxselect") as CheckBox); DropDownList Commision = row.Cells[1].FindControl("ddlCommision") as DropDownList; if ((chk != null && chk.Checked) || SelectAll) { OracleCommand cmd = OraCon.CreateCommand(); cmd.CommandText = "SP_FAS_IBT_CONFIRMATION"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("vPolicyNo", OracleType.VarChar).Value = row.Cells[6].Text.ToString(); cmd.Parameters.Add("vProduct", OracleType.VarChar).Value = row.Cells[9].Text.ToString(); cmd.Parameters.Add("vAmount", OracleType.Number).Value = Convert.ToDouble(row.Cells[7].Text); cmd.Parameters.Add("vID", OracleType.Number).Value = Convert.ToInt64(row.Cells[1].Text); cmd.Parameters.Add("vCreatedBy", OracleType.VarChar).Value = usrName; cmd.Parameters.Add("vBatchNo", OracleType.VarChar).Value = BatchNo; cmd.Transaction = transaction; cmd.ExecuteNonQuery();//***********************************************************************************************Test //--------Send Data To TCS for Receipting--------- // Exess // Partial // When Two Debit Returns // When PPW Cancelled //------------------------------------------------ } } } transaction.Commit(); OraCon.Close(); GrdIBTConfirmation.DataSource = null; GrdIBTConfirmation.DataBind(); //--------Load New business and renewal system detected exeptions--------- LoadExeptionData(); //------------------------------------------------------------------------ call_error_msg(true); lblResult.Text = "Confirmation Successfull...!"; CommonCLS.CommonFunctions.Logger(System.Web.VirtualPathUtility.GetFileName(System.Web.HttpContext.Current.Request.Url.AbsolutePath), System.Reflection.MethodBase.GetCurrentMethod().Name, "----IBT Confirmation Successfull ---- Active User: "******"~/IBTLogFiles/Log.txt")); //LoadData(); } catch (Exception ex) { CommonCLS.CommonFunctions.Logger(System.Web.VirtualPathUtility.GetFileName(System.Web.HttpContext.Current.Request.Url.AbsolutePath), System.Reflection.MethodBase.GetCurrentMethod().Name, ex.InnerException.ToString(), Server.MapPath("~/IBTLogFiles/Log.txt")); call_error_msg(false); lblResult.Text = ex.InnerException.ToString(); } } } else { call_error_msg(false); lblResult.Text = "No Matching Records Available!"; } } catch (Exception) { throw; } }