Click here to Skip to main content
15,888,065 members
Search within:


Need of SQL Procedure for update,delete dynamic Columns and rows and Bind to asp.net gridview in the Front End for update and delete operations
Please Sign up or sign in to vote.
4.33/5 (3 votes)
Hi All


I have three tables

1.[record set]

RecordSetId	InventoryId
701	          11
702	          11
703	          12
704	          12
705	          13
706	          13
707	          11
708	          11
709	          19
710	          19
711	          20

2.app_fields

Fieldid	FieldTypeid	FieldName	IsMandatory	IsEditable	InventoryId ValidationSize
201	100	        WebsitetName	1	            0	            11	      NULL
202	100	       	WebsiteUrl	1	0	11	NULL
203	100	       	CountryName	1	0	11	NULL
204	100	       	KraftRegion	1	0	11	NULL
205	100	       	WebAnalytics	1	0	11	NULL
206	100	       	HostingProvider	1	0	11	NULL
207	100		Channel	        1	0	11	NULL
208	100	        Company	1	0	11	NULL
209	100	ArticleName	1	0	13	NULL
210	100	ArrticleLocation	1	0	13	NULL
211	100	CountryName	1	0	13	NULL
212	100	KraftRegion	1	0	13	NULL
213	100	WebAnalytics	1	0	13	NULL
214	100	ArticleProvider	1	0	13	NULL
215	100	ArticleChannel	1	0	13	NULL
216	100	ArticleCompany	1	0	13	NULL
217	100	AssetName	1	0	12	NULL
218	100	AssetLocation	1	0	12	NULL
219	100	CountryName	1	0	12	NULL
220	100	KraftRegion	1	0	12	NULL
221	100	AssetAnalytics	1	0	12	NULL
222	100	AssetProvider	1	0	12	NULL
223	100	AssetType	1	0	12	NULL
224	100	AssetCompany	1	0	12	NULL
225	100	WebsiteName	0	0	17	0
226	100	Region	0	0	17	0
227	100	WebsiteName	0	0	18	0
228	100	Region	0	0	18	0
229	100	AssetName	1	0	19	50
230	102	AssetType	1	0	19	20
231	100	Comobject	1	0	20	20
232	101	Comcomp	0	1	20	100
233	102	ThirdList	1	1	20	0

3.[Asset detail]

AttributeId	RecordSetId	FieldId	FieldValue
801	701	201	Google
802	701	202	www.Google.com/ncr
803	701	203	USA
804	701	204	Washington
805	701	205	Google Search Engine
806	701	206	Google Web Server
807	701	207	Website
808	701	208	GOOGLE INC
809	702	201	Yahoo
810	702	202	www.yahoo.com
811	702	203	USA
812	702	204	New York
813	702	205	Yahoo Website
814	702	206	Yahoo Web Server
815	702	207	Website
816	702	208	Yahoo Inc
817	703	217	Printer
818	703	218	CP-1st floor
819	703	219	Bangalore-India
820	703	220	India
821	703	221	Printing
822	703	222	HP Laser jet
823	703	223	Print
824	703	224	Infy
825	707	201	WebsitetName1
826	707	202	WebsiteUrl1
827	707	203	CountryName1
828	707	204	KraftRegion1
829	707	205	WebAnalytics1
830	707	206	HostingProvider1
831	707	207	Channel1
832	707	208	Company1
833	708	201	WebsitetName2
834	708	202	WebsiteUrl2
835	708	203	CountryName2
836	708	204	KraftRegion2
837	708	205	WebAnalytics2
838	708	206	HostingProvider2
839	708	207	Channel2
840	708	208	Company2
841	709	229	AssetName1
842	709	230	AssetType1
843	710	229	AssetName2
844	710	230	AssetType2
845	711	231	Col1
846	711	232	Col2
847	711	233	Col3


I want the output based on inventoryid [for example:inventoryid=11] so I queried like this with the corresponding datas to load in it.and the very most important things to keep it in mind that ...all the tables are Dynamic and will updated using excel sheet by administrator and in the UI part the User have to Enabled for update operation alone..I am in need of Updating operation in the stored procedure


SQL
SELECT AF.FIELDNAME,AD.FIELDVALUE FROM  [APP_FIELDS] AF INNER JOIN [ASSET DETAIL] AD ON AD.FIELDID = AF.FIELDID INNER JOIN [RECORD SET] AS RS ON AD.RECORDSETID=RS.RECORDSETID WHERE RS.INVENTORYID =11


and the output is

FIELDNAME	FIELDVALUE
WebsitetName	Google
WebsiteUrl	www.Google.com/ncr
CountryName	USA
KraftRegion	Washington
WebAnalytics	Google Search Engine
HostingProvider	Google Web Server
Channel	         Website
Company	        GOOGLE INC
WebsitetName	Yahoo
WebsiteUrl	www.yahoo.com
CountryName	USA
KraftRegion	New York
WebAnalytics	Yahoo Website
HostingProvider	Yahoo Web Server
Channel  	Website
Company	        Yahoo Inc


I want to transpose the resultant table with for the inventoryid=11 with the table headers and Datas should be arranged in rows like this
the output i want to be 

WebsitetName	WebsiteUrl  CountryName KraftRegion WebAnalytics HostingProvider Channel Company
Google        www.Google.com    USA    Washington   Google Search Engine  Google Web Server   Website   GOOGLE INC
yahoo	     www.yahoo.com    .............................................................................
WebsitetName1 ..................................................................................



what i tried so far is

just pasting the pseudocode of a first try:

SQL
DECLARE #temptable table
temptable=select * from app_fields where inventoryid=11 into ##temptable where 1=2
Declare @RecordSetId,FieldId

Declare @TableData
set @ReacordSetId=select RecordSetId from [Record Set] from where inventoryid=11
set FieldId=temptable[0]
foreach row in temptable
TableData+= getFieldValue(@RecordSetId,FieldId) as temptable[row][2]

end foreach

@print TableData 
--------------------------------------------------------
create function getFieldValue( @RecordSetId int,@FieldId int) 
RETURNS TABLE AS RETURN
( select FieldValue from [Asset Detail] where RecordSetId=@RecordSetId and FieldId=@FieldId )
GO
SELECT * FROM [ASSET DETAIL]

------------------------------------------------------------

the second thing is the stored proc :


SQL
CREATE PROCEDURE [dbo].[SP_APP_FIELDS]
 @inventoryid [int]
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @cols AS NVARCHAR(MAX),
        @cols_WITH_MAX AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX);
-- @inventoryid
SELECT @cols_WITH_MAX =
STUFF((SELECT DISTINCT ',MAX('+ QUOTENAME(fieldname) +') AS ' + QUOTENAME(fieldname) FROM app_fields
WHERE inventoryid = @inventoryid FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')

select @cols =
STUFF((SELECT distinct ',' + QUOTENAME(fieldname) from app_fields
where inventoryid = @inventoryid FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')

set @query = 'SELECT  '+ @cols_WITH_MAX +' FROM
   (SELECT [inventoryid], ' + @cols + ' from
   (SELECT FieldValue,fieldname,AF.inventoryid FROM  [app_fields] AF join [ASSET DETAIL] AD on AD.FieldId = AF.FieldId
 --join [RECORD SET] RS ON AF.inventoryid = RS.inventoryid
 where AF.inventoryid ='+cast(@inventoryid as varchar(20)) +')X
            pivot
            (
                max([FieldValue])
                for [fieldname] in (' + @cols + ')
            ) p )a GROUP BY [inventoryid]'
print(@query)
execute(@query)
end
GO



Even I can give you guys the DB scripts too...


1.Record Set


SQL
CREATE TABLE [dbo].[RECORD SET](
    [RecordSetId] [int] NOT NULL,
    [InventoryId] [int] NULL,
 CONSTRAINT [PK_RECORD SET] PRIMARY KEY CLUSTERED
(
    [RecordSetId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
INSERT [dbo].[RECORD SET] ([RecordSetId], [InventoryId]) VALUES (701, 11)
INSERT [dbo].[RECORD SET] ([RecordSetId], [InventoryId]) VALUES (702, 11)
INSERT [dbo].[RECORD SET] ([RecordSetId], [InventoryId]) VALUES (703, 12)
INSERT [dbo].[RECORD SET] ([RecordSetId], [InventoryId]) VALUES (704, 12)
INSERT [dbo].[RECORD SET] ([RecordSetId], [InventoryId]) VALUES (705, 13)
INSERT [dbo].[RECORD SET] ([RecordSetId], [InventoryId]) VALUES (706, 13)
INSERT [dbo].[RECORD SET] ([RecordSetId], [InventoryId]) VALUES (707, 11)
INSERT [dbo].[RECORD SET] ([RecordSetId], [InventoryId]) VALUES (708, 11)
INSERT [dbo].[RECORD SET] ([RecordSetId], [InventoryId]) VALUES (709, 19)
INSERT [dbo].[RECORD SET] ([RecordSetId], [InventoryId]) VALUES (710, 19)
INSERT [dbo].[RECORD SET] ([RecordSetId], [InventoryId]) VALUES (711, 20)



2.Asset Detail

SQL
CREATE TABLE [dbo].[ASSET DETAIL](
    [AttributeId] [int] NOT NULL,
    [RecordSetId] [int] NULL,
    [FieldId] [int] NULL,
    [FieldValue] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_ASSET DETAIL] PRIMARY KEY CLUSTERED
(
    [AttributeId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (801, 701, 201, N'Google')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (802, 701, 202, N'www.Google.com/ncr')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (803, 701, 203, N'USA')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (804, 701, 204, N'Washington')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (805, 701, 205, N'Google Search Engine')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (806, 701, 206, N'Google Web Server')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (807, 701, 207, N'Website')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (808, 701, 208, N'GOOGLE INC')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (809, 702, 201, N'Yahoo')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (810, 702, 202, N'www.yahoo.com')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (811, 702, 203, N'USA')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (812, 702, 204, N'New York')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (813, 702, 205, N'Yahoo Website')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (814, 702, 206, N'Yahoo Web Server')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (815, 702, 207, N'Website')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (816, 702, 208, N'Yahoo Inc')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (817, 703, 217, N'Printer')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (818, 703, 218, N'CP-1st floor')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (819, 703, 219, N'Bangalore-India')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (820, 703, 220, N'India')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (821, 703, 221, N'Printing')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (822, 703, 222, N'HP Laser jet')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (823, 703, 223, N'Print')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (824, 703, 224, N'Infy')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (825, 707, 201, N'WebsitetName1')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (826, 707, 202, N'WebsiteUrl1')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (827, 707, 203, N'CountryName1')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (828, 707, 204, N'KraftRegion1')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (829, 707, 205, N'WebAnalytics1')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (830, 707, 206, N'HostingProvider1')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (831, 707, 207, N'Channel1')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (832, 707, 208, N'Company1')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (833, 708, 201, N'WebsitetName2')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (834, 708, 202, N'WebsiteUrl2')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (835, 708, 203, N'CountryName2')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (836, 708, 204, N'KraftRegion2')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (837, 708, 205, N'WebAnalytics2')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (838, 708, 206, N'HostingProvider2')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (839, 708, 207, N'Channel2')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (840, 708, 208, N'Company2')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (841, 709, 229, N'AssetName1')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (842, 709, 230, N'AssetType1')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (843, 710, 229, N'AssetName2')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (844, 710, 230, N'AssetType2')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (845, 711, 231, N'Col1')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (846, 711, 232, N'Col2')
INSERT [dbo].[ASSET DETAIL] ([AttributeId], [RecordSetId], [FieldId], [FieldValue]) VALUES (847, 711, 233, N'Col3')



3.App_fields


SQL
CREATE TABLE [dbo].[APP_FIELDS](
    [Fieldid] [int] NOT NULL,
    [FieldTypeid] [int] NOT NULL,
    [FieldName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [IsMandatory] [bit] NULL,
    [IsEditable] [bit] NULL,
    [InventoryId] [int] NULL,
    [ValidationSize] [int] NULL,
 CONSTRAINT [PK_APP_FIELDS] PRIMARY KEY CLUSTERED
(
    [Fieldid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (201, 100, N'WebsitetName', 1, 0, 11, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (202, 100, N'WebsiteUrl', 1, 0, 11, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (203, 100, N'CountryName', 1, 0, 11, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (204, 100, N'KraftRegion', 1, 0, 11, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (205, 100, N'WebAnalytics', 1, 0, 11, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (206, 100, N'HostingProvider', 1, 0, 11, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (207, 100, N'Channel', 1, 0, 11, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (208, 100, N'Company', 1, 0, 11, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (209, 100, N'ArticleName', 1, 0, 13, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (210, 100, N'ArrticleLocation', 1, 0, 13, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (211, 100, N'CountryName', 1, 0, 13, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (212, 100, N'KraftRegion', 1, 0, 13, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (213, 100, N'WebAnalytics', 1, 0, 13, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (214, 100, N'ArticleProvider', 1, 0, 13, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (215, 100, N'ArticleChannel', 1, 0, 13, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (216, 100, N'ArticleCompany', 1, 0, 13, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (217, 100, N'AssetName', 1, 0, 12, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (218, 100, N'AssetLocation', 1, 0, 12, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (219, 100, N'CountryName', 1, 0, 12, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (220, 100, N'KraftRegion', 1, 0, 12, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (221, 100, N'AssetAnalytics', 1, 0, 12, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (222, 100, N'AssetProvider', 1, 0, 12, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (223, 100, N'AssetType', 1, 0, 12, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (224, 100, N'AssetCompany', 1, 0, 12, NULL)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (225, 100, N'WebsiteName', 0, 0, 17, 0)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (226, 100, N'Region', 0, 0, 17, 0)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (227, 100, N'WebsiteName', 0, 0, 18, 0)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (228, 100, N'Region', 0, 0, 18, 0)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (229, 100, N'AssetName', 1, 0, 19, 50)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (230, 102, N'AssetType', 1, 0, 19, 20)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (231, 100, N'Comobject', 1, 0, 20, 20)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (232, 101, N'Comcomp', 0, 1, 20, 100)
INSERT [dbo].[APP_FIELDS] ([Fieldid], [FieldTypeid], [FieldName], [IsMandatory], [IsEditable], [InventoryId], [ValidationSize]) VALUES (233, 102, N'ThirdList', 1, 1, 20, 0)






the UI page is like this

XML
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="Digital_Asset_Inventory.Default1" %>
<%@ Register src="Header.ascx" tagname="Header" tagprefix="uc1" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <link href="/Styles/Site.css" rel="stylesheet" type="text/css" />
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <table border="0" cellpadding="0" cellspacing="0" width="950px" align="center">
            <%--***** HEADER SECTION *****--%>
        <tr><td valign="top"><uc1:Header ID="Header1" runat="server" /></td></tr>
        <tr>
            <td>
                <asp:DropDownList ID="drp_Inventory" runat="server" AutoPostBack="true">
                    <asp:ListItem Text="Reusable Assets" Value="11"></asp:ListItem>
                </asp:DropDownList>
            </td>
        </tr>
        <tr><td class="h10"></td></tr>
        <%--****** DATA SECTION TO UPDATE ******--%>
        <tr>
            <td>
                <asp:GridView ID="gv_UserContents" runat="server" HeaderStyle-CssClass="GridHeadC" CssClass="GridDataC" AllowPaging="true" AllowSorting="true">
                </asp:GridView>
            </td>
        </tr>
    </table>
    </div>
    </form>
</body>
</html>



and the code behind of the page is, this what i tried so far

C#
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace Digital_Asset_Inventory
{
    public partial class Default1 : System.Web.UI.Page
    {
      
        static string ConnStr = System.Configuration.ConfigurationManager.ConnectionStrings["TEST"].ConnectionString.ToString();
        
        public void Page_Load(object sender, EventArgs e)
        {
            bindgridviewheader(drp_Inventory.SelectedValue.ToString());
        }

       

        public DataTable FlipDataTable(DataTable dthead,DataTable dt)
        {
            DataTable table = new DataTable();
            for (int i = 0; i <= dthead.Rows.Count; i++)
            {
                table.Columns.Add(Convert.ToString(i));
            }
            DataRow dr;
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                dr = table.NewRow();
                dr[0] = dt.Columns[j].ToString();
                for (int k = 1; k <= dt.Rows.Count; k++)
                    dr[k] = dt.Rows[k - 1][j];
                table.Rows.Add(dr);
            }
            return table;
        }

        public DataTable ConvertasColumns(DataTable d,bool IsHeader)
        {
            DataTable dtColums = new DataTable();
            if (IsHeader)
            {
                dtColums = new DataTable();
                string ColName;
                if (d.Rows.Count == 0)
                    return null;

                foreach (DataRow r in d.Rows)
                {
                    ColName = (string)r[0];
                    dtColums.Columns.Add(new DataColumn(ColName, typeof(string)));
                }             
            }
            else 
            {
                dtColums = (DataTable)ViewState["NewTable"];
                int RowId;
                RowId = Convert.ToInt32(d.Rows[0][0]);
                DataRow row = null;
                int col = dtColums.Columns.Count;
                int idx = 0;
                row = dtColums.NewRow();
                dtColums.Rows.Add(row);
                int J = 0;
                int K = dtColums.Columns.Count;
                for (int i = 0; i < (d.Rows.Count / dtColums.Columns.Count); i++)
                {
                    row[idx] = d.Rows[i][1];
                    //if (i % col == 0)
                    //{
                        //if (i != 0)
                        //{
                    if (i > 0)
                    {
                        J = dtColums.Columns.Count*(i);
                        K = dtColums.Columns.Count * (i + 1);
                        row = dtColums.NewRow();
                        idx++;
                        dtColums.Rows.Add(row);
                    }
                            
                            
                    //    }
                    //}
                            int n = 0;
                    for (int j=J; j < K; j++)
                    {
                        //row = dtColums.NewRow();                        

                        dtColums.Rows[i][n] = d.Rows[j][1];
                        n++;
                        
                    }         
                }
                
            }
            ViewState["NewTable"] = dtColums;
            return dtColums;            
        }

       
        public void bindgridviewheader(string id)
        {
            DataTable dthead = new DataTable();
            DataTable dtdata = new DataTable();
            DataTable dt = new DataTable();
            using(SqlConnection con=new SqlConnection(ConnStr))
            {
                string sqlstr = "SELECT FIELDNAME FROM APP_FIELDS WHERE INVENTORYID=" + id + " order by fieldid";
                //string sqlstrdat = "SELECT RS.recordsetID,Fieldvalue FROM  [APP_FIELDS] AF INNER JOIN [ASSET DETAIL] AD ON AD.FIELDID = AF.FIELDID INNER JOIN [RECORD SET] AS RS ON AD.RECORDSETID=RS.RECORDSETID WHERE RS.INVENTORYID = " + id + " order by AF.fieldid ";
                string sqlstrdat = "SELECT RS.recordsetID,Fieldvalue FROM [APP_FIELDS] AF INNER JOIN [ASSET DETAIL] AD ON AD.FIELDID = AF.FIELDID INNER JOIN [RECORD SET] AS RS ON AD.RECORDSETID=RS.RECORDSETID WHERE RS.INVENTORYID = " + id;
                SqlCommand cmdhd = new SqlCommand(sqlstr, con);
                SqlCommand cmddat = new SqlCommand(sqlstrdat, con);
                SqlDataAdapter dahd = new SqlDataAdapter(cmdhd);
                SqlDataAdapter dadat = new SqlDataAdapter(cmddat);
                dahd.Fill(dthead);
                dadat.Fill(dtdata);
                dt = ConvertasColumns(dthead,true);
                dt = ConvertasColumns(dtdata, false);
                //gv_UserContents.DataSource = FlipDataTable(dthead,dt);
                gv_UserContents.DataSource = dt;
                gv_UserContents.DataBind();

            }
        }
    }
}



and the output so far i got is like

WebsitetName WebsiteUrl CountryName KraftRegion WebAnalytics HostingProvider Channel Company
www.Google.com/ncr www.Google.com/ncr USA Washington Google SearchEngine Google Web Server Website GOOGLE INC
Yahoo USA USA New York Yahoo Website Yahoo Web Server Website Yahoo Inc
WebsitetName1 WebsiteUrl1 Washington KraftRegion1 WebAnalytics1 HostingProvider1 Channel1 Company1
WebsitetName2 WebsiteUrl2 CountryName2 KraftRegion2 WebAnalytics2 HostingProvider2 Channel2 Company2


Try 1 on 29-November-2012 :

Altered in C# code behind yesterday and I can able to bind the resultant Query to
the Gridview as like this..you can see the modified code But still struggling with
SQL Procedure to create it simple and for fast Execution..I am looking for Performance too with my page..

Look at the Code that is Updated from my Last Code;



public partial class Default1 : System.Web.UI.Page
{
static string ConnStr = System.Configuration.ConfigurationManager.ConnectionStrings["TEST"].ConnectionString.ToString();
TEST_Business_Entities BusinessLogic = new TEST_Business_Entities();
public void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
ViewState["sortOrder"] = "";
AssetDetail("", "", Convert.ToInt32(drp_Inventory.SelectedValue.ToString()));
}
}
private DataTable ConvertasColumns(DataSet AssetDataset)
{
DataTable AssetDataTbl = new DataTable();
string ColName;
if (AssetDataset.Tables[0].Rows.Count == 0)
{
return null;
}
foreach (DataRow r in AssetDataset.Tables[0].Rows)
{
ColName = (string)r[0];
AssetDataTbl.Columns.Add(new DataColumn(ColName, typeof(string)));
}
DataRow row = null;
row = AssetDataTbl.NewRow();
int col = AssetDataset.Tables[0].Rows.Count;
int index = 0;
for (int i = 0; i < AssetDataset.Tables[1].Rows.Count; i++)
{
if (i % col == 0 && i / col != 0)
{
AssetDataTbl.Rows.Add(row);
row = AssetDataTbl.NewRow();
index = 0;
}
row[index] = AssetDataset.Tables[1].Rows[i][0];
index++;
}
AssetDataTbl.Rows.Add(row);
return AssetDataTbl;
}
public void AssetDetail(string sortexp, string sortdirection, int id)
{
using (SqlConnection con = new SqlConnection(ConnStr))
{
ViewState["InventoryID"] = id;
ViewState["sortexp"] = sortexp;
ViewState["sortdirection"] = sortdirection;
DataSet ds = new DataSet();
ds.Tables.Add(ConvertasColumns(BusinessLogic.GetAssetData(id)));
DataView AssetDataView = new DataView();
AssetDataView = ds.Tables[0].DefaultView;
if (sortexp != string.Empty)
{
AssetDataView.Sort = string.Format("{0} {1}", sortexp, sortdirection);
}
gv_UserContents.DataSource = AssetDataView;
gv_UserContents.DataBind();

}
}
public void gv_UserContents_Sorting(object sender, GridViewSortEventArgs e)
{
AssetDetail(e.SortExpression, sortOrder, Convert.ToInt32(ViewState["InventoryID"]));
}
public string sortOrder
{
get
{
if (ViewState["sortOrder"].ToString() == "desc")
{
ViewState["sortOrder"] = "asc";
}
else
{
ViewState["sortOrder"] = "desc";
}

return ViewState["sortOrder"].ToString();
}
set
{
ViewState["sortOrder"] = value;
}
}

protected void gv_UserContents_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
gv_UserContents.PageIndex = e.NewPageIndex;
AssetDetail(ViewState["sortexp"].ToString(), ViewState["sortdirection"].ToString(), Convert.ToInt32(ViewState["InventoryID"]));
}
}


Business Access Layer :
SQL
public class TEST_Business_Entities
   {
       public DataSet GetAssetData(int id)
       {
           return TEST__DataAccess.GetAssetDetail(id);
       }

   }


Data Access Layer :


public static DataSet GetAssetDetail(int id)
{
DataSet dtTemplate = new DataSet();
SqlDataAdapter da = null;
try
{
using (SqlConnection sqlConn = new SqlConnection(ConnStr))
{
using (SqlCommand SqlCmd = sqlConn.CreateCommand())
{
//SqlCmd.CommandText = "select fieldname from APP_FIELDS where inventoryid = " + id + ";select fieldvalue,rs.recordsetid from [ASSET DETAIL] ad join [record set] rs on ad.recordsetid = rs.recordsetid join [APP_FIELDS] af on af.fieldid = ad.fieldid where rs.inventoryid = " + id + " order by rs.recordsetid,af.fieldid";
SqlCmd.CommandText = "select fieldname from APP_FIELDS where inventoryid = " + id + ";select fieldvalue,rs.recordsetid from [ASSET DETAIL] ad join [record set] rs on ad.recordsetid = rs.recordsetid join [APP_FIELDS] af on af.fieldid = ad.fieldid where rs.inventoryid = " + id + " order by rs.recordsetid,af.fieldid";
SqlCmd.CommandType = CommandType.Text;
if (sqlConn.State != ConnectionState.Open)
{
sqlConn.Open();
}
SqlCmd.Connection = sqlConn;
da = new SqlDataAdapter(SqlCmd);
da.Fill(dtTemplate);
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
da = null;
}
return dtTemplate;
}




But for updating and deleting process...Its better to have stored proc and bind to the Gridview and to customize we can pass a
boolean whether for update or delete the data that we need..

any pointers will be highly appreciated ..

thanks in advance...

Prasyee.
Posted
Updated 10-Jul-13 19:46pm
v24
Add a Solution
Comments
Nelek 28-Nov-12 18:18pm    
I am sorry I can not help you, but get my 5 for one of the best structurated questions I have seen in a while.
Prasyee 29-Nov-12 0:08am    
Thanks first...But indeed I need a Solution for this..

2 solutions

While I did not go through the whole thing, looking at the desired output you need a pivot query!

blatant plug for article Pivot two or more columns in SQL Server 2005[^]. This may help it is perfectly valid for 2008 and later!
 
Share this answer
 
Posted
Comments
Prasyee 29-Nov-12 4:44am    
Thanks for your Reply...

I have tried with pivot.you can see my proc..but i am getting only one row data as a o/p..can you guide me with that..I wanna dynamic thing of creating the header and corresponding data below in it..always welcome with any kind of pointers..
Hi you could try following query:

SQL
DECLARE @FieldName NVARCHAR(MAX)
SET @FieldName = 'SELECT '
SELECT @FieldName =  @FieldName + ''''+AD.FIELDVALUE + ''''+ ' AS ' +  AF.FIELDNAME+ ',' FROM
[APP_FIELDS] AF
INNER JOIN [ASSET DETAIL] AD ON AD.FIELDID = AF.FIELDID
INNER JOIN [RECORD SET] AS RS ON AD.RECORDSETID=RS.RECORDSETID WHERE RS.INVENTORYID =11

SET @FieldName = LEFT(@FieldName, LEN(@FieldName) - 1)


EXEC sp_Executesql @FieldName
 
Share this answer
 
Posted
Comments
Prasyee 29-Nov-12 23:53pm    
Thanks for your reply..
I got your query worked to give the output in a single line..
Is it Possible to make it in a tabular format.

this is what i got from your query..

WebsitetName WebsiteUrl CountryName KraftRegion WebAnalytics HostingProvider Channel Company WebsitetName WebsiteUrl CountryName KraftRegion WebAnalytics HostingProvider Channel Company WebsitetName WebsiteUrl CountryName KraftRegion WebAnalytics HostingProvider Channel Company WebsitetName WebsiteUrl CountryName KraftRegion WebAnalytics HostingProvider Channel Company
Google www.Google.com/ncr USA Washington Google Search Engine Google Web Server Website GOOGLE INC Yahoo www.yahoo.com USA New York Yahoo Website Yahoo Web Server Website Yahoo Inc WebsitetName1 WebsiteUrl1 CountryName1 KraftRegion1 WebAnalytics1 HostingProvider1 Channel1 Company1 WebsitetName2 WebsiteUrl2 CountryName2 KraftRegion2 WebAnalytics2 HostingProvider2 Channel2 Company2
Prasyee 30-Nov-12 0:43am    
Can you Please try for any further modification with the query to give the ouput what i need??..Me too will try and trying to do the same..
Thanks Once Again..Need more pointers
Prasyee 12-Dec-12 5:28am    
help needed
Rohit Shrivastava 12-Dec-12 9:42am    
I am not sure what do you mean by tabular format?
Add a Solution

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

  Print Answers RSS
Top Experts
Last 24hrsThis month
Related Questions
Gridview not updating
How to Update and Delete the Rows of Gridview using LINQ to SQL in asp.net
Edit, Update & Delete in Gridview
Binding all the database tables data into gridview and implement insert,delete and update functionality in gridview
Gridview code for insert,delete,update
Why I Must Bind Gridview On Delete,Update,Cancel Events
front end and back end code of update and delete by using stored procedure
C# Datagridview update and delete
How Change the Color of Gridview Row on Delete Button
Edit ,Update, Delete and Add new row into same gridview into asp.net with sql server

Advertise
Privacy
Cookies
Terms of Use
Last Updated 11 Jul 2013
Layout: fixed | fluid
Copyright © CodeProject, 1999-2024
All Rights Reserved.

Web01 2.8:2024-04-02:1

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