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
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