Create and Use Drop Down List - ASP.NET Program

Design a form and write code to:

        a) Make a drop down list. Populate and display students name in drop down list.
        b) Select a student from DDL and display his details in underlying text boxes.
        c) Add a record.
        d) Delete selected record.
        e) Edit selected record.

Name of table StudentMaster (studID, studName, DOB, Percentage, courseName, Address)

Answer:

Introduction:
In this application we will perform Select, Insert, Update and Delete operation on the table “StudentMaster”. The vb.net language is used to develop this application.

Steps to develop this Application:

1. Create the table “StudentMaster” in SQL Sever database

create table StudentMaster (studID int, studName varchar(50), DOB date, Percentage float, courseName varchar(20), Address varchar(100))

2. Develop the page in VB.NET using which we can perform select, insert, update and delete operation on database table.

student-record.aspx

<!DOCTYPE html>
<head runat="server">
    <title>Student Record</title>
</head>
     <%@ Import Namespace="System.Data.SqlClient" %>
    <%@ Import Namespace="System.Data" %>
    <%@ Import Namespace="System" %>
    <script language="VB" runat="server">

        Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
            Dim cmd As New SqlCommand
            If IsPostBack = False Then
                Fillcombo()
                binddata()
            End If
        End Sub

        Sub Fillcombo()
            Using oConn As New SqlConnection
                Dim cmd As New SqlCommand
                oConn.ConnectionString = ConfigurationSettings.AppSettings("ConnectionString")
                oConn.Open()
                Dim dr As SqlDataReader
                cmd = New SqlCommand("Select * from StudentMaster")
                cmd.Connection = oConn
                dr = cmd.ExecuteReader()
                ddlStudName.DataSource = dr
                ddlStudName.DataTextField = "studName"
                ddlStudName.DataValueField = "studID"
                ddlStudName.DataBind()
                ddlStudName.Items.Insert(0, "Select Student")
                'ddlStudName.SelectedValue = 0
                dr.Close()
                dr = Nothing
            End Using
        End Sub

        Sub binddata()
            txtname.Text = ""
            txtdob.Text = ""
            txtpercentage.Text = ""
            txtcourse.Text = ""
            txtaddress.Text = ""
        End Sub

        Protected Sub btnAdd_Click(sender As Object, e As EventArgs)
            Using oConn As New SqlConnection
                Dim cmd As New SqlCommand
                Dim StudentName As String = txtname.Text
                Dim DOB As String = txtdob.Text
                Dim Percentage As String = txtpercentage.Text
                Dim Course As String = txtcourse.Text
                Dim Address As String = txtaddress.Text
                oConn.ConnectionString = ConfigurationSettings.AppSettings("ConnectionString")
                oConn.Open()
                Dim dr As SqlDataReader
                cmd = New SqlCommand("insert into StudentMaster values ('" + StudentName + "', '" + DOB + "', '" + Percentage + "', '" + Course + "', '" + Address + "') ")
                cmd.Connection = oConn
                dr = cmd.ExecuteReader()
                ClientScript.RegisterClientScriptBlock(Me.GetType, "alert", "alert('Record Added Successfully')", True)
                oConn.Close()
                binddata()
            End Using
        End Sub

        Protected Sub btnUpdate_Click(sender As Object, e As EventArgs)
            Using oConn As New SqlConnection
                Dim cmd As New SqlCommand
                Dim StudentName As String = txtname.Text
                Dim DOB As String = txtdob.Text
                Dim Percentage As String = txtpercentage.Text
                Dim Course As String = txtcourse.Text
                Dim Address As String = txtaddress.Text
                oConn.ConnectionString = ConfigurationSettings.AppSettings("ConnectionString")
                oConn.Open()
                Dim dr As SqlDataReader
                cmd = New SqlCommand("update StudentMaster set studName= '" + StudentName + "',DOB= '" + DOB + "',Percentage= '" + Percentage + "' ,courseName= '" + Course + "',Address= '" + Address + "' where studID= " + ddlStudName.Text)
                cmd.Connection = oConn
                dr = cmd.ExecuteReader()
                ClientScript.RegisterClientScriptBlock(Me.GetType, "alert", "alert('Record Updated Successfully')", True)
                oConn.Close()
                binddata()
            End Using
        End Sub

        Protected Sub btnDelete_Click(sender As Object, e As EventArgs)
            Using oConn As New SqlConnection
                Dim cmd As New SqlCommand
                Dim StudentName As String = txtname.Text
                Dim DOB As String = txtdob.Text
                Dim Percentage As String = txtpercentage.Text
                Dim Course As String = txtcourse.Text
                Dim Address As String = txtaddress.Text
                oConn.ConnectionString = ConfigurationSettings.AppSettings("ConnectionString")
                oConn.Open()
                Dim dr As SqlDataReader
                cmd = New SqlCommand("delete from StudentMaster where studID= " + ddlStudName.Text)
                cmd.Connection = oConn
                dr = cmd.ExecuteReader()
                ClientScript.RegisterClientScriptBlock(Me.GetType, "alert", "alert('Record Deleted Successfully')", True)
                oConn.Close()
                binddata()
            End Using
        End Sub

        Protected Sub ddlStudName_SelectedIndexChanged(sender As Object, e As EventArgs)
            Using oConn As New SqlConnection
                Dim cmd As New SqlCommand
                oConn.ConnectionString = ConfigurationSettings.AppSettings("ConnectionString")
                oConn.Open()
                Dim dr As SqlDataReader
                cmd = New SqlCommand("Select * from StudentMaster where studID=" + ddlStudName.Text)
                cmd.Connection = oConn
                dr = cmd.ExecuteReader()
                If (dr.Read = True) Then
                    txtname.Text = dr("studName").ToString()
                    txtdob.Text = dr("DOB").ToString()
                    txtpercentage.Text = dr("Percentage").ToString()
                    txtcourse.Text = dr("courseName").ToString()
                    txtaddress.Text = dr("Address").ToString()
                End If
                oConn.Close()
            End Using
        End Sub

    </script>
<body>
    <form id="form1" runat="server">
        <fieldset style="width:280px;background-color:aqua" >
        <legend>Student Record</legend>

       <table>
           <tr>
               <td>StudentName: </td>
               <td><asp:TextBox ID="txtname" runat="server" Text=""></asp:TextBox></td>
               <td><asp:dropdownlist id="ddlStudName" runat="server" DataTextField="studName"  DataValueField="studID" AppendDataBoundItems="True" OnSelectedIndexChanged="ddlStudName_SelectedIndexChanged" AutoPostBack="True" ></asp:dropdownlist></td>
           </tr>
           <tr>
               <td>DOB: </td>
               <td><asp:TextBox ID="txtdob" runat="server" text=""></asp:TextBox></td>
           </tr>
           <tr>
               <td>Percentage:</td>
               <td><asp:TextBox ID="txtpercentage" runat="server" Text=""></asp:TextBox></td>
           </tr>
           <tr>
               <td>courseName:</td>
               <td><asp:TextBox ID="txtcourse" runat="server" Text=""></asp:TextBox></td>
           </tr>
           <tr>
               <td>Address:</td>
               <td><asp:TextBox ID="txtaddress" runat ="server" Text =""></asp:TextBox></td>
           </tr>
           <tr>
                <td></td>
           </tr>
           <tr>
               <td><asp:Button ID="btnadd" runat="server" Text ="AddRecord" OnClick="btnAdd_Click" /></td>
               <td><asp:Button ID="btnupdate" runat ="server" Text="UpdateRecord" onClick="btnUpdate_Click" /></td>
               <td><asp:Button ID="btndelete" runat="server" Text ="DeleteRecord" OnClick ="btnDelete_Click" /></td>
           </tr>
        </table>
      </fieldset>
   </form>
</body>
</html>