当我运行 ExecuteScalar 时获取 sqlException

本文关键字：获取 sqlException ExecuteScalar 运行 | 更新日期: 2023-09-27 18:32:32

我创建了一个使用 ExecuteScalar 从数据库中获取标量的方法，但它抛出了异常。SQL 选择语句对我来说看起来是正确的。有人可以帮我吗？

我得到的确切错误是

"发票"附近的语法不正确。

法典：

public static String GetTotalBalanceDue() 
{
        decimal totalBalanceDue;
        string selectStatement =
            "SELECT SUM(InvoiceTotal - PaymantTotal - CreditTotal) " +
            "AS BalanceDue FROM Invoices" +
            "WHERE InvoiceTotal - PaymantTotal - CreditTotal > 0";
        try
        {
            using (SqlConnection connection = PayablesDBConnection.GetConnection())
            {
                connection.Open();
                using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
                {
                    totalBalanceDue = (decimal)selectCommand.ExecuteScalar();
                }
            }
        }
        catch (SqlException ex)
        {
            //exceptions are thrown to the controller, then to the view
            //Please make sure that do not use MessageBox.Show(ex.Message) in the DAL
            //because it couples the DAL with the view
            //throw is used instead of throw ex because the former preserves the stack trace
            throw;
        }
        catch (Exception ex)
        {
            throw;
        }
        return System.Convert.ToString(totalBalanceDue);
    }

我将我的选择语句修改为：

            string selectStatement =
                "SELECT SUM(InvoiceTotal - PaymentTotal - CreditTotal)" +
                "AS BalanceDue" +
                "FROM Invoices " +
                " WHERE vendorID =" + vendorID;

但我仍然得到

"发票"附近的语法不正确。

当我运行 ExecuteScalar 时获取 sqlException

这是因为您返回的是记录集，而不是标量

更新：还因为表名和 where 子句的开头之间没有空格。 我相应地更新了示例

编写查询时，它将返回符合您条件的所有发票。因此，如果您有 10 张发票的到期总余额> 0，您将获得 10 条记录。

如果要获取标量值，则必须将查询限制为一行。我要用发票 ID 猜测

string selectStatement =
            "SELECT SUM(InvoiceTotal - PaymantTotal - CreditTotal) " +
            "AS BalanceDue FROM Invoices" +
            " WHERE InvoiceTotal - PaymantTotal - CreditTotal > 0 AND INVOICEID = " + InvoiceId.ToString();

否则，您将不得不更新代码以能够容纳多行结果

尝试

public static String GetTotalBalanceDue() 
    {
        decimal totalBalanceDue;
        DataTable results = new DataTable();
        string selectStatement =
            "SELECT SUM(InvoiceTotal - PaymantTotal - CreditTotal) " +
            "AS BalanceDue FROM Invoices" +
            " WHERE InvoiceTotal - PaymantTotal - CreditTotal > 0";
        try
        {
            using (SqlConnection connection = PayablesDBConnection.GetConnection())
            {
                connection.Open();
                using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
                {
                     using(SqlDataAdapter adapter = new SqlDataAdapter(selectCommand))
{
    adapter.Fill(results);
}
                }
            }

        }
        catch (SqlException ex)
        {
            //exceptions are thrown to the controller, then to the view
            //Please make sure that do not use MessageBox.Show(ex.Message) in the DAL
            //because it couples the DAL with the view
            //throw is used instead of throw ex because the former preserves the stack trace
            throw;
        }
        catch (Exception ex)
        {
            throw;
        }
<</div> div class="answers">

修改 

 string selectStatement =
            "SELECT SUM(InvoiceTotal - PaymantTotal - CreditTotal) " +
            "AS BalanceDue FROM Invoices" +
            "WHERE InvoiceTotal - PaymantTotal - CreditTotal > 0";

自：

string selectStatement =
            "   SELECT SUM(InvoiceTotal - PaymantTotal - CreditTotal) " +
            "   AS BalanceDue FROM Invoices" +
            "   WHERE InvoiceTotal - PaymantTotal - CreditTotal > 0";
相关文章：