Solved

need stored procedure expert and asp.net 2.0 genius

Posted on 2008-10-30
13
233 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

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…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

737 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