join problem from linq to sql

2 posts, 0 answers
  1. mehmood ahmed
    mehmood ahmed avatar
    7 posts
    Member since:
    Dec 2009

    Posted 31 Dec 2009 Link to this post

    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:




  2. Damyan Bogoev
    Admin
    Damyan Bogoev avatar
    581 posts

    Posted 04 Jan 2010 Link to this post

    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.
  3. DevCraft banner
Back to Top