join problem from linq to sql

[mehmood ahmed]

what is the error with it.

and please convert this to lamda expression

 VTSDataContext db = new VTSDataContext();



    



        var saleObj = from s in db.Sales







                join sd in db.SaleDetails on s.Reciept_Number equals sd.Reciept_Number







                join pr in db.Products on sd.product_ID equals pr.product_ID







                where s.Reciept_Number == int.Parse(ddl_RNO_Edit.SelectedItem.Text)







                orderby s.Reciept_Number ascending







                select new







                {



                    s.Sale_Date,



                    s.CustomerID,



                    s.ManagerID,



                    s.SalesPersonID,



                    sd.product_Quantity,



                    pr.product_Name







                };



        foreach (var item in saleObj)



        {



            txt_SDate_Edit.Text = item.Sale_Date.ToString();



            ddl_Customer_Edit.Text = item.CustomerID.ToString();



            txt_SQty_Edit.Text = item.product_Quantity.ToString();



            ddl_ProductName_Edit.Text = item.product_Name.ToString();



            ddl_Manager_Edit.Text = item.ManagerID.ToString();



            ddl_SalesPerson_Edit.Text = item.SalesPersonID.ToString();



        }

tables are 3:

CREATE TABLE [dbo].[Sales](
    [Reciept_Number] [int] NOT NULL,
    [Sale_Date] [datetime] NOT NULL,
    [CustomerID] [int] NULL,
    [ManagerID] [int] NULL,
    [SalesPersonID] [int] NULL,
 CONSTRAINT [PK_Sales] PRIMARY KEY CLUSTERED
(
    [Reciept_Number] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

==========================================================

CREATE TABLE [dbo].[SaleDetails](
    [Reciept_Number] [int] NOT NULL,
    [SNo] [int] NOT NULL,
    [product_ID] [int] NOT NULL,
    [product_Quantity] [int] NOT NULL,
 CONSTRAINT [PK_SaleDetails] PRIMARY KEY CLUSTERED
(
    [Reciept_Number] ASC,
    [SNo] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

===============================================================
CREATE TABLE [dbo].[Products](
    [product_ID] [int] NOT NULL,
    [product_Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [product_Category_ID] [int] NOT NULL CONSTRAINT [DF_Table_1_CategoryID]  DEFAULT ((0)),
    [product_Price] [int] NOT NULL,
    [product_Image] [image] NULL,
    [product_Description] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
(
    [product_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

when i try to debug then it throw an exception iin this line:

   ddl_ProductName_Edit.Text = item.product_Name.ToString();







exception is this:

System.ArgumentOutOfRangeException was unhandled by user code
  Message="'ddl_ProductName_Edit' has a SelectedValue which is invalid because it does not exist in the list of items.\r\nParameter name: value"
  Source="System.Web"
  ParamName="value"
  StackTrace:
       at System.Web.UI.WebControls.ListControl.set_SelectedValue(String value)
       at System.Web.UI.WebControls.ListControl.set_Text(String value)
       at SalesPerson_AddSale.ddl_RNO_Edit_SelectedIndexChanged(Object sender, EventArgs e) in e:\Lectures\sullAll\lectures\finalProject\Final Project Application\VehicleTrackingSystem\SalesPerson\AddSale.aspx.cs:line 218
       at System.EventHandler.Invoke(Object sender, EventArgs e)
       at System.Web.UI.WebControls.ListControl.OnSelectedIndexChanged(EventArgs e)
       at System.Web.UI.WebControls.DropDownList.RaisePostDataChangedEvent()
       at System.Web.UI.WebControls.DropDownList.System.Web.UI.IPostBackDataHandler.RaisePostDataChangedEvent()
       at System.Web.UI.Page.RaiseChangedEvents()
       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
  InnerException:

[Damyan Bogoev]

Hi mehmood ahmed,

The exception is thrown when you try to edit an entry that contains data that has been deleted. You could avoid the error by following these steps:
1. Remove the SelectedValue property from the dropdown list to remove your binding;
2. Add a default ListItem to the dropdown list:

<asp:ListItem Text="Please Select" Value=""></asp:ListItem>

3. Inside the Page PreRender event handler you should handle the SelectedValue property logic. It will search through the data items bound to your dropdown, and if your value is not found then display the "Please Select" option;

Before using the lambda expression corresponding to the provided Linq query you should add two references - from Sales to SalesDetails and from Products to SalesDetails:
1. Run the Reverse Mapping Wizard
2. Locate and expand the SalesDetails tree. Click on the Reciept_Number node
3. On the right click on the CreateRef button
4. Enter a value for the Field Name – sale. Select a Sale from the Type dropdown. Check the Create one-to-many list option.
5. Inside the Field to Column Mapping box select the Reciept_Number from the Column dropdown for the Sale.recieptNumber
6. Repeat the 1-5 steps to create Products – SalesDetails reference
When you finish with the preceding steps click on the "Generate & Save Config" button to generate the classes.

Now you could use the following query with a lambda expression:

var saleObj = scope.Extent<SaleDetail>().
            Where(sd => sd.RecieptNumber == receiptNumber).
            OrderBy(sd => sd.RecieptNumber).
            Select(sd => new
               {
                sd.Sale.SaleDate, sd.Sale.CustomerID, sd.Sale.ManagerID,
                sd.Sale.SalesPersonID, sd.ProductQuantity, sd.Product.ProductName
               }
            );

Hope that helps.

Greetings,
Damyan Bogoev
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.