Saturday, May 18, 2013

How to open Dropdown on another Dropdown selection in Asp.net


Introduction: In this post I will try to explain how we can open another dropdown on another dropdown selection and insert their data in to database.

Description:
I have created four tables.
BOOK_NAME
BOOK_ID
int
BOOK_NAME
varchar(50)
BOOK_ID is primary key.
PUBLICATION_NAME
PUBLICATION_ID
int
PUBLICATION_NAME
varchar(50)
BOOK_ID_FK
varchar(50)
PUBLICATION_ID is primary key and BOOK_ID_FK is foreign key.
AUTHOR_NAME
AUTHOR_ID
int
AUTHOR_NAME
varchar(50)
PUBLICATION_ID_FK
varchar(50)
AUTHOR_ID is primary key and PUBLICATION_ID_FK is foreign key.
BOOK_DETAIL
Id
int
BOOK
varchar(50)
PUBLICATION
varchar(50)
AUTHOR
varchar(50)


Open visual studio and add new webform to application.
. Add the Connectionstring in web.config file of website.
<configuration>
       <system.web>
              <compilation debug="true" targetFramework="4.0"/>
       </system.web>
       <connectionStrings>
              <add name="CON" connectionString="Data Source=SYS-1F78031ED0A;Initial Catalog=TestBlog;Integrated Security=True"/>
       </connectionStrings>
</configuration>
Drag and drop the Dropdownlist controls from Toolbox.
  <table><tr><td>
        Book Name:&nbsp;</td><td> <asp:DropDownList ID="drpbook" runat="server" AutoPostBack="True"
            onselectedindexchanged="DropDownList1_SelectedIndexChanged">
        </asp:DropDownList></td></tr>  
       <tr><td>&nbsp;&nbsp;&nbsp;&nbsp;</td></tr>
      <tr> <td> Publication Name:</td><td><asp:DropDownList ID="drppublication" runat="server" AutoPostBack="True"
            onselectedindexchanged="DropDownList2_SelectedIndexChanged">
        </asp:DropDownList></td></tr>
        <tr><td>&nbsp;&nbsp;&nbsp;&nbsp;</td></tr>
        <tr><td>  Author Name :  </td>
        <td> <asp:DropDownList ID="dropauthor" runat="server">
        </asp:DropDownList></td></tr>
        <tr><td>&nbsp;&nbsp;&nbsp;&nbsp;</td></tr>
       <tr><td></td><td> <asp:Button ID="btnsave" runat="server" Text="Insert" onclick="btnsave_Click" /></td></tr>
         </table>

Now go to .aspx.cs page.
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["CON"].ToString());
    protected void Page_Load(object sender, EventArgs e)
    {
        if(!IsPostBack)
        {
            Binddropdown();
        }
    }
    protected void DropDownList2_SelectedIndexChanged(object sender, EventArgs e)
    {
        string query = "select * from AUTHOR_NAME where PUBLICATION_ID_FK=" + drppublication.SelectedValue + "";
        SqlCommand cmd = new SqlCommand(query, con);
        SqlDataAdapter adp = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        adp.Fill(dt);
        dropauthor.DataSource = dt;
        dropauthor.DataTextField = "AUTHOR_NAME";
        dropauthor.DataValueField = "AUTHOR_ID";
        dropauthor.DataBind();
        dropauthor.Items.Insert(0, new ListItem("---Select---", "0"));

    }

    private void Binddropdown()
    {
        SqlDataAdapter adp = new SqlDataAdapter("Select * from BOOK_NAME", con);
        DataTable dt = new DataTable();
        adp.Fill(dt);
        drpbook.DataSource = dt;
        drpbook.DataTextField = "BOOK_NAME";
        drpbook.DataValueField = "BOOK_ID";
        drpbook.DataBind();
        drpbook.Items.Insert(0, new ListItem("---Select---", "0"));
        drppublication.Items.Insert(0, new ListItem("---Select---", "0"));
        dropauthor.Items.Insert(0, new ListItem("---Select---", "0"));
    }
    protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
    {
        string query = "select * from PUBLICATION_NAME where BOOK_ID_FK='" + drpbook.SelectedValue + "'";
        SqlCommand cmd = new SqlCommand(query, con);
        SqlDataAdapter adp = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        adp.Fill(dt);
        drppublication.DataSource = dt;
        drppublication.DataTextField = "PUBLICATION_NAME";
        drppublication.DataValueField = "PUBLICATION_ID";
        drppublication.DataBind();
        drppublication.Items.Insert(0, new ListItem("---Select---", "0"));
    }
    protected void btnsave_Click(object sender, EventArgs e)
    {
        string command = "insert into BOOK_DETAIL(BOOK,PUBLICATION,AUTHOR) values('"+drpbook.SelectedItem+"','"+drppublication.SelectedItem+"','"+dropauthor.SelectedItem+"')";
        SqlCommand cmd = new SqlCommand(command, con);
        con.Open();
            cmd.ExecuteNonQuery();
        dropauthor.SelectedIndex =-1;
        drppublication.SelectedIndex = -1;
        drpbook.SelectedIndex = -1;
        Response.Write("Data Save Successfully");
    }

In VB

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Private con As New SqlConnection(ConfigurationManager.ConnectionStrings("CON").ToString())

    Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles drpbook.SelectedIndexChanged
        Dim query As String = "select * from PUBLICATION_NAME where BOOK_ID_FK='" + drpbook.SelectedValue & "'"
        Dim cmd As New SqlCommand(query, con)
        Dim adp As New SqlDataAdapter(cmd)
        Dim dt As New DataTable()
        adp.Fill(dt)
        drppublication.DataSource = dt
        drppublication.DataTextField = "PUBLICATION_NAME"
        drppublication.DataValueField = "PUBLICATION_ID"
        drppublication.DataBind()
        drppublication.Items.Insert(0, New ListItem("---Select---", "0"))
    End Sub
    Private Sub Binddropdown()
        Dim adp As New SqlDataAdapter("Select * from BOOK_NAME", con)
        Dim dt As New DataTable()
        adp.Fill(dt)
        drpbook.DataSource = dt
        drpbook.DataTextField = "BOOK_NAME"
        drpbook.DataValueField = "BOOK_ID"
        drpbook.DataBind()
        drpbook.Items.Insert(0, New ListItem("---Select---", "0"))
        drppublication.Items.Insert(0, New ListItem("---Select---", "0"))
        dropauthor.Items.Insert(0, New ListItem("---Select---", "0"))
    End Sub
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            Binddropdown()
        End If
    End Sub
    Protected Sub DropDownList2_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
        Dim query As String = "select * from AUTHOR_NAME where PUBLICATION_ID_FK=" + drppublication.SelectedValue & ""
        Dim cmd As New SqlCommand(query, con)
        Dim adp As New SqlDataAdapter(cmd)
        Dim dt As New DataTable()
        adp.Fill(dt)
        dropauthor.DataSource = dt
        dropauthor.DataTextField = "AUTHOR_NAME"
        dropauthor.DataValueField = "AUTHOR_ID"
        dropauthor.DataBind()
        dropauthor.Items.Insert(0, New ListItem("---Select---", "0"))

    End Sub
    Protected Sub btnsave_Click(ByVal sender As Object, ByVal e As EventArgs)
        Dim command As String = "insert into BOOK_DETAIL(BOOK,PUBLICATION,AUTHOR) values ('" & drpbook.SelectedItem.Text & "','" & drppublication.SelectedItem.Text & "','" & dropauthor.SelectedItem.Text & "')"
        Dim cmd As New SqlCommand(command, con)
        con.Open()
        cmd.ExecuteNonQuery()
        dropauthor.SelectedIndex = -1
        drppublication.SelectedIndex = -1
        drpbook.SelectedIndex = -1
        Response.Write("Data Save Successfully")
    End Sub

Now run the project and check the result.

Related Articles on Dropdownlist:

Ø  How to bind Dropdownlist with database in Asp.net?

Is it helpful?

If yes post your comment to admire my work. You can like me on Facebook, Google+, Linkedin and Twitter via hit on Follow us Button and also can get update follow by Email.

No comments:

Post a Comment