Scalar variable Error

[Elmo_loves_you]

Hello Everyone :S

I am rather new to ASP.NET. I have an SQL Server 2005 database set up. So far its quite basic with just two tables (UsersLogin, Roles). My aim is to allow users of different ranks to login to the system ie. Admin, Staff etc.

UsersLogin table holds (UserID, Username, Password, RoleID)
Roles table holds (RoleID, RoleTitle)

I have created a stored procedure that returns the roleTitle based on the username and password entered. I was thinking that this approach would enable me to direct the user to the appropriate page for their rank (in the code behind page), the code is as follows:

//-----------------------------Code Sample--------------------------------
CREATE PROCEDURE dbo.ValidateUser
/* Parameters needed to obtain data from user*/
(
@UserName VARCHAR(20) = NULL
@Password VARCHAR(20) = NULL
@RoleTitle VARCHAR(20) = NULL
)
AS
SELECT Roles.RoleTitle
FROM Roles INNER JOIN
UsersLogin ON Roles.RoleID = UsersLogin.RoleID
WHERE (UsersLogin.Username = @UserName) AND (UsersLogin.Password = @Password)
RETURN @RoleTitle

//-----------------------------END Code Sample--------------------------------

I can execute the query successfully within the design wizard and I get the result I want but I can't save the stored procedure. I am prompted with the following Error Message:

Incorrect syntax near '@Password'
Must declare the scalar variable '@UserName'
Must decalre the scalar variable '@RoleTitle'

I have spent some time reading articles and browsing other threads that have suggested using '?' but this hasnt worked.

Has anyone any ideas? Thanks in advance

Elmo :)

[ravichandra]

Remove

RETURN @RoleTitle
and try

Good luck

[Elmo_loves_you]

Remove

RETURN @RoleTitle
and try

Good luck

Thankyou very much, it worked :)

I now have my login page identifying the correct rank .... wooohooo

though thats just one of a million other things I have to do :)

[salaam_taher]

Hi Friends,

I am getting the same error "Must declare scalar variable" but in very strange condition. Here is my code.

strSql = "select count(clnt_id) from tbl_clnt_custom with(nolock) where org_id=@strOrgId and custom_id=@strCustFldId and clnt_id=@strClntId and (custom_value is null or rtrim(ltrim(custom_value))='')"

objDb.addParameter("@strOrgId", SqlDbType.VarChar, 25, strOrgId, DB.sql.Validator.Yes)
                   
 objDb.addParameter("@strCustFldId", SqlDbType.VarChar, 50, strCustFldId, DB.sql.Validator.Yes)
                    
objDb.addParameter("@strClntId", SqlDbType.VarChar, 50, i_strClntId, DB.sql.Validator.Yes)

                    
strSql1 = "select count(clnt_id) from tbl_clnt_custom with(nolock) where org_id=@OrgId and custom_id=@CustFldId and clnt_id=@ClntId"

objDb.addParameter("@OrgId", SqlDbType.VarChar, 25, strOrgId, DB.sql.Validator.Yes)

objDb.addParameter("@CustFldId", SqlDbType.VarChar, 50, strCustFldId, DB.sql.Validator.Yes)
                    
objDb.addParameter("@ClntId", SqlDbType.VarChar, 50, i_strClntId, DB.sql.Validator.Yes)

                    
If objDb.ExecScalarSql(strSql, DB.sql.sqlType.text) > 0 Or objDb.ExecScalarSql(strSql1, DB.sql.sqlType.text) = 0 Then
                  Return False
End If

I am using a custom made addParameter function for adding the parameters. In second SQL statement i am getting the error. Can any one plz help ???