Data not Saving in Database after Creating (CRUD)-CodePudding

I am working on simple CRUD application where I have two tables:

Patient

CNIC (varchar 50 and PK)
Name (varchar 50)

PatientVaccines

Cnic (varchar 50 and FK)
VaccinationName (varchar)
VaccinationDate (varchar)
CenterAddress (varchar)

I know making string as PK, FK is not a good approach but this is my requirement.

I have a PatientDBContext class where I perform CRUD operations:

public class PatentDBContext
{
    string cs = ConfigurationManager.ConnectionStrings["Myconnection"].ConnectionString;

    public List<Patient> getPatients()
    {
        List<Patient> PatientList = new List<Patient>();
        SqlConnection con = new SqlConnection(cs);

        string query = "SELECT p.CNIC, p.Name, pv.cnic, pv.VaccinationName, pv.VaccinationDate, pv.CenterAddress FROM Patient AS p JOIN PatientVaccines AS pv ON p.CNIC = pv.cnic";

        SqlCommand cmd = new SqlCommand(query, con);

        con.Open();

        SqlDataReader dr = cmd.ExecuteReader();

        while (dr.Read())
        {
            Patient p = new Patient();
         
            p.CNIC = dr["CNIC"].ToString();
            p.Name = dr["Name"].ToString();
            p.VaccinationName = dr["VaccinationName"].ToString();
            //p.VaccinationDate = dr["VaccinationDate"].ToString();
            p.CentreAddress = dr["CenterAddress"].ToString();

            PatientList.Add(p);
        }

        con.Close();

        return PatientList;
    }

    public bool AddPatient(Patient pat)
    {
        SqlConnection con = new SqlConnection();
        SqlCommand cmd = new SqlCommand("spAddPatient", con);
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.AddWithValue("@CNIC", pat.CNIC);
        cmd.Parameters.AddWithValue("@Name", pat.Name);
        cmd.Parameters.AddWithValue("@VaccinationName", pat.VaccinationName);
        cmd.Parameters.AddWithValue("@VaccinationDate", pat.VaccinationDate);
        cmd.Parameters.AddWithValue("@CenterAddress", pat.CentreAddress);

        con.Open();
        int i = cmd.ExecuteNonQuery();
        con.Close();

        if (i > 0)
        {
            return true;
        }
        else
        {
            return false;
        }
    }

    public bool UpdatePatient(Patient pat)
    {
        SqlConnection con = new SqlConnection();
        string query = "UPDATE PatientVaccines SET  VaccinationName = @VaccinationName, VaccinationDate = @VacinationDate, CenterAddress = @CenterAddress WHERE Cnic = @Cnic";

        SqlCommand cmd = new SqlCommand(query, con);
        //cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.AddWithValue("@CNIC", pat.CNIC);
        //cmd.Parameters.AddWithValue("@Name", pat.Name);
        cmd.Parameters.AddWithValue("@VaccinationName", pat.VaccinationName);
        cmd.Parameters.AddWithValue("@VaccinationDate", pat.VaccinationDate);
        cmd.Parameters.AddWithValue("@CenterAddress", pat.CentreAddress);

        con.Open();
        int i = cmd.ExecuteNonQuery();
        con.Close();

        if (i > 0)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
}

Errors is this class is in getPatient() function I comment it out p.VaccinationDate that shows an error that I cannot convert implicitly type string to DateTime, how do I convert it to DateTime?

I have another function names AddPatient()that now show any error or bug but when I click submit button after input records it doesn't perform any action.

HomeController

public class HomeController : Controller
{
    // GET: Home
    public ActionResult Index()
    {
        PatentDBContext db = new PatentDBContext();
        List<Patient> obj = db.getPatients();

        return View(obj);
    }

    public ActionResult Create()
    {
        return View();
    }

    [HttpPost]
    public ActionResult Create(Patient pat)
    {
        try
        {
            if (ModelState.IsValid == true)
            {
                PatentDBContext context = new PatentDBContext();
                bool check = context.AddPatient(pat);

                if (check == true)
                {
                    TempData["InsertMessage"] = "Data Inserted..";
                }
                else
                {
                    TempData["FailureMessage"] = "Data Not Inserted";
                }

                ModelState.Clear();

                return RedirectToAction("Index");
            }
            return View();
        }
        catch
        {
            return View();
        }
    }

    public ActionResult Edit(string Cnin)
    {
        PatentDBContext context = new PatentDBContext();

        //string str = Cnin.ToString();
        var row = context.getPatients().Find(model => model.CNIC = Cnin);
        return View(row);
    }
}

Here I also can't convert implicitly type string to bool

var row = context.getPatients().Find(model => model.CNIC = Cnin);

and finally this is my stored procedure:

ALTER PROCEDURE [dbo].[spAddPatient]
    (@CNIC varchar(50),
     @Name varchar(50),
     @VaccinationName varchar(50),
     @VaccinationDate varchar(50),
     @CenterAddress varchar(50))
AS
BEGIN
    INSERT INTO Patient (CNIC, Name)
    VALUES (@CNIC, @Name)

    INSERT INTO PatientVaccines (Cnic, VaccinationName, VaccinationDate, CenterAddress)
    VALUES (@Cnic, @VaccinationName, @VaccinationDate, @CenterAddress)
END

CodePudding user response：

I pretty sure that you are very new in this technology as there are some basic mistake. I am mentioning some common mistake below:

You should not use varchar as primary key instead use int
Make a relation in both parent and child table using int column.
Do not use varchar for date field, instead use DateTime.

I redesigned two tables as below:

Patient Table

CREATE TABLE [dbo].[Patient](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [CNIC] [varchar](50) NOT NULL,
    [Name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Patient] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

I introduce new column Id that set as

By doing the above, you need to update your Stored Procedure as below:

CREATE PROCEDURE [dbo].[spAddPatient]
    (@CNIC varchar(50),
     @Name varchar(50),
     @VaccinationName varchar(50),
     @VaccinationDate datetime,
     @CenterAddress varchar(50))
AS
BEGIN
    INSERT INTO Patient (CNIC, Name)
    VALUES (@CNIC, @Name)

    INSERT INTO PatientVaccines (PatientId, VaccinationName, VaccinationDate, CenterAddress)
    VALUES (@@Identity, @VaccinationName, @VaccinationDate, @CenterAddress)
END

Here is the complete C# Code where I made some correction

public class PatentDBContext
{
    string cs = ConfigurationManager.ConnectionStrings["Myconnection"].ConnectionString;
    public List<Patient> getPatients()
    {
        List<Patient> PatientList = new List<Patient>();
        SqlConnection con = new SqlConnection(cs);

        string query = "SELECT p.CNIC, p.Name, pv.VaccinationName, pv.VaccinationDate, pv.CenterAddress FROM Patient AS p JOIN PatientVaccines AS pv ON p.Id = pv.PatientId";
        SqlCommand cmd = new SqlCommand(query, con);
        con.Open();
        SqlDataReader dr = cmd.ExecuteReader();

        while (dr.Read())
        {
            Patient p = new Patient();
            p.CNIC = dr["CNIC"].ToString();
            p.Name = dr["Name"].ToString();
            p.VaccinationName = dr["VaccinationName"].ToString();
            p.VaccinationDate = Convert.ToDateTime(dr["VaccinationDate"]);
            p.CenterAddress = dr["CenterAddress"].ToString();

            PatientList.Add(p);
        }

        con.Close();
        return PatientList;
    }

    public bool AddPatient(Patient pat)
    {
        SqlConnection con = new SqlConnection(cs);
        SqlCommand cmd = new SqlCommand("spAddPatient", con);
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.AddWithValue("@CNIC", pat.CNIC);
        cmd.Parameters.AddWithValue("@Name", pat.Name);
        cmd.Parameters.AddWithValue("@VaccinationName", pat.VaccinationName);
        cmd.Parameters.AddWithValue("@VaccinationDate", pat.VaccinationDate);
        cmd.Parameters.AddWithValue("@CenterAddress", pat.CenterAddress);

        con.Open();
        int i = cmd.ExecuteNonQuery();
        con.Close();

        if (i > 0)
        {
            return true;
        }
        else
        {
            return false;
        }
    }

    public bool UpdatePatient(Patient pat)
    {
        SqlConnection con = new SqlConnection(cs);
        string query = "UPDATE PatientVaccines SET  VaccinationName = @VaccinationName, VaccinationDate = @VaccinationDate, CenterAddress = @CenterAddress WHERE PatientId = ( Select Id from Patient where Cnic = @Cnic)";

        SqlCommand cmd = new SqlCommand(query, con);

        cmd.Parameters.AddWithValue("@CNIC", pat.CNIC);
        //cmd.Parameters.AddWithValue("@Name", pat.Name);
        cmd.Parameters.AddWithValue("@VaccinationName", pat.VaccinationName);
        cmd.Parameters.AddWithValue("@VaccinationDate", pat.VaccinationDate);
        cmd.Parameters.AddWithValue("@CenterAddress", pat.CenterAddress);

        con.Open();
        int i = cmd.ExecuteNonQuery();
        con.Close();

        if (i > 0)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
}

CodePudding user response：

I believe your stored procedure is not correct, you can test it beforehand in the database.

// here you should use operator== instead of аssignment operator=
// Have in mind that .Find will throw an error if model with given Cnin is not found
var row = context.getPatients().Find(model => model.CNIC == Cnin);

How to convert a string to datetime object

Create a stored procedure

General advice, you can google the errors you get and find information about them