DESIGN:
<div class="headh">
<h3 class="pageTitle">
<span id="Span1">Bank Upload</span>
</h3>
</div>
<asp:validationsummary id="ValidationSummary1" runat="server" showmessagebox="True"
showsummary="False" />
<table border="0" cellpadding="5" align="center" cellspacing="5" width="80%" style="height: 131px">
<tr class="pageText">
<td style="text-align: right">
<span style="color: red;">*</span>Category:
</td>
<td>
<asp:dropdownlist id="ddlCategory" runat="server">
<asp:ListItem>AXIS</asp:ListItem>
<asp:ListItem>INDUS</asp:ListItem>
<asp:ListItem>KVB</asp:ListItem>
<asp:ListItem></asp:ListItem>
</asp:dropdownlist>
<asp:requiredfieldvalidator initialvalue="0" id="RequiredFieldValidator1" runat="server"
controltovalidate="ddlCategory" display="None" errormessage="Please Select Category"></asp:requiredfieldvalidator>
</td>
<asp:label id="Label1" runat="server" text="Label"></asp:label>
</tr>
<tr class="pageText">
<td style="text-align: right">
<span style="color: red;">*</span>Upload:
</td>
<td>
<asp:fileupload id="FileUpload1" runat="server" onchange="ShowImagePreview(this);" />
</td>
</tr>
<tr class="pageText">
<td style="text-align: right">
</td>
<td>
<asp:button id="Submit" runat="server" text="Submit" onclick="Submit_Click1" />
</td>
</tr>
<tr class="pageText">
<td style="text-align: right">
</td>
<td>
<asp:button id="ButUpdate" runat="server" text="UPDATE" onclick="ButUpdate_Click" />
</td>
</tr>
</table>
SOURCE:
protected void ButUpdate_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{
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("TranDate"), new DataColumn("ValueDate"), new DataColumn("CHQNO")
, new DataColumn("TransactionParticulars"), new DataColumn("Amount"), new DataColumn("Typeamount"), new DataColumn("BranchName")});
using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
{
oda.Fill(dtExcelData);
}
excel_con.Close();
// Adding data to table by removing spaces.
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[7] {
new DataColumn("TranDate", typeof(DateTime)),
new DataColumn("ValueDate", typeof(DateTime)),
new DataColumn("CHQNO",typeof(string)),
new DataColumn("TransactionParticulars",typeof(string)),
new DataColumn("Amount",typeof(decimal)),
new DataColumn("Typeamount",typeof(string)),
new DataColumn("BranchName",typeof(string)),
});
for (int i = 0; i < dtExcelData.Rows.Count; i++)
{
dt.Rows.Add(
dtExcelData.Rows[i]["BankName"].ToString().Trim(),
dtExcelData.Rows[i]["Tran Date"],
dtExcelData.Rows[i]["Value Date"],
dtExcelData.Rows[i]["CHQNO"].ToString().Trim(),
dtExcelData.Rows[i]["Transaction Particulars"].ToString().Trim(),
dtExcelData.Rows[i]["Amount"],
dtExcelData.Rows[i]["Typeamount"].ToString().Trim(),
dtExcelData.Rows[i]["Branch Name"].ToString().Trim());
}
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("Update_Banktransactions"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
cmd.Parameters.AddWithValue("@tblBanktransactions", dt);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
}
}
DATABASE:
CREATE TABLE [dbo].[BankTransactions] AS TABLE(
[BankName] [nvarchar](100) NULL,
[TranDate] [date] NULL,
[ValueDate] [date] NULL,
[CHQNO] [nvarchar](100) NULL,
[Transactionparticulars] [nvarchar](200) NULL,
[Amount] [decimal](18, 0) NULL,
[Typeamount] [nvarchar](100) NULL,
[BranchName] [nvarchar](100) NULL
)
GO
CREATE TYPE [dbo].[BankTransactionType] AS TABLE(
[TranDate] [date] NULL,
[ValueDate] [date] NULL,
[CHQNO] [nvarchar](100) NULL,
[Transactionparticulars] [nvarchar](200) NULL,
[Amount] [decimal](18, 0) NULL,
[Typeamount] [nvarchar](100) NULL,
[BranchName] [nvarchar](100) NULL
)
GO
CREATE PROCEDURE [dbo].[Update_BankTransactions]
@tblBankTransactions BankTransactionType READONLY
AS
BEGIN
SET NOCOUNT ON;
MERGE INTO BankTransactions c1
USING @tblBankTransactions c2
ON c1.TranDate=c2.TranDate
WHEN MATCHED THEN
UPDATE SET
c1.ValueDate = c2.ValueDate,
c1.ChekNo = c2.CHQNO,
c1.Remark = c2.TransactionParticulars,
c1.Amount = c2.Amount,
c1.TypeofAmount = c2.Typeamount,
c1.BranchName = c2.BranchName
WHEN NOT MATCHED THEN
INSERT VALUES(c2.TranDate, c2.ValueDate, c2.CHQNO,c2.TransactionParticulars,c2.Amount,c2.Typeamount,c2.BranchName);
END
ERROR MESSAGE SHOW: THE TABLE COLUMN DID NOT MACHED
BUT ADD BANKNAME THE MESSAGE DIR NOT SHOW
BEFORE UPLOAD RESULT
14.10.2016 14.10.2016 123456 NEFT 2500 DR AXIS(KERALA) AXIS
14.10.2016 14.10.2016 123456 NEFT 2500 DR AXIS(KERALA) AXIS
14.10.2016 14.10.2016 123456 NEFT 2500 DR AXIS(KERALA) AXIS
14.10.2016 14.10.2016 123456 NEFT 2500 DR AXIS(KERALA) AXIS
AFTER UPLOAD RESULT
14.10.2016 14.10.2016 123456 NEFT 2500 DR AXIS(KERALA)
14.10.2016 14.10.2016 123456 NEFT 2500 DR AXIS(KERALA)
14.10.2016 14.10.2016 123456 NEFT 2500 DR AXIS(KERALA)
14.10.2016 14.10.2016 123456 NEFT 2500 DR AXIS(KERALA)
BANKNAME COLUMN NOT IN EXCEL SHEET
