CODEWITHSUNDEEP
c#sql-serverasp.net-mvccrud
Zong
Zong

Reputation: 133

Data not Saving in Database after Creating (CRUD)

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

Patient

PatientVaccines

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

Upvotes: 0

Views: 1105

Answers (2)

Khabir
Khabir

Reputation: 5844

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

  1. You should not use varchar as primary key instead use int
  2. Make a relation in both parent and child table using int column.
  3. 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 IDENTITY so that this column will get value automatically like 1, 2, 3

PatientVaccines Table

CREATE TABLE [dbo].[PatientVaccines](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [VaccinationName] [varchar](50) NULL,
    [VaccinationDate] [datetime] NULL,
    [CenterAddress] [varchar](50) NULL,
    [PatientId] [int] NOT NULL,
 CONSTRAINT [PK_PatientVaccines] 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 two new columns Id and PatientId. when you insert a patient, the Id field will get a number automaticaly and that id will be inserted into PatientVaccines Table as PatientId so that you can find the relational data. Also I used datetime for VaccinationDate.

FOREIGN KEY CONSTRAINT

ALTER TABLE [dbo].[PatientVaccines]  WITH CHECK ADD  CONSTRAINT [FK_PatientVaccines_Patient] FOREIGN KEY([PatientId])
REFERENCES [dbo].[Patient] ([Id])

This is a constraint or rules that will restrict you to insert data that is not relational. for example: you do not have a record of patient with Id 101 but you are trying to insert PatientVaccines record with PatientId 101 then this rule will restrict you to do that.

Here is the Sql Diagram of Two tables

Sql Diagram of Two tables

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;
        }
    }
}

Upvotes: 3

Michael Crippa
Michael Crippa

Reputation: 113

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

Upvotes: 0

Related Questions