Populate ASP.Net GridView with Join Query Error: Invalid column name

slb2013

SOLVED CLOSED ACTIVE ANSWERED
User: slb2013
Posted: on Jun 04, 2018 12:58 AM
Forum: Data Controls
Answer: 1
Views: 3141
Sample Code: Download

I am trying to create a project for the school called "people skills"

I have 3 tables for joining into as one datagridview information: 1 table called: applicant, table 2 jobs and table 3 skills.

I have textbox search, 2 dropdowlists : one for jobname and other for language skills and button search. must allow any number of skills per person, any number of jobs, and any number of applicants.

When I combine the 3 tables for Inner Join all the info to be display in gridview on the load, and doing search for any number of skills per person, any of jobs and any number of applicants.

I am having a little problem for accepting data from inner join variable data field like a.applname, j.job_id, s.langskills, etc... I am get error for invalid column names as 'j.jobposition, etc...

peopleskills.aspx:

<form id="form1" runat="server">
<asp:ScriptManager ID="ScriptManager1" runat="server" />
<div>
    <h3 style="font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif; color: dodgerblue">
        Applicants Skills</h3>
    <br />
    <asp:GridView ID="GridViewAppJobSkills" runat="server" AllowSorting="True" AlternatingRowStyle-BackColor="#C9C9C9"
        AutoGenerateColumns="False" BackColor="#F0F0F0" BorderColor="White" BorderStyle="Double"
        BorderWidth="1px" CellPadding="3" CellSpacing="1" DataKeyNames="id" Font-Names="Consolas"
        Font-Size="10pt" ForeColor="Black" Width="875px" Style="margin-left: 3px;">
        <Columns>
            <asp:TemplateField>
                <ItemTemplate>
                    <asp:Label ID="applname" runat="Server" Font-Names="tahoma" Font-Size="8pt" Text='<%# Eval("a.applname") %>'
                        Width="70px" />
                </ItemTemplate>
                <HeaderTemplate>
                    Applicant Name
                </HeaderTemplate>
            </asp:TemplateField>
            <asp:TemplateField>
                <ItemTemplate>
                    <asp:Label ID="jobid" runat="Server" Font-Names="tahoma" Font-Size="8pt" Text='<%# Eval("a.job_id") %>' />
                </ItemTemplate>
                <HeaderTemplate>
                    Job ID
                </HeaderTemplate>
            </asp:TemplateField>
            <asp:TemplateField>
                <ItemTemplate>
                    <asp:Label ID="email" runat="Server" Font-Names="tahoma" Font-Size="8pt" Text='<%# Eval("a.email") %>'
                        Width="100px" />
                </ItemTemplate>
                <HeaderTemplate>
                    Email
                </HeaderTemplate>
            </asp:TemplateField>
            <asp:TemplateField>
                <ItemTemplate>
                    <asp:Label ID="website" runat="Server" Font-Names="tahoma" Font-Size="8pt" Text='<%# Eval("a.website") %>'
                        Width="100px" />
                </ItemTemplate>
                <HeaderTemplate>
                    WebSite
                </HeaderTemplate>
            </asp:TemplateField>
            <asp:TemplateField>
                <ItemTemplate>
                    <asp:Label ID="coverletter" runat="Server" Font-Names="tahoma" Font-Size="8pt" Text='<%# Eval("a.cover_letter") %>'
                        Width="100px" />
                </ItemTemplate>
                <HeaderTemplate>
                    CoverLetter
                </HeaderTemplate>
            </asp:TemplateField>
            <asp:TemplateField>
                <ItemTemplate>
                    <asp:Label ID="jobname" runat="Server" Font-Names="tahoma" Font-Size="8pt" Text='<%# Eval("j.jobposition") %>'
                        Width="100px" />
                </ItemTemplate>
                <HeaderTemplate>
                    Job Name
                </HeaderTemplate>
            </asp:TemplateField>
            <asp:TemplateField>
                <ItemTemplate>
                    <asp:Label ID="languageskills" runat="Server" Font-Names="tahoma" Font-Size="8pt"
                        Text='<%# Eval("s.langskills") %>' Width="100px" />
                </ItemTemplate>
                <HeaderTemplate>
                    Language Skills
                </HeaderTemplate>
            </asp:TemplateField>
            <asp:TemplateField>
                <ItemTemplate>
                    <asp:Label ID="applid" runat="Server" Font-Names="tahoma" Font-Size="8pt" Text='<%# Eval("s.applicant_id") %>'
                        Width="100px" />
                </ItemTemplate>
                <HeaderTemplate>
                    Applicant ID
                </HeaderTemplate>
            </asp:TemplateField>
            <asp:TemplateField>
                <ItemTemplate>
                    <asp:Label ID="createddate" runat="Server" Font-Names="tahoma" Font-Size="8pt" Text='<%# Eval("s.created_at") %>'
                        Width="100px" />
                </ItemTemplate>
                <HeaderTemplate>
                    Created Date
                </HeaderTemplate>
            </asp:TemplateField>
            <asp:TemplateField>
                <ItemTemplate>
                    <asp:Label ID="updatedate" runat="Server" Font-Names="tahoma" Font-Size="8pt" Text='<%# Eval("s.updated_at") %>'
                        Width="100px" />
                </ItemTemplate>
                <HeaderTemplate>
                    Updated Date
                </HeaderTemplate>
            </asp:TemplateField>
        </Columns>
    </asp:GridView>
</form>

peopleskills.aspx.cs

public partial class LoadGridView : System.Web.UI.Page
{
    string ConnectionString = @"Data Source=DESKTOP-SRJMEQ5; Initial Catalog=NetWebApps; Persist Security Info=True;User ID=sa; Password=!sql2017$";
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            PopulateGridView();

        }
    }

    void PopulateGridView()
    {
        DataTable dtbl = new DataTable();
        using (SqlConnection sqlCon = new SqlConnection(ConnectionString))
        {
            sqlCon.Open();
            SqlDataAdapter sqlda = new SqlDataAdapter("SELECT a.id, " +
            "a.applname, " +
            "a.email, " +
            "a.website, " +
            "a.cover_letter, " +
            "a.job_id, " +
            "j.jobposition, " +
            "s.langskills, " +
            "s.applicant_id, " +
            "CONVERT (varchar(2), DATEPART(mm, s.created_at)) + '/' + CONVERT (varchar(2), DATEPART(dd, s.created_at)) + '/' + CONVERT (varchar(4), DATEPART(yyyy, s.created_at)) AS [CreatedDate], CONVERT (varchar(2), DATEPART(mm, s.updated_at)) + '/' + CONVERT (varchar(2), DATEPART(dd, s.updated_at)) + '/' + CONVERT (varchar(4), DATEPART(yyyy, s.updated_at)) AS [UpdateDate] " +
            "FROM applicants AS a " +
            "INNER JOIN dbo.jobs AS j " +
            "ON a.job_id = j.id " +
            "INNER JOIN dbo.skills AS s " +
            "ON a.id = s.applicant_id " +
            "ORDER BY 1, 2", sqlCon);

            sqlda.Fill(dtbl);
        }
        if (dtbl.Rows.Count > 0)
        {
            GridViewAppJobSkills.DataSource = dtbl;
            GridViewAppJobSkills.DataBind();
        }
        else
        {
            dtbl.Rows.Add(dtbl.NewRow());
        }
    }
}

Your help is very much appreciated to get results of display on the datagridview and search functions using textbox search with the selection of dropdownlists to get results on datagridview.

My SQL query Inner join is working fine but I need to find out to add a.applname, a.email, etc... to add in <%# Eval("a.applname") gridview and gives me an error invalid column name for the datagridview.

Thanks much.

pandeyis...

0 5

Reply
ANSWER
User: pandeyism
Replied: on Jun 05, 2018 12:52 AM
Modified: on Jun 05, 2018 01:13 AM
Report

Hi slb2013,

You need to write the Column name only while passing to the Eval.

Don't give a.applname use directly column name applname. If you have multiple same column name then in the select query give alias name and pass that alis name to the Eval.

And instead of using inline query always use StoredProcedure.

SQL

CREATE PROCEDURE[Customers_GetCustomersOrder]
AS
BEGIN  
  SELECT TOP 10
		 O.CustomerID,
		 C.ContactName,
		 C.City
  FROM Customers C
  INNER JOIN Orders O ON C.CustomerID=O.CustomerID
END

HTML

<asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:TemplateField HeaderText="CustomerID">
            <ItemTemplate>
                <asp:Label ID="lblCustomerID" runat="Server" Text='<%# Eval("CustomerID") %>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="ContactName">
            <ItemTemplate>
                <asp:Label ID="lblContactName" runat="Server" Text='<%# Eval("ContactName") %>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="City">
            <ItemTemplate>
                <asp:Label ID="lblCity" runat="Server" Text='<%# Eval("City") %>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>

Namespaces

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

Code

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        string constr = ConfigurationManager.ConnectionStrings["Constring"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand("Customers_GetCustomersOrder", con))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    using (DataTable dt = new DataTable())
                    {
                        da.Fill(dt);
                        this.gvCustomers.DataSource = dt;
                        this.gvCustomers.DataBind();
                    }
                }
            }
        }
    }
}

Screenshot

Populate ASP.Net GridView with Join Query Error: Invalid column name

Company

Explore

Follow us

Contact Us