asp.net sql или проблема с коллекцией

Ниже приведен код на Placementsearch.aspx.cs

protected void Ok_Click(object sender, EventArgs e) { try { if (Page.IsValid) { int course_id = Convert.ToInt32(course.SelectedValue); int passoutYear = Convert.ToInt32(passout.SelectedValue); int currentBacklog = Convert.ToInt32(currrentBacklogDDL.SelectedValue); int sex = Convert.ToInt32(gender.SelectedValue); int? eGap = null; int? firstYrPercent = null; int? secondYrPercent = null; int? thirdYrPercent = null; int? finalYearpercent = null; int? currentDegeePercentage = null; int? highSchoolPercentge = null; int? higherSchoolPercentage = null; int? grauationPercentage = null; int? diplomaPercentage = null; int? noOfAtkt = null; DateTime? date = null; if (!string.IsNullOrEmpty(DOB.Text.Trim())) { string dateOfBirth = DOB.Text.Trim(); DateTime birthDate = DateTime.ParseExact(dateOfBirth, "dd/mm/yyyy", System.Globalization.CultureInfo.InvariantCulture); string outPut = birthDate.ToString("mm/dd/YYYY"); date = Convert.ToDateTime(outPut); } if (!String.IsNullOrEmpty(educationGap.Text.Trim())) { eGap = Convert.ToInt32(educationGap.Text.Trim()); } if (!string.IsNullOrEmpty(firstYear.Text)) { firstYrPercent = Convert.ToInt32(firstYear.Text.Trim()); } if (!string.IsNullOrEmpty(secondYear.Text.Trim())) { secondYrPercent = Convert.ToInt32(secondYear.Text.Trim()); } if (!string.IsNullOrEmpty(thirdYear.Text)) { thirdYrPercent = Convert.ToInt32(thirdYear.Text.Trim()); } if (!string.IsNullOrEmpty(finalyear.Text.Trim())) { finalYearpercent = Convert.ToInt32(finalyear.Text.Trim()); } if (!string.IsNullOrEmpty(currentDegree.Text.Trim())) { currentDegeePercentage = Convert.ToInt32(currentDegree.Text.Trim()); } if (!string.IsNullOrEmpty(higherSchool.Text.Trim())) { higherSchoolPercentage = Convert.ToInt32(higherSchool.Text.Trim()); } if (!string.IsNullOrEmpty(highSchool.Text.Trim())) { highSchoolPercentge = Convert.ToInt32(highSchool.Text.Trim()); } if (!string.IsNullOrEmpty(graduation.Text.Trim())) { grauationPercentage = Convert.ToInt32(graduation.Text.Trim()); } if (!string.IsNullOrEmpty(diploma.Text.Trim())) { diplomaPercentage = Convert.ToInt32(diploma.Text.Trim()); } if (!string.IsNullOrEmpty(atkt.Text.Trim())) { noOfAtkt = Convert.ToInt32(atkt.Text.Trim()); } Dictionary<string, object> paramList = new Dictionary<string, object>(); paramList.Add("@courseId", course_id); paramList.Add("@passoutYear", passoutYear); paramList.Add("@currentBacklog", currentBacklog); paramList.Add("@sex", sex); paramList.Add("@eGap", eGap); paramList.Add("@firstYrPercent", firstYrPercent); paramList.Add("@secondYrPercent", secondYrPercent); paramList.Add("@thirdYrPercent", thirdYrPercent); paramList.Add("@finalYearpercent", finalYearpercent); paramList.Add("@currentDegeePercentage", currentDegeePercentage); paramList.Add("@highSchoolPercentge", highSchoolPercentge); paramList.Add("@higherSchoolPercentage", higherSchoolPercentage); paramList.Add("@grauationPercentage", grauationPercentage); paramList.Add("@diplomaPercentage", diplomaPercentage); paramList.Add("@noOfAtkt", noOfAtkt); paramList.Add("@date", date); StringBuilder branchId= new StringBuilder(); foreach (ListItem li in branch.Items) { if (li.Selected) { branchId.Append(Convert.ToInt32(li.Value)); } } DataTable dt = searchManager.GetEligibleStudent(paramList, branchId); } } catch (Exception ex) { COMMON.logger.Error("Error On Button click Ok", ex); } 

это метод, который вызывается со страницы выше. этот метод находится на классе searchstudentDAO.cs

 public DataTable GetEligibleStudent(Dictionary<string, object> paramList, StringBuilder branchId) { try { string cmd = @"SELECT * FROM [tbl_students] WHERE course_id=@courseId AND branch_id IN(" + branchId + @") AND (@firstYrPercent is null OR first_year_percent>=@firstYrPercent) AND (@secondYrpercent is null OR second_year_percent>=@secondYrPercent) AND (@thirdYrPercent is null OR third_year_percent>=@thirdYrPercent) AND (@finalYearpercent is null OR final_year_percent>=@finalYearpercent) AND (@currentDegeePercentage is null OR current_degree_percent>=@currentDegeePercentage) AND (@passoutYear is null OR passing_year>=@passoutYear) AND (@currentBacklog is null OR current_backlog<=@currentBacklog) AND gender=@sex AND (@eGap is null OR gapin_education<=@eGap) AND (@highSchoolPercentge is null OR highschool_percentage>=@highSchoolPercentge) AND (@higherSchoolPercentage is null OR ssc_percentage>=@higherSchoolPercentage) AND (@grauationPercentage is null OR graduation_percentage>=@grauationPercentage) AND (@diplomaPercentage is null OR diploma_percentage>=@diplomaPercentage) AND (@noOfAtkt is null OR number_of_ATKT<=@noOfAtkt) AND (@date is null OR DOB>=@date)"; //string cmd = "SELECT * FROM [tbl_branch] WHERE course_id IN(" + sb + ")"; if (dbManager.OpenConnection()) { dt = dbManager.GetDataTable(cmd, paramList); return dt; } else { dt = null; return dt; } } catch (Exception ex) { COMMON.logger.Error("Error ON Method Getting Eligible Students:SearchStudentDAO", ex); dt = null; return dt; throw; } finally { dbManager.CloseConnection(); } } 

И followin – это код, вызываемый из приведенного выше метода на paage App_Code / DBManager.cs

 public DataTable GetDataTable(string strCmd, Dictionary<string, object> paramList) { try { DataTable dt = new DataTable(); SqlCommand cmd = new SqlCommand(strCmd, _sqlCon); foreach (KeyValuePair<string, object> kvp in paramList) { cmd.Parameters.AddWithValue(kvp.Key, kvp.Value); } SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(dt); return dt; } catch (Exception ex) { logger.Error("Error when executing Query " + strCmd, ex); throw; return null; } } 

Теперь я получаю исключение для тех нулевых параметров, которые не указаны «Parameterized Query» (@courseId int, @ passoutYear int, @ currentBacklog int, @ sex int, @ eG 'ожидает параметр @finalYearpercent, который не был указан. "

пожалуйста, поправьте меня, где я ошибаюсь

Вместо того, чтобы просто не добавлять эти параметры, если они равны нулю, вам нужно добавить их, но с «значением», установленным в DBNull.Value.