如何从MS Access数据库中的超过2个表中检索数据 [英] how to retrive data from more then 2 tables from MS access database

问题描述

我想从彼此连接的3个表中检索MS Access数据库中的数据.
我在业务层中编写了此代码

i want to retrive data from MS Access database from 3 tables connected with each other..
i wrote this code in business layer

public List<ProductOrderModel> Show()
       {
            OleDbConnection cn;
            try
            {
                ProductOrderModel dtoobj = new ProductOrderModel();
                DataLayer dalobj = new DataLayer();
                OleDbCommand cmdshow = new OleDbCommand();

                cmdshow.CommandText = "select [CustomerVT].[Customer_Name],[OrderVT].[Order_Date],[ProductVT].[Price],[ProductVT].[Item_Name],[OrderVT].[Quantity],[OrderVT].[Order_ID] from  [OrderVT] inner join [CustomerVT] on [OrderVT].[Customer_ID]=[CustomerVT].[Customer_ID] inner join [ProductVT] on [OrderVT].[Product_ID]=[ProductVT].[Product_ID] ";

                 List<ProductOrderModel> Ldemo = new List<ProductOrderModel>();

                return dalobj.executereader(Ldemo, cmdshow, "BillingData");



            }
            catch (Exception ex2)
            {
                throw new DataException("error....." + ex2.Message);

            }

       }

和executereader()的代码是.

and code for executereader() is.

public List<ProductOrderModel> executereader(List<ProductOrderModel> Ldemo, OleDbCommand cmdshow, string tablename)
        {
            OleDbConnection cn;
            try
            {
                cn = this.getconnection();

                cmdshow.Connection = cn;
                cn.Open();

                OleDbDataReader rd = cmdshow.ExecuteReader();
                while (rd.Read())
                {
                    ProductOrderModel dtoobj1 = new ProductOrderModel();

                    dtoobj1.InvoiceNo = Convert.ToInt32(rd["Order_ID"].ToString());
                    dtoobj1.CustomerName = rd["Customer_Name"].ToString();
                   
                   dtoobj1.ItemName = rd["Item_Name"].ToString();
                    dtoobj1.Quantity = Convert.ToInt32(rd["Quantity"].ToString());
                    dtoobj1.Price = Convert.ToInt32(rd["Price"].ToString());
                    dtoobj1.OrderDate = Convert.ToDateTime(rd["Order_Date"].ToString());
                  
                

                    Ldemo.Add(dtoobj1);

                }
                cn.Close();
                return Ldemo;
            }
            catch (Exception ex2)
            {
                throw new DataException("error....." + ex2.Message);

            }

        }

但显示错误，即查询表达式"[OrderVT]中的语法错误(缺少运算符).[Customer_ID] = [CustomerVT].[Customer_ID]内部联接了[OrderVT]上的[ProductVT].[Product_ID] = [ProductVT ].[Product_ID]''.

请帮忙.

but it shows error that Syntax error (missing operator) in query expression ''[OrderVT].[Customer_ID]=[CustomerVT].[Customer_ID] inner join [ProductVT] on [OrderVT].[Product_ID]=[ProductVT].[Product_ID]''.

please help. thanks in advance.

推荐答案

访问权限一次仅允许连接2个表...但是您可以将其愚弄"为通过用括号将每个连接包围起来，从而获得更多...像这样

Access only allows 2 tables to be joined at a time ... but you can "fool" it into allowing more by surrounding each join with brackets ... like this

select [CustomerVT].[Customer_Name],[OrderVT].[Order_Date],[ProductVT].[Price],
[ProductVT].[Item_Name],[OrderVT].[Quantity],[OrderVT].[Order_ID]
from   (    
[OrderVT]
inner join [CustomerVT] on [OrderVT].[Customer_ID]=[CustomerVT].[Customer_ID] 
     )
inner join [ProductVT] on [OrderVT].[Product_ID]=[ProductVT].[Product_ID]

间距并不重要，我只是这样做是为了引起人们对括号的注意

如果要添加更多表，则可以执行此操作...

The spacing isn''t important, I only did that to draw attention to the brackets

If you want to add more tables then you would do this ...

select [CustomerVT].[Customer_Name],[OrderVT].[Order_Date],[ProductVT].[Price],
[ProductVT].[Item_Name],[OrderVT].[Quantity],[OrderVT].[Order_ID]
from   (  (    
[OrderVT]
inner join [CustomerVT] on [OrderVT].[Customer_ID]=[CustomerVT].[Customer_ID] 
     )
inner join [ProductVT] on [OrderVT].[Product_ID]=[ProductVT].[Product_ID]
)
inner join [AnotherTable] on ....

这篇关于如何从MS Access数据库中的超过2个表中检索数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！