This code is working but in many column values is zero while in excel file value is not zero...
so how i can insert corect value in sql from excel plz help me..
Thanks...
public void FindExcelData(string pathss, string sheetss)
{
chkProc = 0;
OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("" + pathss + "") + ";Extended Properties=Excel 8.0");
OleDbCommand ocmd = new OleDbCommand("select * from [" + sheetss + "$]", oconn);
oconn.Open();
OleDbDataReader odr = ocmd.ExecuteReader();
while (odr.Read())
{
SBPDCL_EnergyBillID = ScratchMaxid("NBPDCL_EnergyBill", "did");
CON_ACNO = valid(odr, 0);
CON_LOC = valid(odr, 1);
CON_NO = valid(odr, 2);
CAT_CODE = valid(odr, 3);
CAT = valid(odr, 4);
CAT_SUB = valid(odr, 5);
CAT_METER = valid(odr, 6);
FEDER_TYPE = valid(odr, 7);
LOAD_SAN = valid(odr, 8);
LOAD_CON = valid(odr, 9);
MD = valid(odr, 10);
MD_TAR = valid(odr, 11);
PR_TAR = valid(odr, 12);
NAME = valid(odr, 13);
F_H_NAME = valid(odr, 14);
ADDRESS = valid(odr, 15);
ADDRESS_1 = valid(odr, 16);
CON_STATUS = valid(odr, 17);
READ_DATE = valid(odr, 18);
LREAD_DATE = valid(odr, 19);
PAST_READ = valid(odr, 20);
PR_READ = valid(odr, 21);
UNIT_CON = valid(odr, 22);
MIN_UNIT = valid(odr, 23);
UNIT_CH = valid(odr, 24);
ADD_UNIT = valid(odr, 25);
TEMP_READ = valid(odr, 26);
MRP_READ = valid(odr, 27);
FREE_UNIT = valid(odr, 28);
FREE_ENG = valid(odr, 29);
AVG_READ = valid(odr, 30);
DATE_AVG = valid(odr, 31);
MC_MONTH = valid(odr, 32);
MC_UNIT = valid(odr, 33);
NO_BILLMON = valid(odr, 34);
NO_RENTMON = valid(odr, 35);
ENG_CHARGE = valid(odr, 36);
ENG_AC_CH = valid(odr, 37);
FIX_CHARGE = valid(odr, 38);
MTR_RENTCH = valid(odr, 39);
M_RENT_APP = valid(odr, 40);
DPS = valid(odr, 41);
FPFCA = valid(odr, 42);
FPPCA = valid(odr, 43);
CAP_CHARGE = valid(odr, 44);
MCB_CHARGE = valid(odr, 45);
MIS_CHARGE = valid(odr, 46);
ELEC_DUTY = valid(odr, 47);
PFS = valid(odr, 48);
OTHER_CH = valid(odr, 49);
TOT_ASST = valid(odr, 50);
GRAND_TOT = valid(odr, 51);
REBATE = valid(odr, 52);
REBATE_PF = valid(odr, 53);
NET_AMT = valid(odr, 54);
GRACE_AMT = valid(odr, 55);
LESS_CR = valid(odr, 56);
BL_BASIS = valid(odr, 57);
BILL_CYCLE = valid(odr, 58);
BILL_DATE = valid(odr, 59);
DUE_DATE = valid(odr, 60);
BILL_MONTH = valid(odr, 61);
GRACE_DATE = valid(odr, 62);
F_DATE = valid(odr, 63);
T_DATE = valid(odr, 64);
ARR_DUTY = valid(odr, 65);
ARR_ENG = valid(odr, 66);
ARR_DPS = valid(odr, 67);
ARR_PFS = valid(odr, 68);
ARR_DPSPFS = valid(odr, 69);
ARR_ABENG = valid(odr, 70);
ARR_ABDPS = valid(odr, 71);
CF_DUTY = valid(odr, 72);
CF_ENG = valid(odr, 73);
CF_DPS = valid(odr, 74);
CF_PFS = valid(odr, 75);
CF_DPSPFS = valid(odr, 76);
CF_ABENG = valid(odr, 77);
CF_ABDPS = valid(odr, 78);
RNS_ARR_M = valid(odr, 79);
RNS_ARR_U = valid(odr, 80);
RNS_ARR_AM = valid(odr, 81);
RNS_ARR_MS = valid(odr, 82);
RNS_ARR_US = valid(odr, 83);
RNS_ARR_AS = valid(odr, 84);
RNS_CF_M = valid(odr, 85);
RNS_CF_U = valid(odr, 86);
RNS_CF_AMT = valid(odr, 87);
RNS_CF_MS = valid(odr, 88);
RNS_CF_US = valid(odr, 89);
RNS_CF_AS = valid(odr, 90);
RNS_MRP_M = valid(odr, 91);
RNS_MRP_U = valid(odr, 92);
COLL_MODE = valid(odr, 93);
RECPT_CODE = valid(odr, 94);
RECEPT_NO = valid(odr, 95);
COLL_DATE = valid(odr, 96);
COLL_AMT = valid(odr, 97);
NON_PRECP = valid(odr, 98);
NON_PRECNO = valid(odr, 99);
NON_PAMT = valid(odr, 100);
NON_PDATE = valid(odr, 101);
RCDC_AMT = valid(odr, 102);
TYPE_RCDC = valid(odr, 103);
RCDC_DATE = valid(odr, 104);
R_REC_CODE = valid(odr, 105);
R_REC_NO = valid(odr, 106);
REBATE_ALL = valid(odr, 107);
NO_REC = valid(odr, 108);
NO_RECON = valid(odr, 109);
COLL_AMTON = valid(odr, 110);
TYPE_BILL = valid(odr, 111);
COLL_BODY = valid(odr, 112);
TR_ABEENG = valid(odr, 113);
TR_ABEDPS = valid(odr, 114);
ADJ_ENG = valid(odr, 115);
ADJ_DUTY = valid(odr, 116);
ADJ_DPS = valid(odr, 117);
ADJ_UNIT = valid(odr, 118);
BADJ_ENG = valid(odr, 119);
BADJ_DPS = valid(odr, 120);
BADJ_DUTY = valid(odr, 121);
CADJ_ENG = valid(odr, 122);
CADJ_DUTY = valid(odr, 123);
CADJ_DPS = valid(odr, 124);
TROD_ENG = valid(odr, 125);
TROD_DUTY = valid(odr, 126);
TROD_DPS = valid(odr, 127);
TROD_ABENG = valid(odr, 128);
TROD_ABDPS = valid(odr, 129);
REM_CORR = valid(odr, 130);
REM_METER = valid(odr, 131);
REM_FPPCA = valid(odr, 132);
LEG_STATUS = valid(odr, 133);
LEG_DATE = valid(odr, 134);
PAY_MODE = valid(odr, 135);
L_PAY = valid(odr, 136);
L_PAYDT = valid(odr, 137);
L_RECNO = valid(odr, 138);
P_CENGCH = valid(odr, 139);
P_CDUTY = valid(odr, 140);
P_CDPS = valid(odr, 141);
P_AENGCH = valid(odr, 142);
P_ADUTY = valid(odr, 143);
P_ADPS = valid(odr, 144);
P_NENG = valid(odr, 145);
P_NDUTY = valid(odr, 146);
P_NDPS = valid(odr, 147);
DOC = valid(odr, 148);
DIV = valid(odr, 149);
SUB_DIV = valid(odr, 150);
SECTION = valid(odr, 151);
METER_NO = valid(odr, 152);
METER_CAP = valid(odr, 153);
CAP_CH = valid(odr, 154);
MR_MF = valid(odr, 155);
STATUS_DT = valid(odr, 156);
SD_INST = valid(odr, 157);
ERR = valid(odr, 158);
PHASE = valid(odr, 159);
MRP_DATE = valid(odr, 160);
MTR_STAT = valid(odr, 161);
READ_STAT = valid(odr, 162);
CON_STAT = valid(odr, 163);
LINE_STAT = valid(odr, 164);
TR_NO = valid(odr, 165);
POLE_NO = valid(odr, 166);
ROUT_NO = valid(odr, 167);
LDB_MON = valid(odr, 168);
TBB_MON = valid(odr, 169);
LDR_MON = valid(odr, 170);
TBR_MON = valid(odr, 171);
MRPB_MON = valid(odr, 172);
SRCB_MON = valid(odr, 173);
CATC_MON = valid(odr, 174);
LOADC_MON = valid(odr, 175);
NAMEC_MON = valid(odr, 176);
NARR_MON = valid(odr, 177);
AREA_CODE = valid(odr, 178);
ID_AREA = valid(odr, 179);
ID_NO = valid(odr, 180);
FLAG_NSRC = valid(odr, 181);
FLAG_RCDC = valid(odr, 182);
FLAG_DC = valid(odr, 183);
FLAG_MRP = valid(odr, 184);
FLAG_BNP = valid(odr, 185);
FLAG_LNP = valid(odr, 186);
FLAG_BP = valid(odr, 187);
FLAG_LP = valid(odr, 188);
FEE_NAME = valid(odr, 189);
FEE_TYPE = valid(odr, 190);
FEE_CODE = valid(odr, 191);
INT_SD = valid(odr, 192);
TDS_SD = valid(odr, 193);
BOOK_NO = valid(odr, 194);
DUE_AREA = valid(odr, 195);
PSS = valid(odr, 196);
L1_UNIT = valid(odr, 197);
L2_UNIT = valid(odr, 198);
L3_UNIT = valid(odr, 199);
L4_UNIT = valid(odr, 200);
L1_MONTH = valid(odr, 201);
L2_MONTH = valid(odr, 202);
L3_MONTH = valid(odr, 203);
L4_MONTH = valid(odr, 204);
if (READ_DATE == "0")
{
READ_DATE = "";
}
if (LREAD_DATE == "0")
{
LREAD_DATE = "";
}
if (DATE_AVG == "0")
{
DATE_AVG = "";
}
if (BILL_DATE == "0")
{
BILL_DATE = "";
}
if (DUE_DATE == "0")
{
DUE_DATE = "";
}
if (BILL_MONTH == "0")
{
BILL_MONTH = "";
}
if (GRACE_DATE == "0")
{
GRACE_DATE = "";
}
if (F_DATE == "0")
{
F_DATE = "";
}
if (T_DATE == "0")
{
T_DATE = "";
}
if (COLL_DATE == "0")
{
COLL_DATE = "";
}
if (NON_PDATE == "0")
{
NON_PDATE = "";
}
if (RCDC_DATE == "0")
{
RCDC_DATE = "";
}
if (LEG_DATE == "0")
{
LEG_DATE = "";
}
if (L_PAYDT == "0")
{
L_PAYDT = "";
}
if (DOC == "0")
{
DOC = "";
}
if (STATUS_DT == "0")
{
STATUS_DT = "";
}
if (MRP_DATE == "0")
{
MRP_DATE = "";
}
query = "";
query += " insert into NBPDCL_EnergyBill ";
query += " ( ";
query += " did,CON_ACNO,CON_LOC,CON_NO,CAT_CODE,CAT,CAT_SUB,CAT_METER,FEDER_TYPE,LOAD_SAN,LOAD_CON,MD,MD_TAR,";
query += " PR_TAR,NAME,F_H_NAME,ADDRESS,ADDRESS_1,CON_STATUS,READ_DATE,LREAD_DATE,PAST_READ,PR_READ,";
query += " UNIT_CON,MIN_UNIT,UNIT_CH,ADD_UNIT,TEMP_READ,MRP_READ,FREE_UNIT,FREE_ENG,AVG_READ,DATE_AVG,";
query += " MC_MONTH,MC_UNIT,NO_BILLMON,NO_RENTMON,ENG_CHARGE,ENG_AC_CH,FIX_CHARGE,MTR_RENTCH,M_RENT_APP,";
query += " DPS,FPFCA,FPPCA,CAP_CHARGE,MCB_CHARGE,MIS_CHARGE,ELEC_DUTY,PFS,OTHER_CH,TOT_ASST,GRAND_TOT,";
query += " REBATE,REBATE_PF,NET_AMT,GRACE_AMT,LESS_CR,BL_BASIS,BILL_CYCLE,BILL_DATE,DUE_DATE,BILL_MONTH,";
query += " GRACE_DATE,F_DATE,T_DATE,ARR_DUTY,ARR_ENG,ARR_DPS,ARR_PFS,ARR_DPSPFS,ARR_ABENG,ARR_ABDPS,";
query += " CF_DUTY,CF_ENG,CF_DPS,CF_PFS,CF_DPSPFS,CF_ABENG,CF_ABDPS,RNS_ARR_M,RNS_ARR_U,RNS_ARR_AM,";
query += " RNS_ARR_MS,RNS_ARR_US,RNS_ARR_AS,RNS_CF_M,RNS_CF_U,RNS_CF_AMT,RNS_CF_MS,RNS_CF_US,RNS_CF_AS,";
query += " RNS_MRP_M,RNS_MRP_U,COLL_MODE,RECPT_CODE,RECEPT_NO,COLL_DATE,COLL_AMT,NON_PRECP,NON_PRECNO,";
query += " NON_PAMT,NON_PDATE,RCDC_AMT,TYPE_RCDC,RCDC_DATE,R_REC_CODE,R_REC_NO,REBATE_ALL,NO_REC,NO_RECON,";
query += " COLL_AMTON,TYPE_BILL,COLL_BODY,TR_ABEENG,TR_ABEDPS,ADJ_ENG,ADJ_DUTY,ADJ_DPS,ADJ_UNIT,BADJ_ENG,";
query += " BADJ_DPS,BADJ_DUTY,CADJ_ENG,CADJ_DUTY,CADJ_DPS,TROD_ENG,TROD_DUTY,TROD_DPS,TROD_ABENG,TROD_ABDPS,";
query += " REM_CORR,REM_METER,REM_FPPCA,LEG_STATUS,LEG_DATE,PAY_MODE,L_PAY,L_PAYDT,L_RECNO,P_CENGCH,P_CDUTY,";
query += " P_CDPS,P_AENGCH,P_ADUTY,P_ADPS,P_NENG,P_NDUTY,P_NDPS,DOC,DIV,SUB_DIV,SECTION,METER_NO,METER_CAP,";
query += " CAP_CH,MR_MF,STATUS_DT,SD_INST,ERR,PHASE,MRP_DATE,MTR_STAT,READ_STAT,CON_STAT,LINE_STAT,TR_NO,";
query += " POLE_NO,ROUT_NO,LDB_MON,TBB_MON,LDR_MON,TBR_MON,MRPB_MON,SRCB_MON,CATC_MON,LOADC_MON,NAMEC_MON,NARR_MON,AREA_CODE,";
query += " ID_AREA,ID_NO,FLAG_NSRC,FLAG_RCDC,FLAG_DC,FLAG_MRP,FLAG_BNP,FLAG_LNP,FLAG_BP,FLAG_LP,";
query += " FEE_NAME,FEE_TYPE,FEE_CODE,INT_SD,TDS_SD,BOOK_NO,DUE_AREA,PSS, L1_UNIT, L2_UNIT, L3_UNIT, L4_UNIT,L1_MONTH, L2_MONTH, L3_MONTH, L4_MONTH";
query += " ) ";
query += " values ";
query += " ( ";
query += " " + SBPDCL_EnergyBillID + ",'" + CON_ACNO + "','" + CON_LOC + "','" + CON_NO + "'," + CAT_CODE + ",'" + CAT + "','" + CAT_SUB + "','" + CAT_METER + "','" + FEDER_TYPE + "'," + LOAD_SAN + "," + LOAD_CON + "," + MD + ",'" + MD_TAR + "',";
query += " '" + PR_TAR + "','" + NAME + "','" + F_H_NAME + "','" + ADDRESS + "','" + ADDRESS_1 + "','" + CON_STATUS + "','" + READ_DATE + "','" + LREAD_DATE + "'," + PAST_READ + "," + PR_READ + ",";
query += " " + UNIT_CON + "," + MIN_UNIT + "," + UNIT_CH + "," + ADD_UNIT + "," + TEMP_READ + "," + MRP_READ + "," + FREE_UNIT + "," + FREE_ENG + "," + AVG_READ + ",'" + DATE_AVG + "',";
query += " " + MC_MONTH + "," + MC_UNIT + "," + NO_BILLMON + "," + NO_RENTMON + "," + ENG_CHARGE + "," + ENG_AC_CH + "," + FIX_CHARGE + "," + MTR_RENTCH + ",'" + M_RENT_APP + "',";
query += " " + DPS + "," + FPFCA + "," + FPPCA + "," + CAP_CHARGE + "," + MCB_CHARGE + "," + MIS_CHARGE + "," + ELEC_DUTY + "," + PFS + "," + OTHER_CH + "," + TOT_ASST + "," + GRAND_TOT + ",";
query += " " + REBATE + "," + REBATE_PF + "," + NET_AMT + "," + GRACE_AMT + "," + LESS_CR + ",'" + BL_BASIS + "'," + BILL_CYCLE + ",'" + BILL_DATE + "','" + DUE_DATE + "','" + BILL_MONTH + "',";
query += " '" + GRACE_DATE + "','" + F_DATE + "','" + T_DATE + "'," + ARR_DUTY + "," + ARR_ENG + "," + ARR_DPS + "," + ARR_PFS + "," + ARR_DPSPFS + "," + ARR_ABENG + "," + ARR_ABDPS + ",";
query += " " + CF_DUTY + "," + CF_ENG + "," + CF_DPS + "," + CF_PFS + "," + CF_DPSPFS + "," + CF_ABENG + "," + CF_ABDPS + "," + RNS_ARR_M + "," + RNS_ARR_U + "," + RNS_ARR_AM + ",";
query += " " + RNS_ARR_MS + "," + RNS_ARR_US + "," + RNS_ARR_AS + "," + RNS_CF_M + "," + RNS_CF_U + "," + RNS_CF_AMT + "," + RNS_CF_MS + "," + RNS_CF_US + "," + RNS_CF_AS + ",";
query += " " + RNS_MRP_M + "," + RNS_MRP_U + ",'" + COLL_MODE + "','" + RECPT_CODE + "'," + RECEPT_NO + ",'" + COLL_DATE + "'," + COLL_AMT + ",'" + NON_PRECP + "'," + NON_PRECNO + ",";
query += " " + NON_PAMT + ",'" + NON_PDATE + "'," + RCDC_AMT + "," + TYPE_RCDC + ",'" + RCDC_DATE + "','" + R_REC_CODE + "'," + R_REC_NO + "," + REBATE_ALL + "," + NO_REC + "," + NO_RECON + ",";
query += " " + COLL_AMTON + ",'" + TYPE_BILL + "','" + COLL_BODY + "'," + TR_ABEENG + "," + TR_ABEDPS + "," + ADJ_ENG + "," + ADJ_DUTY + "," + ADJ_DPS + "," + ADJ_UNIT + "," + BADJ_ENG + ",";
query += " " + BADJ_DPS + "," + BADJ_DUTY + "," + CADJ_ENG + "," + CADJ_DUTY + "," + CADJ_DPS + "," + TROD_ENG + "," + TROD_DUTY + "," + TROD_DPS + "," + TROD_ABENG + "," + TROD_ABDPS + ",";
query += " '" + REM_CORR + "','" + REM_METER + "','" + REM_FPPCA + "'," + LEG_STATUS + ",'" + LEG_DATE + "'," + PAY_MODE + "," + L_PAY + ",'" + L_PAYDT + "','" + L_RECNO + "'," + P_CENGCH + "," + P_CDUTY + ",";
query += " " + P_CDPS + "," + P_AENGCH + "," + P_ADUTY + "," + P_ADPS + "," + P_NENG + "," + P_NDUTY + "," + P_NDPS + ",'" + DOC + "','" + DIV + "','" + SUB_DIV + "','" + SECTION + "','" + METER_NO + "','" + METER_CAP + "',";
query += " '" + CAP_CH + "'," + MR_MF + ",'" + STATUS_DT + "'," + SD_INST + ",'" + ERR + "'," + PHASE + ",'" + MRP_DATE + "'," + MTR_STAT + "," + READ_STAT + "," + CON_STAT + "," + LINE_STAT + ",'" + TR_NO + "',";
query += " '" + POLE_NO + "','" + ROUT_NO + "','" + LDB_MON + "','" + TBB_MON + "','" + LDR_MON + "','" + TBR_MON + "','" + MRPB_MON + "','" + SRCB_MON + "','" + CATC_MON + "','" + LOADC_MON + "','" + NAMEC_MON + "','" + NARR_MON + "','" + AREA_CODE + "',";
query += " '" + ID_AREA + "'," + ID_NO + ",'" + FLAG_NSRC + "','" + FLAG_RCDC + "','" + FLAG_DC + "','" + FLAG_MRP + "','" + FLAG_BNP + "','" + FLAG_LNP + "','" + FLAG_BP + "','" + FLAG_LP + "',";
query += " '" + FEE_NAME + "','" + FEE_TYPE + "','" + FEE_CODE + "'," + INT_SD + "," + TDS_SD + ",'" + BOOK_NO + "','" + DUE_AREA + "','" + PSS + "'," + L1_UNIT + "," + L2_UNIT + "," + L3_UNIT + "," + L4_UNIT + ",'" + L1_MONTH + "','" + L2_MONTH + "','" + L3_MONTH + "','" + L4_MONTH + "'";
query += " ) ";
forTran(query);
}
oconn.Close();
if (chkProc == 1)
{
tran.Commit();
lblExcept.Text = "Process Completed";
txtTitle.Text = "";
txtSheetName.Text = "";
txtRemarks.Text = "";
txtTitle.Focus();
if (Convert.ToInt32(con.State) >= 1)
{
con.Close();
}
}
else
{
tram = 0;
throw new Exception("Error");
}
}
Submit an article or tip