Solved

need stored procedure expert and asp.net 2.0 genius

Posted on 2008-10-30
13
231 Views
Last Modified: 2010-04-21





this sp below allows me to see data in my viewgrid as crosstab view like.

view url for sample
http://i361.photobucket.com/albums/oo58/gianitoo22/test.jpg

I need to not show row column and also i need to add hyperlink for the items display at the bottom of the columns.  how do i do that if this stored procedure is too complicated.  

my viewgrid code is below as well.  


create     PROCEDURE dbo.wmItemOptionGroupsStepsAllNewSp
 @ll INTEGER =null
, @cs INT  = null

, @en INT  = null

, @lh INT  = null
 AS





DECLARE

                @top INT

,               @SQL VARCHAR(4000)

 

--SET @ll = 154

--SET @lh = 155

--SET @cs = 189

--SET @en =

 

CREATE TABLE #CrossTab(

                Row INT

,               GroupName NVARCHAR(20)

,               Item NVARCHAR(30)

)

 

DECLARE @t TABLE(

                opt INT

)

 

IF @ll IS NOT NULL INSERT INTO @t (opt) VALUES (@ll)

IF @cs IS NOT NULL INSERT INTO @t (opt) VALUES (@cs)

IF @lh IS NOT NULL INSERT INTO @t (opt) VALUES (@lh)

IF @en IS NOT NULL INSERT INTO @t (opt) VALUES (@en)

 

SET @top = 0

SELECT @top = COUNT(*) FROM @t

 

INSERT INTO #CrossTab (GroupName, Item)

SELECT LEFT(h.item, 4), h.item

  FROM wmItemOptionHdr AS h (NOLOCK)

  JOIN wmItemOption AS o (NOLOCK) ON (o.config_num = h.config_num)

  JOIN @t AS t ON (t.opt = o.option_num)

 WHERE ISNULL(h.obs_date,'1/1/2030') > GETDATE()

 GROUP BY h.item

HAVING COUNT(*) = @top

 

UPDATE k

   SET Row = (SELECT COUNT(*) FROM #CrossTab AS p WHERE p.GroupName = k.GroupName AND p.Item <= k.item)

  FROM #CrossTab AS k

 

SET @SQL = 'SELECT p.Row'

SELECT @SQL = @SQL + ', (SELECT MAX(k.Item) FROM #CrossTab AS k WHERE k.GroupName = '''

                                                + GroupName + ''' AND k.Row = p.Row GROUP BY k.GroupName) as '

                                                + GroupName + CHAR(10)

  FROM #CrossTab

 GROUP BY GroupName

 

SET @SQL = @SQL + ' FROM #CrossTab AS p GROUP BY p.Row'

 

EXEC (@SQL)

 

DROP TABLE #CrossTab












GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

<asp:GridView
                                        ID="GridView2" runat="server" AutoGenerateColumns="True" BackColor="White"
                                        BorderColor="White" BorderStyle="Ridge" BorderWidth="2px" CellPadding="3"
                                        CellSpacing="1" DataSourceID="SqlDataSource2"
                                        EmptyDataText="No options available" GridLines="None" Width="400px">
                                        <RowStyle BackColor="White" BorderWidth="1px" ForeColor="Black" />
                                        <Columns>
                                         
                                       
                                        </Columns>
                                        <FooterStyle BackColor="#C6C3C6" ForeColor="Black" />
                                        <PagerStyle BackColor="#C6C3C6" ForeColor="Black" HorizontalAlign="Right" />
                                        <SelectedRowStyle BackColor="#9471DE" Font-Bold="True" ForeColor="White" />
                                        <HeaderStyle BackColor="#FE8330" Font-Bold="True" ForeColor="#E7E7FF" />
                                        <AlternatingRowStyle BackColor="#cccccc" />
                                    </asp:GridView>
0
Comment
Question by:gianitoo
  • 6
  • 6
13 Comments
 
LVL 37

Expert Comment

by:samtran0331
ID: 22843431
If you don't want to mess with the existing stored procedure too much (or not at all), I see 2 options,

1. Modify that stored procedure, and dump #CrossTab table into another temp table of your own, then you can select and filter columns/rows.

2. leave that stored procedure alone and instead of binding the grid with a sqldatasource, do it in codebehind, dump the stored procedure into a datatable and the datatable/dataview can be filtered and sorted...then bind that to your grid
0
 
LVL 8

Expert Comment

by:tiagosalgado
ID: 22843464
Set the AutoGenarateColumns propertie to false and add your bound columns.
"i need to add hyperlink for the items display at the bottom of the columns." < can you give more details ?

<columns>
<asp:BoundField DataField="your_column_name" HeaderText="XPTO" />
</columns>

Open in new window

0
 
LVL 1

Author Comment

by:gianitoo
ID: 22843528
I know how to bound columns to grid, the issue is  that I dont understand that stored procedure (created by one of the db guys and he is on vacation) and have no clue what the columns are called.   all i can find is row but the rest is generated dynamically.  

if you look at this image below
http://i361.photobucket.com/albums/oo58/gianitoo22/test.jpg
you will see items below that column names.  i would like to hyperlink to how is shown below but i have no clue what my items or columns are called.  Look at the SP to see if you can understand.  

           <asp:TemplateField  HeaderText="Mills">
                <ItemTemplate>
                    <asp:HyperLink ID="lnkFile" runat="server"
                            NavigateUrl='<%#Eval("item", "options.aspx?option={0}")%>'
                        Text='<%#Eval ("item") %>' ></asp:HyperLink>
                </ItemTemplate>

            </asp:TemplateField>

y
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 37

Expert Comment

by:samtran0331
ID: 22843617
"GroupName" is the columns, "item" is the data in each cell
0
 
LVL 37

Expert Comment

by:samtran0331
ID: 22843673
are you wanting to turn the text in each cell into a link?
0
 
LVL 1

Author Comment

by:gianitoo
ID: 22843695
is it possible to create the url from the stored procedure?  
on item so it goes to
option.aspx?id=whatever item is
0
 
LVL 1

Author Comment

by:gianitoo
ID: 22843736
yes yes yes  i am wanting to turn the text in each cell into a link?
0
 
LVL 37

Expert Comment

by:samtran0331
ID: 22843949
leave everything as it is, try adding a rowdatabound event with this code:


        If e.Row.RowType = DataControlRowType.DataRow Then
            For i As Integer = 1 To e.Row.Cells.Count - 1
                e.Row.Cells(i).Text = "<a href=""options.aspx?option=" & e.Row.Cells(i).Text & """>" & e.Row.Cells(i).Text & "</a>"
            Next
        End If

Open in new window

0
 
LVL 37

Accepted Solution

by:
samtran0331 earned 500 total points
ID: 22843995
I notice you have empty cells...so you might need this extra check...try:
        If e.Row.RowType = DataControlRowType.DataRow Then
            For i As Integer = 1 To e.Row.Cells.Count - 1
                If e.Row.Cells(i).Text.Trim() <> "&nbsp;" Then
                    e.Row.Cells(i).Text = "<a href=""options.aspx?option=" & e.Row.Cells(i).Text & """>" & e.Row.Cells(i).Text & "</a>"
                End If
            Next
        End If

Open in new window

0
 
LVL 1

Author Comment

by:gianitoo
ID: 22844076
You are a freaking genious
0
 
LVL 1

Author Closing Comment

by:gianitoo
ID: 31511765
He is just a genius
0
 
LVL 1

Author Comment

by:gianitoo
ID: 22853798
I tweaked my sp do i dont display row number and know first column is not linked.  

how can i tweak this code so it links .  attaching sp in case.  

    Protected Sub GridView2_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs)
        If e.Row.RowType = DataControlRowType.DataRow Then
            For i As Integer = 1 To e.Row.Cells.Count - 1
                If e.Row.Cells(i).Text.Trim() <> "&nbsp;" Then
                    e.Row.Cells(i).Text = "<a href=""options.aspx?option=" & e.Row.Cells(i).Text & """>" & e.Row.Cells(i).Text & "</a>"
                End If
            Next
        End If
    End Sub
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
 
 
 
 
 
 
 
 
 
ALTER        PROCEDURE dbo.wmItemOptionGroupsStepsAllNewSp 
 @ll INTEGER =null
, @cs INT  = null
 
, @en INT  = null
 
, @lh INT  = null
 AS
 
 
 
 
 
DECLARE
 
                @top INT 
 
,               @SQL VARCHAR(4000)
 
 
 
--SET @ll = 154
 
--SET @lh = 155
 
--SET @cs = 189
 
--SET @en = 
 
 
 
CREATE TABLE #CrossTab(
 
                Row INT 
 
,               GroupName NVARCHAR(20)
 
,               Item NVARCHAR(30)
 
)
 
 
 
DECLARE @t TABLE(
 
                opt INT
 
)
 
 
 
IF @ll IS NOT NULL INSERT INTO @t (opt) VALUES (@ll)
 
IF @cs IS NOT NULL INSERT INTO @t (opt) VALUES (@cs)
 
IF @lh IS NOT NULL INSERT INTO @t (opt) VALUES (@lh)
 
IF @en IS NOT NULL INSERT INTO @t (opt) VALUES (@en)
 
 
 
SET @top = 0
 
SELECT @top = COUNT(*) FROM @t
 
 
 
INSERT INTO #CrossTab (GroupName, Item) 
 
SELECT LEFT(h.item, 4), h.item
 
  FROM wmItemOptionHdr AS h (NOLOCK)
 
  JOIN wmItemOption AS o (NOLOCK) ON (o.config_num = h.config_num)
 
  JOIN @t AS t ON (t.opt = o.option_num)
 
 WHERE ISNULL(h.obs_date,'1/1/2030') > GETDATE()
 
 GROUP BY h.item
 
HAVING COUNT(*) = @top
 
 
 
UPDATE k
 
   SET Row = (SELECT COUNT(*) FROM #CrossTab AS p WHERE p.GroupName = k.GroupName AND p.Item <= k.item)
 
  FROM #CrossTab AS k
 
 
SET @SQL = 'SELECT '
SELECT @SQL = @SQL + '(SELECT MAX(k.Item) FROM #CrossTab AS k WHERE k.GroupName = ''' 
                                                + GroupName + ''' AND k.Row = p.Row GROUP BY k.GroupName) as ' 
                                                + GroupName + CHAR(10) + ', '
  FROM #CrossTab
 GROUP BY GroupName
 
SET @SQL = SUBSTRING(@SQL,1,LEN(@SQL)-1) + ' FROM #CrossTab AS p GROUP BY p.Row'
EXEC (@SQL) 
 
DROP TABLE #CrossTab
 
 
 
 
 
 
 
 
 
 
 
 
 
 
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Open in new window

0
 
LVL 37

Expert Comment

by:samtran0331
ID: 22853882
I started the "For" loop at 1 to exclude the first column, but if you want to link it, then change the 1 to zero
Protected Sub GridView2_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs)
        If e.Row.RowType = DataControlRowType.DataRow Then
            For i As Integer = 0 To e.Row.Cells.Count - 1
                If e.Row.Cells(i).Text.Trim() <> "&nbsp;" Then
                    e.Row.Cells(i).Text = "<a href=""options.aspx?option=" & e.Row.Cells(i).Text & """>" & e.Row.Cells(i).Text & "</a>"
                End If
            Next
        End If
    End Sub

Open in new window

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question