Click here to Skip to main content
15,867,141 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I need to develop a simple master detail webform in aspnet. data is from excel xlsx 2 sheets .

Sheet1 :ProductL1

ID ParentID Title
1 0 P1_1
2 0 P1_2

Sheet2 :ProductL2

ID Parent_ID Title
1 1 P2_1
2 1 P2_2
3 1 P2_3
4 1 P2_4
5 2 P2_5
6 2 P2_6


in web.config :

C#
<pre lang="xml"><connectionStrings>
    <add name="MSIDConn" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\MISD_report.xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1;ImportMixedTypes=Text;TypeGuessRows=0'" providerName="System.Data.OleDb"/>
  </connectionStrings>



in default.aspx :

XML
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_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 runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
        <asp:DropDownList ID="PL1" runat="server" Height="32px"  Width="186px"
            onselectedindexchanged="PL1_SelectedIndexChanged" CausesValidation="True"
            AutoPostBack="True">
        </asp:DropDownList>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
        <asp:DropDownList ID="PL2" runat="server" Height="32px" Width="186px"
            AutoPostBack="True">
        </asp:DropDownList>

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
        <asp:DropDownList ID="PL3" runat="server" Height="32px" Width="186px">
        </asp:DropDownList>

        <br />
        <br />
        <br />
          &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
          <asp:DropDownList ID="DT1" runat="server" Height="32px" Width="186px">
        </asp:DropDownList>
        &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
          <asp:DropDownList ID="regions" runat="server" Height="32px" Width="186px">
        </asp:DropDownList>
        &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
          <asp:DropDownList ID="locations" runat="server" Height="32px" Width="186px">
        </asp:DropDownList>
        <br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

    </div>
    </form>
</body>
</html>




in Default.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.Configuration;
using System.Data;
using System.Data.OleDb;

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

    protected void Page_Load(object sender, EventArgs e)
    {

            string connString = ConfigurationManager.ConnectionStrings["MSIDConn"].ConnectionString;
            OleDbConnection oledbConn = new OleDbConnection(connString);


            oledbConn.Open();
            OleDbCommand cmd1 = new OleDbCommand("SELECT ID ,ParentID ,Title FROM [ProductL1$]", oledbConn);
            OleDbDataAdapter adaptor1 = new OleDbDataAdapter();

            adaptor1.SelectCommand = cmd1;
            DataSet ds1 = new DataSet();
            adaptor1.Fill(ds1, "ProductL1");
            PL1.DataSource = ds1.Tables[0].DefaultView;
            PL1.DataValueField = "ID";
            PL1.DataTextField = "Title";
            PL1.DataBind();



    }

    protected void PL1_SelectedIndexChanged(object sender, EventArgs e)
    {

        if (IsPostBack)
        {
            string connString = ConfigurationManager.ConnectionStrings["MSIDConn"].ConnectionString;
            OleDbConnection oledbConn = new OleDbConnection(connString);

            int aa = 0;
            int.TryParse(PL1.SelectedValue, out aa);
            OleDbCommand cmd2 = new OleDbCommand("SELECT ID ,Parent_ID  ,Title FROM [ProductL2$] Where  Parent_ID = @parentID ", oledbConn);
            cmd2.Parameters.AddWithValue("@parentID", aa);
            OleDbDataAdapter adaptor2 = new OleDbDataAdapter();
            adaptor2.SelectCommand = cmd2;
            DataSet ds2 = new DataSet();
            //        System.Diagnostics.Debug.Print(cmd2.CommandText);

            adaptor2.Fill(ds2, "ProductL2");
            PL2.DataSource = ds2.Tables[0].DefaultView;
            PL2.DataValueField = "ID";
            PL2.DataTextField = "Title";
            PL2.DataBind();
            oledbConn.Close();
        }



    }
}



Quote:
on running this : it only select P1_1 and details records from ProductL2 sheets, unable to select any other ProductL1 ID than P1_1 ... ( can't select P1_2 , it reset to P1_1 automatically ) .. where in code i doing reset? please let me know ..
Posted
Updated 10-May-15 23:05pm
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900