c# - How to check passport number exist or not?

Question

I want to check passport number exist or not ,

before I used this code to check if integer number exist or not ,

but passport number column in MSSQL type varchar(50).

what I tried

1- created stored procedure to read ID No :

create proc [dbo].[VALIDATE_PATIENT_IDNO]

@patient_id varchar(50)
as 
select Patient_id from Patients
where Patient_id = @patient_id

2- I created this code in C# to validate id no exist or not :

public int? VALIDATE_PATIENT_IDNO(string patient_id)
        {
            DAL.DataAccessLayer DAL = new DAL.DataAccessLayer();
            DataTable dt = new DataTable();
            SqlParameter[] Param = new SqlParameter[1];

            Param[0] = new SqlParameter("@patient_id", SqlDbType.VarChar,50);
            Param[0].Value = patient_id;

            dt = DAL.SelectData("VALIDATE_PATIENT_IDNO", Param);
            DAL.close();

            if (dt.Rows.Count > 0)
            {
                DataRow row = dt.Rows[0];
                int? patientNumber = row.Field<int>("patient_id");
                return patientNumber;
            }

            // return null otherwise
            return null;
        }

3- when type the id no or passport no when key down code :

private void textIDNO_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                if (string.IsNullOrEmpty(textIDNO.Text))
                {
                    txtpcfileno.Focus();
                }

                 else
                {
                    var patientNumber = patient.VALIDATE_PATIENT_IDNO(textIDNO.Text); // int?
                    bool patientExists = patientNumber.HasValue;

                    if (patientExists == true)
                    {
                        MessageBox.Show("Id or Passport No Exist ", "ID EXIST", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                        return;
                    }
                    else
                    {
                        txtpcfileno.Focus();
                    }
                }   
            }
            
                  
        }

4- I have error appeared in the code in step 2 :

Additional information: Specified cast is not valid.
int? patientNumber = row.Field<int>("patient_id");

How to change the code in step 2 and solve this error and check string value not int? ?

question from:https://stackoverflow.com/questions/65625951/how-to-check-passport-number-exist-or-not

Answer 1

Letting the naming confusions (passportnumber vs patientid) aside, you probably don't want to return the found patientids (because you already know them, as they are part of your selection condition) but the count.

Furthermore, your patientid seems to be a string, yet in your result you try to cast this to an integer. That is not possible, thus the error.

You can try as follows:

create proc [dbo].[VALIDATE_PATIENT_IDNO]

@patient_id varchar(50)
as 
select count(Patient_id) as patientcount from Patients
where Patient_id = @patient_id

Assuming that patient_id is the primary key of your table, this will either return 1 if a row with the given id exists or 0 if not.

Then you can do

int? patientNumber = row.Field<int>("patientcount");

and then

bool patientExists = patientNumber > 0;