?
Solved

need stored procedure expert and asp.net 2.0 genius

Posted on 2008-10-30
13
Medium Priority
?
235 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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 2000 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 Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

752 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