CFF Coding Source - ASP.NET (C#) Fill select menu with records from a database, insert selected records with live updated data

HTML

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Load.aspx.cs" Inherits="Load" %>
<%
    String getMyID = Request.QueryString["MyID"];
    %>
<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
   <%if (getMyID != null && getMyID != string.Empty)
       {%>
   <strong>ASP.NET (C# Version) SQL Server Database Driven Dropdown Menu</strong><br />
This demonstration loads all values into a Select Menu from a database table.<br />
    <form id="target" runat="server">
        <div>
            <asp:DropDownList ID="DropDownList1" runat="server">
                
            </asp:DropDownList>
        </div>
        <div class="postData"></div>
    <input type="hidden" name="MyID" value="<%=Request.QueryString["MyID"]%>" />
</form>
    <div id="postDataL"></div>
<script src="https://www.cffcs.com/Assets/jquery-3.6.0.js"></script>
    <script src="Newajaxsbmt.js"></script>
<script type="text/javascript">
// We need the delay to get the database to load to the page after inserting the record.
$(this).delay(1000).queue(function() {
$( "#target" ).change(function( event ) {
    $("#postDataL").load("LoadIt.ashx?MyID=<%=getMyID%>");
    $("#postDataL").css({ border: '1px double #4cff00', width: '15%' });
  event.preventDefault();
});
    $(this).dequeue();
    
});
</script>
    
<%}
    else
    { %>
        <a href="?MyID=1">Load ID</a> to procedd
    <% }%>
</body>
</html>

JavaScript

$(document).ready(function(){
    $('form').change(function(e){
        e.preventDefault();
        $.ajax({
            url: "Update.ashx",
            type: "POST",
            data: $(this).serialize(),
            success: function(data){
                $(".postData").html(data);
            },
            error: function(){
                $(".postData").html("Form submission failed!");
            }
        });

    });
});

SQL

ASP.NET

<?xml version="1.0"?>
<configuration>
  <connectionStrings configSource="database.config" />
  <system.web>
    <compilation debug="true" strict="false" explicit="true" targetFramework="4.7.2" />
    <httpRuntime targetFramework="4.7.2" />
  </system.web>
</configuration>

<?xml version="1.0"?>
<connectionStrings>
  <add name ="Virtual-Learning" connectionString="Data Source=sqlcorecs-01\sql2019;Database=Virtual-Class-01;User ID=testuser;Password=testuser;MultipleActiveResultSets=True"/>
</connectionStrings>

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.Configuration;
using System.Data.SqlClient;

protected void Page_Load(object sender, EventArgs e)
    {
        if (!this.IsPostBack)
        {
            string cn = ConfigurationManager.ConnectionStrings["Virtual-Learning"].ConnectionString;
            using (SqlConnection con = new SqlConnection(cn))
            {
                using (SqlCommand cmd = new SqlCommand("SELECT ddId, ddName FROM Dropdown"))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = con;
                    con.Open();
                    DropDownList1.DataSource = cmd.ExecuteReader();
                    DropDownList1.DataTextField = "ddName";
                    DropDownList1.DataValueField = "ddId";
                    DropDownList1.DataBind();
                    con.Close();
                }
            }
            DropDownList1.Items.Insert(0, new ListItem("--Select Programming Language--", "0"));
        }
    }

<%@ WebHandler Language="C#" Class="LoadIt" %>

using System;
using System.Web;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

 public void ProcessRequest (HttpContext context) {
        string getID = context.Request["DropDownList1"];
        string getMyID = context.Request["MyID"];
        //context.Response.Write(getID + "-" + getMyID);
        var LessonCon = new SqlConnection();
        SqlCommand LessonCMD = new SqlCommand();

        LessonCon = new SqlConnection(ConfigurationManager.ConnectionStrings["Virtual-Learning"].ConnectionString);
        LessonCon.Open();
        SqlDataReader rsDD;

        SqlCommand getDD = new SqlCommand("SELECT DropDown.ddID, Dropdown2.MyID, DropDown.ddName FROM DropDown INNER JOIN Dropdown2 ON DropDown.ddID = Dropdown2.ddid WHERE Dropdown2.MyID = @MyID", LessonCon);
        getDD.Parameters.Add(new SqlParameter("@MyID", getMyID));

        rsDD = getDD.ExecuteReader();
        while (rsDD.Read())
        {
            context.Response.Write(rsDD["ddID"] + "-" + rsDD["ddName"] + "<br />");
        }
        rsDD.Close();
        LessonCon.Close();

    }

<%@ WebHandler Language="C#" Class="Update" %>

using System;
using System.Web;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

public void ProcessRequest (HttpContext context) {
      
        string getID = context.Request["DropDownList1"];
        string getMyID = context.Request["MyID"];
            //context.Response.Write(getID + "-" + getMyID);
        var LessonCon = new SqlConnection();
        SqlCommand LessonCMD = new SqlCommand();

        LessonCon = new SqlConnection(ConfigurationManager.ConnectionStrings["Virtual-Learning"].ConnectionString);
        LessonCon.Open();


        SqlCommand getDD = new SqlCommand("SELECT DropDown.ddID, Dropdown2.MyID FROM DropDown INNER JOIN Dropdown2 ON DropDown.ddID = Dropdown2.ddid WHERE DropDown.ddID = @ddID AND Dropdown2.MyID = @MyID", LessonCon);
        getDD.Parameters.Add(new SqlParameter("@ddID", getID));
        getDD.Parameters.Add(new SqlParameter("@MyID", getMyID));
        SqlDataReader rsDD;
        rsDD = getDD.ExecuteReader();
        if (rsDD.Read())
        {
            context.Response.Write("The record already exist, <br />please choose another one.");
        }
        else
            try
            {
                SqlCommand objCmd = new SqlCommand();
                string strSQL;
                strSQL = "insert into Dropdown2 (ddid, MyID)values(@ddid, @MyID)";
                objCmd = new SqlCommand(strSQL, LessonCon);
                objCmd.Parameters.Add(new SqlParameter("@ddid", getID));
                objCmd.Parameters.Add(new SqlParameter("@MyID", getMyID));
                objCmd.ExecuteNonQuery();
                context.Response.Write("Recorded inserted Successfully!");
            }
            catch (Exception ex)
            {
                context.Response.Write("Sorry, there seems to be an issue, <br />please try again later.");
            }
            finally

            {
                rsDD.Close();
                LessonCon.Close();
            } 

    }

Preview

About Company

Top Viewed Articles