I have a few Textboxes on my receipt form that represent VAT, Withholding Tax and Stamp Duty. But when the values of these textboxes are empty data cannot be inserted into database. How can I make it that if these textboxes are empty null values will be inserted and if these textboxes also have values in them, the values will be inserted as well?
Finally, each time I change CurrencyDropDown Selected Value from DropDownList control, the Amount Label inside Gridview, Total label and GrandTotal label values disappear or even when I delete an Item from GridView using the trash icon in the GridView row, the Last row Amount, Total and GrandTotal labels change.
Please I will like to have a perfect experience and let every value I type remain unchanged even there is a postback o the page.
Is this possible?
Here is my insert code (C#) and HTML
HTML
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<style>
.page { width: 300px; height: 350px; position: absolute; margin: auto; }
.parent { margin-left: auto; margin-right: auto; padding: 5px; display: flex; background-color: #ffffff; width: 100%; }
@media screen and (min-width: 360px) and (max-width:640px) {
.parent { width: 100%; }
}
h3 { font-size: 2.2rem; margin: 0 auto; font-weight: 500; line-height: normal; vertical-align: baseline; letter-spacing: 0px; word-spacing: 0px; text-align: start; }
@media screen and (max-width: 600px) {
h3 { font-size: 1.8rem; }
}
.child { width: 100%; height: auto; padding: 4px; }
#midcont2 { position: relative; background-color: #ffffff; border: 1px solid #ccc; }
.dsign { float: left; width: 350px; }
.currency-symbol { font-size: 11pt; }
#GrandTotal { font-weight: 500; font-size: 11pt; }
.grid-corner { -webkit-border-radius: 4px; -moz-border-radius: 4px; border-radius: 5px; overflow-x: scroll; }
.optional { font-size: 0.6rem; color: #fc610f; font-weight: 400; }
</style>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/js/bootstrap.min.js" integrity="sha384-ChfqqxuZUCnJSK3+MXmPNIyE6ZbWh2IMqE241rYiqJxyMiZ6OW/JmZQ5stwEULTy" crossorigin="anonymous"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.0/umd/popper.min.js" integrity="sha384-cs/chFZiN24E4KMATLdqdvsezGxaGsi4hLGOzlXwp5UZB1LY//20VyM2taTB4QvJ" crossorigin="anonymous"></script>
<script type="text/javascript">
$(function () {
CalculateTotal();
});
$("body").on("change keyup", "[id*=Quantity],[id*=Rate]", function () {
var row = $(this).closest("tr");
//Check whether Quantity value is valid Float number.
var quantity = parseFloat($.trim($(row).find("[id*=Quantity]").val()));
if (isNaN(quantity)) {
quantity = 0;
}
var rate = parseFloat($.trim($(row).find("[id*=Rate]").val()));
if (isNaN(rate)) {
rate = 0;
}
//Calculate and update Row Total.
$("[id*=Amount]", row).html(numberWithCommas(parseFloat(quantity * rate).toFixed(2)));
CalculateTotal();
});
function numberWithCommas(x) {
return x.toString().replace(/\B(?=(\d{3})+(?!\d))/g, ",");
}
$("body").on("change keyup", "[id*=VAT],[id*=WhTax],[id*=Stampduty]", function () {
CalculateTotal()
});
function CalculateTotal() {
//Calculate and update Grand Total.
var grandTotal = 0;
$("[id*=Amount]").each(function () {
var amount = 0;
if (!isNaN($(this).html().replace(/,/g, "")) && $(this).html().replace(/,/g, "") != "") {
amount = $(this).html().replace(/,/g, "");
}
grandTotal = grandTotal + parseFloat(amount);
});
$("[id*=Total]").html(numberWithCommas(parseFloat(grandTotal).toFixed(2)));
var wht = $("[id*=WhTax]").val() == "" || $("[id*=WhTax]").val() == undefined ? "0" : parseInt($("[id*=WhTax]").val()) / 100;
var std = $("[id*=Stampduty]").val() == "" || $("[id*=Stampduty]").val() == undefined ? "0" : parseInt($("[id*=Stampduty]").val()) / 100;
var vat = $("[id*=VAT]").val() == "" || $("[id*=VAT]").val() == undefined ? "0" : parseInt($("[id*=VAT]").val()) / 100;
grandTotal = grandTotal + (grandTotal * vat) + (grandTotal * wht) + (grandTotal * std);
$("[id*=GrandTotal]").html(numberWithCommas(parseFloat(grandTotal).toFixed(2)));
}
</script>
</head>
<body>
<form id="form1" runat="server">
<asp:ScriptManager ID="scriptManager1" runat="server"></asp:ScriptManager>
<div class="col-sm-6" style="width: 100%; margin: 0 auto;">
<div class="parent" style="margin-left: auto; margin-right: auto; padding: 6px; display: flex; background-color: #ffffff; width: 100%; height: auto;">
<div class="child" id="midcont2" style="width: 100%; height: auto; border: none;">
<div class="grid-corner" style="width: 100%; background-color: white; margin: 0 auto; padding: 5px;">
<asp:UpdatePanel ID="panelcurrency" runat="server" Font-Size="9pt" UpdateMode="Conditional">
<ContentTemplate>
<div style="margin-bottom: 0.5%; padding: 10px;">
<h3 style="color: #145c7c;">Receipt</h3>
<hr style="margin-top: 0;" />
<div class="">
<br />
<label class="info-head">Your Information</label>
<hr style="margin-top: 0;" />
<div class="row">
<div class="col-md-12">
<div class="form-group">
<div class="input-group" style="font-weight: 500; font-size: 14pt;">
<asp:Label ID="FromName" runat="server" ForeColor="#40576d" Text="" />
</div>
</div>
</div>
</div>
<div class="row">
<div class="col-md-12">
<div class="form-group">
<label runat="server" id="Label17">Email</label>
<div class="input-group">
<asp:TextBox ID="FromMail" runat="server" CssClass="form-control" placeholder="Email Address" BorderStyle="Solid" BorderWidth="1"></asp:TextBox>
</div>
</div>
</div>
</div>
<hr />
<label class="info-head">Customer Information</label>
<br />
<div class="row">
<div class="col-md-7">
<div class="form-group">
<label runat="server" id="Label13">Name</label>
<div class="input-group">
<asp:TextBox ID="ToName" runat="server" CssClass="form-control" Style="text-transform: capitalize;" BorderStyle="Solid" BorderWidth="1" />
</div>
</div>
</div>
<div class="col-md-5"></div>
</div>
<div class="row">
<div class="col-md-12">
<div class="form-group">
<label runat="server" id="Label20">Email</label>
<div class="input-group">
<asp:TextBox ID="ToMail" runat="server" CssClass="form-control" BorderStyle="Solid" BorderWidth="1"></asp:TextBox>
</div>
</div>
</div>
</div>
<div class="col-md-6">
<div class="form-group">
<label runat="server" id="Label22">Select preferrd currency</label>
<div class="input-group">
<asp:DropDownList ID="CurrencyDropDown" runat="server" CssClass="form-control" AutoPostBack="true" OnSelectedIndexChanged="CurrencyDropDown_SelectedIndexChanged">
</asp:DropDownList>
</div>
</div>
</div>
</div>
<br />
</div>
<br />
<asp:GridView ID="Gridview1" runat="server" Font-Size="10pt" HeaderStyle-Font-Size="10pt" CellPadding="5" GridLines="None" ShowFooter="True" AutoGenerateColumns="False" HeaderStyle-Font-Bold="false"
Style="width: 100%" FooterStyle-BackColor="#fdfdfd" HeaderStyle-ForeColor="#000000" Height="50px" HeaderStyle-Height="10px">
<Columns>
<asp:TemplateField HeaderText="Description" ItemStyle-Width="45%" HeaderStyle-Font-Bold="false">
<ItemTemplate>
<asp:TextBox ID="textBox1" runat="server" Class="form-control" Width="100%" Font-Size="10pt" Style="overflow: hidden; text-transform: capitalize; resize: none;" />
</ItemTemplate>
<FooterStyle HorizontalAlign="Left" VerticalAlign="Bottom" />
<FooterTemplate>
<asp:LinkButton ID="AddNew" runat="server" Font-Size="10pt" ForeColor="#145c7c" OnCommand="ButtonAdd_Command">New item</asp:LinkButton>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Quantity" ItemStyle-Width="15%" HeaderStyle-Font-Bold="false">
<ItemTemplate>
<asp:TextBox CssClass="form-control" ID="Quantity" Font-Size="10pt" runat="server" Width="100%" />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Unit" ItemStyle-Width="20%" HeaderStyle-Font-Bold="false">
<ItemTemplate>
<asp:Label class="currency-symbol" runat="server" ID="symbolA" Style="font-weight: 400;"></asp:Label>
<asp:TextBox ID="Rate" runat="server" Font-Size="10pt" Width="90%" CssClass="form-control" placeholder="0.00" />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Amount" ItemStyle-Width="50%" HeaderStyle-Font-Bold="false">
<ItemTemplate>
<asp:Label class="currency-symbol" runat="server" ID="symbolB" Style="font-weight: 400;"></asp:Label>
<asp:Label ID="Amount" runat="server" Text="0.00" Font-Size="10pt"></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<asp:LinkButton ID="Remove" ForeColor="Red" runat="server">
<i class="fad fa-trash" aria-hidden="true" style="margin: 0 7px; font-size: 1.4rem; color: #0b2436;"></i>
</asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<HeaderStyle Height="10px" />
</asp:GridView>
<hr />
<div runat="server" style="width: 100%; margin: 0 auto; font-weight: 500; margin-bottom: 1%;">
<div class="total" style="margin-right: 0%; padding: 6px;">
<div class="row">
<div class="col-md-6"></div>
<div class="col-md-6">
<div class="form-group">
<asp:Label ID="Label1" runat="server" Font-Size="10pt" Text="Subtotal"></asp:Label>
<asp:Label ID="warnlbl" runat="server" Font-Size="6pt"></asp:Label>
<div class="input-group">
<asp:Label ID="currency" class="currency-symbol" runat="server" Text=""></asp:Label>
<asp:Label ID="Total" runat="server" Font-Size="10pt" Text=""></asp:Label>
</div>
</div>
</div>
</div>
<div class="row">
<div class="col-md-6"></div>
<div class="col-md-6">
<div class="form-group">
<asp:Label ID="Label9" runat="server" Text="Add Vat"></asp:Label>
<label class="optional">(optional)</label>
<div class="input-group">
<div class="input-group-append">
<span class="input-group-text" style="color: #2d6193; border: none; background-color: #fff; border: 0.5px solid #ccc; border-radius: 3px; border-top-right-radius: unset; border-bottom-right-radius: unset;">
<span>%</span>
</span>
</div>
<asp:TextBox ID="VAT" runat="server" CssClass="form-control" Width="100px" Text="0" Font-Size="9pt" onkeypress="return onlyNumbersWithDot(event);" />
</div>
</div>
</div>
</div>
<div class="row">
<div class="col-md-6"></div>
<div class="col-md-6">
<div class="form-group">
<asp:Label ID="Label2" runat="server" Text="Withholding tax"></asp:Label>
<label class="optional">(optional)</label>
<div class="input-group">
<div class="input-group-append">
<span class="input-group-text" style="color: #2d6193; border: none; background: #fff; border: 0.5px solid #ccc; border-radius: 4px; border-top-right-radius: unset; border-bottom-right-radius: unset;">
<span>%</span>
</span>
</div>
<asp:TextBox ID="WhTax" runat="server" CssClass="form-control" Width="100px" Text="0" Font-Size="9pt" onkeypress="return onlyNumbersWithDot(event);" />
</div>
</div>
</div>
</div>
<div class="row">
<div class="col-md-6"></div>
<div class="col-md-6">
<div class="form-group">
<asp:Label ID="Label4" runat="server" Text="Stamp Duty"></asp:Label>
<label class="optional">(optional)</label>
<div class="input-group">
<div class="input-group-append">
<span class="input-group-text" style="color: #2d6193; border: none; background-color: #fff; border: 0.5px solid #ccc; border-radius: 3px; border-top-right-radius: unset; border-bottom-right-radius: unset;">
<span>%</span>
</span>
</div>
<asp:TextBox ID="Stampduty" runat="server" CssClass="form-control" Width="100px" Text="0" Font-Size="9pt" onkeypress="return onlyNumbersWithDot(event);" />
</div>
</div>
</div>
</div>
<div class="row">
<div class="col-md-6"></div>
<div class="col-md-6">
<div class="form-group">
<asp:Label ID="Label25" runat="server" Font-Size="11pt" Text="Total"></asp:Label>
<div class="input-group">
<asp:Label class="currency-symbol" ID="curcy" runat="server" Text=""></asp:Label>
<asp:Label ID="GrandTotal" Font-Size="11pt" runat="server" Text=""></asp:Label>
</div>
</div>
</div>
</div>
</div>
</div>
</ContentTemplate>
<Triggers>
<asp:AsyncPostBackTrigger ControlID="CurrencyDropDown" EventName="SelectedIndexChanged" />
</Triggers>
</asp:UpdatePanel>
<br />
</div>
</div>
</div>
<div style="padding: 10px; display: flex; width: 100%; height: auto;">
<asp:Button ID="Button1" runat="server" CssClass="btn btn-primary" Font-Size="11pt" BackColor="#32657c" Text="Insert" OnClick="Button1_Click" />
</div>
</div>
</form>
</body>
</html>
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
SetInitialRow();
DropDownCurrency();
}
}
private void DropDownCurrency()
{
using (SqlConnection con = new SqlConnection())
{
con.ConnectionString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
using (SqlCommand cmd = new SqlCommand("SELECT Symbol FROM TableCurrency", con))
{
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
con.Open();
CurrencyDropDown.DataSource = cmd.ExecuteReader();
CurrencyDropDown.DataTextField = "Symbol";
CurrencyDropDown.DataValueField = "Symbol";
CurrencyDropDown.DataBind();
con.Close();
CurrencyDropDown.Items.Insert(0, new ListItem("Select Symbol", " "));
CurrencyDropDown.Items[0].Selected = true;
CurrencyDropDown.Items[0].Attributes["disabled"] = "disabled";
}
}
}
protected void CurrencyDropDown_SelectedIndexChanged(object sender, EventArgs e)
{
CurrencyDropDown.SelectedItem.Text = CurrencyDropDown.SelectedItem.Text.ToString();
currency.Text = CurrencyDropDown.SelectedItem.Value.ToString();
curcy.Text = CurrencyDropDown.SelectedItem.Value.ToString();
foreach (GridViewRow row in Gridview1.Rows)
{
if (row.RowType == DataControlRowType.DataRow)
{
Label lbl2 = (Label)row.FindControl("symbolB");
lbl2.Text = CurrencyDropDown.SelectedItem.Value.ToString();
}
}
}
private void SetInitialRow()
{
DataTable dt = new DataTable();
DataRow dr = null;
dt.Columns.Add(new DataColumn("RowNumber", typeof(string)));
dt.Columns.Add(new DataColumn("Column1", typeof(string)));
dt.Columns.Add(new DataColumn("Column2", typeof(string)));
dt.Columns.Add(new DataColumn("Column3", typeof(string)));
dt.Columns.Add(new DataColumn("Total", typeof(string)));
dr = dt.NewRow();
dr["RowNumber"] = 1;
dr["Column1"] = string.Empty;
dr["Column2"] = string.Empty;
dr["Column3"] = string.Empty;
dr["Total"] = string.Empty;
dt.Rows.Add(dr);
ViewState["CurrentTable"] = dt;
Gridview1.DataSource = dt;
Gridview1.DataBind();
}
private void AddNewRowToGrid()
{
int rowIndex = 0;
if (ViewState["CurrentTable"] != null)
{
DataTable dtCurrentTable = (DataTable)ViewState["CurrentTable"];
DataRow drCurrentRow = null;
if (dtCurrentTable.Rows.Count > 0)
{
for (int i = 1; i <= dtCurrentTable.Rows.Count; i++)
{
TextBox box1 = (TextBox)Gridview1.Rows[rowIndex].Cells[1].FindControl("TextBox1");
TextBox box2 = (TextBox)Gridview1.Rows[rowIndex].Cells[2].FindControl("Quantity");
TextBox box3 = (TextBox)Gridview1.Rows[rowIndex].Cells[3].FindControl("Rate");
Label lblTtotal = (Label)Gridview1.Rows[rowIndex].Cells[4].FindControl("Amount");
drCurrentRow = dtCurrentTable.NewRow();
drCurrentRow["RowNumber"] = i + 1;
drCurrentRow["Column1"] = box1.Text;
drCurrentRow["Column2"] = box2.Text;
drCurrentRow["Column3"] = box3.Text;
drCurrentRow["Total"] = string.Format("{0:#,0.00}", (Convert.ToDecimal(box2.Text) * Convert.ToDecimal(box3.Text)));
rowIndex++;
}
dtCurrentTable.Rows.Add(drCurrentRow);
ViewState["CurrentTable"] = dtCurrentTable;
Gridview1.DataSource = dtCurrentTable;
Gridview1.DataBind();
}
}
else
{
ScriptManager.RegisterStartupScript(this, GetType(), "Message", "alert('Empty Data');", true);
}
SetPreviousData();
}
private void SetPreviousData()
{
int rowIndex = 0;
if (ViewState["CurrentTable"] != null)
{
DataTable dt = (DataTable)ViewState["CurrentTable"];
if (dt.Rows.Count > 0)
{
for (int i = 1; i < dt.Rows.Count; i++)
{
TextBox box1 = (TextBox)Gridview1.Rows[rowIndex].Cells[1].FindControl("TextBox1");
TextBox box2 = (TextBox)Gridview1.Rows[rowIndex].Cells[2].FindControl("Quantity");
TextBox box3 = (TextBox)Gridview1.Rows[rowIndex].Cells[3].FindControl("Rate");
Label lblTtotal = (Label)Gridview1.Rows[rowIndex].Cells[4].FindControl("Amount");
box1.Text = dt.Rows[i]["Column1"].ToString();
box2.Text = dt.Rows[i]["Column2"].ToString();
box3.Text = dt.Rows[i]["Column3"].ToString();
lblTtotal.Text = dt.Rows[i]["Total"].ToString();
rowIndex++;
}
}
ViewState["CurrentTable"] = dt;
}
}
protected void ButtonAdd_Command(Object sender, CommandEventArgs e)
{
AddNewRowToGrid();
}
protected void Button1_Click(object sender, EventArgs e)
{
try
{
if (!string.IsNullOrEmpty(VAT.Text) & !string.IsNullOrEmpty(WhTax.Text) & !string.IsNullOrEmpty(Stampduty.Text) & !string.IsNullOrEmpty(FromMail.Text) & !string.IsNullOrEmpty(ToName.Text) & !string.IsNullOrEmpty(ToMail.Text))
{
using (SqlConnection con = new SqlConnection())
{
con.ConnectionString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
int rowIndex = 0;
StringCollection sc = new StringCollection();
if (ViewState["CurrentTable"] != null)
{
DataTable dtCurrentTable = (DataTable)ViewState["CurrentTable"];
if (dtCurrentTable.Rows.Count > 0)
{
for (int i = 1; i <= dtCurrentTable.Rows.Count; i++)
{
TextBox box1 = (TextBox)Gridview1.Rows[rowIndex].Cells[1].FindControl("TextBox1");
TextBox box2 = (TextBox)Gridview1.Rows[rowIndex].Cells[2].FindControl("Quantity");
TextBox box3 = (TextBox)Gridview1.Rows[rowIndex].Cells[3].FindControl("Rate");
Label total = (Label)Gridview1.Rows[rowIndex].Cells[4].FindControl("Amount");
if (!string.IsNullOrEmpty(box1.Text) && !string.IsNullOrEmpty(box2.Text)
&& !string.IsNullOrEmpty(box3.Text))
{
sc.Add(box1.Text + "," + box2.Text + "," + box3.Text + "," + total.Text);
}
else
{
ScriptManager.RegisterStartupScript(this, GetType(), "Message", "alert('Empty Row Data');", true);
}
rowIndex++;
}
InsertRecords(sc);
}
}
}
}
else
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('You have empty values in some fields');", true);
}
}
catch (SqlException ex)
{
string msg = "Error:";
msg += ex.Message;
throw new Exception(msg);
}
}
private void InsertRecords(StringCollection sc)
{
if (sc.Count == 0)
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('row is empty');", true);
}
else
{
StringBuilder sb = new StringBuilder(string.Empty);
string[] splitItems = null;
List<string> item = new List<string>();
List<string> quantity = new List<string>();
List<decimal> rate = new List<decimal>();
List<decimal> amount = new List<decimal>();
foreach (string Item in sc)
{
if (Item.Contains(","))
{
splitItems = Item.Split(",".ToCharArray());
item.Add(splitItems[0]);
quantity.Add(splitItems[1]);
rate.Add(Convert.ToDecimal(string.Format("{0:N2}", (Convert.ToDecimal(splitItems[1]) * Convert.ToDecimal(splitItems[2])))));
amount.Add(Convert.ToDecimal(string.Format("{0:N2}", (Convert.ToDecimal(splitItems[1]) * Convert.ToDecimal(splitItems[2])))));
}
}
if (!string.IsNullOrEmpty(CurrencyDropDown.SelectedItem.Value))
{
using (SqlConnection con = new SqlConnection())
{
con.ConnectionString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
try
{
decimal total = amount.Sum();
//This cannot insert when the VAT, WhTax and Stampduty Textboxes are empty.
decimal grandTotal = total + (total * (Convert.ToDecimal(VAT.Text) + Convert.ToDecimal(WhTax.Text) + Convert.ToDecimal(Stampduty.Text)) / 100);
string sqlStatement = "INSERT INTO RECEIPTS (fname, fmail, Tname, Tmail, Item, Qty, Rate, Amount, Total, holdtax, stampD, VAT, GrandTotal, CSymbol) VALUES (@fname, @fmail, @Tname, @Tmail, @Item, @Qty, @Rate, @Amount, @Total, @holdtax, @stampD, @VAT, @GrandTotal, @CSymbol)";
con.Open();
SqlCommand cmd = new SqlCommand(sqlStatement, con);
cmd.Parameters.AddWithValue("@fname", FromName.Text.ToString());
cmd.Parameters.AddWithValue("@fmail", FromMail.Text.ToString());
cmd.Parameters.AddWithValue("@Tname", ToName.Text.ToString());
cmd.Parameters.AddWithValue("@Tmail", ToMail.Text.ToString());
cmd.Parameters.AddWithValue("@Item", string.Join(",", item.ToArray()));
cmd.Parameters.AddWithValue("@Qty", string.Join(",", quantity.ToArray()));
cmd.Parameters.AddWithValue("@Rate", string.Join(",", rate.ToArray()));
cmd.Parameters.AddWithValue("@Amount", string.Join(",", amount.ToArray()));
cmd.Parameters.AddWithValue("@Total", total);
cmd.Parameters.AddWithValue("@holdtax", WhTax.Text.ToString());
cmd.Parameters.AddWithValue("@stampD", Stampduty.Text.ToString());
cmd.Parameters.AddWithValue("@VAT", VAT.Text.ToString());
cmd.Parameters.AddWithValue("@GrandTotal", grandTotal);
cmd.Parameters.AddWithValue("@CSymbol", CurrencyDropDown.SelectedValue);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
string msg = "Insert Error:";
msg += ex.Message;
throw new Exception(msg);
}
finally
{
con.Close();
}
}
}
else
{
ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert('Choose currency');", true);
}
}
}
