Solved

How to populate repeater control with results from a stored procedure

Posted on 2007-03-27
8
1,939 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Expert Comment

by:fakir420
ID: 18799844
To bind to repeater:

repeater.DataSource = dtReturn
repreater.DataBind
0
 

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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

I have developed many web applications with asp & asp.net and to add and use a dropdownlist was always a very simple task, but with the new asp.net, setting the value is a bit tricky and its not similar to the old traditional method. So in this a…
I recently went through the process of creating a Calendar Control of events with the basis of using a database to keep track of the dates that are selectable, one requirement was to have the selected date pop-up in a simple lightbox.  At first this…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…
Suggested Courses

740 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