CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2011
    Posts
    2

    Angry 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

  2. #2
    Join Date
    Feb 2011
    Location
    United States
    Posts
    1,016

    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.

  3. #3
    Join Date
    Jul 2011
    Posts
    2

    Angry 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'.

  4. #4
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,490

    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
  •  





Click Here to Expand Forum to Full Width

Featured