Answer

Create time Sheet use Asp.net

Arun pandey

1.6k

Q. 1 . how to create time table sheet in company employee ?
Q.2. how to import excel data in sql server 2008 r2 but four table ??
as
table 1----- employee ---- is master table like fild -- S.No--------------------------------------------------------------------------------------------------------------------
Relevant Provision---------------------------------------------------------------------------------------------------
Compliance Activity (with short description)-------------------------------------------------------------
Consequence of non-compliance----------------------------------------------------------------------
Relevant Form-------------------------------------------------------------------------------------------
Deadline----------------------------------------------------------------------------------------------
Proof of completion----------------------------------------------------------------------------
Concerned persons--------------------------------------------------------------------------
Status of the respective activity-------------------------------------------------
Relevant_Act---------------------------------------------------------------Act_Id=2Relevant_Rule-----------------------------------------------------------Rule_Id=3
Law _Area---------------------------------------------------------------Area_Id=4

table 2..........................................Relevant_Rule------------- Rule_Id , Name
table 3..................................... Law Area------------- Area_Id , Name
table 4.......................................Relevant_Act ------------- Act_Id , Name
solve this problem

One table import data

Default5.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default5.aspx.cs" Inherits="Default5" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<body>

<div style=" margin-left: 9%; margin-right: 9%; margin-bottom: 2%; padding-top: 4%;"><h1 style="text-align:center;">Upload Excel</h1></div>

<asp:GridView ID="gvupload" runat="server" width="100%"

EmptyDataText="No records found">

</asp:GridView>

</div>

<asp:FileUpload ID="fileupload" runat="server" style="color: Red;font-size: medium;float: left;"/>

<a href="#"><asp:ImageButton ID="LinkButton1" runat="server"

ImageUrl="~/Images/images.jpg" width="87px" height="21px" alt="IMPORT"

onclick="imp_Click"/>

</a>

<asp:Label ID="lblmsg" runat="server"></asp:Label>

</div>

</form>

</body>

</html>

Default5.aspx.cs

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.Data.OleDb;

using System.Data.SqlClient;

public partial class Default5 : System.Web.UI.Page

{

Connection con = new Connection();

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

BindGrid();

}

protected void imp_Click(object sender, EventArgs e)

{

if (fileupload.HasFile)

{

try

{

string filename = fileupload.PostedFile.FileName;

fileupload.SaveAs(Server.MapPath("~/Document/") + filename);

}

catch (Exception ex)

{

}

string xx = fileupload.FileName.ToString();

string filePath = HttpContext.Current.Server.MapPath("~/Document/" + xx);

OleDbConnection oconn = new OleDbConnection

("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0");//OledbConnection and

try

{

OleDbCommand ocmd = new OleDbCommand("select * from [Sheet1$]", oconn);

oconn.Open();

OleDbDataReader odr = ocmd.ExecuteReader();

string name= "";

string Address = "";

string city= "";

string Phone = "";

int aaa = 0;

while (odr.Read())

{

aaa = aaa + 1;

name = valid(odr, 1);

Address = valid(odr, 2);

city = valid(odr, 3);

Phone = valid(odr, 4);

if (aaa > 2)

updatedataintosql(name, Address, city, Phone);

}

oconn.Close();

}

catch (DataException ee)

{

lblmsg.Text = ee.Message;

lblmsg.ForeColor = System.Drawing.Color.Red;

}

finally

{

lblmsg.Text = "Data Updated Sucessfully";

//lblmsg.Text = filePath;

lblmsg.ForeColor = System.Drawing.Color.Green;

}

public void updatedataintosql(string name, string Address, string city, string Phone)

{

string qry = "insert into upload values('" + name.Replace("'", "") + "','" + Address.Replace("'", "") + "','" + city.Replace("'", "") + "','" + Phone.Replace("'", "") + "')";

int dts = con.ExecuteQuery(qry);

if (dts == 1)

{

}

protected string valid(OleDbDataReader myreader, int stval)//if any columns are

{

object val = myreader[stval];

if (val != DBNull.Value)

return val.ToString();

else

return Convert.ToString(0);

}