Solved

need stored procedure expert and asp.net 2.0 genius

Posted on 2008-10-30
13
234 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

 
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

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that undeā€¦
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

724 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