ASP.NET 2.0: Nested Repeater Using Stored Procedure

[kwilliams]

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/tutorials/c...epeater-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.spElectionResultsCREATE PROCEDURE [dbo].[spElectionResults] ASSET NOCOUNT OFFSELECT ContestID, ContestTitleFROM tblElectionResultsWHERE (VoteFor <> '00')GROUP BY ContestID, ContestTitleHAVING (COUNT(ContestID) >= 1)ORDER BY ContestTitle ASC, ContestID ASCSET NOCOUNT ONGOcontests.aspx.vb

'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

[jesh]

I typically bind DataTables to my Repeaters.Loading a DataTable with C#

DataTable results = new DataTable();SqlCommand command = new SqlCommand("storedprocedurename", connection);command.CommandType = CommandType.StoredProcedure;SqlDataReader reader = command.ExecuteReader();results.Load(reader);reader.Close();reader.Dispose();command.Dispose();

Then, if you are using DataTables, it doesn't matter how you get the data, you just bind the DataTable to the Repeater

myRepeater.DataSource = results;myRepeater.DataBind();

[aspnetguy]

I almost always use DataTables for controls that allow databinding. This allows you to easily change your method for retrieving the data with minimal affect on the rest of your code. Like jesh said.