Insert (Save) sum of unit price and quantity using ASP.Net UserControl in C# and VB.Net

Dear Micah,

Kindly refer below sample.

HTML

<div>
    <asp:ScriptManager ID="sm1" runat="server" />
    <asp:UpdatePanel ID="up1" runat="server">
        <ContentTemplate>
            <asp:ListBox ID="lbCategories" CssClass="form-control dual_select" runat="server" multiple=""
                AutoPostBack="true" OnSelectedIndexChanged="OnSelectedIndexChanged"></asp:ListBox>
            <hr />
            <div>
                <table>
                    <tr>
                        <td>
                            <asp:PlaceHolder ID="ph1" runat="server" />
                            <br />
                            <asp:Button ID="btnAdd" runat="server" Text="Add" />
                        </td>
                    </tr>
                </table>
            </div>
        </ContentTemplate>
    </asp:UpdatePanel>
    <br />
    <asp:Literal ID="ltlCount" runat="server" Text="0" Visible="false" />
    <asp:Literal ID="ltlRemoved" runat="server" Visible="false" />
    <br />
    <br />
    <asp:GridView runat="server" ID="gvInsertedRecords" />
</div>
<br />
<asp:Button Text="Save" runat="server" OnClick="OnSave" />

Namespaces

C#

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

VB.Net

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

Code

C#

protected void Page_Load(object sender, System.EventArgs e)
{
    if (!this.IsPostBack)
    {
        string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT TOP 4 CategoryID,CategoryName FROM Categories"))
            {
                cmd.CommandType = CommandType.Text;
                cmd.Connection = con;
                con.Open();
                lbCategories.DataSource = cmd.ExecuteReader();
                lbCategories.DataValueField = "CategoryID";
                lbCategories.DataTextField = "CategoryName";
                lbCategories.DataBind();
                con.Close();
            }
        }
    }
    AddAndRemoveDynamicControls();
}

private void AddAndRemoveDynamicControls()
{
    Control c = GetPostBackControl(Page);
    if ((c != null) && c.GetType() == typeof(Button))
    {
        if ((c as Button).Text != "Save")
        {
            if (c.ID.ToString() == "btnAdd")
            {
                ltlCount.Text = (Convert.ToInt16(ltlCount.Text) + 1).ToString();
            }
        }
    }
    ph1.Controls.Clear();
    int ControlID = 0;
    for (int i = 0; i <= (Convert.ToInt16(ltlCount.Text) - 1); i++)
    {
        UserControl DynamicUserControl = (UserControl)LoadControl("UserControl.ascx");
        while (InDeletedList("uc" + ControlID) == true)
        {
            ControlID += 1;
        }
        DynamicUserControl.ID = "uc" + ControlID;
        DynamicUserControl.RemoveUserControl += this.HandleRemoveUserControl;
        ph1.Controls.Add(DynamicUserControl);
        ControlID += 1;
    }
}

public void HandleRemoveUserControl(object sender, EventArgs e)
{
    Button remove = (sender as Button);
    UserControl DynamicUserControl = (UserControl)remove.Parent;
    ph1.Controls.Remove((UserControl)remove.Parent);
    ltlRemoved.Text += DynamicUserControl.ID + "|";
    ltlCount.Text = (Convert.ToInt16(ltlCount.Text) - 1).ToString();
}

private bool InDeletedList(string ControlID)
{
    string[] DeletedList = ltlRemoved.Text.Split('|');
    for (int i = 0; i <= DeletedList.GetLength(0) - 1; i++)
    {
        if (ControlID.ToLower() == DeletedList[i].ToLower())
        {
            return true;
        }
    }
    return false;
}

public Control GetPostBackControl(Page page)
{
    Control control = null;

    string ctrlname = page.Request.Params.Get("__EVENTTARGET");
    if ((ctrlname != null) & ctrlname != string.Empty)
    {
        control = page.FindControl(ctrlname);
    }
    else
    {
        foreach (string ctl in page.Request.Form)
        {
            Control c = page.FindControl(ctl);
            if (c is System.Web.UI.WebControls.Button)
            {
                control = c;
                break;
            }
        }
    }
    return control;
}

protected void OnSelectedIndexChanged(object sender, EventArgs e)
{
    int index = lbCategories.SelectedIndex;
    for (int i = 0; i < ph1.Controls.Count; i++)
    {
        for (int j = 0; j < ph1.Controls[i].Controls.Count; j++)
        {
            if (ph1.Controls[i].Controls[j].GetType() == typeof(TextBox))
            {
                if (j == 1)
                {
                    TextBox txt = ph1.Controls[i].Controls[j] as TextBox;
                    if (string.IsNullOrEmpty(txt.Text))
                    {
                        txt.Text = lbCategories.SelectedItem.Text;
                        return;
                    }
                }
            }
        }
    }
}

protected void OnSave(object sender, EventArgs e)
{
    DataTable dt = new DataTable();
    dt.Columns.AddRange(new DataColumn[] { new DataColumn("Item"), new DataColumn("Price"), new DataColumn("Quantity") });
    foreach (Control c in ph1.Controls)
    {
        if (c.GetType().Name.ToLower() == "usercontrol_ascx")
        {
            UserControl uc = (UserControl)c;
            TextBox tbItem = uc.FindControl("txtItem") as TextBox;
            TextBox tbPrice = uc.FindControl("txtPrice") as TextBox;
            TextBox tqty = uc.FindControl("txtQuantity") as TextBox;
            if (!string.IsNullOrEmpty(tbItem.Text.Trim()) && !string.IsNullOrEmpty(tbPrice.Text.Trim()) && !string.IsNullOrEmpty(tqty.Text.Trim()))
            {
                dt.Rows.Add(tbItem.Text.Trim(), tbPrice.Text.Trim(), tqty.Text.Trim());
                int inserted = Insert(tbItem.Text.Trim(), tbPrice.Text.Trim(), tqty.Text.Trim());
                if (inserted > 0)
                {
                    UpdateStock(tbItem.Text.Trim(), tqty.Text.Trim());
                }
            }
        }
    }
    gvInsertedRecords.DataSource = dt;
    gvInsertedRecords.DataBind();
}

private int Insert(string item, string price, string qty)
{
    int i = 0;
    string constr = ConfigurationManager.ConnectionStrings["DB"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        string query = "INSERT INTO tblStates2 VALUES (@Item, @Price, @Quantity, @Sum)";
        using (SqlCommand cmd = new SqlCommand(query))
        {
            cmd.Connection = con;
            cmd.Parameters.AddWithValue("@Item", item);
            cmd.Parameters.AddWithValue("@Price", price);
            cmd.Parameters.AddWithValue("@Quantity", qty);
            cmd.Parameters.AddWithValue("@Sum", Convert.ToDecimal(price) * Convert.ToDecimal(qty));
            con.Open();
            i = cmd.ExecuteNonQuery();
            con.Close();
        }
    }

    return i;
}

private void UpdateStock(string item, string qty)
{
    string constr = ConfigurationManager.ConnectionStrings["DB"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        string query = "UPDATE Stock SET Qty = Qty - @Quantity WHERE Item = @Item";
        using (SqlCommand cmd = new SqlCommand(query))
        {
            cmd.Connection = con;
            cmd.Parameters.AddWithValue("@Item", item);
            cmd.Parameters.AddWithValue("@Quantity", qty);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Using con As SqlConnection = New SqlConnection(constr)
            Using cmd As SqlCommand = New SqlCommand("SELECT TOP 4 CategoryID,CategoryName FROM Categories")
                cmd.CommandType = CommandType.Text
                cmd.Connection = con
                con.Open()
                lbCategories.DataSource = cmd.ExecuteReader()
                lbCategories.DataValueField = "CategoryID"
                lbCategories.DataTextField = "CategoryName"
                lbCategories.DataBind()
                con.Close()
            End Using
        End Using
    End If

    AddAndRemoveDynamicControls()
End Sub

Private Sub AddAndRemoveDynamicControls()
    Dim c As Control = GetPostBackControl(Page)

    If (c IsNot Nothing) AndAlso c.GetType() Is GetType(Button) Then
        If (TryCast(c, Button)).Text <> "Save" Then
            If c.ID.ToString() = "btnAdd" Then
                ltlCount.Text = (Convert.ToInt16(ltlCount.Text) + 1).ToString()
            End If
        End If
    End If

    ph1.Controls.Clear()
    Dim ControlID As Integer = 0
    For i As Integer = 0 To (Convert.ToInt16(ltlCount.Text) - 1)
        Dim DynamicUserControl As UserControl = CType(LoadControl("UserControl.ascx"), UserControl)
        While InDeletedList("uc" & ControlID) = True
            ControlID += 1
        End While
        DynamicUserControl.ID = "uc" & ControlID
        AddHandler DynamicUserControl.RemoveUserControl, AddressOf HandleRemoveUserControl
        ph1.Controls.Add(DynamicUserControl)
        ControlID += 1
    Next
End Sub

Public Sub HandleRemoveUserControl(ByVal sender As Object, ByVal e As EventArgs)
    Dim remove As Button = TryCast(sender, Button)
    Dim DynamicUserControl As UserControl = CType(remove.Parent, UserControl)
    ph1.Controls.Remove(CType(remove.Parent, UserControl))
    ltlRemoved.Text += DynamicUserControl.ID & "|"
    ltlCount.Text = (Convert.ToInt16(ltlCount.Text) - 1).ToString()
End Sub

Private Function InDeletedList(ByVal ControlID As String) As Boolean
    Dim DeletedList As String() = ltlRemoved.Text.Split("|"c)
    For i As Integer = 0 To DeletedList.GetLength(0) - 1
        If ControlID.ToLower() = DeletedList(i).ToLower() Then
            Return True
        End If
    Next

    Return False
End Function

Public Function GetPostBackControl(ByVal page As Page) As Control
    Dim control As Control = Nothing
    Dim ctrlname As String = page.Request.Params.[Get]("__EVENTTARGET")
    If (ctrlname IsNot Nothing) And ctrlname <> String.Empty Then
        control = page.FindControl(ctrlname)
    Else
        For Each ctl As String In page.Request.Form
            Dim c As Control = page.FindControl(ctl)
            If TypeOf c Is Button Then
                control = c
                Exit For
            End If
        Next
    End If
    Return control
End Function

Protected Sub OnSelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
    Dim index As Integer = lbCategories.SelectedIndex
    For i As Integer = 0 To ph1.Controls.Count - 1
        For j As Integer = 0 To ph1.Controls(i).Controls.Count - 1
            If ph1.Controls(i).Controls(j).GetType() Is GetType(TextBox) Then
                If j = 1 Then
                    Dim txt As TextBox = TryCast(ph1.Controls(i).Controls(j), TextBox)
                    If String.IsNullOrEmpty(txt.Text) Then
                        txt.Text = lbCategories.SelectedItem.Text
                        Return
                    End If
                End If
            End If
        Next
    Next
End Sub

Protected Sub OnSave(ByVal sender As Object, ByVal e As EventArgs)
    Dim dt As DataTable = New DataTable()
    dt.Columns.AddRange(New DataColumn() {New DataColumn("Item"), New DataColumn("Price"), New DataColumn("Quantity")})
    For Each c As Control In ph1.Controls
        If c.[GetType]().Name.ToLower() = "usercontrol_ascx" Then
            Dim uc As UserControl = CType(c, UserControl)
            Dim tbItem As TextBox = TryCast(uc.FindControl("txtItem"), TextBox)
            Dim tbPrice As TextBox = TryCast(uc.FindControl("txtPrice"), TextBox)
            Dim tqty As TextBox = TryCast(uc.FindControl("txtQuantity"), TextBox)
            If Not String.IsNullOrEmpty(tbItem.Text.Trim()) AndAlso Not String.IsNullOrEmpty(tbPrice.Text.Trim()) AndAlso Not String.IsNullOrEmpty(tqty.Text.Trim()) Then
                dt.Rows.Add(tbItem.Text.Trim(), tbPrice.Text.Trim(), tqty.Text.Trim())
                Dim inserted As Integer = Insert(tbItem.Text.Trim(), tbPrice.Text.Trim(), tqty.Text.Trim())
                If inserted > 0 Then
                    UpdateStock(tbItem.Text.Trim(), tqty.Text.Trim())
                End If
            End If
        End If
    Next

    gvInsertedRecords.DataSource = dt
    gvInsertedRecords.DataBind()
End Sub

Private Function Insert(ByVal item As String, ByVal price As String, ByVal qty As String) As Integer
    Dim i As Integer = 0
    Dim constr As String = ConfigurationManager.ConnectionStrings("DB").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Dim query As String = "INSERT INTO tblStates2 VALUES (@Item, @Price, @Quantity, @Sum)"
        Using cmd As SqlCommand = New SqlCommand(query)
            cmd.Connection = con
            cmd.Parameters.AddWithValue("@Item", item)
            cmd.Parameters.AddWithValue("@Price", price)
            cmd.Parameters.AddWithValue("@Quantity", qty)
            cmd.Parameters.AddWithValue("@Sum", Convert.ToDecimal(price) * Convert.ToDecimal(qty))
            con.Open()
            i = cmd.ExecuteNonQuery()
            con.Close()
        End Using
    End Using

    Return i
End Function

Private Sub UpdateStock(ByVal item As String, ByVal qty As String)
    Dim constr As String = ConfigurationManager.ConnectionStrings("DB").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Dim query As String = "UPDATE Stock SET Qty = Qty - @Quantity WHERE Item = @Item"
        Using cmd As SqlCommand = New SqlCommand(query)
            cmd.Connection = con
            cmd.Parameters.AddWithValue("@Item", item)
            cmd.Parameters.AddWithValue("@Quantity", qty)
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
        End Using
    End Using
End Sub