I am Getting error for only Product_Get Action using Single stored Procedure for insert and Select both ,
but using Seprate procedure for Select And Insert
working well, Please help me to resolve the error,
and able to work with single procedure
System.Data.SqlClient.SqlException: Error converting data type varchar to numeric.in MVC
Product Class In Model Folder
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace AjaxDemo.Models
{
public class Product
{
public int ProdID { get; set; }
public string ProdName { get; set; }
public string ProdManufacturer { get; set; }
public Decimal ProdPrice { get; set; }
public DateTime ProdManufacturingDate { get; set; }
public DateTime ProdExpiryDate { get; set; }
}
}
Productrepository Class In Model Folder
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
namespace AjaxDemo.Models
{
public class ProductRepository
{
string constr = ConfigurationManager.ConnectionStrings["DataContext"].ConnectionString;
DataSet ds = new DataSet();
public void InsertData(Product prd)
{
using (SqlConnection con = new SqlConnection(constr))
{
SqlCommand cmd = new SqlCommand("SpProd", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ProdName", prd.ProdName);
cmd.Parameters.AddWithValue("@ProdManufacturer", prd.ProdManufacturer);
cmd.Parameters.AddWithValue("@ProdPrice", prd.ProdPrice);
cmd.Parameters.AddWithValue("@ProdManufacturingDate", prd.ProdManufacturingDate);
cmd.Parameters.AddWithValue("@ProdExpiryDate", prd.ProdExpiryDate);
cmd.Parameters.AddWithValue("@Action", "Insert");
if (con.State == ConnectionState.Closed)
{
con.Open();
}
cmd.ExecuteNonQuery();
con.Close();
}
}
public IEnumerable<Product> GetAllProduct()
{
IList<Product> prd = new List<Product>();
using (SqlConnection con = new SqlConnection(constr))
{
SqlCommand cmd = new SqlCommand("SpProd", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Action", "Select");
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Product prod = new Product();
prod.ProdID = Convert.ToInt32(dr["ProdID"]);
prod.ProdName = dr["ProdName"].ToString();
prod.ProdManufacturer = dr["ProdManufacturer"].ToString();
prod.ProdPrice = Convert.ToDecimal(dr["ProdPrice"]);
prod.ProdManufacturingDate = Convert.ToDateTime(dr["ProdManufacturingDate"]);
prod.ProdExpiryDate = Convert.ToDateTime(dr["ProdExpiryDate"]);
prd.Add(prod);
}
con.Close();
}
return prd;
}
}
}
Controller In Home
#region "Namespace"
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Text;
using AjaxDemo.Models;
#endregion
namespace AjaxDemo.Controllers
{
public class HomeController : Controller
{
#region "Get Product DataPage"
[HttpGet]
[ActionName("Product_Get")]
public ActionResult Product_Get()
{
ProductRepository pr = new ProductRepository();
var listdata = pr.GetAllProduct().ToList();
return View(listdata);
}
#endregion
#region "Get Product Page"
public ActionResult Product()
{
return View();
}
#endregion
#region "Post Product DataPage"
[HttpPost]
[ActionName("Product")]
public ActionResult Product_Post(Product prd)
{
ProductRepository pr = new ProductRepository();
if (ModelState.IsValid)
{
pr.InsertData(prd);
}
return View();
}
#endregion
}
}
Product_Get View In Home View
@model IEnumerable<AjaxDemo.Models.Product>
@{
ViewBag.Title = "Get Product ";
}
<h2>Product_Get</h2>
<p>
@Html.ActionLink("Create New", "Create")
</p>
<table class="table">
<tr>
<th>
@Html.DisplayNameFor(model => model.ProdID)
</th>
<th>
@Html.DisplayNameFor(model => model.ProdName)
</th>
<th>
@Html.DisplayNameFor(model => model.ProdManufacturer)
</th>
<th>
@Html.DisplayNameFor(model => model.ProdPrice)
</th>
<th>
@Html.DisplayNameFor(model => model.ProdManufacturingDate)
</th>
<th>
@Html.DisplayNameFor(model => model.ProdExpiryDate)
</th>
<th></th>
</tr>
@foreach (var item in Model) {
<tr>
<td>
@Html.DisplayFor(modelItem => item.ProdID)
</td>
<td>
@Html.DisplayFor(modelItem => item.ProdName)
</td>
<td>
@Html.DisplayFor(modelItem => item.ProdManufacturer)
</td>
<td>
@Html.DisplayFor(modelItem => item.ProdPrice)
</td>
<td>
@Html.DisplayFor(modelItem => item.ProdManufacturingDate)
</td>
<td>
@Html.DisplayFor(modelItem => item.ProdExpiryDate)
</td>
<td>
@Html.ActionLink("Edit", "Edit", new { /* id=item.PrimaryKey */ }) |
@Html.ActionLink("Details", "Details", new { /* id=item.PrimaryKey */ }) |
@Html.ActionLink("Delete", "Delete", new { /* id=item.PrimaryKey */ })
</td>
</tr>
}
</table>
Product Html
@model AjaxDemo.Models.Product
@{
ViewBag.Title = "Product";
}
<h2>Product</h2>
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
<div>
<h4>Product</h4>
<hr />
@Html.ValidationSummary(true)
<div >
@Html.LabelFor(model => model.ProdName)
<div >
@Html.EditorFor(model => model.ProdName)
@Html.ValidationMessageFor(model => model.ProdName)
</div>
</div>
<div >
@Html.LabelFor(model => model.ProdManufacturer)
<div >
@Html.EditorFor(model => model.ProdManufacturer)
@Html.ValidationMessageFor(model => model.ProdManufacturer)
</div>
</div>
<div >
@Html.LabelFor(model => model.ProdPrice)
<div >
@Html.EditorFor(model => model.ProdPrice)
@Html.ValidationMessageFor(model => model.ProdPrice)
</div>
</div>
<div >
@Html.LabelFor(model => model.ProdManufacturingDate)
<div >
@Html.EditorFor(model => model.ProdManufacturingDate)
@Html.ValidationMessageFor(model => model.ProdManufacturingDate)
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.ProdExpiryDate, new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.ProdExpiryDate)
@Html.ValidationMessageFor(model => model.ProdExpiryDate)
</div>
</div>
<div class="form-group">
<div class="col-md-offset-2 col-md-10">
<input type="submit" value="Create" class="btn btn-default" />
</div>
</div>
</div>
}
<div>
@Html.ActionLink("Back to List", "Product_Get")
</div>
Sql Server Table
And Stored Procedure
Create table Product
(
ProdID int Identity(1,1) primary key,
ProdName varchar(100),
ProdManufacturer varchar(100),
ProdPrice Decimal(5,2),
ProdManufacturingDate DateTime,
ProdExpiryDate DateTime ,
)
Procedure
Alter proc SpProd
@ProdID int ='',
@ProdName varchar(100)='',
@ProdManufacturer varchar(100)='',
@ProdPrice Decimal(5,2)='',
@ProdManufacturingDate DateTime='',
@ProdExpiryDate DateTime ='',
@Action varchar(100)=''
AS
Begin
if(@Action='Insert')
Begin
insert into Product(ProdName,ProdManufacturer,ProdPrice,ProdManufacturingDate,ProdExpiryDate) values
(@ProdName,@ProdManufacturer,@ProdPrice,@ProdManufacturingDate,@ProdExpiryDate)
End
if(@Action='Select')
Begin
Select * from Product
End
END
When I write Seprate Stored procedure for
insert and Select then it gives no error
but using single Stored procedure for
Insert and select then gives Error
Error converting data type varchar to numeric.in MVC
