如何解决这个MS sql查询它给了我错误 [英] how to solve this MS sql query it gives me error

问题描述

cmd = new SqlCommand("SELECT (invoiceNo) as [Invoice No],(InvoiceDate) as [Invoice Date],(Sales.CustomerID) as [Customer ID],(CustomerName) as [Customer Name],(GrandTotal) as [Grand Total],(TotalPayment) as [Total Payment],(PaymentDue) as [Payment Due] from Sales,Customer where Sales.CustomerID=Customer.CustomerID and InvoiceDate between #" + DateTimePicker2.Text + "# And #" + DateTimePicker1.Text + "# and PaymentDue > 0 order by InvoiceDate desc", con);

推荐答案

您可以使用更加格式化的字符串构建，以便更容易看到错误。

You can use a more formatted string build up to make it easier to see the error.

    StringBuilder cmdString = new StringBuilder();
    cmdString.Append(@"SELECT (invoiceNo) as [Invoice No]");
    cmdString.Append(@",(InvoiceDate) as [Invoice Date]");
    cmdString.Append(@",(Sales.CustomerID) as [Customer ID]");
    cmdString.Append(@",(CustomerName) as [Customer Name]");
    cmdString.Append(@",(GrandTotal) as [Grand Total]");
    cmdString.Append(@",(TotalPayment) as [Total Payment]");
    cmdString.Append(@",(PaymentDue) as [Payment Due]");
->  cmdString.Append(@"from Sales, Customer");
    cmdString.Append(@"where Sales.CustomerID = Customer.CustomerID");
    cmdString.Append(@"and InvoiceDate between #");
    cmdString.Append(DateTimePicker2.Text);
    cmdString.Append(@"# And #");
    cmdString.Append(DateTimePicker1.Text);
    cmdString.Append(@"# and PaymentDue > 0");
    cmdString.Append(@"order by InvoiceDate desc");

看看 - >指出的行。

语法是否正确？

Have a look at the line pointed out by ->.
Is that syntax correct?

我要做的第一件事是重构查询以将各种AND语句放在括号中;



例如

First thing I would do is restructure the query to put the various AND statements in brackets;

e.g.

cmd = new SqlCommand("SELECT (invoiceNo) as [Invoice No],
                             (InvoiceDate) as [Invoice Date],
                             (Sales.CustomerID) as [Customer ID],
                             (CustomerName) as [Customer Name],
                             (GrandTotal) as [Grand Total],
                             (TotalPayment) as [Total Payment],
                             (PaymentDue) as [Payment Due]
                      FROM Sales,Customer
                      WHERE (Sales.CustomerID = Customer.CustomerID) 
                      AND   (InvoiceDate BETWEEN #" + DateTimePicker2.Text + "# 
                                             AND #" + DateTimePicker1.Text + "#)
                      AND   (PaymentDue > 0)
                      ORDER BY InvoiceDate desc"
               , con);

检查日期字段确保你没有在早期版本之前获得后一个。



另外，格式化DateFields以确保它们正确地出现在SQL中。我总是格式化到YYYY-MMM-DD所以总是没有歧义。



您是否测试了两个单独的部分？即销售数据和使用已知变量的客户数据？然后将这两个部分连接起来作为一个完整的查询进行测试。

Check the date fields to make sure you haven't got the later one before the early one.

Also, Format the DateFields to make sure they are appearing correctly to SQL. i always format to YYYY-MMM-DD so there is always no ambiguity.

Did you test the two seperate parts? i.e. the Sales data and the Customer data using known variables? then join the two parts together to test as a complete query.

cmd = new SqlCommand（SELECT（invoiceNo） ）作为[Invoice No]，（InvoiceDate）作为[发票日期]，（Sales.CustomerID）作为[客户ID]，（CustomerName）作为[客户名称]，（GrandTotal）作为[Grand Total]，（TotalPayment）作为[总付款]，（PaymentDue）作为[付款到期]来自销售，客户，其中Sales.CustomerID = Customer.CustomerID和InvoiceDate之间的'+ DateTimePicker2.Text +'和+ DateTimePicker1.Text +和PaymentDue> 0订单由InvoiceDate desc，con）;



尝试使用#use'his this

Hi ,

cmd = new SqlCommand("SELECT (invoiceNo) as [Invoice No],(InvoiceDate) as [Invoice Date],(Sales.CustomerID) as [Customer ID],(CustomerName) as [Customer Name],(GrandTotal) as [Grand Total],(TotalPayment) as [Total Payment],(PaymentDue) as [Payment Due] from Sales,Customer where Sales.CustomerID=Customer.CustomerID and InvoiceDate between '" + DateTimePicker2.Text + "' And '" + DateTimePicker1.Text + "' and PaymentDue > 0 order by InvoiceDate desc", con);

Try to use instead # use ' this one

这篇关于如何解决这个MS sql查询它给了我错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！