Click here to Skip to main content
15,868,141 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have two tables in my database, these tables are Patient having 3 column Id(PK), Cnic, Name and PatientVaccines having column Id(FK), Cnic, VaccinationName, VaccinationDate, CenterAddress.

I am implementing CRUD operation on them but i am stuck with fetching data from two tables into one view, here is my code

Patient class
C#
public class Patient
   {
       public int Id { get; set; }
       public string Cnic { get; set; }
       public string Name { get; set; }
       public string VaccinationName { get; set; }
       public DateTime VaccinationDate { get; set; }
       public string CenterAddress { get; set; }

   }


PatientDBContext class
C#
public class PatientDBContext
   {
       string cs = ConfigurationManager.ConnectionStrings["CRUDwithSPs"].ConnectionString;

       public List<Patient> getPatient()
       {
           List<Patient> patientList = new List<Patient>();

           SqlConnection con = new SqlConnection(cs);
           SqlCommand cmd = new SqlCommand("showPatient", con);
           cmd.CommandType = CommandType.StoredProcedure;
           con.Open();

           SqlDataReader dr = cmd.ExecuteReader();

           while (dr.Read())
           {
               Patient p = new Patient();

               //p.Id = Convert.ToInt32(dr.GetValue(0).ToString());
               p.Id = Convert.ToInt32(dr["Id"].ToString());
               p.Cnic = dr["Cnic"].ToString();
               p.Name = dr["Name"].ToString();
               p.VaccinationName = dr["VaccinationName"].ToString();
               //p.VaccinationDate = dr["VaccinationDate"].ToString();
               p.CenterAddress = dr["CenterAddress"].ToString();

               patientList.Add(p);

           }


           con.Close();






           return patientList;

       }


   }


This is my HomeController
C#
public class HomeController : Controller
   {
       // GET: Home
       public ActionResult Index()
       {
           PatientDBContext db = new PatientDBContext();
           List<Patient> obj = db.getPatient();
           return View(obj);
       }
   }


What I have tried:

I tried few things like

C#
p.Id= (Convert.ToInt32(dr["Id"])).ToString();

and
C#
p.Id = Convert.ToInt32(dr.GetValue(0).ToString());


but still getting IndexOutOfRange exception

Please also tell me what im doing wrong here

p.VaccinationDate = dr["VaccinationDate"].ToString();
Posted
Updated 28-Nov-21 5:20am
Comments
Richard MacCutchan 28-Nov-21 8:25am    
Why are you converting a value to a string just in order to convert it back to a value?

Also, it would help if we could see your table structure, and the code of your stored procedure.

See if this doesn't help you.

I created a Patient object from the info I could gather from your sample code. The important part are the constructors.

C#
// I added a default constructor, and a constructor that accepts a DataRow object. 
// This hides the act of populating the patient object which a) makes the most 
// outward-facing code more simple, and b) moves this code into a single location, 
// just in case you have multiple places in the code that creates this object.
public class Patient
{
	public int    Id             ;
	public string Cnic           ;
	public string Name           ;
	public string VaccinationName;
	public string CenterAddress  ;
	public Patient(){ }
	public Patient(DataRow row)
	{
		this.Id              = (Int32)row["Id"];

        // my guess is that your index problem lies here, and I suspect 
        // that it's the "Cnic" column since it appears that you 
        // misspelled "Clinic".
		this.Cnic            = row["Cnic"].ToString();

		this.Name            = row["Name"].ToString();
		this.VaccinationName = row["VaccinationName"].ToString();
		this.CenterAddress   = row["CenterAddress"].ToString();
	}
}

public List<Patient> getPatient()
{
    // ignoring your sub-par naming of the data reader object (I would 
    // have called it "reader"), we move these two vars  up to the top 
    // of the method.
	SqlDataReader dr = null;
	DataTable table = null;

	List<Patient> patientList = new List<Patient>();

    // next, I use the "using" construct, which allows automatic cleanup 
    // of closeable/disposable objects
	using (SqlConnection con = new SqlConnection(cs))
	{
		con.Open();
		using (SqlCommand cmd = new SqlCommand("showPatient", con))
		{
			cmd.CommandType = CommandType.StoredProcedure;
			dr = cmd.ExecuteReader();

            // there's no point in proceeding if the reader doesn't have 
            // any data
			if (dr.HasRows)
			{
                // put the reader data into a data table
				table = new DataTable();
				table.Load(dr);

                // and now we can use "foreach" to iterate the rows, thus 
                // eliminating the possibility of getting an index out of 
                // range on the table rows
				foreach(DataRow row in table.Rows)
				{
                    // since we moved the actual property assignments to 
                    // the Patient entity itself, this code reads much 
                    // better (less cluttered).
					patientList.Add(new Patient(row));
				}
			}
		}
	}
	return patientList;
}
 
Share this answer
 
You get an out of range error when the index you are providing does not exist.
Unfortunately, it needs your code ruining with your data to work out exactly where, and we have no access to either of those.

So, it's going to be up to you.
Fortunately, you have a tool available to you which will help you find out what is going on: the debugger. If you don't know how to use it then a quick Google for "Visual Studio debugger" should give you the info you need.

Put a breakpoint on the first line in the function, and run your code through the debugger. Then look at your code, and at your data and work out what should happen manually. Then single step each line checking that what you expected to happen is exactly what did. When it isn't, that's when you have a problem, and you can back-track (or run it again and look more closely) to find out why.

Sorry, but we can't do that for you - time for you to learn a new (and very, very useful) skill: debugging!
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900