Results 1 to 2 of 2

Thread: ASP.NET 2.0: Nested Repeater Using Stored Procedure

  1. #1
    Join Date
    May 2003

    Question ASP.NET 2.0: 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.

    CREATE PROCEDURE [dbo].[spElectionResults] AS


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


    '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
            '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
            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
            'Close DB connection
        End Sub
    It's the end of the world as we know it...and I feel fine

  2. #2
    Join Date
    Sep 2004
    Northeast, FL
    Based on your procedure, I don't see how your data is hierarchic. But I did notice some room for improvement in your code, so here are some modifications with notes.

    I haven't tested this code, so it might have errors but it is close enough. Also, I haven't written long hand data access code in a while but I think it is right. One thing I wanted to note is the fact that in your code you call ExecuteNonQuery and then ExecuteReader on the command, the ExecuteNonQuery is completely unnecessary.

    'More times than not, class scoped variables are not necessary.  In your code, they are not saving any memory and they could bite your arse down the road.
    'You really don't want to worry about whether a connection is closed/created, a data reader is still open, etc.  Just declare them when you need them and get rid of them when you're done.
    Sub Page_Load(ByVal Sender As Object, ByVal E As EventArgs)
        Dim electionResultSet As New DataSet
        'You should really considering moving data access and most logic into their own layers.
        Dim connString As String = System.Configuration.ConfigurationManager.AppSettings("strConn")
        Using conn As New SqlConnection(connString)
            Using da As New SqlDataAdapter("spElectionResults", conn)
            End Using
        End Using
        'Assuming you have an ASP Repeater with an Id of "ElectionResultsRepeater"
        Me.ElectionResultsRepeater.DataSource = electionResultSet.Tables(0)
        'After some refactoring, your code might look like this and your concerns have been separated.
        'Me.ElectionResultsRepeater.DataSource = ElectionResults.GetElectionResults()
    End Sub

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
HTML5 Development Center