大家好,我对从不同表中选择多个字段并将其绑定到一个结果中感到困惑,为了让您更好地理解它,我在下面发布了我的代码和ERD。感谢您的答复。请告诉我我在哪里错了tnx :)
这是我的ERD:
这是代码:
try
{
OleDbConnection Con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\\MotoFix.mdb;");
OleDbCommand command = new OleDbCommand();
OleDbDataAdapter adapter = new OleDbDataAdapter();
DataTable dt = new DataTable();
Con.Open();
command.CommandText = String.Format("SELECT fn.Customer + ' ' + mi.Customer + ' ' + ln.Customer as [CUSTOMER FULLNAME], fn.Cashier + ' ' + mi.Cashier + ' ' + ln.Cashier as [CASHIER FULLNAME], prodName.Product as [PRODUCT NAME], prodDescription.Product as [PRODUCT DESCRIPTION], prodBrand.Product as [PRODUCT BRAND], prodQuantity.Transaction as [PRODUCT QUANTITY], prodTotalPrice.Transaction as [SUBTOTAL], job.Personnel as [Referral] FROM Product , [Order] , [Transaction] , Customer , Cashier , Personnel WHERE prodCode.Product = ProdCode.Order AND orderNo.Order = orderNo.Transaction AND pID.Personnel = pID.Transaction AND custID.Customer = custID.Transaction AND userID.Cashier = userID.Transaction AND sDate.Transaction = '{0}'", strDate);
command.Connection = Con;
adapter.SelectCommand = command;
adapter.Fill(dt);
Con.Close();
Con.Dispose();
gridViewTransac_1.DataSource = dt;
}
catch (Exception ex)
{
XtraMessageBox.Show(ex.Message);
}
附加信息:没有为一个或多个必需参数提供值。
我不是一个好的sql查询生成器,但是它不应该是TableName.FieldName而不是其他方法吗?
无论如何,尝试将TableName放在首位:
SELECT Customer.fn + ' ' + customer.mi + ' ' + customer.ln as [CUSTOMER FULLNAME]
, Cashier.f. + ' ' + Cashier.mi + ' ' + customer.ln as [CASHIER FULLNAME]
, Product.prodName as [PRODUCT NAME]
, Product.prodDescription as [PRODUCT DESCRIPTION]
, Product.prodBrand as [PRODUCT BRAND]
, Transaction.prodQuantity as [PRODUCT QUANTITY]
, Transaction.prodTotalPrice as [SUBTOTAL]
, Personnel.job. as [Referral]
FROM Product
, [Order]
, [Transaction]
, Customer
, Cashier
, Personnel
WHERE Product.prodCode = Order.ProdCode
AND Order.orderNo = Transaction.orderNo
AND Personnel.pID= Transaction.pID
AND Customer.custID = Transaction.custID
AND Cashier.userID = Transaction.userID
AND Transaction.sDate = @transDate
对于@transDate,您应该添加:
command.Parameters.AddWithValue("@transDate", someDate);
或者你可以像以前那样简单地做
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句