Good Day! I am a newbie on ASP.NET (MVC) and trying to display a value (from a Stored Procedure) after the value of the date has been changed. This is what it looks like on the design Once the date is picked, the user will click the Create button and the value should be displayed on the "Sales" textbox
This is my Stored Procedure for it
CREATE PROCEDURE [dbo].[SP_DAILY_SALES]
-- Add the parameters for the stored procedure here
@Order_date date
AS
BEGIN TRANSACTION;
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
BEGIN TRY
SET NOCOUNT OFF;
DECLARE @SumTable table
(
dailySales decimal (18,2)
)
DECLARE @dailySales decimal(18,2)
SET @dailySales = (
SELECT SUM(NET_AMOUNT)
FROM [ORDER]
WHERE ORDER_DATE=@Order_date)
INSERT INTO @SumTable (dailySales)
VALUES (@dailySales)
-- Insert statements for procedure here
SELECT dailySales
FROM @SumTable
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
END CATCH;and this is where I call it (Repository)
public bool DisplayDailySales(DateTime orderdate)
{
connection();
SqlCommand com = new SqlCommand("SP_DAILY_SALES", con);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.Add("@Order_date", SqlDbType.DateTime).Value = orderdate;
con.Open();
int i = com.ExecuteNonQuery();
con.Close();
if (i >= 1)
{
return true;
}
else
{
return false;
}
}My Controller
[HttpGet]
public ActionResult Statistics()
{
OrderVM dSales = new OrderVM();
var currentdate = DateTime.Now;
dSales.ORDER_DATE = currentdate;
return View();
}
[HttpPost]
public ActionResult Statistics(OrderVM order)
{
try
{
DateTime orderdate = order.ORDER_DATE;
ViewData["ORDERDATE"] = orderdate;
if (ModelState.IsValid)
{
OrderRepository orderRepo = new OrderRepository();
orderRepo.DisplayDailySales(orderdate);
}
return View();
}
catch
{
return View();
}
}And lastly, in my view as of now is this
<div class="panel-body">
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
@Html.ValidationSummary(true)
<div class="form-horizontal">
<div class="form-group">
@Html.LabelFor(model => model.ORDER_DATE, "Date", htmlAttributes: new { @class = "col-xs-4 col-sm-offset-1 col-sm-4" })
<div class="col-lg-6 col-lg-6 col-md-6 col-md-6 col-sm-6 col-sm-6 col-xs-6 col-xs-6">
@Html.TextBoxFor(model => model.ORDER_DATE, "{0:yyyy-MM-dd}", new { @class = "form-control", @type = "date", @id = "Orderdate" })
@Html.ValidationMessageFor(model => model.ORDER_DATE, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.dailySales, "Sales", htmlAttributes: new { @class = "col-xs-4 col-sm-offset-1 col-sm-4" })
<div class="col-lg-6 col-lg-6 col-md-6 col-md-6 col-sm-6 col-sm-6 col-xs-6 col-xs-6">
@Html.TextBoxFor(model => model.dailySales, new { @class = "form-control" })
@Html.ValidationMessageFor(model => model.dailySales, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
<div class="col-lg-3 pull-right">
<input type="submit" value="Create" class="btn btn-primary" />
</div>
</div>
</div>
}
</div>Hope someone can help me out on this. Thank You!
