p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   ASP.NET 2.0 Professional (http://p2p.wrox.com/forumdisplay.php?f=215)
-   -   Nested Repeater Using Stored Procedure (http://p2p.wrox.com/showthread.php?t=69522)

kwilliams July 21st, 2008 02:59 PM

Nested Repeater Using Stored Procedure
 
I found a great tutorial on how to use the nested Repeater control to display hierarchical data using ASP.NET 2.0 and VB.NET at http://www.aspnettutorials.com/tutor...peater-vb.aspx. But it uses SELECT statements within the codefile, while I want to use stored procedures instead. So if someone could give me a basic example of how to modify its code using stored procedures instead, it would be greatly appreciated. I've included the code to reference that I've already created that uses stored procedures. Thanks.

spElectionResults
CREATE PROCEDURE [dbo].[spElectionResults] AS

SET NOCOUNT OFF

SELECT ContestID, ContestTitle
FROM tblElectionResults
WHERE (VoteFor <> '00')
GROUP BY ContestID, ContestTitle
HAVING (COUNT(ContestID) >= 1)
ORDER BY ContestTitle ASC, ContestID ASC

SET NOCOUNT ON
GO

contests.aspx.vb
Code:

'Declare global variables
    Dim sqlConn As SqlConnection
    Dim strConnection As String
    Public dr As SqlDataReader

    'Declare the parameters for stored procedures
    Private cmd_electionresults As New SqlCommand()

    Sub Page_Load(ByVal Sender As Object, ByVal E As EventArgs)

        Dim ds As New DataSet

        'Assign connection string
        strConnection = System.Configuration.ConfigurationManager.AppSettings("strConn")
        sqlConn = New SqlConnection(strConnection)

        'Open DB connection
        sqlConn.Open()

        'Declare variables
        Dim strContestID As String = Nothing, strContestTitle As String = Nothing

        'Declare stored procedure
        cmd_electionresults = New SqlCommand("spElectionResults", sqlConn)
        cmd_electionresults.CommandType = CommandType.StoredProcedure

        'Execute stored procedure and data reader
        cmd_electionresults.ExecuteNonQuery()
        dr = cmd_electionresults.ExecuteReader()

        While dr.Read()
            'Assign variables from DB table
            strContestID = dr("ContestID").ToString()
            strContestTitle = dr("ContestTitle").ToString()

            'Trim trailing whitespace from address variables
            strContestID = strContestID.Trim
            strContestTitle = strContestTitle.Trim

            'Test variables
            'Response.Write("strContestID: " & strContestID & "<br />")
            'Response.Write("strContestTitle: " & strContestTitle & "<br />")

            'Assign labels
            lblContestID.Text = strContestID
            lblContestTitle.Text = strContestTitle
        End While

        dr.Close()

        'Close DB connection
        sqlConn.Close()

    End Sub



KWilliams

alliancejhall July 21st, 2008 06:49 PM

get rid of this line here:

'Execute stored procedure and data reader
     cmd_electionresults.ExecuteNonQuery()
        dr = cmd_electionresults.ExecuteReader()

leaving:

'Execute stored procedure and data reader
        dr = cmd_electionresults.ExecuteReader()



Jason Hall


All times are GMT -4. The time now is 09:13 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.