CRUD using ASP.NET MVC

Hai guys,, l trying to learn CRUD in Vs 2019 Mvc Aplication. I Get error in Update and Delete Record.

the id taken properly in the Url but the data not loaded on view AddOrEdit.cshtml. I'm not use EF for the CRUD operation.

I'm get error like this when the edit is click.

the error when delete click

this the controller

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.Rendering;
using Microsoft.EntityFrameworkCore;
using WebApplicationMvc.Data;
using WebApplicationMvc.Models;
using Microsoft.Extensions.Configuration;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Data.SqlClient;
namespace WebApplicationMvc.Controllers
{
public class GuestBookController : Controller
{
private readonly IConfiguration _configuration;
public GuestBookController(IConfiguration configuration)
{
this._configuration = configuration;
}
// GET: GuestBook
public IActionResult Index()
{
DataTable dt = new DataTable();
using (SqlConnection con = new SqlConnection(_configuration.GetConnectionString("db_mvc")))
{
con.Open();
SqlCommand cmd = new SqlCommand("spGetAllGuestBook", con);
SqlDataAdapter adap = new SqlDataAdapter(cmd);
cmd.CommandType = CommandType.StoredProcedure;
adap.Fill(dt);
}
return View(dt);
}
// GET: GuestBook/AddOrEdit/
public IActionResult AddOrEdit(int? id)
{
GuestBookViewModel guestBookViewModel = new GuestBookViewModel();
if (id > 0)
guestBookViewModel = GetGuestBookByID(id);
return View(guestBookViewModel);
}
// POST: GuestBook/AddOrEdit
// To protect from overposting attacks, enable the specific properties you want to bind to.
// For more details, see http://go.microsoft.com/fwlink/?LinkId=317598.
[HttpPost]
[ValidateAntiForgeryToken]
public IActionResult AddOrEdit(int id, [Bind("Id,Name,Email,Message")] GuestBookViewModel guestBookViewModel)
{
if (ModelState.IsValid)
{
using (SqlConnection con = new SqlConnection(_configuration.GetConnectionString("db_mvc")))
{
con.Open();
SqlCommand cmd = new SqlCommand("spSaveOrEdit", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("Id", guestBookViewModel.Id);
cmd.Parameters.AddWithValue("Name", guestBookViewModel.Name);
cmd.Parameters.AddWithValue("Email", guestBookViewModel.Email);
cmd.Parameters.AddWithValue("Message", guestBookViewModel.Message);
cmd.ExecuteNonQuery();
}
return RedirectToAction(nameof(Index));
}
return View();
}
// GET: GuestBook/Delete/5
public IActionResult Delete(int? id)
{
GuestBookViewModel guestBookViewModel = new GuestBookViewModel();
guestBookViewModel = GetGuestBookByID(id);
return View(guestBookViewModel);
}
// POST: GuestBook/Delete/5
[HttpPost, ActionName("Delete")]
[ValidateAntiForgeryToken]
public IActionResult DeleteConfirmed(int id)
{
using (SqlConnection con = new SqlConnection(_configuration.GetConnectionString("db_mvc")))
{
con.Open();
SqlCommand cmd = new SqlCommand("spDeleteGuestBookByID", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("Id", id);
cmd.ExecuteNonQuery();
return RedirectToAction(nameof(Index));
}
}
[NonAction]
public GuestBookViewModel GetGuestBookByID(int? id)
{
GuestBookViewModel guestBookViewModel = new GuestBookViewModel();
using (SqlConnection con = new SqlConnection(_configuration.GetConnectionString("db_mvc")))
{
DataTable dt = new DataTable();
con.Open();
SqlDataAdapter adap = new SqlDataAdapter("spGetGuestBookByID", con);
adap.SelectCommand.CommandType = CommandType.StoredProcedure;
adap.SelectCommand.Parameters.AddWithValue("@Id", id);
adap.Fill(dt);
if (dt.Rows.Count == 1)
{
guestBookViewModel.Id = Convert.ToInt32(dt.Rows[0]["Id"].ToString());
guestBookViewModel.Name = dt.Rows[1]["Name"].ToString();
guestBookViewModel.Email = dt.Rows[2]["Email"].ToString();
guestBookViewModel.Message = dt.Rows[3]["Message"].ToString();
}
return guestBookViewModel;
}
}
}
}

this the index.cshtml

@model System.Data.DataTable
@{
ViewData["Title"] = "List Guest Book";
}
class="jumbotron bg-info">
class="text-center">@ViewData["Title"]

class="btn btn-light">

"AddOrEdit"

>Create New

class="table table-bordered">

class="thead-light text-center">

Name

Message

@for (int i = 0; i < Model.Rows.Count; i++)

{

@Model.Rows[i]["Name"]

@Model.Rows[i]["Email"]

@Model.Rows[i]["Message"]

"AddOrEdit"

asp-route-id="@Model.Rows[i]["Id"]">Edit |

"Delete"

asp-route-id="@Model.Rows[i]["Id"]">Delete

}

Hi,

Please change here this will resolve your issue,

[NonAction]
public GuestBookViewModel GetGuestBookByID(int? id)
{
GuestBookViewModel guestBookViewModel = new GuestBookViewModel();
using (SqlConnection con = new SqlConnection(_configuration.GetConnectionString("db_mvc")))
{
DataTable dt = new DataTable();
con.Open();
SqlDataAdapter adap = new SqlDataAdapter("spGetGuestBookByID", con);
adap.SelectCommand.CommandType = CommandType.StoredProcedure;
adap.SelectCommand.Parameters.AddWithValue("@Id", id);
adap.Fill(dt);
if (dt.Rows.Count == 1)
{
guestBookViewModel.Id = Convert.ToInt32(dt.Rows[0]["Id"].ToString());
guestBookViewModel.Name = dt.Rows[0]["Name"].ToString();
guestBookViewModel.Email = dt.Rows[0]["Email"].ToString();
guestBookViewModel.Message = dt.Rows[0]["Message"].ToString();
}
return guestBookViewModel;
}
}
}

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Kumar5y ago
Accepted answer
You are getting values for id so it will get only one row so you need to user dt.Rows[0]. It means first row only.
Tri Setia5y ago
@Suraj Dubey
Thansk for the refcences link
Tri Setia5y ago
@jignes Kumar
alhamdulilah the issue was solved sir,,
why the index [0] sir
guestBookViewModel.Id = Convert.ToInt32(dt.Rows[0]["Id"].ToString());
guestBookViewModel.Name = dt.Rows[0]["Name"].ToString();
guestBookViewModel.Email = dt.Rows[0]["Email"].ToString();
guestBookViewModel.Message = dt.Rows[0]["Message"].ToString();

not like this sir..
guestBookViewModel.Id = Convert.ToInt32(dt.Rows[0]["Id"].ToString());
guestBookViewModel.Name = dt.Rows[1]["Name"].ToString();
guestBookViewModel.Email = dt.Rows[2]["Email"].ToString();
guestBookViewModel.Message = dt.Rows[3]["Message"].ToString();
please explain sir, so that I understand.
Suraj Dubey5y ago
try this

http://www.mukeshkumar.net/articles/mvc/crud-operation-with-database-first-approach-in-asp-net-mvc
Jignesh Kumar5y ago
Hi,

Please change here this will resolve your issue,
[NonAction]
public GuestBookViewModel GetGuestBookByID(int? id)
{
GuestBookViewModel guestBookViewModel = new GuestBookViewModel();
using (SqlConnection con = new SqlConnection(_configuration.GetConnectionString("db_mvc")))
{
DataTable dt = new DataTable();
con.Open();
SqlDataAdapter adap = new SqlDataAdapter("spGetGuestBookByID", con);
adap.SelectCommand.CommandType = CommandType.StoredProcedure;
adap.SelectCommand.Parameters.AddWithValue("@Id", id);
adap.Fill(dt);
if (dt.Rows.Count == 1)
{
guestBookViewModel.Id = Convert.ToInt32(dt.Rows[0]["Id"].ToString());
guestBookViewModel.Name = dt.Rows[0]["Name"].ToString();
guestBookViewModel.Email = dt.Rows[0]["Email"].ToString();
guestBookViewModel.Message = dt.Rows[0]["Message"].ToString();
}
return guestBookViewModel;
}
}
}

CRUD using ASP.NET MVC

@ViewData["Title"]

@ViewData["Title"]

GuestBookViewModel