locked
Display data from SQL View into a ListView on an ASP.net page / Error: NotSupportedException RRS feed

  • Question

  • User-642568311 posted

    Goal: Display data from SQL View into a ListView on an asp.net page There is a new database with a new view and I need to point it to that

    ERROR:

    System.Reflection.TargetInvocationException: 'Exception has been thrown by the target of an invocation.' Inner Exception NotSupportedException: The specified type member 'Alternate_ID' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.

    System.InvalidOperationException: 'When the DataBoundControl has paging enabled, either the SelectMethod should return an IQueryable or should have all these mandatory parameters : int startRowIndex, int maximumRows, out int totalRowCount'

    Tried: I've looked through the tutorials on listview, documentation, itemtype, data binding, etc. One of the SO I found was : The specified type member is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported Ive looked into the models, views, checked spelling, designer files, pocos, etc.

    CODE:

    default.aspx:

     <asp:ListView id="lstClaims" runat="server" ItemType="Placeholder.Data.vx_EligibilitySearch" SelectMethod="lstMembers_GetData" OnItemCommand="lstMembers_ItemCommand" 
            ItemPlaceholderID="litPlaceHolder">
     <LayoutTemplate>
                <div class="table-responsive">
                    <table class="table">
                        <thead>
                            <tr>
                                <th></th>
                                <th><asp:LinkButton id="Sort_Claim_Number" runat="server" CommandArgument="Group_Description" CommandName="Sort">Group</asp:LinkButton></th>
                                <th><asp:LinkButton id="Sort_Claim_Status" runat="server" CommandArgument="Group_ID" CommandName="Sort">Group ID</asp:LinkButton></th>
                                <th><asp:LinkButton id="Sort_Plan" runat="server" CommandArgument="Alternate_ID" CommandName="Sort">Member ID</asp:LinkButton></th>
    
    </thead>
                        <asp:Literal ID="litPlaceHolder" runat="server" />
                    </table>
                </div>
            </LayoutTemplate>
    
    
    
     <ItemTemplate>
                <tr>
                    <td><%# Item.Group_Description %></td>
                    <td><%# Item.Group_ID %></td>
                    <td><%# Item.Alternate_ID %></td>
    
      </tr>
            </ItemTemplate>
        </asp:ListView>

    Note: protected global::System.Web.UI.WebControls.ListView lstClaims; is in default.aspx.designer.cs

    This points to [ lstClaims.DataBind(); ] :

      if (memberIDIsValid || memberIsValid)
                {
                    ShouldSearch = true;
                    lstClaims.DataBind();
                }

    This is in the method: lnkSearch_Click, which is called from the aspx page here (When clicking the Search button):

    <p><asp:LinkButton ID="lnkSearch" runat="server" OnClick="lnkSearch_Click" CssClass="btn btn-default"><i class="fa fa-search"></i> Search Members</asp:LinkButton></p>

    Method to get Data:

    public IQueryable<vx_EligibilitySearch> lstMembers_GetData()
            {
                litNumSearchResults.Text = "";
    
                // IQueryable<Member> members = null;
                IQueryable<vx_EligibilitySearch> members = null;
    
    
                try
                {
                    if (!ShouldSearch)
                        return members;
    
                    panelSearchForm.Visible = false;
                    lnkButtonBackToSearch.Visible = true;
    
                    members = GetDataFromQuery();
    
    
    public IQueryable<vx_EligibilitySearch> GetDataFromQuery()  
            {
                // IQueryable<Member> members = null;
                IQueryable<vx_EligibilitySearch> members = null;
    
    
    
                try
                {
                    // members = somedb.Members.AsNoTracking().AsQueryable(); //.OrderBy(a => a.Claim_Number)
                    members = somedb.vx_EligibilitySearch.AsNoTracking().AsQueryable(); //.OrderBy(a => a.Claim_Number)
    
    
                    if (!string.IsNullOrEmpty(Search_MemberID))
                        // members = members.Where(m => m.Subscriber_ID == Search_MemberID).AsQueryable();
                        members = members.Where(m => m.Alternate_ID == Search_MemberID).AsQueryable();

    In partial class vx_EligibilitySearch I have:

    public string Alternate_ID { get; set; }

    More:

    namespace Placeholder.Data
    {
        using System;
        using System.Collections.Generic;
    
        public partial class vx_EligibilitySearch
        {
            public string Subscriber_ID { get; set; }
    
    
            public string Group_Description { get; set; }
            public string Group_ID { get; set; }
            public string Alternate_ID { get; set; }
            public string Member_Name { get; set; }
            public string Sex { get; set; }
            public Nullable<System.DateTime> Birth_Date { get; set; }

    DbContext :

    members = somedb.vx_EligibilitySearch.AsNoTracking().AsQueryable(); //.OrderBy(a => a.Claim_Number)

    Going to Definition of somedb takes me to BasePage.cs with:

    protected VBADemoEntities somedb = new VBADemoEntities();

    When I go to definition of VBADemoEntities:

    namespace Placeholder.Data
    
    public partial class VBADemoEntities : DbContext
        {
            public VBADemoEntities()
                : base("name=VBADemoEntities")
            {
            }
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                throw new UnintentionalCodeFirstException();
            }
    
            public virtual DbSet<Auth> Auths { get; set; }
            ...........
            public virtual DbSet<Member> Members { get; set; }
            public virtual DbSet<vx_EligibilitySearch> vx_EligibilitySearch { get; set; }
    Thursday, August 23, 2018 1:00 PM

Answers

  • User475983607 posted

    The error indicates, Alternate_ID, does not exist in the table or view.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, August 24, 2018 2:06 PM

All replies

  • User-893317190 posted

    Hi understandcsharp ,

    I try to simulate your code ,but I couldn't reproduce your problem.

    Below is my code.

    <asp:ListView id="lstClaims" runat="server" ItemType="MyWebFormCases.Models.vx_EligibilitySearch" SelectMethod="lstMembers_GetData" 
            ItemPlaceholderID="litPlaceHolder">
     <LayoutTemplate>
                <div class="table-responsive">
                    <table class="table">
                        <thead>
                            <tr>
                                <th></th>
                                <th><asp:LinkButton id="Sort_Claim_Number" runat="server" CommandArgument="Group_Description" CommandName="Sort">Group</asp:LinkButton></th>
                                <th><asp:LinkButton id="Sort_Claim_Status" runat="server" CommandArgument="Group_ID" CommandName="Sort">Group ID</asp:LinkButton></th>
                                <th><asp:LinkButton id="Sort_Plan" runat="server" CommandArgument="Alternate_ID" CommandName="Sort">Member ID</asp:LinkButton></th>
    
    </thead>
                        <asp:Literal ID="litPlaceHolder" runat="server" />
                    </table>
                </div>
            </LayoutTemplate>
    
    
    
     <ItemTemplate>
                <tr>
                    <td></td>
                    <td><%# Item.Group_Description %></td>
                    <td><%# Item.Group_Id%></td>
                    <td><%# Item.Alternate_Id %></td>
    
      </tr>
            </ItemTemplate>
        </asp:ListView>
    
            <p><asp:LinkButton ID="lnkSearch" runat="server"  CssClass="btn btn-default"><i class="fa fa-search"></i> Search Members</asp:LinkButton></p>
        </form>

    Code behind.

     // my dbcontext
            EntityExe somedb = new EntityExe();
            public IQueryable<vx_EligibilitySearch> lstMembers_GetData()
            {
                //litNumSearchResults.Text = "";
    
      
                IQueryable<vx_EligibilitySearch> members = null;
    
    
                //if (!ShouldSearch)
                //    return members;
    
                //panelSearchForm.Visible = false;
                //lnkButtonBackToSearch.Visible = true;
    
                members = GetDataFromQuery();
    
            
                return members;
    
            }
    
            public IQueryable<vx_EligibilitySearch> GetDataFromQuery()
            {
                // IQueryable<Member> members = null;
                IQueryable<vx_EligibilitySearch> members = null;
                
                members = somedb.vx_EligibilitySearch.AsNoTracking().AsQueryable(); //.OrderBy(a => a.Claim_Number)
    
    
                //if (!string.IsNullOrEmpty(Search_MemberID))
                // members = members.Where(m => m.Subscriber_ID == Search_MemberID).AsQueryable();
            
                members = members.Where(m => m.Alternate_Id=="alter1").AsQueryable();
                return members;
    
            }

    Model.

     public partial class vx_EligibilitySearch
        {
            [StringLength(50)]
            public string Group_Description { get; set; }
    
            [StringLength(50)]
            public string Group_Id { get; set; }
    
            [StringLength(50)]
            public string Alternate_Id { get; set; }
    
            
            public int id { get; set; }
        }

    Dbcontext.

     public partial class EntityExe : DbContext
        {
            public EntityExe()
                : base("name=EntityExe1")
            {
            }
    
            public virtual DbSet<vx_EligibilitySearch> vx_EligibilitySearch { get; set; }
           
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
            }
        }

     It seems that your code is not complete.Could you post  your complete code ?

    Best regards,

    Ackerly Xu

    Friday, August 24, 2018 9:59 AM
  • User-642568311 posted
    • Debugging/Error:
      • o I did WriteLine Output to see the process flow, and it printed out:
        • Inside GetDataFromQuery() method
        • Search_MemberID is NOT null
        • Exception thrown: 'System.InvalidOperationException' in EntityFramework.dll
        • about to be null

     

    • o So it is now  getting an InvalidOperation Exception. This is in the output in console, not in the app/browser.

    I have the code behind now as:

    Vx_EligibilitySearch.cs has:


        public string Alternate_ID { get; set; }
        public string Group_Description { get; set; }
        public string CCC_Group_ID { get; set; }
        public string Member_Name { get; set; }
        public string Sex { get; set; }
        public string Birth_Date { get; set; }


    default.aspx.cs :


    public IQueryable<Placeholder.Data.vx_EligibilitySearch> GetDataFromQuery()

    {
    System.Diagnostics.Debug.WriteLine("Inside GetDataFromQuery() method");
    try
    {
    if (!string.IsNullOrEmpty(Search_MemberID))
    {
    System.Diagnostics.Debug.WriteLine("Search_MemberID is NOT null");

    var memberEligibility = dbVBA.vx_EligibilitySearch.Where(m => m.Alternate_ID == Search_MemberID).AsQueryable();
    System.Diagnostics.Debug.WriteLine(memberEligibility);

    return memberEligibility;
    }
    }

    catch (Exception)
    {
    }
    // return members;
    System.Diagnostics.Debug.WriteLine("about to be null");

    return null;
    }

    protected void lnkSearch_Click(object sender, EventArgs e)
    {

    ShouldSearch = false;
    litError.Text = "";
    ClearAllSearchInputVars();

    // Member ID
    if (!string.IsNullOrEmpty(tbSearchMemberID.Text))
    Search_MemberID = tbSearchMemberID.Text;


    var memberIDIsValid = false;
    if (!string.IsNullOrEmpty(Search_MemberID))
    memberIDIsValid = true;

    var memberIsValid = false;
    if (!string.IsNullOrEmpty(Search_MemberLastName) && !string.IsNullOrEmpty(Search_DateOfBirth))
    {
    memberIsValid = true;
    }

    if (memberIDIsValid || memberIsValid)
    {
    ShouldSearch = true;
    lstClaims.DataBind();
    }
    else
    {
    litError.Text = "Either a Member ID OR a Member Last Name and Date of Birth are required";
    }
    }

    Friday, August 24, 2018 1:06 PM
  • User475983607 posted

    The error indicates, Alternate_ID, does not exist in the table or view.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, August 24, 2018 2:06 PM