Hi everyone,
Basically I want to display data from two tables in my database within Visual studio 2010 into one data gridview. Just a brief background to my project - it is a website where users can upload content(Images and video).
I have three tables - one table 'Incident' stores the file path of the content and name of the file, the date and time it was uploaded and what user has uploaded it.
The other table User_Acc stores the usernames,emails and passwords of users.
The third table Business stores basic information about the particular business/organisation the user belongs to.
However the user in the Incident Table is in the form of an ID whereas I want to display the 'Username' from the User_Acc table instead. Similarly the Business is in the form of an ID in the Incident Table whereas I want to display the Business_Name from the Business Table.
Here is my code:
Welcome.aspx:
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" EmptyDataText = "No files uploaded">
<Columns>
<asp:BoundField DataField = "Incident_ID" HeaderText = "Incident_ID" />
<asp:BoundField DataField = "Description" HeaderText = "Description" />
<asp:ImageField DataImageUrlField="File_Path" ControlStyle-Width="100"
ControlStyle-Height = "100" HeaderText = "Preview Image"/>
</Columns>
</asp:GridView>
Welcome.aspx.cs
protected void Page_Load(object sender, EventArgs e)
{
Label1.Text = "Welcome " + Session["User"];
Label2.Text = "Business: " + Session["BusinessName"];
SqlConnection conn;
SqlCommand comm;
String connectionString = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
conn = new SqlConnection(connectionString);
comm = new SqlCommand("SELECT * from Business a, User_Acc c Where c.Username = @Username AND a.Business_ID = c.Business_ID", conn);
comm.Parameters.Add("@Username", System.Data.SqlDbType.VarChar).Value = Session["User"];
conn.Open();
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
lblOwner.Text += reader["Owner_Name"];
lblBusiness.Text += reader["Business_Name"];
lblAddress.Text += reader["Address_Line_1"] + " - " + reader["Address_Line_2"] + " - " + reader["Address_Line_3"];
lblProvince.Text += reader["Provence"];
lblCounty.Text += reader["County"];
lblVATNo.Text += reader["Vat_No"];
lblEmail.Text += reader["Email"];
lblTelephone.Text += reader["Telephone"];
}
SqlConnection connWrite;
connWrite = new SqlConnection(connectionString);
DataTable dt = new DataTable();
SqlDataAdapter sda = new SqlDataAdapter();
using (SqlCommand comm1 = new SqlCommand("SELECT * FROM Incident Order by Incident_ID", connWrite))
try
{
connWrite.Open();
sda.SelectCommand = comm1;
sda.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
finally
{
connWrite.Close();
sda.Dispose();
connWrite.Dispose();
}
reader.Close();
conn.Close();
}
}