Reputation: 3
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
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