dynamic pivot data to display in gridview

[thep1]

i have a stored procedure that i would like to have its data displayed in gridview but when i call he stored procedure its only showing me the header row only.

here is the stored procedure
--Declare necessary variables
DECLARE @SQLQuery AS NVARCHAR(MAX)
DECLARE @PivotColumns AS NVARCHAR(MAX)

--Get unique values of pivot column
SELECT @PivotColumns= COALESCE(@PivotColumns + ',','') + QUOTENAME(ItemLineInventorySiteRefFullName)
FROM (SELECT DISTINCT ItemLineInventorySiteRefFullName FROM .) AS PivotExample

SELECT @PivotColumns

--Create the dynamic query with all the values for
--pivot column at runtime
SET @SQLQuery =
N'SELECT ItemInventoryRefListID, ' + @PivotColumns + '
FROM .
PIVOT( SUM(CURRENT_AVAILABLE_AT_LOCATION)
FOR ItemLineInventorySiteRefFullName IN (' + @PivotColumns + ')) AS P'

SELECT @SQLQuery
--Execute dynamic query
EXEC sp_executesql @SQLQuery

The return looks like this

[1 - NJ],[2 - NJ2],[6 - NC2],[Drop Ship],[5 - NC],[4 - MD2],[3 - MD],[Unspecified Site]

SELECT ItemInventoryRefListID, [1 - NJ],[2 - NJ2],[6 - NC2],[Drop Ship],[5 - NC],[4 - MD2],[3 - MD],[Unspecified Site] FROM [dbo].[v_InventoryByLocation] PIVOT( SUM(CURRENT_AVAILABLE_AT_LOCATION) FOR ItemLineInventorySiteRefFullName IN ([1 - NJ],[2 - NJ2],[6 - NC2],[Drop Ship],[5 - NC],[4 - MD2],[3 - MD],[Unspecified Site])) AS P

ItemInventoryRefListID 1 - NJ 2 - NJ2 6 - NC2 Drop Ship 5 - NC 4 - MD2 3 - MD Unspecified Site
10B0001-1193778348 41 NULL NULL NULL NULL NULL NULL NULL
1210001-1179844954 NULL NULL NULL NULL NULL NULL NULL NULL
1220001-1174069636 1 NULL NULL NULL NULL NULL NULL NULL


and in VS gridview when i call the SP i get only:
[1 - NJ],[2 - NJ OCEAN],[6 - NC OCEAN],[Drop Ship],[5 - NC],[4 - MD OCEAN],[3 - MD],[Unspecified Site]


code behind that does the databind
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim strConnString As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
Dim con As New SqlConnection(strConnString)
Dim cmd As New SqlCommand()
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "_InventoryBySiteDetailsReportProc"
cmd.Connection = con

Try
con.Open()
gvItemByLoc.EmptyDataText = "No Records Found"
gvItemByLoc.DataSource = cmd.ExecuteReader()
gvItemByLoc.DataBind()
Catch ex As Exception
Throw ex
Finally
con.Close()
con.Dispose()
End Try
End Sub

Grid View
<asp:GridView ID="gvItemByLoc" runat="server" AutoGenerateColumns="True">
</asp:GridView>


i dont know know if this is the rite way? if not is there something else i can do so i can display this data in gridview?


This is the data i want to fill up in the gridview
ItemInventoryRefListID 1 - NJ 2 - NJ2 6 - NC2 Drop Ship 5 - NC 4 - MD2 3 - MD Unspecified Site
10B0001-1193778348 41 NULL NULL NULL NULL NULL NULL NULL
1210001-1179844954 NULL NULL NULL NULL NULL NULL NULL NULL
1220001-1174069636 1 NULL NULL NULL NULL NULL NULL NULL

 

[thep1]

never mind got it to work.

removed the 2 selects and now the data I want is displayed.

SELECT @PivotColumns
SELECT @SQLQuery

 

[cronywalls]

Check this...gridview tutorial

 

[jbenson001]

The problem is you are using a data reader and I am not sure why, it is not saving you anything, but just causing issues:

https://stackoverflow.com/questions/16381490/binding-sqldatareader-to-gridview-c-sharp

https://www.codeproject.com/Questions/679137/fill-gridview-from-datareader

Save yourself the aggravation and just put your results into a datatable and bind that to your grid