Solved

How to populate repeater control with results from a stored procedure

Posted on 2007-03-27
8
1,947 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
Industry Leaders: 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
In .NET 2.0, Microsoft introduced the Web Site.  This was the default way to create a web Project in Visual Studio 2005.  In Visual Studio 2008, the Web Application has been restored as the default web Project in Visual Studio/.NET 3.x The Web Si…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

631 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