• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 158
  • Last Modified:

I keep getting an error saying "Procedure or function "findModules" expects a parameter '@UserName', which was not supplied.

Here is my code. It says that I am not supplying it to the stored procedure but I clearly am. Can someone please help me with this.

Public Class ASPNETDB
    Public Shared Function getModuleNames() As ModuleList

        Dim modules As New ModuleList
        Dim conn As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ConnectionString3").ToString()
        Dim mysource As SqlDataSource = New SqlDataSource(conn, "findModules")

        Dim sqlConn As New SqlConnection(conn)
        sqlConn.Open()
        Dim Cmd As New SqlCommand()
        Cmd.CommandText = "findModules"
        Cmd.CommandType = Data.CommandType.StoredProcedure
        Cmd.Connection = sqlConn
        Dim NameParameter As New SqlParameter("@UserName", Data.SqlDbType.VarChar)
        NameParameter.Value = HttpContext.Current.User.Identity.Name
        NameParameter.Direction = ParameterDirection.Input
        Cmd.Parameters.Add(NameParameter)
        Cmd.ExecuteNonQuery()
        sqlConn.Close()


        Dim result As IEnumerable = mysource.Select(DataSourceSelectArguments.Empty)
        Dim row As DataRowView
        For Each row In result
            modules.Add(New ModuleFind( _
                          row("ModuleId").ToString(), _
                          row("ModuleName").ToString()) _
              )

Here is my stored procedure

ALTER PROCEDURE dbo.findModules  
      (
      @UserName varchar
      
      )
   
    AS
    DECLARE @UserID uniqueidentifier
    SET @UserID = (SELECT UserId FROM aspnet_Users WHERE UserName = @UserName)
   
SELECT        UserId, ModuleId, ModuleName
FROM              Modules
WHERE         Modules.UserId = @UserID
0
rcon1con
Asked:
rcon1con
  • 4
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the problem is that your run the procedure, but the code is close:
Public Class ASPNETDB
    Public Shared Function getModuleNames() As ModuleList 
        Dim modules As New ModuleList
        Dim conn As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ConnectionString3").ToString() 
        Dim sqlConn As New SqlConnection(conn)
        sqlConn.Open()
        Dim Cmd As New SqlCommand()
        Cmd.CommandText = "findModules"
        Cmd.CommandType = Data.CommandType.StoredProcedure
        Cmd.Connection = sqlConn
        Dim NameParameter As New SqlParameter("@UserName", Data.SqlDbType.VarChar)
        NameParameter.Value = HttpContext.Current.User.Identity.Name
        NameParameter.Direction = ParameterDirection.Input
        Cmd.Parameters.Add(NameParameter) 
        Dim mysource As SqlDataSource = New SqlDataSource(conn, Cmd) 
        sqlConn.Close() 

        Dim result As IEnumerable = mysource.Select(DataSourceSelectArguments.Empty)
        Dim row As DataRowView
        For Each row In result
            modules.Add(New ModuleFind( _
                          row("ModuleId").ToString(), _
                          row("ModuleName").ToString()) _
              )

Open in new window

0
 
rcon1conAuthor Commented:
Can you describe what you mean? and any suggestions as to how i might fix it?
Thank you
0
 
rcon1conAuthor Commented:
I ran it with the changes and it gives the error 'Value of System.data.sqlclient.sqlcommand' cannot be converted to string

Dim mysource As SqlDataSource = New SqlDataSource(conn, Cmd)
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
rcon1conAuthor Commented:
fixed it myself
0
 
käµfm³d 👽Commented:
The problem is that you are setting the SqlDataSource's select command to your stored procedure name, but you do not add a parameter to the SqlDataSource's parameter list. Your extra command object is not needed. I have cleaned up the code some:
Public Class ASPNETDB
    Public Shared Function getModuleNames() As ModuleList
 
        Dim modules As New ModuleList
        Dim conn As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ConnectionString3").ToString()
        Dim mysource As SqlDataSource = New SqlDataSource(conn, "findModules")
 
        Dim NameParameter As New SqlParameter("@UserName", Data.SqlDbType.VarChar)
        NameParameter.Value = HttpContext.Current.User.Identity.Name
        NameParameter.Direction = ParameterDirection.Input
        mysource.SelectCommand.Parameters.Add(NameParameter)
        mysource.SelectCommand.CommandType = Data.CommandType.StoredProcedure
 
        Dim result As IEnumerable = mysource.Select(DataSourceSelectArguments.Empty)
        Dim row As DataRowView
        For Each row In result
            modules.Add(New ModuleFind( _
                          row("ModuleId").ToString(), _
                          row("ModuleName").ToString()) _
              )

Open in new window

0
 
rcon1conAuthor Commented:
I already have it fixed but thanks anyway
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

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