Solved

How to populate repeater control with results from a stored procedure

Posted on 2007-03-27
8
1,926 Views
Last Modified: 2011-10-03
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
0
Comment
Question by:rss2
  • 4
  • 3
8 Comments
 
LVL 28

Expert Comment

by:mmarinov
ID: 18799269
0
 

Author Comment

by:rss2
ID: 18799337
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
 
LVL 1

Expert Comment

by:fakir420
ID: 18799830
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
 
LVL 1

Expert Comment

by:fakir420
ID: 18799844
To bind to repeater:

repeater.DataSource = dtReturn
repreater.DataBind
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:rss2
ID: 18800343
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
 

Author Comment

by:rss2
ID: 18800349
sorry.. more on the error above..

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

Thanks,
rss2
0
 
LVL 1

Accepted Solution

by:
fakir420 earned 500 total points
ID: 18800413
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
 

Author Comment

by:rss2
ID: 18800747
Awesome. Thank you!!!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How do I get the id from URL? 19 53
asp.net bundle 8 46
How can I use this extension method? 8 45
c# regex: extract & replace text between braces 6 43
User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

932 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now