CODEWITHSUNDEEP
c#sqlasp.netsql-server
Safri
Safri

Reputation: 3

How to use a JOIN SQL statement in ASP.NET

OK so I am a programming student with a headache that hopefully someone will be able to cure.

I am currently creating a website using ASP.NET in Microsoft Visual Studio for a project and here is my problem:

I really don't know how to combine data from two sets of data.

Here is the table structure for both the tables:

LeaveCategory

Id is the primary key for LeaveCategory.

Data for LeaveCategory

LeaveType

Id is the primary key and LeaveCategoryId is the foreign key for LeaveType.

Data for LeaveType

Output:

Leave Type ACL1 from LeaveType ----> Category AL from LeaveCategory
Leave Type ACL2 from LeaveType ----> Category AL from LeaveCategory
Leave Type ACL3 from LeaveType ----> Category AL from LeaveCategory
Leave Type ACL4 from LeaveType ----> Category AL from LeaveCategory
Leave Type OPL from LeaveType -----> Category ML from LeaveCategory
Leave Type HPL from LeaveType -----> Category ML from LeaveCategory
Leave Type CGL from LeaveType -----> Category UL from LeaveCategory
Leave Type MGL from LeaveType -----> Category UL from LeaveCategory
Leave Type SDL from LeaveType -----> Category ML from LeaveCategory

.aspx page need to retrieve from both table using JOIN sql statement. But I really don't know how to type it :(((

So here's my code:

SqlConnection conn = null;
SqlCommand cmd = null;
string connectionString = null;
protected void Page_Load(object sender, EventArgs e)
{
    SqlDataReader dr = null;

    connectionString = ConfigurationManager.ConnectionStrings["LeaveManagementCS"].ConnectionString;

    string sql = "SELECT * FROM LeaveType";

    try
    {
        conn = new SqlConnection(connectionString);

        cmd = new SqlCommand(sql, conn);

        conn.Open();

        dr = cmd.ExecuteReader();

        lblOutput.Text = "<table Border='1'>";
        lblOutput.Text += "<tr><th>Id </th><th>Leave Type</th><th>Description</th><th>Number of Days</th></tr>";

        while (dr.Read())
        {
            string id = dr["Id"].ToString();

            string type = dr["Type"].ToString();

            string desc = dr["Description"].ToString();

            string nod = dr["NumofDays"].ToString();

            lblOutput.Text += "<tr><td>" + id + "</td>";
            lblOutput.Text += "<td>" + type + "</td>";
            lblOutput.Text += "<td>" + desc + "</td>";
            lblOutput.Text += "<td>" + nod + "</td></tr>";
        }
        dr.Close();

        lblOutput.Text += "</table>";
    }
    catch (Exception ex)
    {
        lblOutput.Text = "Error Message" + ex.Message;
    }
    finally
    {
        if (conn != null)
            conn.Close();
    }
}

Upvotes: 0

Views: 72

Answers (1)

Nathan
Nathan

Reputation: 503

I want to start off by saying that the way you are going about building this page is very brittle and prone to errors and problems. I would not recommend doing this in an actual business situation. For demonstration purposes, in an educational context, I suppose it is acceptable. With that said, here is the SQL to get the data you asked for.

string sql = @"
SELECT 
  LT.Id,
  LT.Type,
  LT.Description,
  LT.NumofDays,
  LC.Description CategoryDescription
FROM 
  LeaveType LT
  JOIN LeaveCategory LC
    ON LT.LeaveCategoryId = LC.Id";

Upvotes: 1

Related Questions