Above Excel sheet contain

Dist column contain all cells with left alignment its datatype is string

SCNO column contain all cells with left alignment its datatype is string

ConnLoad column contains all cells with right alignment its datatype is digit type

Here CDAMT contain all cells with right alignment tis datatype is digit type

digit type means (decimal or integer or long)

above form contain Excel fields like Dist, Category,SCNo, ConnLoad ,CDAMT ….. all these columns can pass as varchars

but when entering in above form ConnLoad and CDAMT must enter digits only because in Excel sheet its alignment is right

side so it accept digits only. When you enter non-numeric (alphabets ) then

it shows an error Data Type Mismatch Criteria Expression

Submit button coding

public void add()
{
string connectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\D5MASTER_JAN_2009.xls;Extended Properties=Excel 8.0;”;
OleDbConnection objConn = new OleDbConnection(connectionString);

string query = “insert into [Satya$](DIST,SCNO,NAME,CATEGORY,CONNLOAD,DATEOFREL,DISTRIBUTI,CDAMT,PHASE,GROP,SUB,STATUS,”
+ “POLE_NUM,SECTION_NAME,FEEDER,STRU,PHONE,METER_NUM,METER_MAKE) ”
+ “values(” + txtDist.Text.Trim() + “,’” + txtSCNo.Text.Trim() + “‘,’” + txtName.Text.Trim() + “‘,’” + txtCategory.Text.Trim() + “‘,’” + txtConnload.Text + “‘,’” + txtDateOfRel.Text.Trim() + “‘,’” + txtDistribution.Text.Trim() + “‘,’” + txtCDAMT.Text.Trim() + “‘,’” + txtPhase.Text.Trim() + “‘,’” + txtGrop.Text.Trim() + “‘,’” + txtSUB.Text.Trim() + “‘,’” + txtStatus.Text.Trim() + “‘,’” + txtPoleNum.Text.Trim() + “‘,’” + txtSectionName.Text.Trim() + “‘,’” + txtFeeder.Text.Trim() + “‘,’” + txtStrv.Text.Trim() + “‘,’” + txtPhone.Text.Trim() + “‘,’” + txtMeterNum.Text.Trim() + “‘,’” + txtMeterMake.Text.Trim() + “‘)”;
OleDbCommand objCmd = new OleDbCommand(query, objConn);

objConn.Open();
objCmd.ExecuteNonQuery();
objConn.Close();
}

using Class file

using System.Data.OleDb;

/// <summary>
/// Summary description for clsReport
/// </summary>
public class clsReport
{

#region “ConnectionString”
//for Office 2007
//string connectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\D5MASTER_JAN_2009.xls;Extended Properties=\”Excel 8.0;HDR=YES;\”";
//OleDbConnection objConn = new OleDbConnection(connectionString);
//for office 97-2003
string connectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\D5MASTER_JAN_2009.xls;Extended Properties=Excel 8.0;”;
#endregion

#region “Contructors”
private long c_Dist;
private string c_SCNo;
private string c_Name;
private string c_Category;
private decimal c_ConnLoad;
private string c_DateOfRel;
private string c_Distributi;
private string c_CDAMT;
private string c_Phase;
private string c_Grop;
private string c_SUB;
private string c_Status;
private string c_PoleNum;
private string c_SectionName;
private string c_Feeder;
private string c_Strv;
private string c_Phone;
private string c_MeterNum;
private string c_MeterMake;
#endregion

#region “Properties”
public string sMeterMake
{
get { return c_MeterMake; }
set { c_MeterMake = value; }
}

public string sMeterNum
{
get { return c_MeterNum; }
set { c_MeterNum = value; }
}

public string sPhone
{
get { return c_Phone; }
set { c_Phone = value; }
}

public string sStrv
{
get { return c_Strv; }
set { c_Strv = value; }
}

public string sFeeder
{
get { return c_Feeder; }
set { c_Feeder = value; }
}

public string sSectionName
{
get { return c_SectionName; }
set { c_SectionName = value; }
}

public string sPoleNum
{
get { return c_PoleNum; }
set { c_PoleNum = value; }
}

public string sStatus
{
get { return c_Status; }
set { c_Status = value; }
}

public string sSUB
{
get { return c_SUB; }
set { c_SUB = value; }
}

public string sGrop
{
get { return c_Grop; }
set { c_Grop = value; }
}

public string sPhase
{
get { return c_Phase; }
set { c_Phase = value; }
}

public string sCDAMT
{
get { return c_CDAMT; }
set { c_CDAMT = value; }
}

public string sDistributi
{
get { return c_Distributi; }
set { c_Distributi = value; }
}

public string sDateOfRel
{
get { return c_DateOfRel; }
set { c_DateOfRel = value; }
}

public decimal sConnLoad
{
get { return c_ConnLoad; }
set { c_ConnLoad = value; }
}

public string sCategory
{
get { return c_Category; }
set { c_Category = value; }
}

public string sName
{
get { return c_Name; }
set { c_Name = value; }
}

public string sSCNo
{
get { return c_SCNo; }
set { c_SCNo = value; }
}

public long nDist
{
get { return c_Dist; }
set { c_Dist = value; }
}
#endregion

#region “Initialization”
public clsReport()
{

}
#endregion

#region “GetReportData()”
public DataSet GetReportData()
{
//string connectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\D5MASTER_JAN_2009.xls;Extended Properties=\”Excel 8.0;HDR=YES;\”";
OleDbConnection objConn = new OleDbConnection(connectionString);

string query = “select * from [satya$]“;
OleDbCommand objCmd = new OleDbCommand(query, objConn);

DataSet objDs = new DataSet();
OleDbDataAdapter objDa = new OleDbDataAdapter(objCmd);

objConn.Open();
objDa.Fill(objDs);
objCmd.ExecuteNonQuery();
objConn.Close();
return objDs;
}
#endregion

#region “SearchReportData()”
public DataSet SearchReportData()
{
OleDbConnection objConn = new OleDbConnection(connectionString);

//string query = “select * from [satya$] where Dist=’” + txtDistrict.Text.Trim() + “‘”;
string query = “select * from [satya$] where SCNo=?”;
OleDbCommand objCmd = new OleDbCommand(query, objConn);

objCmd.Parameters.Add(”@SCNO”, OleDbType.VarChar).Value = sSCNo;

DataSet objDs = new DataSet();
OleDbDataAdapter objDa = new OleDbDataAdapter(objCmd);

objConn.Open();
objDa.Fill(objDs);
objCmd.ExecuteNonQuery();
objConn.Close();
return objDs;
}
#endregion

#region “AddData()”
public long AddData()
{
OleDbConnection objConn = new OleDbConnection(connectionString);
string query = “insert into [Satya$](DIST,SCNO,NAME,CATEGORY,CONNLOAD,DATEOFREL,DISTRIBUTI,CDAMT,PHASE,GROP,SUB,STATUS,POLE_NUM,SECTION_NAME,FEEDER,STRU,PHONE,METER_NUM,METER_MAKE) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)”;

OleDbCommand objCmd = new OleDbCommand(query, objConn);
objCmd.CommandType = CommandType.Text;

objCmd.Parameters.Add(”@DIST”, OleDbType.BigInt).Value = nDist;
objCmd.Parameters.Add(”@SCNo”, OleDbType.VarChar).Value = sSCNo;
objCmd.Parameters.Add(”@NAME”, OleDbType.VarChar).Value = sName;
objCmd.Parameters.Add(”@CATEGORY”, OleDbType.VarChar).Value = sCategory;
objCmd.Parameters.Add(”@CONNLOAD”, OleDbType.Decimal).Value = sConnLoad;
objCmd.Parameters.Add(”@DATEOFREL”, OleDbType.VarChar).Value = sDateOfRel;
objCmd.Parameters.Add(”@DISTRIBUTI”, OleDbType.VarChar).Value = sDistributi;
objCmd.Parameters.Add(”@CDAMT”, OleDbType.VarChar).Value = sCDAMT;
objCmd.Parameters.Add(”@PHASE”, OleDbType.VarChar).Value = sPhase;
objCmd.Parameters.Add(”@GROP”, OleDbType.VarChar).Value = sGrop;
objCmd.Parameters.Add(”@SUB”, OleDbType.VarChar).Value = sSUB;
objCmd.Parameters.Add(”@STATUS”, OleDbType.VarChar).Value = sStatus;
objCmd.Parameters.Add(”@POLE_NUM”, OleDbType.VarChar).Value = sPoleNum;
objCmd.Parameters.Add(”@SECTION_NAME”, OleDbType.VarChar).Value = sSectionName;
objCmd.Parameters.Add(”@FEEDER”, OleDbType.VarChar).Value = sFeeder;
objCmd.Parameters.Add(”@STRU”, OleDbType.VarChar).Value = sStrv;
objCmd.Parameters.Add(”@PHONE”, OleDbType.VarChar).Value = sPhone;
objCmd.Parameters.Add(”@METER_NUM”, OleDbType.VarChar).Value = sMeterNum;
objCmd.Parameters.Add(”@METER_MAKE”, OleDbType.VarChar).Value = sMeterMake;

objConn.Open();
long value = long.Parse(objCmd.ExecuteNonQuery().ToString());
objConn.Close();
return value;
}
#endregion

}

default.aspx.cs

public partial class addData : System.Web.UI.Page
{

#region “Declaration”
clsReport objReport = new clsReport();

#endregion

#region “Page_Load”
protected void Page_Load(object sender, EventArgs e)
{

txtSCNo.Attributes.Add(”onkeypress”, “return isNumberKey(event)”);
txtCategory.Attributes.Add(”onkeypress”, “return isNumberKey(event)”);
txtCDAMT.Attributes.Add(”onkeypress”, “return isNumberKey(event)”);
txtPhase.Attributes.Add(”onkeypress”, “return isNumberKey(event)”);
}
#endregion

#region “imgbtnSubmit_Click”
protected void imgbtnSubmit_Click(object sender, ImageClickEventArgs e)
{
objReport.nDist = long.Parse(txtDist.Text.Trim());
objReport.sSCNo = txtSCNo.Text.Trim();
objReport.sName = txtName.Text.Trim();
objReport.sCategory = txtCategory.Text.Trim();
objReport.sConnLoad =Convert.ToDecimal(txtConnload.Text.Trim());
objReport.sDateOfRel = txtDateOfRel.Text.Trim();
objReport.sDistributi = txtDistribution.Text.Trim();
objReport.sCDAMT = txtCDAMT.Text.Trim();
objReport.sPhase = txtPhase.Text.Trim();
objReport.sGrop = txtGrop.Text.Trim();
objReport.sSUB = txtSUB.Text.Trim();
objReport.sStatus = txtStatus.Text.Trim();
objReport.sPoleNum = txtPoleNum.Text.Trim();
objReport.sSectionName = txtSectionName.Text.Trim();
objReport.sFeeder = txtFeeder.Text.Trim();
objReport.sStrv = txtStrv.Text.Trim();
objReport.sPhone = txtPhone.Text.Trim();
objReport.sMeterNum = txtMeterNum.Text.Trim();
objReport.sMeterMake = txtMeterMake.Text.Trim();

long value = objReport.AddData();

}
#endregion

Note: DateTime datatype in excel is center Alignment

Happy coding….