动态SQL查询

Question

我正在尝试基于年份、学院、季度和课程检索信息。这取决于用户想要的具体程度(年度、季度、课程等)。

    string a = "";

    if (DDLYear.SelectedValue == "Select")
    {
       return;
    }
    else
     {
        a = DDLYear.SelectedValue;
     }

 if (DDLFaculty.SelectedValue != "Select")
            {
                arrFields.Add("Employee.Employee_ID = " + DDLFaculty.SelectedValue);
            }
            if (DDLQuarter.SelectedValue != "Select")
            {
                arrFields.Add("Quarter.Quarter_Name = " + DDLQuarter.SelectedValue);
            }
            if (DDLCourse.SelectedValue != "Select")
            {
                arrFields.Add("Course.Title = " + DDLCourse.SelectedValue);
            }

 Custom.SelectCommand = Custom.SelectCommand = 
   "SELECT AVG(Rating.Score) AS YearsAverageScore 
    FROM Rating INNER JOIN Survey ON Rating.Survey_ID = Survey.Survey_ID 
    INNER JOIN Course_Quarter ON Survey.CourseQuarter_ID = Course_Quarter.CourseQuarter_ID 
   INNER JOIN Quarter ON Course_Quarter.Quarter_ID = Quarter.Quarter_ID 
   INNER JOIN Employee ON Course_Quarter.Employee_ID = Employee.Employee_ID 
   WHERE (Quarter.Year = " + a + String.Join("and ", arrFields.ToArray()) + ")";

当我试图检索2000年关于雇员'A‘的信息时，我遇到了一个错误：“’mployee‘附近的语法不正确”。除了Employee之外，代码中没有'mployee‘。当我在“Employee”之间添加一个空格时，我遇到了同样的错误，除了它说：‘Employee’附近的语法不正确“

错误报告：

Incorrect syntax near 'Employee'. 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'Employee'.

Source Error: 

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.  

Stack Trace: 


[SqlException (0x80131904): Incorrect syntax near 'Employee'.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +2073502
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +5064460
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() +234
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2275
   System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +33
   System.Data.SqlClient.SqlDataReader.get_MetaData() +86
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +311
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +987
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141
   System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12
   System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +10
   System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +144
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +319
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +92
   System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1618
   System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +21
   System.Web.UI.WebControls.DataBoundControl.PerformSelect() +143
   System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +74
   System.Web.UI.WebControls.GridView.DataBind() +4
   System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +66
   System.Web.UI.WebControls.GridView.OnPreRender(EventArgs e) +26
   System.Web.UI.Control.PreRenderRecursiveInternal() +103
   System.Web.UI.Control.PreRenderRecursiveInternal() +175
   System.Web.UI.Control.PreRenderRecursiveInternal() +175
   System.Web.UI.Control.PreRenderRecursiveInternal() +175
   System.Web.UI.Control.PreRenderRecursiveInternal() +175
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2496

Answer 1

您应该在替换后给我们SQL，但您的问题可能是其中之一：

和你得到的是"Quarter.Year =a .“由于“a”后面缺少一个空格，所以您正在与我猜测的字符串字段(QuarterName、Course Title)进行比较的值没有引号，并且字符串开头的“”可能不会出现。

Answer 2

在SQL中对字符串使用相等时，需要用引号将其括起来。通常是'。如果列类型是numeric (即long、int等)，则不需要引号。

您也可以使用String.Format，这将提高可读性。一个这样的例子是：String.Format("Quarter.Quarter_Name = '{0}'", DDLQuarter.SelectedValue.ToString());

以下是字符串相等修复后的原始代码：

 if (DDLFaculty.SelectedValue != "Select")
        {
            arrFields.Add("Employee.Employee_ID = '" + DDLFaculty.SelectedValue + "'");
        }
        if (DDLQuarter.SelectedValue != "Select")
        {
            arrFields.Add("Quarter.Quarter_Name = '" + DDLQuarter.SelectedValue + "'");
        }
        if (DDLCourse.SelectedValue != "Select")
        {
            arrFields.Add("Course.Title = '" + DDLCourse.SelectedValue + "'");
        }