Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2011
    Location
    Houston, TX
    Posts
    32

    Question Unanswered: Stored Procedure Woes

    Hello,

    I'm getting a strange error from my compiler that I can't seem to figure out.

    I have this stored procedure that looks like this:
    Code:
    ALTER PROCEDURE getUser
    	@Username nvarchar(20),
    	@UserID int OUTPUT,
    	@PW nvarchar(20) OUTPUT,
    	@FirstLogin Date OUTPUT
    
    AS
    
    SELECT @UserID = UserID,
    		@PW = PW,
    		@FirstLogin = FirstLogin
    FROM tblUsers
    WHERE Username = @Username
    When I manually execute the stored procedure and input the values, i get this crumby output:
    Running [dbo].[getUser] ( @Username = Atsukoarai86, @UserID = <DEFAULT>, @PW = <DEFAULT>, @FirstLogin = <DEFAULT> ).

    Procedure or function 'getUser' expects parameter '@UserID', which was not supplied.
    No rows affected.
    (0 row(s) returned)
    @UserID = <NULL>
    @PW = <NULL>
    @FirstLogin = <NULL>
    @RETURN_VALUE =
    Finished running [dbo].[getUser].


    ... so I'm not sure exactly what is going on here.

    Also:

    Code:
    'Create a new Connection 
                conn = New SqlConnection("Data Source=.\SQLExpress; AttachDbFilename=|DataDirectory|Claytor'sWebApp.mdf; Integrated Security = True; User Instance= True")
                'Create a new SqlCommand
                cmd = New SqlCommand
    
                With cmd
                    .CommandType = Data.CommandType.StoredProcedure
                    .CommandText = "getUser"
                    .Connection = conn
                End With
    
                parm = cmd.Parameters.Add(New SqlParameter("@Username", Data.SqlDbType.NVarChar))
                With parm
                    .Direction = Data.ParameterDirection.Input
                    .Value = txtUsername.Text
                End With
    
                'Define and add the output parameters
                parm = cmd.Parameters.Add(New SqlParameter("@UserID", Data.SqlDbType.Int))
                'Specify the parameter Direction
                parm.Direction = Data.ParameterDirection.Output
    
                parm = cmd.Parameters.Add(New SqlParameter("@PW", Data.SqlDbType.NVarChar))
                parm.Direction = Data.ParameterDirection.Output
    
                parm = cmd.Parameters.Add(New SqlParameter("@FirstLogin", Data.SqlDbType.Date))
                parm.Direction = Data.ParameterDirection.Output
    
                'Open the connection
                conn.Open()
    
                cmd.ExecuteNonQuery()
    
                userID = cmd.Parameters("@UserID").Value
                Dim pw As String = cmd.Parameters("@PW").Value.ToString
                Dim FirstLogin As Date = cmd.Parameters("@FirstLogin").Value
    The line cmd.ExecuteNonQuery() says to me "String[2]: the Size property has an invalid size of 0."

    i have no idea what is wrong here. Someone shine a light on this.

  2. #2
    Join Date
    Nov 2011
    Location
    Houston, TX
    Posts
    32

    Talking ugh, I fixed it.

    Here was the problem..

    parm = cmd.Parameters.Add(New SqlParameter("@PW", Data.SqlDbType.NVarChar))
    it's an nvarchar(20)... had to use the constructor to specify the size

    parm = cmd.Parameters.Add(New SqlParameter("@PW", Data.SqlDbType.NVarChar, 20)) <--- works now.


    UGH. lol I knew it had to be something idiotic like that.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •