SqlException: Conversion failed when converting the varchar value to data type int in ASP.Net Core MVC

trisetia302
 
on Sep 22, 2021 07:07 AM
948 Views

Hi,

I was tried to filter data stock but get the error message "SqlException: Conversion failed when converting the varchar value '10 And Stock <=15' to data type int."

When I tried the query on sql server work fine but when I implemented on code get the error message.

Any help could be appreciate.

The View

<div align="left">
    <form asp-action="CekStockObat" asp-controller="ReportObat" method="post">
        <table>
            <tr>
                <td>
                    Enter Parameter
                    <input type="text" id="parameter1" name="parameter1" class="form-control" placeholder=">=0"/>
                    <input type="text" id="parameter2" name="parameter2" class="form-control" placeholder="<=10"/>
                    <button type="submit" class="btn btn-success btn-sm"> Cek Stock</button>
                </td>
            </tr>
        </table>
    </form>
</div

The Controller.cs

[HttpPost]
public IActionResult CekStockObat(int parameter1, int parameter2)
{
    List<ObatModel> obats = new List<ObatModel>();
    using (SqlConnection con = new SqlConnection(this._configuration.GetConnectionString("Db_Klinik")))
    {
        using (SqlCommand cmd = new SqlCommand(null))
        {
            con.Open();
            cmd.Connection = con;
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.CommandText = "Select * From Tbl_Obat Where Stock >='"+ parameter1 +" And Stock <=" + parameter2 + "'";
            using (SqlDataReader rdr = cmd.ExecuteReader())
            {
                while (rdr.Read())
                {
                    obats.Add(new ObatModel
                    {
                        ID_Obat = rdr["ID_Obat"].ToString(),
                        Nama_Obat = rdr["Nama_Obat"].ToString(),
                        Jenis_Obat = rdr["Jenis_Obat"].ToString(),
                        Harga = Convert.ToDecimal(rdr["Harga"].ToString()),
                        Stock = Convert.ToInt32(rdr["Stock"].ToString())
                    });
                }
            }
        }
    }
    return View(obats.ToList());
}

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Sep 23, 2021 04:48 AM

Hi trisetia302,

Check this example. Now please take its reference and correct your code.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

Model

public class EmployeeModel
{
    public string Id { get; set; }
    public string Name { get; set; }
    public string City { get; set; }
    public string Country { get; set; }
}

Controller

public class HomeController : Controller
{
    public IActionResult Index()
    {
        List<EmployeeModel> obats = new List<EmployeeModel>();
        return View(obats);
    }

    [HttpPost]
    public IActionResult CekStockObat(int parameter1, int parameter2)
    {
        List<EmployeeModel> obats = new List<EmployeeModel>();
        using (SqlConnection con = new SqlConnection("Server=.;DataBase=Northwind;UID=sa;PWD=pass@123"))
        {
            using (SqlCommand cmd = new SqlCommand(null))
            {
                con.Open();
                cmd.Connection = con;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = "Select * From Employees Where EmployeeID >=" + parameter1 + " And EmployeeID <=" + parameter2;
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        obats.Add(new EmployeeModel
                        {
                            Id = rdr["EmployeeID"].ToString(),
                            Name = rdr["FirstName"].ToString(),
                            City = rdr["City"].ToString(),
                            Country = rdr["Country"].ToString()
                        });
                    }
                }
            }
        }

        return View("Index", obats.ToList());
    }
}

View

@model IEnumerable<Filter_Between_Integer_MVC_Core.Models.EmployeeModel>
@addTagHelper *, Microsoft.AspNetCore.Mvc.TagHelpers
@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
</head>
<body>
    <form asp-action="CekStockObat" asp-controller="Home" method="post">
        <table>
            <tr>
                <td>
                    Enter Parameter
                    <input type="text" id="parameter1" name="parameter1" class="form-control" placeholder=">=0" />
                    <input type="text" id="parameter2" name="parameter2" class="form-control" placeholder="<=10" />
                    <button type="submit" class="btn btn-success btn-sm"> Cek Stock</button>
                </td>
            </tr>
        </table>
    </form>
    <hr />
    <table class="table">
        <thead>
            <tr>
                <th>Id</th>
                <th>Name</th>
                <th>City</th>
                <th>Country</th>
            </tr>
        </thead>
        <tbody>
            @foreach (var item in Model)
            {
                <tr>
                    <td>@Html.DisplayFor(modelItem => item.Id)</td>
                    <td>@Html.DisplayFor(modelItem => item.Name)</td>
                    <td>@Html.DisplayFor(modelItem => item.City)</td>
                    <td>@Html.DisplayFor(modelItem => item.Country)</td>
                </tr>
            }
        </tbody>
    </table>
</body>
</html>

Screenshot