CODEWITHSUNDEEP
c#
Abdullah
Abdullah

Reputation: 1021

How to solve issue cannot implicitly convert System.Data.Datatable to int?

I want to select the patient_no from sql server database and assign it to int variable by using stored procedure , then compare this value before insert into the database , my issue when i assign the value to variable by using stored procedure the following error appeared cannot implicitly convert type System.Data.Datatable to int.

I am using windows forms application not webform.

I tried to solve this error but i cannot .

1- the stored procedure to read patient_no :

create proc [VALIDATE_PATIENT_EXIST]
@Patient_No int
as 
select Patient_No from Users_web
where patient_no = @Patient_No

2- The public void used to get data from database :

public DataTable VALIDATE_PATIENT_EXIST(int Patient_No)
        {
            DAL.DataAccessLayer DAL = new DAL.DataAccessLayer();
            DataTable dt = new DataTable();
            SqlParameter[] Param = new SqlParameter[1];
            Param[0] = new SqlParameter("@Patient_No", SqlDbType.Int);
            Param[0].Value = Patient_No;
            dt = DAL.SelectData("VALIDATE_PATIENT_EXIST", Param);
            DAL.close();
            return dt;
        }

3- The DataAccessLayer class which is used to read data from database:

public DataTable SelectData(string stored_procedure, SqlParameter[] param)
        {
            SqlCommand sqlcmd = new SqlCommand();
            sqlcmd.CommandType = CommandType.StoredProcedure;
            sqlcmd.CommandText = stored_procedure;
            sqlcmd.Connection = sqlconnection;

            if (param != null)
            {
                for (int i = 0; i < param.Length; i++)
                {
                    sqlcmd.Parameters.Add(param[i]);
                }
            }
            SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }

4- i called the class and stored procedure and try to assign returned value from stored procedure to int value :

BL.CLS_PATIENTS patient = new BL.CLS_PATIENTS();
int patient_exist = patient.VALIDATE_PATIENT_EXIST(Convert.ToInt32(txtPatientNo.Text));

the error appeared in step 4 , how to solve this error and what i need to change in my code.

I need to assign patient_no to int variable then i need to compare it and validate it if patient exist then show message this patient already exist if patient not exist then i will insert it to the database tables.

Upvotes: 0

Views: 7083

Answers (1)

haldo
haldo

Reputation: 16701

The method is returning a DataTable not an int.

You need to access the data in the DataTable. This can be done in many ways:

BL.CLS_PATIENTS patient = new BL.CLS_PATIENTS();
var patientData = patient.VALIDATE_PATIENT_EXIST(Convert.ToInt32(txtPatientNo.Text));  // datatable

DataRow row = patientData.Rows[0];
int patientExists = row.Field<int>("Patient_No");

Or another option is:

int patientExists = int.Parse(patientData.Rows[0]["Patient_No"]);

A better option would be to update the VALIDATE_PATIENT_EXISTS method and refactor slightly:

    public int? GetPatientNumber(int Patient_No)
    {
        DAL.DataAccessLayer DAL = new DAL.DataAccessLayer();
        DataTable dt = new DataTable();
        SqlParameter[] Param = new SqlParameter[1];
        Param[0] = new SqlParameter("@Patient_No", SqlDbType.Int);
        Param[0].Value = Patient_No;
        dt = DAL.SelectData("VALIDATE_PATIENT_EXIST", Param);
        DAL.close();

        // if there is at least one row
        if (dt.Rows.Count > 0)
        {
            DataRow row = dt.Rows[0];
            int? patientNumber = row.Field<int>("Patient_No");
            return patientNumber;
        }

        // return null otherwise
        return null;
    }

Then if you need to validate whether the patient exists you could do:

var patientNumber = GetPatientNumber(txtPatientNo.Text); // int?
bool patientExists = patientNumber.HasValue;

Upvotes: 3

Related Questions