error : No value given for one or more required parameters

Question

I am trying to Fetch Id by comparing dataetimepicker's month and year value with date stored in database column of type Date/Time,Below is Query I am using to do this but it is showing me following error at this line while (rs.Read())

error

No value given for one or more required parameters.

Code

public int GetDrID_MonthWise(string DrName,int month,int year, int refDrID)
{
    int data = 0;
    try
    {
        string sql = "Select d.DoctorID From Doctor_Master d,Patient_registration p where d.LastName + ' ' + d.FirstName = '" + DrName + "' AND datepart(mm,p.[RegDate])=@month AND datepart(yyyy,p.[RegDate])=@year AND p.DoctorID=" + refDrID;
        cmd = new OleDbCommand(sql, acccon);
        cmd.Parameters.AddWithValue("@month", month);
        cmd.Parameters.AddWithValue("@year", year);
        rs = cmd.ExecuteReader();
        while (rs.Read())
        {
            data = Convert.ToInt32(rs[0]);
        }
    }
    catch (Exception err)
    {
        MessageBox.Show(err.Message.ToString());
    }
    return data;
}

I am passing month and year values like this ,Where I am doing mistake ?

int month = dateTimePickerMonth.Value.Month;
int year = dateTimePickerMonth.Value.Year;

Answer 1

There is mistake in your query, please do like this,

 string sql = "Select d.DoctorID From Doctor_Master d,Patient_registration p where d.LastName + ' ' + d.FirstName = '" + DrName + "' AND datepart("m",p.[RegDate])=@month AND datepart("yyyy",p.[RegDate])=@year AND p.DoctorID=" + refDrID;

Note: You have to enclosed date interval with "" in DatePart function in ms access and only single m for month. If you didn't enclosed yyyy and m with "", system supposed that yyyy and m are parameter and expected value for these parameter.

If you execute your query in Ms-Aceess sql view then it will show this message "Enter Paremeter Value".

Answer 2

I think you need to check 3 issues

• Check this int month = dateTimePickerMonth.Value.Month; int year = dateTimePickerMonth.Value.Year; values are not null or empty

• The date and time is a datetime datatype , so why you assign this value in integer ? So It's converted to different format .

• And your d.LastName column value is null , If you give the empty values ,then try this way

d.LastName = '"+ string.Empty + " ',.. ..... 

Do check these conditions and try again .