Solved

need stored procedure expert and asp.net 2.0 genius

Posted on 2008-10-30
13
229 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
 
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

758 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now