我有一个返回信息的datatable方法,但是我的参数出了问题,它一直抛出一个必须声明变量'@SUPartnerNo'的错误。我已经尝试过谷歌搜索,但是我找到的解决方案都不能帮助我。而且我不想直接将我的参数嵌入到查询中,因为这不是好的做法。
public DataTable SearchInvoiceHeader(string SUPartnerNo, string invoiceNo, string bdate, string edate)
{
DataTable dt = new DataTable();
try
{
StringBuilder mySelectQuery = new StringBuilder(9000);
mySelectQuery.AppendLine(@"SELECT I.[InvoiceNo]
,I.[SUPartnerNo]
,I.[OrderNo]
,I.[RefNo]
,I.[DocType]
,I.[SAP_Type]
,I.[SUName]
,I.[InvoiceDate]
,I.[PaymentDate]
,I.[BaseLineDate]
,I.[DeliveryDate]
,I.[DeliveryNoteNo]
,I.[TotalAmount]
,I.[StartTime]
,p.ProcessType
,s.DocDate
,s.IDocNo
,s.TotalValue
FROM [dbo].[mainhead] I WITH (NOLOCK)
LEFT JOIN [INV_Processed] p WITH (NOLOCK)
ON p.InvoiceNo = I.InvoiceNo
AND p.PartnerNo = I.SUPartnerNo
LEFT JOIN dbo.INV_SAP s WITH (NOLOCK)
ON s.InvoiceNo = I.InvoiceNo
AND s.SupplierNo = I.SUPartnerNo
WHERE
(I.SUPartnerNo =@SUPartnerNo) OR (I.InvoiceNo = @InvoiceNo)
and I.[StartTime] between @bdate and @edate");
SqlConnection myConnection;
myConnection = new SqlConnection(ConnectionString);
SqlCommand myCommand = new SqlCommand(mySelectQuery.ToString());
myCommand.Parameters.AddWithValue("@SUPartnerNo", SUPartnerNo);
myCommand.Parameters.AddWithValue("@InvoiceNo", invoiceNo);
myCommand.Parameters.AddWithValue("@bdate", bdate);
myCommand.Parameters.AddWithValue("@edate", edate);
myCommand.Connection = myConnection;
myConnection.Open();
SqlDataAdapter mytable = new SqlDataAdapter(mySelectQuery.ToString(), myConnection);
myCommand.ExecuteNonQuery();
mytable.Fill(dt);
myConnection.Close();
mytable.SelectCommand = myCommand;
(不指定 myCommand 的 Connection 属性)。 - user2864740