Solved

Repeater works with sql statement but not with stored procedure?  But stored procedure works alone!

Posted on 2008-10-29
2
192 Views
Last Modified: 2012-05-05
I am trying to use a stored procedure with a repeater

In a previous post I was able to achieve getting the repeater to work with an sql select command, I however cannot get it to work with the stored procedure.  I need to pass in a parameter for the where = part of the select statement.

I will post the working sql command repeater and my failed sproc attempt.
--- working sql command
            Using myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("AGConnectionString").ConnectionString)
 
 
                Using myCommand As New SqlCommand("SELECT UserId, ProfileName, RealName, Age from ag_profiles", myConnection)
 
                    myConnection.Open()
 
                    Dim dr As SqlDataReader = myCommand.ExecuteReader()
 
                    ListProfiles.DataSource = dr
 
                    While (dr.Read())
 
                        ListProfiles.DataBind()
 
                    End While
 
                    dr.Close()
 
                    myConnection.Close()
                End Using
            End Using
 
---- Fail stored procedure
Using myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("AGConnectionString").ConnectionString)
 
                Using myCommand As New SqlCommand("AGProfileSearchName", myConnection)
 
                    myCommand.CommandType = CommandType.StoredProcedure
 
                    myConnection.Open()
 
                    myCommand.Parameters.AddWithValue("@SearchName", SearchName)
 
                    Dim dr As SqlDataReader = myCommand.ExecuteReader()
 
                    ListProfiles.DataSource = dr
 
                    While (dr.Read())
                        UserIdLabel.Text = dr("UserId").ToString().Trim()
 
 
                        ListProfiles.DataBind()
 
                    End While
 
                    dr.Close()
 
                    myConnection.Close()
                End Using
            End Using

Open in new window

0
Comment
Question by:HarleySkater
[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
  • 2
2 Comments
 
LVL 1

Author Comment

by:HarleySkater
ID: 22834907
I got this working with a stored procedure, but I can't get it to work with a stored procedure that has a parameter?????

  if I use a stored procedure with a parameter it throws an error at: da.Fill(ProfileSearchResultSet)
saying:

System.Data.SqlClient.SqlException was unhandled by user code
  Class=16
  ErrorCode=-2146232060
  LineNumber=0
  Message="Procedure or function 'AGProfileSearchName' expects parameter '@SearchName', which was not supplied."
  Number=201
  Procedure="AGProfileSearchName"
  Server="ACF000\SQLEXPRESS"
  Source=".Net SqlClient Data Provider"
  State=4
 
            Using myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("AGConnectionString").ConnectionString)
 
                Using myCommand As New SqlCommand("AGProfileSearchName", myConnection)
 
 
                    myCommand.CommandType = CommandType.StoredProcedure
                    myCommand.Parameters.AddWithValue("@SearchName", "a")
 
 
                    myConnection.Open()
 
 
                    Using da As New SqlDataAdapter("AGProfileSearchName", myConnection)
 
                        da.Fill(ProfileSearchResultSet)
                    End Using
 
                    myConnection.Close()
                End Using
 
 
                'ASP Repeater with an Id of "ListProfilesRepeater"
                Me.ListProfilesRepeater.DataSource = ProfileSearchResultSet.Tables(0)
                Me.ListProfilesRepeater.DataBind()
 
            End Using

Open in new window

0
 
LVL 1

Accepted Solution

by:
HarleySkater earned 0 total points
ID: 22835185
fixed with this :

                    Using da As New SqlDataAdapter("AGProfileSearchName", myConnection)
                        da.SelectCommand = myCommand
                        da.Fill(ProfileSearchResultSet)
                    End Using
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

630 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