Solved

How to populate repeater control with results from a stored procedure

Posted on 2007-03-27
8
1,930 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone 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

In an ASP.NET application, I faced some technical problems. In this article, I list them out and show the solutions that I found.  I hope it will be useful. Problem: After closing a pop-up window, the parent page should be refreshed automaticall…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

840 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