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

HTML

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Load.aspx.vb" Inherits="Load" %>
<%
    Dim getMyID As String = Request.QueryString("MyID")
    %>
<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <%if getMyID="" then%>
<a href="?MyID=1">Load ID</a> to proceed
<%elseif getMyID<>"" then%>
   <strong>ASP.NET (VB 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>
    <% End If %>
</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>

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

Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        If Not Me.IsPostBack Then
            Dim cn As String = ConfigurationManager.ConnectionStrings("Virtual-Learning").ConnectionString
            Using con As New SqlConnection(cn)
                Using cmd As 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()
                End Using
            End Using
            DropDownList1.Items.Insert(0, New ListItem("--Select Programming Language--", "0"))
        End If
    End Sub

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

 Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
        Dim getID As String = context.Request.Form("DropDownList1")
        Dim getMyID As String = context.Request.QueryString("MyID")
        'context.Response.Write(getMyID & "-" & getID)
        'context.Response.End()
        Dim strSQL As String
        Dim objCmd As SqlCommand = Nothing
        Dim LessonCon As New SqlConnection
        LessonCon = New SqlConnection(ConfigurationManager.ConnectionStrings("Virtual-Learning").ConnectionString)
        LessonCon.Open()
        Dim getDD As 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))
        Dim rsDD As SqlDataReader
        rsDD = getDD.ExecuteReader()
        While rsDD.Read()
            context.Response.Write(rsDD("ddID") & "-" & rsDD("ddName") & "<br />")
            'If rsDD.Read() Then
            '    context.Response.Write(rsDD("ddID") & "-" & rsDD("ddName") & "<br />")
            'Else
            '    context.Response.Write("No records exist at this moment. Please choose one to begin.")
            'End If
        End While
        rsDD.Close()
        LessonCon.Close()
    End Sub

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

Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
        Dim getID As String = context.Request.Form("DropDownList1")
        Dim getMyID As String = context.Request.Form("MyID")
        'context.Response.Write(getMyID & "-" & getID)
        'context.Response.End()
        Dim strSQL As String
        Dim objCmd As SqlCommand = Nothing
        Dim cn As New SqlConnection
        cn = New SqlConnection(ConfigurationManager.ConnectionStrings("Virtual-Learning").ConnectionString)
        cn.Open()
        Dim getDD As 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", cn)
        getDD.Parameters.Add(New SqlParameter("@ddID", getID))
        getDD.Parameters.Add(New SqlParameter("@MyID", getMyID))
        Dim rsDD As SqlDataReader
        rsDD = getDD.ExecuteReader()
        If rsDD.Read() Then
            context.Response.Write("The record already exists, please choose another one.")
        Else
            strSQL = "insert into Dropdown2 (ddid, MyID)values(@ddid, @MyID)"
            objCmd = New SqlCommand(strSQL, cn)
            objCmd.Parameters.Add(New SqlParameter("@ddid", getID))
            objCmd.Parameters.Add(New SqlParameter("@MyID", getMyID))
            objCmd.ExecuteNonQuery()
            context.Response.Write("Recorded inserted Successfully!")
        End If
        rsDD.Close()
        LessonCon.Close()
    End Sub

Preview

About Company

Top Viewed Articles