Click here to Skip to main content
15,940,246 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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:

XML
<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();

}


}
Posted

You have several options here -

1. If you have links between the tables, use a query with join and make a one datatable to view it in one grid view
2. If there are no relations at all between tables, I don't see any reason to use one grid view to show all the 3 tables. Create a link at the top and shuffle the views using the link.
Or simply show 3 grids for 3 tables.


Good Luck!
Cheers
Sandip
 
Share this answer
 
I figured that you want to display username (available in user_acc table) and business name (available in business table) along side related data from incident table in one gridview. then you got to join the 3 tables like this, assuming that you have a user_id field and business_id field in the incident table:
SQL
SELECT user_acc.username, business.business_name, [other fields] FROM
user_acc JOIN incident ON user_acc.id=incident.user_id
JOIN business ON incident.business_id=business.id
WHERE condition...

As for the construction of parameterized query, check this out: SQL Injection And Parameterized Queries[^]
 
Share this answer
 
v3
Comments
Member 10609511 24-Feb-14 10:52am    
I have my procedure working however there is one field that was causing problems and I had to remove it. I have a table called Incident_Type and within this table there are 2 fields - Incident_ID(Int) and Incident_Type_Name(Varchar). However when I go to add this to my join and run my procedure I am getting the error - Multi-part Identifier cannot be bound and when I play around I also get an error saying cannot find Incident_Type.Incident_Type_Name even though it is spelt correctly

Here is my Proc:

ALTER PROCEDURE dbo.DisplayContent
(
@BusinessName varchar(100))

AS
SELECT user_acc.username, business.business_name, Incident.Incident_ID, Incident.File_Path, Incident.Description, Incident.Date_Time, Business.Provence, Business.County FROM
user_acc JOIN incident ON user_acc.User_id=incident.user_id
JOIN business ON incident.business_id=business.Business_id
WHERE Business.Business_Name = @BusinessName
RETURN

When I write it like this I get errors:

ALTER PROCEDURE dbo.DisplayContent
(
@BusinessName varchar(100))

AS
SELECT user_acc.username, business.business_name, Incident_Type.Incident_Type_Name, Incident.Incident_ID, Incident.File_Path, Incident.Description, Incident.Date_Time, Business.Provence, Business.County FROM
user_acc JOIN incident ON user_acc.User_id=incident.user_id
JOIN business ON incident.business_id=business.Business_id
WHERE Business.Business_Name = @BusinessName
RETURN
Peter Leow 24-Feb-14 11:28am    
That is because you have included incident_type.incident_type_name without the incident_type table in the query. I supposed the incident_id in the incident_type table is linked to incident_id in the incident table. Try this:
SELECT user_acc.username, business.business_name, Incident_Type.Incident_Type_Name, Incident.Incident_ID, Incident.File_Path, Incident.Description, Incident.Date_Time, Business.Provence, Business.County FROM user_acc JOIN incident ON user_acc.User_id=incident.user_id JOIN business ON incident.business_id=business.Business_id
JOIN incident_type ON incident_type.incident_id = incident.incident_id
WHERE Business.Business_Name = @BusinessName

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900