I want to fire an event when text change in a textbox in asp.net.
Something like database update. I used text change function is there any other way to do it so.
What I have tried:
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace ProductManagement
{
public partial class ProductEntry : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
loadDropdown();
GridView1.Visible = false;
}
}
public void loadDropdown()
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
con.Open();
string sql = "Select p_code, p_name from tblproductdetails";
SqlCommand cmd = new SqlCommand(sql, con);
SqlDataReader read = cmd.ExecuteReader();
while (read.Read())
{
ListItem listItem = new ListItem();
listItem.Value = read["p_code"].ToString();
listItem.Text = read["p_code"].ToString() + "-" + read["p_name"].ToString();
DropDownList1.Items.Add(listItem);
}
read.Close();
con.Close();
con.Dispose();
}
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
int a = Convert.ToInt32(DropDownList1.SelectedValue);
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
con.Open();
string sql = "Select p_price, p_discount from tblproductdetails where p_code= '" + a + "'";
SqlCommand cmd = new SqlCommand(sql, con);
SqlDataReader read = cmd.ExecuteReader();
while (read.Read())
{
TextBox2.Text = read["p_price"].ToString();
TextBox3.Text = read["p_discount"].ToString();
}
read.Close();
con.Close();
con.Dispose();
}
protected void Button1_Click(object sender, EventArgs e)
{
if (TextBox1.Text != "")
{
GridView1.Visible = true;
ObjectDataSource1.InsertParameters["pcode"].DefaultValue = DropDownList1.SelectedValue;
ObjectDataSource1.InsertParameters["quantity"].DefaultValue = TextBox1.Text;
ObjectDataSource1.InsertParameters["price"].DefaultValue = TextBox2.Text;
ObjectDataSource1.InsertParameters["discount"].DefaultValue = TextBox3.Text;
ObjectDataSource1.InsertParameters["total"].DefaultValue = TextBox4.Text;
ObjectDataSource1.Insert();
}
TextBox1.Text = null;
TextBox2.Text = null;
TextBox3.Text = null;
TextBox4.Text = null;
}
protected void Button2_Click(object sender, EventArgs e)
{
GridView1.Visible = false;
TextBox6.Text = null;
TextBox11.Text = null;
TextBox9.Text = null;
TextBox10.Text = null;
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
con.Open();
int maxorderno = GetMaxOrderNo(con) +1;
SqlTransaction tr = null;
tr = con.BeginTransaction();
string sql = "update tbltemporder set orderno = '"+ maxorderno +"' where orderno is null";
try
{
SqlCommand cmd = new SqlCommand(sql, con, tr);
cmd.ExecuteNonQuery();
tr.Commit();
con.Close();
}
catch (Exception ex)
{
tr.Rollback();
con.Close();
}
SqlConnection con2 = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
con2.Open();
string sql2 = "select p_code, quantity, price, discount, total from tbltemporder where orderno in(select max(orderno) from tbltemporder)";
SqlCommand cmd2 = new SqlCommand(sql2, con2);
SqlDataReader read2 = cmd2.ExecuteReader();
if (read2.HasRows)
{
DataTable dt = new DataTable();
dt.Load(read2);
GridView2.DataSource = dt;
GridView2.DataBind();
}
read2.Close();
con2.Close();
con2.Dispose();
SqlConnection con3 = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
con3.Open();
SqlCommand cmd3 = new SqlCommand("gettotal", con3);
cmd2.CommandType = CommandType.StoredProcedure;
SqlDataReader read3 = cmd3.ExecuteReader();
while(read3.Read())
{
TextBox7.Text = read3["total"].ToString();
TextBox8.Text = read3["discount"].ToString();
TextBox5.Text = read3["orderno"].ToString();
}
read3.Close();
con3.Close();
con3.Dispose();
}
private int GetMaxOrderNo(SqlConnection con)
{
int max = 0;
SqlDataReader read = (null);
string sql = "SELECT MAX(orderno)as orderno FROM tbltemporder";
SqlCommand comm = new SqlCommand(sql, con);
read = comm.ExecuteReader();
if (read.HasRows)
{
while (read.Read())
{
if(String.IsNullOrEmpty(read["orderno"].ToString()))
max = 0;
else
max = read.GetInt32(0);
}
}
read.Close();
return max;
}
protected void TextBox5_TextChanged(object sender, EventArgs e)
{
GridView1.Visible = false;
int x = Convert.ToInt32(TextBox5.Text);
SqlConnection con2 = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
con2.Open();
string sql2 = "select p_code, quantity, price, discount, total from tbltemporder where orderno = '"+x+"'";
SqlCommand cmd2 = new SqlCommand(sql2, con2);
SqlDataReader read2 = cmd2.ExecuteReader();
if (read2.HasRows)
{
DataTable dt = new DataTable();
dt.Load(read2);
GridView2.DataSource = dt;
GridView2.DataBind();
}
read2.Close();
con2.Close();
con2.Dispose();
SqlConnection con3 = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
con3.Open();
SqlCommand cmd3 = new SqlCommand("getordernototal", con3);
cmd3.CommandType = CommandType.StoredProcedure;
cmd3.Parameters.Add("@orderno", SqlDbType.Int).Value = x;
SqlDataReader read3 = cmd3.ExecuteReader();
while (read3.Read())
{
TextBox7.Text = read3["total"].ToString();
TextBox8.Text = read3["discount"].ToString();
}
read3.Close();
con3.Close();
con3.Dispose();
}
protected void TextBox6_TextChanged(object sender, EventArgs e)
{
GridView1.Visible = false;
SqlConnection con2 = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
con2.Open();
string sql2 = "select c_name from tblcustomer where c_code = '" + TextBox6.Text + "'";
SqlCommand cmd2 = new SqlCommand(sql2, con2);
SqlDataReader read2 = cmd2.ExecuteReader();
while(read2.Read())
{
TextBox11.Text = read2["c_name"].ToString();
}
read2.Close();
con2.Close();
con2.Dispose();
}
protected void TextBox9_TextChanged(object sender, EventArgs e)
{
GridView1.Visible = false;
int x = Convert.ToInt32(TextBox5.Text);
decimal y = Convert.ToDecimal(TextBox9.Text);
SqlConnection con3 = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
con3.Open();
SqlCommand cmd3 = new SqlCommand("spgetnettotal", con3);
cmd3.CommandType = CommandType.StoredProcedure;
cmd3.Parameters.Add("@orderno", SqlDbType.Int).Value = x;
cmd3.Parameters.Add("@taxrate", SqlDbType.Decimal).Value = y;
SqlDataReader read3 = cmd3.ExecuteReader();
while (read3.Read())
{
TextBox10.Text = read3["col1"].ToString();
}
con3.Close();
con3.Dispose();
}
}
}
I also used a data access layer seperately.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
namespace ProductManagement
{
public class TempOrder
{
public int p_code { get; set; }
public int quantity { get; set; }
public decimal price { get; set; }
public decimal discount { get; set; }
public decimal total { get; set; }
}
public class DataAccess
{
public static List<TempOrder> GetAllOrders()
{
List<TempOrder> listtemporder = new List<TempOrder>();
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
con.Open();
string sql = "select p_code, quantity, price, discount, total from tbltemporder where orderno is null";
SqlCommand cmd = new SqlCommand(sql, con);
SqlDataReader read = cmd.ExecuteReader();
while (read.Read())
{
TempOrder temporder = new TempOrder();
temporder.p_code = Convert.ToInt32(read["p_code"].ToString());
temporder.quantity = Convert.ToInt32(read["quantity"].ToString());
temporder.price = Convert.ToDecimal(read["price"].ToString());
temporder.discount = Convert.ToDecimal(read["discount"].ToString());
temporder.total = Convert.ToDecimal(read["total"].ToString());
listtemporder.Add(temporder);
}
return listtemporder;
}
public static void DeleteOrder(int pcode)
{
SqlTransaction tr = null;
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
con.Open();
tr = con.BeginTransaction();
string sql = "delete from tbltemporder where p_code = '" + pcode + "' and orderno is null";
try
{
SqlCommand cmd = new SqlCommand(sql, con, tr);
cmd.ExecuteNonQuery();
tr.Commit();
con.Close();
}
catch (Exception ex)
{
tr.Rollback();
con.Close();
}
}
public static int InsertOrder(int pcode, int quantity, decimal price, decimal discount, decimal total)
{
int i = 0;
SqlTransaction tr = null;
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
con.Open();
tr = con.BeginTransaction();
string sql = "insert into tbltemporder (p_code, quantity, price, discount, total) values (@p_code , @quantity , @price , @discount , @total)";
try
{
SqlCommand cmd = new SqlCommand(sql, con, tr);
SqlParameter paramName = new SqlParameter("@p_code", pcode);
cmd.Parameters.Add(paramName);
SqlParameter paramBirthday = new SqlParameter("@quantity", quantity);
cmd.Parameters.Add(paramBirthday);
SqlParameter paramRegdate = new SqlParameter("@price", price);
cmd.Parameters.Add(paramRegdate);
SqlParameter paramStream = new SqlParameter("@discount", discount);
cmd.Parameters.Add(paramStream);
SqlParameter paramReguser = new SqlParameter("@total", total);
cmd.Parameters.Add(paramReguser);
i = cmd.ExecuteNonQuery();
tr.Commit();
con.Close();
}
catch (Exception ex)
{
tr.Rollback();
con.Close();
}
return i;
}
public static int UpdateOrder(int quantity, decimal price, decimal discount, decimal total, int p_code)
{
int i = 0;
SqlTransaction tr = null;
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
con.Open();
tr = con.BeginTransaction();
string sql = "update tbltemporder set p_code = '" + p_code + "', quantity = '" + quantity + "', price = '" + price + "', discount = '" + discount + "', total='" + total + "' where orderno is null";
try
{
SqlCommand cmd = new SqlCommand(sql, con, tr);
i = cmd.ExecuteNonQuery();
tr.Commit();
con.Close();
}
catch (Exception ex)
{
tr.Rollback();
con.Close();
}
return i;
}
}
}
These are my stored procedures:
CREATE procedure [dbo].[spgetnettotal]
(@orderno int,
@taxrate decimal(18,2))
as
begin
declare @subtotal decimal(18,2),
@distotal decimal(18,2),
@Result decimal(18,2)
SET @subtotal = (
select sum(total) as total
from tbltemporder where orderno = @orderno
);
SET @distotal = (
select sum(discount) as discount
from tbltemporder where orderno = @orderno
);
set @Result = (@subtotal - @distotal) +((@subtotal - @distotal) * (@taxrate/100))
select * from
(values (@Result)) x(col1)
end
create procedure [dbo].[gettotal]
as
begin
select sum(total) as total, sum(discount)as discount, orderno
from tbltemporder
where orderno
in(select max(orderno) from tbltemporder)
group by orderno
end
create procedure [dbo].[getordernototal]
(@orderno int)
as
begin
select sum(total) as total, sum(discount) as discount from tbltemporder where orderno = @orderno
end
These are my scripts:
$(document).ready(function () {
$("#TextBox4").focus(function () {
var unitPrice = $("#TextBox2").val();
var qty = $("#TextBox1").val();
var discount = $("#TextBox3").val();
var total = (qty * unitPrice) - discount;
$(this).val(total).css("background-color", "#C4F3FE");
});
});
body {
}
#content {
width:900px;
margin: auto;
padding-left: 30px;
padding-top: 20px;
border: 3px solid #0C99B9;
background-color: #D6DFE1;
}
tr{
padding: 10px;
}
* {
box-sizing: border-box;
}
.column {
float: left;
width: 35%;
padding: 10px;
height: 300px;
}
.row:after {
content: "";
display: table;
clear: both;
}
This is the frontend code specific to the aspx.cs file:
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ProductEntry.aspx.cs" Inherits="ProductManagement.ProductEntry" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script src="scripts/stylejs.js"></script>
<link href="content/stylecss.css" rel="stylesheet" />
</head>
<body>
<form id="form1" runat="server">
<div id="content">
<table>
<tr>
<td>
<asp:Label ID="Label1" runat="server" Text="Select Products"></asp:Label></td>
<td>
<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="true" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged">
<asp:ListItem Text="Select Name" Value="" />
</asp:DropDownList>
</td>
</tr>
<tr>
<td><asp:Label ID="Label2" runat="server" Text="Type Quantity"></asp:Label></td>
<td> <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td><asp:Label ID="Label3" runat="server" Text="Price"></asp:Label></td>
<td>
<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td><asp:Label ID="Label4" runat="server" Text="Discount"></asp:Label></td>
<td>
<asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td><asp:Label ID="Label5" runat="server" Text="Total"></asp:Label></td>
<td>
<asp:TextBox ID="TextBox4" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td></td>
<td>
<asp:Button ID="Button1" runat="server" Text="Add to Order" OnClick="Button1_Click" />
</td>
</tr>
</table>
<span> </span>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="3" DataSourceID="ObjectDataSource1" Width="600px" BackColor="White" BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px">
<Columns>
<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
<asp:BoundField DataField="p_code" HeaderText="p_code" SortExpression="p_code" />
<%----%>
<asp:BoundField DataField="quantity" HeaderText="quantity" SortExpression="quantity" />
<asp:BoundField DataField="price" HeaderText="price" SortExpression="price" />
<asp:BoundField DataField="discount" HeaderText="discount" SortExpression="discount" />
<asp:BoundField DataField="total" HeaderText="total" SortExpression="total" />
</Columns>
<FooterStyle BackColor="White" ForeColor="#000066" />
<HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />
<RowStyle ForeColor="#000066" />
<SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" />
<SortedAscendingCellStyle BackColor="#F1F1F1" />
<SortedAscendingHeaderStyle BackColor="#007DBB" />
<SortedDescendingCellStyle BackColor="#CAC9C9" />
<SortedDescendingHeaderStyle BackColor="#00547E" />
</asp:GridView>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" DeleteMethod="DeleteOrder" InsertMethod="InsertOrder" SelectMethod="GetAllOrders" TypeName="ProductManagement.DataAccess" UpdateMethod="UpdateOrder">
<DeleteParameters>
<asp:Parameter Name="pcode" Type="Int32" />
</DeleteParameters>
<InsertParameters>
<asp:Parameter Name="pcode" Type="Int32" />
<asp:Parameter Name="quantity" Type="Int32" />
<asp:Parameter Name="price" Type="Decimal" />
<asp:Parameter Name="discount" Type="Decimal" />
<asp:Parameter Name="total" Type="Decimal" />
</InsertParameters>
<UpdateParameters>
<%----%>
<asp:Parameter Name="quantity" Type="Int32" />
<asp:Parameter Name="price" Type="Decimal" />
<asp:Parameter Name="discount" Type="Decimal" />
<asp:Parameter Name="total" Type="Decimal" />
</UpdateParameters>
</asp:ObjectDataSource>
<br />
<asp:Button ID="Button2" runat="server" Text="Place Order" OnClick="Button2_Click" />
<br />
<hr/>
<h3>Customer Order</h3>
<table>
<tr>
<td>
<asp:Label ID="Label6" runat="server" Text="Order No"></asp:Label>
</td>
<td>
<asp:TextBox ID="TextBox5" runat="server" OnTextChanged="TextBox5_TextChanged"></asp:TextBox>
</td>
<td>
</td>
<td>
</td>
</tr>
<tr>
<td>
<asp:Label ID="Label7" runat="server" Text="Customer Code"></asp:Label>
</td>
<td>
<asp:TextBox ID="TextBox6" runat="server" OnTextChanged="TextBox6_TextChanged"></asp:TextBox>
</td>
<td>
<asp:Label ID="Label12" runat="server" Text="Customer Name"></asp:Label>
</td>
<td>
<asp:TextBox ID="TextBox11" runat="server"></asp:TextBox>
</td>
</tr>
</table>
<span> </span>
<%----%>
<asp:GridView ID="GridView2" runat="server" CellPadding="3" Width="600px" BackColor="White" BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px">
<FooterStyle BackColor="White" ForeColor="#000066" />
<HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />
<RowStyle ForeColor="#000066" />
<SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" />
<SortedAscendingCellStyle BackColor="#F1F1F1" />
<SortedAscendingHeaderStyle BackColor="#007DBB" />
<SortedDescendingCellStyle BackColor="#CAC9C9" />
<SortedDescendingHeaderStyle BackColor="#00547E" />
</asp:GridView>
<div class="row">
<div class="column"></div>
<div class="column">
<table style="float:left">
<tr>
<td>
<asp:Label ID="Label8" runat="server" Text="Sub Total"></asp:Label>
</td>
<td>
<asp:TextBox ID="TextBox7" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
<asp:Label ID="Label9" runat="server" Text="Discount Total"></asp:Label>
</td>
<td>
<asp:TextBox ID="TextBox8" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
<asp:Label ID="Label10" runat="server" Text="Tax Rate"></asp:Label>
</td>
<td>
<asp:TextBox ID="TextBox9" runat="server" OnTextChanged="TextBox9_TextChanged"></asp:TextBox>
</td>
</tr>
<tr>
<td>
<asp:Label ID="Label11" runat="server" Text="Net Total"></asp:Label>
</td>
<td>
<asp:TextBox ID="TextBox10" runat="server"></asp:TextBox>
</td>
</tr>
</table>
</div>
</div>
</div>
</form>
</body>
</html>