Jay
asked on
System.Web.HttpException: A field or property with the name 'ORG_ID' was not found on the selected data source.
For my Gridview, i want it to display (Prospective), (Individual Associate) or (Corporate Associate). It displays (Prospective) and (Individual Associate), but the sql takes Org_Id as Person_Id and the (Corporate Associate) label cant work !!
aspx code
cs code
sql code
aspx code
<asp:GridView CssClass="table table-hover BlueTable" ID="gvProspective" runat="server" EmptyDataText="No Records Found" ShowHeaderWhenEmpty="true" AutoGenerateColumns="false" GridLines="None" UseAccessibleHeader="true" BorderStyle="None">
<Columns>
<asp:BoundField DataField="PERSON_ID" ItemStyle-CssClass="hiddencol" HeaderStyle-CssClass="hiddencol" />
<asp:BoundField DataField="ORG_ID" ItemStyle-CssClass="hiddencol" HeaderStyle-CssClass="hiddencol" />
<asp:TemplateField>
<itemtemplate>
<asp:Label ID="lblname" runat="server" Text='<%#Eval("FULLNAME_NAMETAGS")%>' ></asp:Label>
<asp:label ID="lblPros" runat="server" Text="(Prospective)" Visible='<%#bool.Parse(HasExistingProspective((int)(Eval("PERSON_ID")))) %>' />
<asp:label ID="lblIA" runat="server" Text="(Individual Associate)" Visible='<%#bool.Parse(HasExistingIA((int)(Eval("PERSON_ID")))) %>'/>
<asp:label ID="lblCA" runat="server" Text="(Corporate Associate)" Visible='<%#bool.Parse(HasExistingCA((int)(Eval("ORG_ID")))) %>'/>
</itemtemplate>
</asp:TemplateField>
<asp:BoundField DataField="DONATION_AMOUNT" HtmlEncode="False" DataFormatString="{0:c}" HeaderText="Donation Amount" HeaderStyle-BackColor="#007bff" HeaderStyle-ForeColor="White" />
<asp:BoundField DataField="DONATION_DT" HtmlEncode="False" DataFormatString="{0:d}" HeaderText="Donation Date" HeaderStyle-BackColor="#007bff" HeaderStyle-ForeColor="White" />
<asp:BoundField DataField="EVENT_NAME" HeaderText="Related to Events" HeaderStyle-BackColor="#007bff" HeaderStyle-ForeColor="White" />
<asp:CommandField EditText="Select" HeaderText="Settings" ShowEditButton="true" HeaderStyle-BackColor="#007bff" HeaderStyle-ForeColor="White" />
</Columns>
</asp:GridView>
cs code
protected void Page_Load(object sender, EventArgs e)
{
//getAllPerspectives
if (!IsPostBack)
{
gvProspective.DataSource = fundraising.getAllPerspectives();
gvProspective.DataBind();
gvProspective.HeaderRow.TableSection = TableRowSection.TableHeader;
Session.Add("ProspectiveID", null);
}
else
{
//If error, display failure message
ScriptManager.RegisterStartupScript(Page, GetType(), "AlertFailureDisplay", "displayFailure();", true);
}
}
protected string HasExistingProspective(int personId)
{
return fundraising.HasExistingProspective(personId) ? "true" : "false";
}
protected string HasExistingIA(int personId)
{
return fundraising.HasExistingIA(personId) ? "true" : "false";
}
protected string HasExistingCA(int orgId)
{
return fundraising.HasExistingCA(orgId) ? "true" : "false";
}
sql code
public DataTable getAllPerspectives()
{
string queryStr = "SELECT p.PERSON_ID, p.FULLNAME_NAMETAGS, d.DONATION_AMOUNT, d.DONATION_DT, d.EVENT_NAME FROM membership.TBL_PERSON p " +
"INNER JOIN dbo.TBL_DONATION d ON p.PERSON_ID = d.PERSON_ID WHERE PROSPECTIVE_ID IS NOT NULL UNION " +
"SELECT p.PERSON_ID, p.FULLNAME_NAMETAGS, d.DONATION_AMOUNT, d.DONATION_DT, d.EVENT_NAME from membership.TBL_PERSON p INNER JOIN dbo.TBL_DONATION d ON p.PERSON_ID = d.PERSON_ID " +
"INNER JOIN membership.TBL_MEMBERSHIP m on p.person_id = m.person_id UNION " +
"SELECT o.ORG_ID, o.NAME AS FULLNAME_NAMETAGS, d.DONATION_AMOUNT, d.DONATION_DT, d.EVENT_NAME FROM membership.TBL_ORGANISATION o INNER JOIN dbo.TBL_DONATION d ON o.ORG_ID = d.ORG_ID " +
"INNER JOIN membership.TBL_MEMBERSHIP m on o.ORG_ID = m.ORG_ID";
DataTable dt = dbhelp.ExecDataReader(queryStr);
return dt;
}
//gridview
public bool HasExistingProspective(int personId)
{
bool hasPaid = true;
List<SqlCommand> transcommand = new List<SqlCommand>();
SqlCommand mycmd = new SqlCommand();
//string query = "Select * from shared.TBL_CONTRIBUTION where Member_Id=(Select Member_Id from membership.TBL_MEMBERSHIP " +
// " Where Person_Id=" + personId + ")";
string query = "select * from membership.TBL_PERSON p " +
"inner join membership.tbl_membership m on p.person_id = m.person_id where M.Member_Id=(Select Member_Id from membership.TBL_MEMBERSHIP " +
" Where Person_Id=" + personId + ")";
DataTable dataTable = dbhelp.ExecDataReader(query);
if (dataTable != null && dataTable.Rows.Count > 0)
hasPaid = false;
return hasPaid;
}
public bool HasExistingIA(int personId)
{
bool hasPaid = false;
List<SqlCommand> transcommand = new List<SqlCommand>();
SqlCommand mycmd = new SqlCommand();
string query = "SELECT p.PERSON_ID, p.FULLNAME_NAMETAGS, d.DONATION_AMOUNT, d.DONATION_DT, d.EVENT_NAME from membership.TBL_PERSON p INNER JOIN dbo.TBL_DONATION d ON p.PERSON_ID = d.PERSON_ID " +
"INNER JOIN membership.TBL_MEMBERSHIP m on p.person_id = m.person_id where m.Member_Id=(Select Member_Id from membership.TBL_MEMBERSHIP " +
"Where Person_Id=" + personId + ")";
DataTable dataTable = dbhelp.ExecDataReader(query);
if (dataTable != null && dataTable.Rows.Count > 0)
hasPaid = true;
return hasPaid;
}
public bool HasExistingCA(int orgId)
{
bool hasPaid = false;
List<SqlCommand> transcommand = new List<SqlCommand>();
SqlCommand mycmd = new SqlCommand();
string query = "SELECT o.ORG_ID, o.NAME AS FULLNAME_NAMETAGS, d.DONATION_AMOUNT, d.DONATION_DT, d.EVENT_NAME FROM membership.TBL_ORGANISATION o INNER JOIN dbo.TBL_DONATION d ON o.ORG_ID = d.ORG_ID " +
"INNER JOIN membership.TBL_MEMBERSHIP m on o.ORG_ID = m.ORG_ID where m.Member_Id=(Select Member_Id from membership.TBL_MEMBERSHIP " +
"Where ORG_ID=" + orgId + ")";
DataTable dataTable = dbhelp.ExecDataReader(query);
if (dataTable != null && dataTable.Rows.Count > 0)
hasPaid = true;
return hasPaid;
}
ASKER
Hi, i did it for one of the query (last union) but it still takes Org_Id as Person_Id !!
SELECT o.ORG_ID AS ORG_ID, o.NAME AS FULLNAME_NAMETAGS, d.DONATION_AMOUNT, d.DONATION_DT, d.EVENT_NAME FROM membership.TBL_ORGANISATION o INNER JOIN dbo.TBL_DONATION d ON o.ORG_ID = d.ORG_ID
INNER JOIN membership.TBL_MEMBERSHIP m on o.ORG_ID = m.ORG_ID
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
eg.
Select x, y, Person_ID AS Org_ID FROM....
so both sources have the same field names