Hinal Shah

Hinal Shah

  • NA
  • 20
  • 7.1k

How to bind multiple Table data in gridview

May 29 2017 8:30 AM


  1. am using Grid view for displaying the data according to user selection.If in drop down user select all it has to display all the data from the two or more tables.

here is my store procedure:-

USE [TenderCrawling] GO /****** Object: 
StoredProcedure [dbo].[Usp_Temp]
Script Date: 5/29/2017 11:51:33 AM ******/

 SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER
ON GO
-- ============================================= --
 Author: <Author,,Name> -- Create date: <Create Date,,> --
Description
: <Description,,> -- =============================================
ALTER PROCEDURE
[dbo].[Usp_Temp] @SiteId int AS BEGIN SET NOCOUNT ON;
Declare @abc int set @abc=(select count(1) from sitemaster)
Declare
@TemplateName varchar(50) Declare @Isindian bit
Declare @Id int Declare @output nvarchar(max) Declare @temp bit

Select @TemplateName=Template,@Isindian=IsIndianSite from SiteMaster where SiteId=@SiteId SET @TemplateName= (select substring(@TemplateName, 1, (len(@TemplateName) - 4))) SET @TemplateName=@TemplateName+'_ErrorLog'
IF EXISTS
(SELECT 1 FROM Information_SCHEMA.Tables
 WHERE TABLE_NAME
= @TemplateName)
BEGIN
set @temp=1
END
else Begin set @temp=0 End if(@temp=1)
begin
SET
@output='select log,OrganizationName,TenderNumber,RequirementWorkBrief,TenderDetailWorkDescription,NameOfWebSite'
if(@Isindian=0)
 Begin
SET
@output+=',City,State,Country from'+' '+@TemplateName+' '+'e left join sitemaster s on s.SiteId=e.SiteId Where e.IsDuplicate = 0' end else Begin SET @output+= ' from'+' '+@TemplateName+' '+'e left join sitemaster s on s.SiteId=e.SiteId Where e.IsDuplicate = 0 ' end end print @output exec sp_executesql @output END
end
else
Begin
SET @output+= ' from'+' '+@TemplateName+' '+'e left join sitemaster s on s.SiteId=e.SiteId Where e.IsDuplicate = 0 '
end
end
print @output exec sp_executesql @output
END
 
 --here @TemplatName is the name of the table present in Information_SCHEMA. but the porblem i am facing is the fields .If it is indian then @templatName_ErrorLog table dose not contain field like city,country and state.where as if its is not indian it contain fields like city ,country and state. 
 
 
--when code runs in asp.net application its throws error that invalid column name city ,country ,state because i am trying to bind all the indian and global table data at same time in the grid view by selecting option All from dropdown. 
 
 here is my .net code:-
 

 List<Errorlog> lst = new List<Errorlog>();
List<int> id = new List<int>();
SqlCommand cmd = new SqlCommand("Select SiteId from SiteMaster", con);

con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
id.Add(Convert.ToInt16(dr["SiteId"]));

}
dr.Close();
foreach (int k in id)
{
DynamicParameters param = new DynamicParameters();
param.Add("SiteId", k, DbType.Int16);
List<Errorlog> temp = con.Query<Errorlog>("Usp_Temp", param, null, true, 200, CommandType.StoredProcedure).ToList();

lst.AddRange(temp);

}

if (lst.Count != 0)
{

grd.DataSource = lst;
grd.DataBind();
}
 
 
 
 
 
 
 
 
 

Answers (3)