How to populate repeater control with results from a stored procedure

I have a repeater control and a SqlDataSource1 object on the aspx page.

In the code behind the page, I am calling a stored procedure. I would like to use the results of this stored procedure to populate the repeater control.

Is that possible?

The reason I would like to do this is because the stored procedure has parameters and I would like to pass in values for those parameters that are held as property values in a class (I use property Set and property Get for these values throughout these pages).

Alternatively, is it possible to still use the SqlDataSource1 object AND pass it the property values in the code?

Here is an example of my code:
SetVars.vb-
Public Class SetVars
    Public strIndicator As String
    Public Property UserGreeting() As String
        Get
            UserGreeting = strUser_Greeting
        End Get
        Set(ByVal value As String)
            strUser_Greeting = value
        End Set
    End Property


firstpage.aspx.vb (this page Sets the value)
Dim sv as New SetVars
sv.strIndicator = "ALL"

secondpage.aspx.vb (this page has the SqlDataSource1 object but I'd rather call the stored proc and use the results from that..)
Protected Sub LoadData()
        Dim conn As New SqlConnection
        Dim cmd As New SqlCommand
        Dim connstring As String
        Dim sv As New SetVars

        connstring = Connector.ConnectStringBuild
        conn.ConnectionString = connstring
        cmd.Connection = conn
        cmd.CommandText = "ACT_GET_COLLECTION_DETAILS"
        cmd.CommandType = CommandType.StoredProcedure

        cmd.Parameters.Add("@INDICATOR", SqlDbType.VarChar).Value = sv.strIndicator

        conn.Open()
        cmd.ExecuteNonQuery()
        conn.Close()

        conn = Nothing
        cmd = Nothing
    End Sub
rss2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mmarinovCommented:
0
rss2Author Commented:
Thanks. These examples pass in the actual SQL query. I would like to use a stored procedure.

How would I fill a data table or data reader or data adapter using the results of a stored procedure, per my code above?

Then how do I bind that data to the repeater control?
0
fakir420Commented:
Try this, you can also use a DataSet to fill as opposed to DataTable depending on how many tables your stored procedure returns.  ExecuteNonQuery is not for returning data, it is for things like update and insert statements.

Protected Sub LoadData()
        Dim conn As New SqlConnection
        Dim cmd As New SqlCommand
        Dim adapter as New SqlDataAdapter
        Dim dtReturn as New DataTable
        Dim connstring As String
        Dim sv As New SetVars

        connstring = Connector.ConnectStringBuild
        conn.ConnectionString = connstring
        cmd.Connection = conn
        cmd.CommandText = "ACT_GET_COLLECTION_DETAILS"
        cmd.CommandType = CommandType.StoredProcedure

        cmd.Parameters.Add("@INDICATOR", SqlDbType.VarChar).Value = sv.strIndicator
        adapter.SelectCommand = objCmd

        conn.Open()
        adapter.Fill(dtReturn)
        conn.Close()

        conn = Nothing
        cmd = Nothing
    End Sub
0
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

fakir420Commented:
To bind to repeater:

repeater.DataSource = dtReturn
repreater.DataBind
0
rss2Author Commented:
On the adapter.Fill(dtReturn) line, it's says "InvalidOperationException was unhandled by user code"

Do you know what that means? I can't figure out what's wrong.
0
rss2Author Commented:
sorry.. more on the error above..

It says:
"The SelectCommand property has not been initialized before calling 'Fill'."

Thanks,
rss2
0
fakir420Commented:
Sorry, in my code I used

adapter.SelectCommand = objCmd

you command object is named differently so use

adapter.SelectCommand = cmd

instead and that should work.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rss2Author Commented:
Awesome. Thank you!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.