C# - NullReferenceException while moving data from Excel to SQL Server -
i developing web application in have import data in sql server given excel files using c# , asp.net mvc. purpose followed this article. used exceldatareader read excel files. furthermore have used sqlbulkcopy in code insert data database. following code:
the create method
var bdata = getbilldata(); var connstring = configurationmanager.connectionstrings["wasabill"].connectionstring; datatable table = new datatable(); using (var reader = objectreader.create(bdata)) { table.load(reader); } using (sqlbulkcopy bcp = new sqlbulkcopy(connstring)) { bcp.columnmappings.add("accountno", "accountno"); bcp.columnmappings.add("billno", "billno"); bcp.columnmappings.add("category", "category"); bcp.columnmappings.add("billing_period", "billing_period"); bcp.columnmappings.add("name", "name"); bcp.columnmappings.add("address", "address"); bcp.columnmappings.add("issue_date", "issue_date"); bcp.columnmappings.add("due_date", "due_date"); bcp.columnmappings.add("water_bill", "water_bill"); bcp.columnmappings.add("sewerage_bill", "sewerage_bill"); bcp.columnmappings.add("aquifer_charges", "aquifer_charges"); bcp.columnmappings.add("current_amount", "current_amount"); bcp.columnmappings.add("arrears", "arrears"); bcp.columnmappings.add("service_charges", "service_charges"); bcp.columnmappings.add("payable_within_duedate", "payable_within_duedate"); bcp.columnmappings.add("surcharge", "surcharge"); bcp.columnmappings.add("payable_after_duedate", "payable_after_duedate"); bcp.columnmappings.add("payment_history_1", "payment_history_1"); bcp.columnmappings.add("paid_1", "paid_1"); bcp.columnmappings.add("payment_history_2", "payment_history_2"); bcp.columnmappings.add("paid_2", "paid_2"); bcp.columnmappings.add("payment_history_3", "payment_history_3"); bcp.columnmappings.add("paid_3", "paid_3"); bcp.columnmappings.add("area", "area"); bcp.columnmappings.add("water_rate", "water_rate"); bcp.columnmappings.add("sewerage_rate", "sewerage_rate"); bcp.columnmappings.add("discharge_basis", "discharge_basis"); bcp.columnmappings.add("pump_size", "pump_size"); bcp.columnmappings.add("ferrule_size", "ferrule_size"); bcp.columnmappings.add("meter_type", "meter_type"); bcp.columnmappings.add("meter_status", "meter_status"); bcp.columnmappings.add("last_readin", "last_readin"); bcp.columnmappings.add("current_reading", "current_reading"); bcp.columnmappings.add("water_aquiffer_charges", "water_aquiffer_charges"); bcp.destinationtablename = "wasa_bill_detail"; bcp.writetoserver(table); } var rowcount = table.rows.count; //number of rows in data table //if (modelstate.isvalid) //{ // db.wasa_bill_detail.add(wasa_bill_detail); // db.savechanges(); // return redirecttoaction("index"); //} tempdata["rowcount"] = rowcount; return redirecttoaction("index"); the method reads excel file , returns data list
public ienumerable<wasa_bill_detail> getbilldata() { list<wasa_bill_detail> billdetailelist = new list<wasa_bill_detail>(); //string path = @tempdata["filepath"].tostring();//@"e:\w317.xlsx"; string path = tempdata["filepath"].tostring(); string excelpath = server.mappath(path); if(path!=null) { var exceldata = new exceldata(excelpath); var billrecords = exceldata.getdata("sheet1"); foreach (var row in billrecords) { var billdetail = new wasa_bill_detail() { accountno = row["account#"].tostring(), billno = row["billno"].tostring(), category = row["category"].tostring(), billing_period = row["billing_period"].tostring(), name = row["name"].tostring(), address = row["address"].tostring(), issue_date = row["issue_date"].tostring(), due_date = row["due_date"].tostring(), water_bill = row["water_bill"].tostring(), sewerage_bill = row["sewerage bill"].tostring(), aquifer_charges = row["aquifer"].tostring(), current_amount = row["current amount"].tostring(), arrears = row["arrears"].tostring(), service_charges = row["service charges"].tostring(), payable_within_duedate = row["payable within duedate"].tostring(), surcharge = row["surcharge"].tostring(), payable_after_duedate = row["after due date"].tostring(), payment_history_1 = row["pay history 1"].tostring(), paid_1 = row["paid 1"].tostring(), payment_history_2 = row["pay hisotry 2"].tostring(), paid_2 = row["paid 2"].tostring(), payment_history_3 = row["pay hisotry 3"].tostring(), paid_3 = row["paid 3"].tostring(), area = row["area"].tostring(), water_rate = row["water rate"].tostring(), sewerage_rate = row["sewer rate"].tostring(), discharge_basis = row["dischage"].tostring(), pump_size = row["pump size"].tostring(), ferrule_size = row["ferrule size"].tostring(), meter_type = row["meter type"].tostring(), meter_status = row["meter status"].tostring(), last_readin = row["last reading"].tostring(), current_reading = row["current reading"].tostring(), water_aquiffer_charges = row["aquifer charges"].tostring(), }; billdetailelist.add(billdetail); } } return billdetailelist; } everything working fine on development machine. file uploaded , inserted database using bcp.
but when publish hosting server nullreferenceexception occurred @
wasaweb.controllers.admincontrollers.wasa_bill_detailcontroller.getbilldata() +128 i not understand working 100% fine in development machine. have checked file uploaded server.
any this?
you can use this:
private string getstringvalue(object obj) { string str = null; if(obj != null) str = obj.tostring().trim(); return str; } call
...... accountno = getstringvalue(row["account#"]) ......