ERROR MESSAGE:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.
DESIGN:
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="INSERTEXCEL._Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<div style="min-height: 100px;">
<table style="width: 98%; margin: 0px auto;">
<tbody>
<tr>
<td>
<b class="b1h"></b><b class="b2h"></b><b class="b3h"></b><b class="b4h"></b>
<div class="headh">
<h3 style="text-align: center;">
<span id="lblCaption">Bank Transaction</span>
</h3>
</div>
<div class="contenth">
<br />
<table align="center" border="0" width="100%" cellpadding="4" cellspacing="0">
<tbody>
<tr>
<td colspan="7">
<asp:ValidationSummary ID="ValidationSummary1" runat="server" ShowMessageBox="True"
ShowSummary="False" />
</td>
</tr>
<tr>
<td>
<asp:Label ID="Label1" Font-Bold="true" runat="server" Text="Bank Name" Width="150px"></asp:Label>
</td>
<td>
:
</td>
<td>
<asp:DropDownList ID="ddlbank" runat="server">
<asp:ListItem Value="0">--Select--</asp:ListItem>
<asp:ListItem Value="1">Axis Bank</asp:ListItem>
<asp:ListItem Value="2">ICICI Bank</asp:ListItem>
<asp:ListItem Value="3">SBI</asp:ListItem>
<asp:ListItem Value="4">KVB</asp:ListItem>
</asp:DropDownList>
<asp:RequiredFieldValidator ID="RequiredFieldValidator4" runat="server" ControlToValidate="ddlbank"
Display="None" InitialValue="0" ErrorMessage="Please select Bank Name"></asp:RequiredFieldValidator>
</td>
<td>
<asp:FileUpload ID="FileUpload1" runat="server" /><br />
<asp:Button ID="btnUpload" runat="server" OnClick="btnUpload_Click" Text="Upload" />
</td>
</tr>
</tbody>
</table>
</div>
<b class="b4bh"></b><b class="b3bh"></b><b class="b2bh"></b><b class="b1h"></b>
</td>
</tr>
</tbody>
</table>
</div>
<div style="min-height: 500px;">
<table style="width: 98%; margin: 0px auto;">
<tbody>
<tr>
<td>
<b class="b1h"></b><b class="b2h"></b><b class="b3h"></b><b class="b4h"></b>
<div class="headh">
<h3 style="text-align: center;">
<span id="Span1">Transaction List</span>
</h3>
</div>
<div class="contenth">
<br />
<asp:Label ID="lblErr" runat="server"></asp:Label>
<div id="div1">
<div>
<div>
<table cellspacing="0" cellpadding="4" rules="all" class="ViewTable" border="1" id="Table1"
style="background-color: WhiteSmoke; width: 98%; border-collapse: collapse;">
<tbody>
<tr>
<td align="center">
<asp:GridView border="1" ID="grdTrans" runat="server" AllowPaging="True" AutoGenerateColumns="true"
CellPadding="4" ForeColor="#333333" GridLines="None" Width="100%" PageSize="500"
PagerSettings-PageButtonCount="25">
<EmptyDataTemplate>
<div>
No Data Available
</div>
</EmptyDataTemplate>
<Columns>
<asp:TemplateField HeaderText="Sl.No" Visible="true">
<ItemTemplate>
<%# Container.DataItemIndex + 1 %>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="#009cd9" Font-Bold="True" ForeColor="White" />
<RowStyle HorizontalAlign="Left" BackColor="#EFF3FB" />
<EditRowStyle BackColor="#009cd9" />
<SelectedRowStyle BackColor="#009cd9" Font-Bold="True" ForeColor="#333333" />
<PagerStyle BackColor="#009cd9" ForeColor="White" HorizontalAlign="Center" />
<HeaderStyle BackColor="#009cd9" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
<br />
<br />
<asp:Button ID="btnInsert" runat="server" Text="Insert" OnClick="btnInsert_Click" />
</td>
</tr>
</tbody>
</table>
</div>
</div>
</div>
</div>
<b class="b4bh"></b><b class="b3bh"></b><b class="b2bh"></b><b class="b1h"></b>
</td>
</tr>
</tbody>
</table>
</div>
</div>
</form>
</body>
</html>
SOURCE:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.IO;
using System.Data.OleDb;
using System.Data.SqlClient;
public partial class api_BankTransaction : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
bind();
}
}
private void bind()
{
try
{
}
catch (Exception)
{
throw;
}
}
protected void btnUpload_Click(object sender, EventArgs e)
{
try
{
String strConnection = "ConnectionString";
string connectionString = "";
if (FileUpload1.HasFile)
{
string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
string fileExtension = Path.GetExtension(FileUpload1.PostedFile.FileName);
string fileLocation = Server.MapPath("~/Tempempphoto/" + fileName);
FileUpload1.SaveAs(fileLocation);
if (fileExtension == ".xls")
{
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
else if (fileExtension == ".xlsx")
{
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
} OleDbConnection con = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = con;
OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
DataTable dtExcelRecords = new DataTable();
con.Open();
DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
cmd.CommandText = "SELECT * FROM [" + getExcelSheetName + "]";
dAdapter.SelectCommand = cmd;
dAdapter.Fill(dtExcelRecords);
grdTrans.DataSource = dtExcelRecords;
grdTrans.DataBind();
}
}
catch (Exception)
{
throw;
}
}
protected void btnInsert_Click(object sender, EventArgs e)
{
try
{
foreach (GridViewRow grv in grdTrans.Rows)
{
// string d1 = grv.Cells[1].Text.Replace("-", "/").Trim();
//DateTime dt1 = Convert(DateTime, grv.Cells[1].Text);
//DateTime dt2 = Convert.ToDateTime(grv.Cells[2].Text.Replace("-", "/"));
String s = "Insert into tbl_Bank_Transaction ([TransDate],[ValueDate],[Chqno],[Remark],[amount],[Type],[branch],[bank],[Pin_Transno],[refno],[date],[PStatus],[Status]) values( '" + grv.Cells[1].Text + "','" + grv.Cells[2].Text + "','" + grv.Cells[3].Text + "','" + grv.Cells[4].Text + "','" + grv.Cells[5].Text + "','" + grv.Cells[6].Text + "','" + grv.Cells[7].Text + "','" + ddlbank.SelectedItem.Text + "',null,null,'" + DateTime.Now + "','1','1')";
comman.commanclass.ExecInsertQuery(s);
ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "javascript:alert('Inserted');", true);
}
}
catch (Exception)
{
throw;
}
}
}
CLASS:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Web.SessionState;
namespace INSERTEXCEL
{
public class Class1
{
public static void ExecInsertQuery(String query1)
{
string conn = ConfigurationManager.ConnectionStrings["str"].ToString();
SqlConnection con = new SqlConnection(conn);
con.Open();
SqlCommand cmd = new SqlCommand(query1, con);
cmd.CommandType = CommandType.Text;
SqlDataAdapter da = new SqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
con.Close();
}
}
}
DATABASE:
CREATE TABLE [tbl_Bank_Transaction](
[Id] [int] IDENTITY(1,1) NOT NULL,
[TransDate] [datetime] NULL,
[ValueDate] [datetime] NULL,
[Chqno][nvarchar](100) NULL,
[Remark][nvarchar](500)NULL,
[amount][money] NULL,
[Type][nvarchar](25) NULL,
[branch][nvarchar](200) NULL,
[bank][nvarchar](200) NULL,
[Pin_Transno][nvarchar](50) NULL,
[refno][nvarchar](50) NULL,
[date][datetime] NULL,
[PStatus][int] NULL,
[Status] [int] NULL,
CONSTRAINT [PK_tbl_Bank_Transaction] PRIMARY KEY CLUSTERED
([Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] )
ON [PRIMARY]