sql query and asp.net
hello friends following is my query where displaylist is object if stringBuilder which have zero length sometimes but in that case it gives error incorrect syntax near FROM so how can i avoid this problem do i need to write a different query in if else form to avoid this or please sugges开发者_开发技巧t me a best solution to make and also from efficiency and performance point of view
string cmd = @"SELECT [tbl_course].course_name as Course_Name , [tbl_branch].branch_name as Branch_Name,"+displayList+@" FROM [tbl_students], [tbl_course], [tbl_branch]
WHERE [tbl_students].course_id= @courseId
AND [tbl_students].branch_id IN(" + branchId + @")
AND (@firstYrPercent is null OR[tbl_students].first_year_percent>=@firstYrPercent)
AND (@secondYrpercent is null OR[tbl_students].second_year_percent>=@secondYrPercent)
AND (@thirdYrPercent is null OR[tbl_students].third_year_percent>=@thirdYrPercent)
AND (@finalYearpercent is null OR[tbl_students].final_year_percent>=@finalYearpercent)
AND (@currentDegeePercentage is null OR[tbl_students].current_degree_percent>=@currentDegeePercentage)
AND (@passoutYear is null OR[tbl_students].passing_year>=@passoutYear)
AND (@currentBacklog is null OR[tbl_students].current_backlog<=@currentBacklog)
AND [tbl_students].gender=@sex
AND (@eGap is null OR [tbl_students].gapin_education<=@eGap)
AND (@highSchoolPercentge is null OR[tbl_students].highschool_percentage>=@highSchoolPercentge)
AND (@higherSchoolPercentage is null OR[tbl_students].ssc_percentage>=@higherSchoolPercentage)
AND (@grauationPercentage is null OR[tbl_students].graduation_percentage>=@grauationPercentage)
AND (@diplomaPercentage is null OR[tbl_students].diploma_percentage>=@diplomaPercentage)
AND (@noOfAtkt is null OR[tbl_students].number_of_ATKT<=@noOfAtkt)
AND (@validDate is null OR[tbl_students].DOB<=@validDate)
AND [tbl_students].branch_i
d=[tbl_branch].branch_id AND [tbl_students].course_id=[tbl_course].course_id";
The problem is the comma right before you insert the value of displayList. If displayList is empty, then your SQL statement looks like this:
...[tbl_branch].branch_name as Branch_Name, FROM [tbl_students]...
So either include the comma in dispalyList (displayList = ", [tbl_branch].branch_foo"
) or use a conditional statement, when inserting it into the SQL:
string cmd = @"SELECT [tbl_course].course_name as Course_Name , [tbl_branch].branch_name as Branch_Name" + String.IsNullOrEmpty(displayList) ? "" : ", " + displayList + @" FROM [tbl_students]...";
精彩评论