Hello All
i my web page does not load , and on investigation i found that , the problem is fro the third proceedure , which recieves the datatable returned
the code DataTable dtable = GetFromFinacledb(); expects parameters but what do i pass into it
Thanks
Chinedu
// This code calls the proceedure.
protected void Button1_Click(object sender, EventArgs e)
{
IFormatProvider enGBDateFormat = new CultureInfo("en-GB").DateTimeFormat;
GetFromFinacledb(Convert.ToDateTime(startdate.Text, enGBDateFormat).ToString("dd/MMM/yyyy"), Convert.ToDateTime(enddate.Text, enGBDateFormat).ToString("dd/MMM/yyyy"));
// InsertIntoSQLTable();
lblstatus.Text = "Operation Successfull";
}
// This is the proceedure that searches the database based on date range from two textboxes and retutns a datatable dt
public DataTable GetFromFinacledb(string startdate, string enddate)
{
string Query1 = string.Empty;
DataTable dt = new DataTable();
DataSet ds = new DataSet();
try
{
Query1 += @"select a.foracid LOAN_ACCT_NUM, a.acct_name CUSTOMER_NAME, t.sol_desc, b.foracid REPAYMENT_ACCOUNT,g.gender SEX,v.address_line1 ADDRESS, z.email, z.phoneno TELEPHONE_NUMBER,l.dis_amt LOAN_AMOUNT, l.rep_perd_mths LOAN_TENOR, l.dis_shdl_date DISBURSEMENT_DATE, l.ei_perd_end_date EXPIRY_DATE,
(select nvl(eb.tran_date_bal,0) from tbaadm.eab eb
where eb.acid=a.acid and eb.eod_date <='" + startdate + @"' and end_eod_date >= '" + startdate + @"') OUTSTANDING_BALANCE,
(select nvl(sum(decode(part_tran_type,'D',tran_amt,-tran_amt)) ,0)
from tbaadm.htd m where m.acid=a.acid and tran_date between '";
Query1 += startdate + @"' and '" + enddate + "' and tran_particular like '%GINSR/INSFEE%'";
Query1 += @" and m.del_flg='N') Insurance_premium ,
(select nvl(sum(actual_amt_coll),0) ins_amount from tbaadm.cxl f where a.acid=f.TARGET_acid and chrg_tran_date between '&1' and '&2' and event_id='INSFDD' and F.del_flg='N' )INSURANCE_at_disbur
from tbaadm.gam a, tbaadm.lam l, tbaadm.gam b, tbaadm.sol t,crmuser.accounts g,(select Y.orgkey ,X.email,Y.phoneno
from(select orgkey,email from CRMUSER.PHONEEMAIL where email is not null and PREFERREDFLAG='Y') x,
(select orgkey,phoneno from CRMUSER.PHONEEMAIL where phoneno is not null and PREFERREDFLAG='Y') y
where x.orgkey(+) =y.orgkey) z,crmuser.address v
where a.acid=l.acid and l.op_acid=b.acid and a.sol_id=t.sol_id and a.cif_id=g.orgkey
and z.orgkey=a.cif_id and a.cif_id=v.orgkey
and a.schm_code in ('FT004','FT005','FT006')
and a.acct_cls_flg='N' and a.del_flg='N'
and addresscategory <>'Swift'
and PREFERREDADDRESS='Y'";
//Console.WriteLine("........Fetching details from finacle .. ");
//string Query1 = @"select foracid,acct_name,email,a.inst_num AS NUM,a.rcre_time AS TIME,status_ind AS STATUS" +
// " from tbaadm.ici a,tbaadm.gam b,CRMUSER.PHONEEMAIL c where a.acid=b.acid and b.cif_id=c.orgkey(+)" +
// " and preferredflag='Y' and phoneoremail = 'EMAIL'";
OracleConnection ocon = new OracleConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
ocon.Open();
OracleCommand ocmd = new OracleCommand(Query1, ocon);
OracleDataAdapter oda = new OracleDataAdapter(ocmd);
oda.Fill(ds);
dt = ds.Tables[0];
// Console.WriteLine(dt.Rows.Count.ToString());
ocon.Close();
}
catch (Exception ex)
{
ex.Message.ToString();
}
return dt;
}
This is the proceedure that recieves the datatable an maps it to an sqldatabase the error is from the firstline
DataTable dtable = GetFromFinacledb(); // what parameters do i pass into the proceedure GetFromFinacledb()
public void InsertIntoSQLTable()
{
// Merged NCR tables
try
{
DataTable dtable = GetFromFinacledb();
//con = new SqlConnection(C["SQLConnection"]);
// con = new SqlConnection(ConfigurationSettings[""].ToString());
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlconnect"].ConnectionString);
con.Open();
SqlBulkCopy bulk = new SqlBulkCopy(con);
bulk.DestinationTableName = "insurance_premium";
bulk.ColumnMappings.Add("LOAN_ACCT_NUM", "Loan_account_number");
bulk.ColumnMappings.Add("CUSTOMER_NAME", "CUSTOMER_NAME"); // Serial Numbe== Account Number
bulk.ColumnMappings.Add("SOL_DESC", "SOL_DESC");
bulk.ColumnMappings.Add("REPAYMENT_ACCOUNT", "Repayment_Account");
bulk.ColumnMappings.Add("SEX", "SEX");
bulk.ColumnMappings.Add("ADDRESS", "Address");
bulk.ColumnMappings.Add("EMAIL", "Email");
bulk.ColumnMappings.Add("TELEPHONE_NUMBER", "Phone");
bulk.ColumnMappings.Add("LOAN_AMOUNT", "Loan_Amount");
bulk.ColumnMappings.Add("LOAN_TENOR", "Loan_Tenor");
bulk.ColumnMappings.Add("DISBURSEMENT_DATE", "Disbursement_date");
bulk.ColumnMappings.Add("EXPIRY_DATE", "Expiry_Date");
bulk.ColumnMappings.Add("OUTSTANDING_BALANCE", "Balance");
bulk.ColumnMappings.Add("INSURANCE_PREMIUM", "Insurance_premium");
bulk.ColumnMappings.Add("INSURANCE_AT_DISBUR", "Insurance_at_disbursment");
}
catch (Exception ex)
{
throw ex;
}
}