[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 197
  • Last Modified:

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

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
HarleySkater
Asked:
HarleySkater
  • 2
1 Solution
 
HarleySkaterAuthor Commented:
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
 
HarleySkaterAuthor Commented:
fixed with this :

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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now