Arun pandey

Arun pandey

  • NA
  • 6
  • 2.6k

Create time Sheet use Asp.net

Dec 12 2014 7:12 AM
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">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
<body>
    <form id="form1" runat="server">
   <div style=" margin-left: 9%; margin-right: 9%; margin-bottom: 2%;  padding-top: 4%;"><h1 style="text-align:center;">Upload Excel</h1></div>
    <div style=" margin-left: 9%; margin-right: 9%; margin-bottom: 2%;">
    
    <asp:GridView ID="gvupload" runat="server" width="100%" 
            EmptyDataText="No records found">
                        <HeaderStyle BackColor="#89A0FE" />
                        
                    </asp:GridView>
    </div>
    <div style=" margin-left: 35%; margin-right: 9%; margin-bottom: 2%;">
    <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);
    }


    private void BindGrid()
    {
    string quer = "SELECT * from upload";
 
    DataTable dt = con.GetdataTable(quer);

    gvupload.DataSource = dt;
    gvupload.DataBind();
    }
   
}

data base 


USE [Cascading_ddl]
GO

/****** Object:  Table [dbo].[upload]    Script Date: 12/12/2014 17:48:08 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[upload](
[empid] [int] IDENTITY(1,1) NOT NULL,
[name] [nvarchar](50) NULL,
[Address] [nvarchar](50) NULL,
[city] [nvarchar](50) NULL,
[Phone] [nvarchar](50) NULL
) ON [PRIMARY]

GO


this is Arun Pandey 
Mob. 8447804308
 Email- [email protected]