Reads the excel sheet data but unable to insert in database
Below is my code which is used to read the excel sheet the problem is all the columns are of type string in the below code but in my database i have 4 columns of type decimal because of this i am able to readthe excel sheet but i am unable to insert the data in database which gives me this error **The given value of type String from the data source cannot be converted to type decimal of the specified target column.** in actual cs page.how can i insert the data in databaseCode for reading the excel sheet data-
public DataTable Read(string strPath, int isheet)//,DataRow[] dr1
{
dt.Columns.Add("Month", typeof(string));
dt.Columns.Add("ID", typeof(string));
dt.Columns.Add("Client Name", typeof(string));
dt.Columns.Add("Charges", typeof(string));
dt.Columns.Add("Payment", typeof(string));
dt.Columns.Add("Adjustment", typeof(string));
dt.Columns.Add("W/O", typeof(string));
string ID = "";
string url = _urlConverter.getFileURLFromSystemPath(strPath, strPath);
PropertyValue[] loadProps = new PropertyValue[0];
XComponent xComponent = _componentLoader.loadComponentFromURL(url, "_blank", 0, loadProps);
XSpreadsheets oSheets = ((XSpreadsheetDocument)xComponent).getSheets();
XIndexAccess oSheetsIA = (XIndexAccess)oSheets;
XSpreadsheet oSheet = (XSpreadsheet)oSheetsIA.getByIndex(0).Value;
try
{
int iRow = 1;
int iEmpty = 0;
bool boolcontinue = true;
while (boolcontinue)
{
DataRow drNew = dt.NewRow();
ID = ((XText)oSheet.getCellByPosition(0, iRow)).getString();
if (!string.IsNullOrEmpty(ID))
{
iEmpty = 0;
int i = 0;
for (int iCol = 0; iCol < 7; iCol++)
{
drNew[i] = ((XText)oSheet.getCellByPosition(iCol, iRow)).getString();
i++;
}
dt.Rows.Add(drNew);
iRow++;
}
else
{
iEmpty++;
}
if (iEmpty > 10)
{
break;
}
}
}
catch (System.Exception ex)
{
}
finally
{
oSheet = null;
oSheetsIA = null;
oSheet = null;
xComponent.dispose();
localContext = null;
}
return dt;
}
Code in .cs page which inserts the data in database after reading the excel sheet data-
protected void Upload_Click(object sender, EventArgs e)
{
string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
FileUpload1.PostedFile.SaveAs(Server.MapPath("~/Files/" + fileName));
string fullpath = Path.GetFullPath(Server.MapPath("~/Files/" + fileName));
ReadExcelSheet obj = new ReadExcelSheet();
//send filepath,sheet number,selected rows to class file
DataTable dt = obj.Read(fullpath, 1);
bool Ismatch = false;
for (int i = 0; i < dt.Rows.Count; i++)
{
string date = dt.Rows[i]["Month"].ToString();
string[] date1 = date.Split('/');
string month = date1[0];
string year = date1[2];
if (month == txtmonth.Text && year == txtyear.Text)
{
Ismatch = true;
//break;
}
else
{
Ismatch = false;
break;
}
}
if (Ismatch == true)
{
lblmessage.Text = "Valid document";
}
else
{
lblmessage.Text = "Not a Valid document";
Label1.Text = "";
return;
}
//checking the input month and year records exists or not in DB
SqlCommand cmd = new SqlCommand("select Uploaded from TestMCount1 ", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable DBdt = new DataTable();
da.Fill(DBdt);
if (DBdt.Rows.Count > 0)
{
int month1;
bool validMonth = int.TryParse(txtmonth.Text, out month1);
int year1;
bool validYear = int.TryParse(txtyear.Text, out year1);
var filteredRows = from row in DBdt.AsEnumerable()
let date = row.Field<System.DateTime>("date")
where date.Month == month1 && date.Year == year1
select row;
DataRow[] dr = filteredRows.ToArray();
DataTable selectedrows = filteredRows.CopyToDataTable();
if (selectedrows.Rows.Count > 0)
{
for (int i = 0; i <= selectedrows.Rows.Count - 1; i++)
{
string date2 = selectedrows.Rows[i]["Month"].ToString();
con.Open();
SqlCommand cmd1 = new SqlCommand("delete from TestMCount1 where Uploaded='" + date2 + "'", con);
SqlDataAdapter da1 = new SqlDataAdapter(cmd1);
cmd1.ExecuteNonQuery();
con.Close();
}
SqlBulkCopy objbulk = new SqlBulkCopy(con);
objbulk.DestinationTableName = "TestMCount1";
//mapping the columns
objbulk.ColumnMappings.Add("ID", "MID");
objbulk.ColumnMappings.Add("Month", "Uploaded");
objbulk.ColumnMappings.Add("Client Name", "ClientName");
objbulk.ColumnMappings.Add("Charges", "Charge");
objbulk.ColumnMappings.Add("Payment", "Payment");
objbulk.ColumnMappings.Add("Adjustment", "Adjustment");
objbulk.ColumnMappings.Add("W/O", "WO");
con.Open();
objbulk.WriteToServer(dt);
con.Close();
Label1.Text = "Record Uploaded";
}
else
{
SqlBulkCopy objbulk = new SqlBulkCopy(con);
objbulk.DestinationTableName = "TestMCount1";
//mapping the columns
objbulk.ColumnMappings.Add("ID", "MID");
objbulk.ColumnMappings.Add("Month", "Uploaded");
objbulk.ColumnMappings.Add("Client Name", "ClientName");
objbulk.ColumnMappings.Add("Charges", "Charge");
objbulk.ColumnMappings.Add("Payment", "Payment");
objbulk.ColumnMappings.Add("Adjustment", "Adjustment");
objbulk.ColumnMappings.Add("W/O", "WO");
con.Open();
objbulk.WriteToServer(dt);
con.Close();
Label1.Text = "Record Uploaded";
}
}
else
{
SqlBulkCopy objbulk = new SqlBulkCopy(con);
objbulk.DestinationTableName = "TestMCount1";
//mapping the columns
objbulk.ColumnMappings.Add("Month", "Uploaded");
objbulk.ColumnMappings.Add("ID", "MID");
objbulk.ColumnMappings.Add("Client Name", "ClientName");
objbulk.ColumnMappings.Add("Charges", "Charge");
objbulk.ColumnMappings.Add("Payment", "Payment");
objbulk.ColumnMappings.Add("Adjustment", "Adjustment");
objbulk.ColumnMappings.Add("W/O", "WO");
con.Open();
objbulk.WriteToServer(dt);
con.Close();
Label1.Text = "Record Uploaded";
}
}
SQL Table
ID int
Uploaded datetime
MID varchar(500)
ClientName varchar(500)
Charge decimal(19, 4)
Payment decimal(19, 4)
Adjustment decimal(19, 4)
WO decimal(19, 4)
Comment varchar(500)
createdDate datetime