How to get Last entry of Particular ID

Question

Below is my query I want to fetch last entry of visitno ,of particular pid,this query works fine but selects all rows of pid ,visistNo is of Number Datatype

public int GetPatientID_visitNo(int pid)
    {
        int data = 0;
        try
        {
            string sql = "Select VisitNo From Patient_Visit_Details where Patient_ID = " + pid;
            cmd = new OleDbCommand(sql, acccon);
            rs = cmd.ExecuteReader();
            if (rs.HasRows)
            {
                data = Convert.ToInt32(rs[0]);
            }
        }
        catch (Exception err)
        {
        }
        return data;
    }

when I try below Query it gives me error no data exist since my table is new and it dosn't contain any row ,how I can Do this

string sql = "Select MAX(VisitNo) From Patient_Visit_Details where Patient_ID = " + pid;

I want to Make `VisistNO` column as autoIncrement for Particular ID ,so I am trying to Fetch Last entry of `visitNo` so that I can increment it by 1 — Durga, Oct 16 '13 at 08:18

score 4 · Accepted Answer · edited May 23 '17 at 12:10

4

string sql = string.Format(@"SELECT TOP 1 VisitNo
                             FROM patient_Visit_Details
                             WHERE Patient_ID = {0}
                             ORDER BY VisitNo DESC", pid);

Also, use cmd.ExecuteScalar() for this. Check the return value for when there are no rows.

Note also, that if pid has arrived from somewhere outside your application (e.g. a web page), your sql is open to Injection attack.

Now, as has been pointed out - what if there are no rows? See this question here: Handling ExecuteScalar() when no results are returned, the ethos being - check for null and DbNull.Value before trying to convert to an Int32.

edited May 23 '17 at 12:10

Community

1
1

answered Oct 16 '13 at 08:15

Moo-Juice

38,257
10
78
128

1

@juergend, if I read his question correctly, he wants the latest (highest?) `VisitNo` for a given patient? Or have I read the question incorrectly? – Moo-Juice Oct 16 '13 at 08:19
The problem is that if no value exists for a given patient he gets no result. – juergen d Oct 16 '13 at 08:22

Damith · Answer 2 · 2013-10-16T08:27:17.617

1

change SQL statement as beloe

string sql = "Select ISNULL(MAX(VisitNo), 0)  From Patient_Visit_Details where Patient_ID = " + pid;

use ExecuteScalar and add validation for null result

var maxVisit = cmd.ExecuteScalar();

if (maxVisit != null && maxVisit != DBNull.Value) 
{
   data = Convert.ToInt32(maxVisit);
}

edited Oct 16 '13 at 08:27

answered Oct 16 '13 at 08:21

Damith

62,401
13
102
153

How to get Last entry of Particular ID

2 Answers2