Tomk01 says:
Hi,
I have one label , 2 text boxes
TextBox1 = From Date
TextBox2 = To Date
Label 1=To display the Freight
Button=Submit asp Button
My sql query is as follows
select sum(Freight) as TotalFreight from Orders where orderdate > @TextBox1 and OrderDate <@TextBox2
How can we display the total freight through button click in label using jquery ajax.
Please Help
Thanks
Refer the Below sample code for your reference . I used NorthWind Database for this sample.
SQL
CREATE PROCEDURE CalcualteTotalFreightAmount
@FromDate DATETIME
,@Todate DateTime
AS
BEGIN
SELECT Sum(Freight) FROM Orders
WHERE OrderDate Between @FromDate AND @Todate
END
GO
HTML
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
<script type="text/javascript" src="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.2/js/bootstrap.min.js"></script>
<link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.2/css/bootstrap.min.css" />
<script type="text/javascript" src="http://cdnjs.cloudflare.com/ajax/libs/json2/20130526/json2.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-datepicker/1.6.4/js/bootstrap-datepicker.min.js"></script>
<link rel="Stylesheet" type="text/css" href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-datepicker/1.6.4/css/bootstrap-datepicker.css" />
<script type="text/javascript">
$(function () {
$("[id*=txtFromDate],[id*=txtToDate]").datepicker({});
$("[id*=btnCalculateTotal]").on("click", function () {
CalculateTotal();
return false;
});
});
function CalculateTotal() {
var fromDate = jQuery.trim($("[id*=txtFromDate]").val());
var toDate = jQuery.trim($("[id*=txtToDate]").val());
var obj = {};
obj.fromdate = fromDate;
obj.todate = toDate;
$.ajax({
type: "POST",
url: "CalculateTotalFreightBydateUsingJquery.aspx/CalcualteTotalFreightAmount",
data: JSON.stringify(obj),
contentType: "application/json; charset=utf-8",
dataType: "json",
success: OnSuccess,
failure: function (response) {
alert(response.d);
},
error: function (response) {
alert(response.responseText);
}
});
}
function OnSuccess(response) {
$('[Id*=lblTotalAmount]')[0].innerHTML = response.d;
}
</script>
</head>
<body>
<form id="form1" runat="server">
<div>
<div class="row">
<div class="col-md-4">
</div>
</div>
<div class="row">
<div class="col-md-2">
From Date:
</div>
<div class="col-md-2">
<asp:TextBox ID="txtFromDate" runat="server" CssClass="form-control" ReadOnly="true"></asp:TextBox>
</div>
</div>
<div class="row">
<div class="col-md-4">
</div>
</div>
<div class="row">
<div class="col-md-2">
To Date:
</div>
<div class="col-md-2">
<asp:TextBox ID="txtToDate" runat="server" CssClass="form-control" ReadOnly="true"></asp:TextBox>
</div>
</div>
<div class="row">
<div class="col-md-4">
</div>
</div>
<div class="row">
<div class="col-md-2">
Total Freight Amount:
</div>
<div class="col-md-2">
<asp:Label ID="lblTotalAmount" runat="server"></asp:Label>
</div>
</div>
<div class="row">
<div class="col-md-4">
</div>
</div>
<div class="row">
<div class="col-md-4">
<asp:Button ID="btnCalculateTotal" runat="server" Text="Calculate" CssClass="btn btn-primary" />
</div>
</div>
</div>
</form>
</body>
</html>
C#
[WebMethod]
public static double CalcualteTotalFreightAmount(DateTime fromdate, DateTime todate)
{
double TotalFreightAmt;
string connectionString = ConfigurationManager.ConnectionStrings["ConStr1"].ConnectionString;
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("CalcualteTotalFreightAmount");
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@FromDate", fromdate);
cmd.Parameters.AddWithValue("@Todate", todate);
con.Open();
object totalFreightAmount = cmd.ExecuteScalar();
if (totalFreightAmount != null && totalFreightAmount != DBNull.Value)
{
TotalFreightAmt = Convert.ToDouble(totalFreightAmount);
}
else
{
TotalFreightAmt = 0;
}
return TotalFreightAmt;
}
ScreenShot
