-
July 3rd, 2011, 09:05 AM
#1
C# SQL connect with 3 tables
Please see my below SQL connecting MS Access 2007
i need to connect 3 table using with INNER JOIN
see below code
Code:
txtOrderNumber.Text = ReccursingOrderNumber;
string FindRecurssinEditSQL = "SELECT ST.Select_order_price AS Select_order_price,ST.Reference_Number AS RNumber,ST.Order_Date As Odate, ST.CustomerID AS STCustomerID, ST.Sales_Person_Name as STSales_Person_Name, ST.Delivery_To as STDelivery_To, ST.Sales_Type As STSales_Type, ST.Remarks As STRemark, ST.Total_Price As STTotal_Price, ST.Discount As STDiscount, ST.Tax As STTax, ST.NetAmount As STNetAmount, ST.ShipAddress As STShipAddress, ST.Shipby As STShipby, ST.ShippingCost AS STShippingCost, ST.PrivateNote AS STPrivateNote, ST.Terms AS STTerms, ST.NumberofDays AS STNumberofDays, ST.AmountDue AS STAmountDue, ST.Address AS STAddress, ST.Method_of_Payments AS STMethod_of_Payments,";
FindRecurssinEditSQL += "ProductProcess_Details.OrderNumber AS P_OrderNumber, ProductProcess_Details.SubProduct_Number AS P_SubProduct_Number, ProductProcess_Details.Quantity AS P_Quantity, ProductProcess_Details.Retail_Price AS P_Retail_Price,ProductProcess_Details.Old_Recurrsion_Retail_Price AS P_Old_Recurrsion_Retail_Price,";
FindRecurssinEditSQL += "SB.S_Product_Name AS SB_S_Product_Name,SB.S_Measure_Type As S_Measure_Type,";
FindRecurssinEditSQL += "Cus.Cus_Fname AS Cus_Fname";
FindRecurssinEditSQL += " ";//createing space for Sql From tag
FindRecurssinEditSQL += "From (StockControl As ST INNER JOIN SubProduct AS SB ON SB.S_Product_Code=ProductProcess_Details.SubProduct_Number) (INNER JOIN Customer_Details Cus ON Cus.Customer_ID=ST.CustomerID) Where ST.Order_Number='" + ReccursingOrderNumber + "' AND ProductProcess_Details.OrderNumber='" + ReccursingOrderNumber + "'";
//MessageBox.Show(FindRecurssinEditSQL.ToString());
//creating Olecommand
OleDbCommand Editcmd = con.CreateCommand();
Editcmd.CommandText = FindRecurssinEditSQL;
OleDbDataReader editReader = Editcmd.ExecuteReader();
ERROr is
Syntax error in JOIN operation.
its worked with 2 join but when i connect with 3 table its showing Error.
please show me where i'm doing mistake.thx
-
July 3rd, 2011, 11:33 AM
#2
Re: C# SQL connect with 3 tables
This is not an independent clause:
Code:
(INNER JOIN Customer_Details Cus ON Cus.Customer_ID=ST.CustomerID)
I think your statement will work if you remove the ") (" immediately before the second inner join making it:
Code:
FindRecurssinEditSQL += "From (StockControl As ST INNER JOIN SubProduct AS SB ON SB.S_Product_Code=ProductProcess_Details.SubProduct_Number INNER JOIN Customer_Details Cus ON Cus.Customer_ID=ST.CustomerID) Where ST.Order_Number='" + ReccursingOrderNumber + "' AND ProductProcess_Details.OrderNumber='" + ReccursingOrderNumber + "'";
Best guess though from just reading your code.
Victory?
Best Regards,
BioPhysEngr
http://blog.biophysengr.net
--
All advice is offered in good faith only. You are ultimately responsible for effects of your programs and the integrity of the machines they run on.
-
July 3rd, 2011, 03:04 PM
#3
Re: C# SQL connect with 3 tables
I try using your above statement but still i'm getting below Error, i just wondering how to put brackets to make it separatly
Syntax error (missing operator) in query expression 'SB.S_Product_Code=ProductProcess_Details.SubProduct_Number INNER JOIN Customer_Details Cus ON Cus.Customer_ID=ST.CustomerI'.
-
July 3rd, 2011, 05:26 PM
#4
Re: C# SQL connect with 3 tables
Create the join syntax in the Access designer and copy the generated sql into your c# code. Fix up as appropriate.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|