Help with Microsoft.ApplicationBlocks.Data SPOC Sample

  • Thread starter Thread starter Stuart Shay
  • Start date Start date
S

Stuart Shay

Hello All:

I am trying to retreve the Values from the following SPOC (Works in SQL QA)
And Return the Values into my ASP.NET (VB) Code.



---- SPOC Works -----
CREATE PROCEDURE getLoginCredentials
@Email nvarchar(50)
AS
SELECT UserName,Password FROM Users
WHERE Email = @Email
GO

---- Code ------
Private Function ValidateUserAccount(ByVal Email As String) As Integer

Dim UserName, Password As String
Dim myConnection As New
SqlConnection(ConfigurationSettings.AppSettings("connectionString"))

'Setup Parmaters (1 input and 2 output)
Dim arParms() As SqlParameter = New SqlParameter(2) {}

' @Email Input Parameter
arParms(0) = New SqlParameter("@Email", SqlDbType.NVarChar, 50)
arParms(0).Value = Email

' @UserName Output Parameter
arParms(1) = New SqlParameter("@UserName", SqlDbType.NVarChar, 50)
arParms(1).Direction = ParameterDirection.Output

' @Password Output Parameter
arParms(2) = New SqlParameter("@Password", SqlDbType.NVarChar, 50)
arParms(2).Direction = ParameterDirection.Output

Try
' Execute the stored procedure
SqlHelper.ExecuteNonQuery(myConnection,
CommandType.StoredProcedure, "GetLoginCredentials", arParms)

'Create a string array of return values and assign values
returned from stored procedure
Dim arReturnParms() As String = New String(2) {}
arReturnParms(0) = arParms(1).Value.ToString()
arReturnParms(1) = arParms(2).Value.ToString()

Trace.Write("UserName: " + arReturnParms(0))
Trace.Write("Password: " + arReturnParms(1))

' Return output parameters

'Return arReturnParms

Catch ex As Exception
Trace.Write(ex.ToString)
' Throw ex
End Try


-- Trace Output -----

System.Data.SqlClient.SqlException: Procedure or function
GetLoginCredentials has too many arguments specified.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(SqlConnection
connection, CommandType commandType, String commandText, SqlParameter[]
commandParameters)
at LoginDemo.EmailPassword.ValidateUserAccount(String Email) in
D:\ASP.NET\LoginDemo\Authentication\EmailPassword.aspx.vb:line 81
 
Realized 1 min after I sent the post that my SPOC was wrong

CREATE PROCEDURE getLoginCredentials
@Email nvarchar(50),
@UserName nvarchar(50) OUTPUT,
@Password nvarchar(50) OUTPUT
AS
SELECT
@UserName=UserName,
@Password=Password
FROM Users
WHERE Email = @Email
GO

Thanks All
 
Back
Top