Displaying a single row from access database in c#

Question

i have a project, and part of it asks the user to input the ID of the patient to show his/her details This is my code

        sConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=hospital database.accdb";
        dbConn = new OleDbConnection(sConnection);
        dbConn.Open();
        sql = "SELECT * FROM Patients";
        dbCmd = new OleDbCommand();
        dbCmd.CommandText = sql;
        dbCmd.Connection = dbConn;

        dbReader = dbCmd.ExecuteReader();


        listBox1.Items.Clear();




        if (dbReader.HasRows)
        {
            while (dbReader.Read())
            {

                if (dbReader["PatientID"] != DBNull.Value)
                {

                    int anInteger;
                    anInteger = Convert.ToInt32(textBox7.Text);
                    anInteger = int.Parse(textBox7.Text);

                    if (anInteger == 101)
                    {

                    }

                }


            }
        }

in the IF statement, i dont know what to write in it, to display on the row of the patient with this ID only

Please Help!!

Why don't you just query for the one patient info that you want instead of querying all of them? — PhoenixReborn
– PhoenixReborn, Commented Dec 8, 2013 at 17:44

Community · Accepted Answer · 2017-05-23 10:25:00Z

1

Instead of selecting all rows, it is much more efficient to filter the one row you are looking for using a parameter and modifying your SQL statement as follows.

    sql = "SELECT * FROM Patients WHERE PatientID = [pID]";
    dbCmd = new OleDbCommand();
    dbCmd.CommandText = sql;
    dbCmd.Connection = dbConn;
    dbcmd.Parameters.AddWithValue("pID", 101);
    dbReader = dbCmd.ExecuteReader();

I would also suggest looking into the "using" clause. Here's a SO example.

edited May 23, 2017 at 10:25

CommunityBot

11 silver badge

answered Dec 8, 2013 at 17:44

ron tornambe

10.8k8 gold badges38 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gord Thompson Over a year ago

+1 with two very minor comments: (1) Missing ) on AddWithValue. (2) With ACE/Jet OLEDB it is more common to use ? as the parameter placeholders/names since the provider ignores the actual names and only uses the order in which they are specified.

ron tornambe Over a year ago

Thanks Gord. I guess my usage of brackets is a left over from the VBA days.

senthilkumar2185 · Accepted Answer · 2013-12-09 04:46:05Z

1

  sql = "SELECT Count(*) FROM Patients WHERE PatientID = @PID";
    dbCmd = new OleDbCommand();
    dbCmd.CommandText = sql;
    dbCmd.Connection = dbConn;
    dbcmd.Parameters.AddWithValue("@PID", 101);
    Int32 Cnt = dbCmd.ExecuteScalar();
if ( Cnt > 0)
{
// Do Something
}
else  { // Do something}

answered Dec 9, 2013 at 4:46

senthilkumar2185

2,5983 gold badges23 silver badges38 bronze badges

Comments

BRAHIM Kamel · Accepted Answer · 2013-12-08 17:36:23Z

0

you have to use a variable to be sure that your ID was found and and break; to exit your loop once it was found

if (dbReader.HasRows)
        {
           bool found  = false;  
            while (dbReader.Read())
            {

                if (dbReader["PatientID"] != DBNull.Value)
                {

                    int anInteger;
                    anInteger = Convert.ToInt32(textBox7.Text);
                    anInteger = int.Parse(textBox7.Text);

                    if (anInteger == 101)
                    {
                        found = true ;  Break; 

                    }

                }


            }
        }

answered Dec 8, 2013 at 17:36

BRAHIM Kamel

13.8k1 gold badge38 silver badges47 bronze badges

Comments

Sudhakar Tillapudi · Accepted Answer · 2013-12-09 04:41:23Z

0

    int anInteger = Convert.ToInt32(textBox7.Text);        
    sConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=hospital database.accdb";
    dbConn = new OleDbConnection(sConnection);
    dbConn.Open();
    sql = "SELECT * FROM Patients where PatientID=@PatientID";
    dbCmd = new OleDbCommand();
    dbCmd.CommandText = sql;
    dbCmd.Parameters.AddWithValue("@PatientID",anInteger);
    dbCmd.Connection = dbConn;

    dbReader = dbCmd.ExecuteReader();


    listBox1.Items.Clear();
    while (dbReader.Read())
    {
       //now display the reader values here : sample
       //TextBox1.Text=dbReader["name"].ToString();
    }

answered Dec 9, 2013 at 4:41

Sudhakar Tillapudi

26.3k5 gold badges43 silver badges70 bronze badges

Collectives™ on Stack Overflow

Displaying a single row from access database in c#

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related