EXCEl :
1.Tran Date
2.VAlueDate
3.CHQNO
4.Transaction Particulars
5.Amount
6.DR|CR
7.Branch Name
SQL:
1.Tran Date
2.VAlueDate
3.CHQNO
4.Transaction Particulars
5.Amount
6.DR|CR
7.Branch Name
8.BankName
DESIGN:
<div>
<table align="center">
<tr>
<td>
BANKNAME
</td>
<td>
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
UPLOAD
</td>
<td>
<asp:FileUpload ID="FileUpload1" runat="server" />
</td>
</tr>
<tr>
<td>
</td>
<td>
</td>
</tr>
<tr>
<td>
</td>
<td>
<asp:Button ID="upload" runat="server" Text="SUBMIT" OnClick="upload_Click" />
</td>
</tr>
</table>
</div>
SOURCE:
protected void upload_Click(object sender, EventArgs e)
{
//Upload and save the file
string excelPath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
FileUpload1.SaveAs(excelPath);
string conString = string.Empty;
string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
switch (extension)
{
case ".xls": //Excel 97-03
conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx": //Excel 07 or higher
conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
break;
}
conString = string.Format(conString, excelPath);
using (OleDbConnection excel_con = new OleDbConnection(conString))
{
excel_con.Open();
string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
DataTable dtExcelData = new DataTable();
//[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.
dtExcelData.Columns.AddRange(new DataColumn[7] { new DataColumn("Tran Date", typeof(DateTime)),
new DataColumn("Value Date", typeof(DateTime)),
new DataColumn("CHQNO",typeof(string)),
new DataColumn("Transaction Particulars",typeof(string)),
new DataColumn("Amount",typeof(string)),
new DataColumn("DR|CR",typeof(string)),
new DataColumn("Branch Name",typeof(string)),
});
using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
{
oda.Fill(dtExcelData);
}
excel_con.Close();
string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(consString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
//Set the database table name
sqlBulkCopy.DestinationTableName = "dbo.tbl_BankTransaction";
//[OPTIONAL]: Map the Excel columns with that of the database table
sqlBulkCopy.ColumnMappings.Add("Tran Date", "TranDate");
sqlBulkCopy.ColumnMappings.Add("Value Date", "ValueDate");
sqlBulkCopy.ColumnMappings.Add("CHQNO", "ChekNo");
sqlBulkCopy.ColumnMappings.Add("Transaction Particulars", "Remark");
sqlBulkCopy.ColumnMappings.Add("Amount", "Amount");
sqlBulkCopy.ColumnMappings.Add("DR|CR", "Type");
sqlBulkCopy.ColumnMappings.Add("Branch Name", "BranchName");
con.Open();
SqlCommand cmd = new SqlCommand("insert into tbl_BankTransaction (BankName) values ('" + TextBox1.Text + "')", con);
cmd.ExecuteNonQuery();
sqlBulkCopy.WriteToServer(dtExcelData);
con.Close();
}
}
}
}
ACTUAL RESULT:
14.10.2016 14.10.2016 12354 NEFT 2500 DR AXIS(KERALA) NULL
14.10.2016 14.10.2016 12354 NEFT 2500 DR AXIS(KERALA) NULL
14.10.2016 14.10.2016 12354 NEFT 2500 DR AXIS(KERALA) NULL
14.10.2016 14.10.2016 12354 NEFT 2500 DR AXIS(KERALA) NULL
NULL NULL NULL NULL NULL NULL NULL AXIS
EXCEPTING RESULT:
14.10.2016 14.10.2016 12354 NEFT 2500 DR AXIS(KERALA) AXIS
14.10.2016 14.10.2016 12354 NEFT 2500 DR AXIS(KERALA) AXIS
14.10.2016 14.10.2016 12354 NEFT 2500 DR AXIS(KERALA) AXIS
14.10.2016 14.10.2016 12354 NEFT 2500 DR AXIS(KERALA) AXIS
I WANT TO ABOVE RESULT , WHAT TO DO HELP SIR ...