Why do I only able to retrieve the last row of data instead of all the rows? I'm trying to populate a list of rows into a ListView here

It keeps displaying the last row of data when I already do a whole list of search using the SQL statement that i provided it

        try
        {
            string strPatients = "SELECT patientID FROM MEDICALHISTORY";
            SqlCommand cmdPatient = new SqlCommand(strPatients, connection);

            string strMedicalPatients = "SELECT pFirstName, pLastName FROM PATIENT WHERE patientID=@searchPatient";
            SqlCommand cmdPatientHistory = new SqlCommand(strMedicalPatients, connection);

            connection.Open();

            SqlDataReader readPatientID = cmdPatient.ExecuteReader();
            if (readPatientID.Read())
            {
                string getID = readPatientID["patientID"].ToString();
                cmdPatientHistory.Parameters.AddWithValue("@searchPatient", getID);
            }
            readPatientID.Close();

            SqlDataReader readPatients = cmdPatientHistory.ExecuteReader();

            while (readPatients.Read())
            {
                ListViewItem allPatients = new ListViewItem(readPatients["pFirstName"].ToString());
                allPatients.SubItems.Add(readPatients["pLastName"].ToString());

                lsMedicalHistory.Items.Add(allPatients);
            }
            readPatients.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "Error");
        }
        finally
        {
            connection.Close();
        }

Any idea how do I solve this problem? I have been debugging it all day and still no luck of figuring it out. Thanks

Regards, Tyler

Answers


From what I can deduct from your code this is what I think you're trying to do:

  1. Get all the patientid's from MedicalHistory
  2. For each of these ID's you want to get the details from PATIENT (so I assume it's a 1-1 relationship)

The problem you have is that although your first query retrieves multiple values, you only read in the first one and then close the connection:

if (readPatientID.Read())
{
    string getID = readPatientID["patientID"].ToString();
    cmdPatientHistory.Parameters.AddWithValue("@searchPatient", getID);
}
readPatientID.Close();

When this is done you effectively execute the second command with just one ID, which returns exactly one result, which is why you are seeing only one item.

You could get rid of the first query and construct the second query like this:

SELECT pFirstName, pLastName FROM PATIENT WHERE patientID in (select patientid from MedicalHistory)

Now you just need to iterate over those results. The complete code would look something like this (typed in here, so you might need to fix the possible typos):

 try
    {
        string strMedicalPatients = "SELECT pFirstName, pLastName FROM PATIENT WHERE patientID in (select patientid from MedicalHistory)";
        SqlCommand cmdPatientHistory = new SqlCommand(strMedicalPatients, connection);

        connection.Open();

        SqlDataReader readPatients = cmdPatientHistory.ExecuteReader();

        while (readPatients.Read())
        {
            ListViewItem allPatients = new ListViewItem(readPatients["pFirstName"].ToString());
            allPatients.SubItems.Add(readPatients["pLastName"].ToString());

            lsMedicalHistory.Items.Add(allPatients);
        }
        readPatients.Close();
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Error");
    }
    finally
    {
        connection.Close();
    }

Need Your Help

How to see changes in subrepos between commits

mercurial tortoisehg mercurial-subrepos diffmerge

I have a mercurial repo with subrepos (also mercurial). Imagine the situation where I have changed the subrepos and the main repo. Now I want to see the changes between several commits including ...

Python sorted list containing all integers in both consumed list

python list sorting recursion

How would I go about writing a recursive function that consumes two lists and then produces a list with the elements that are in both lists? If both lists have a number twice in each, the produced ...