• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 168
  • 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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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