ExecuteScalar?

[rn5a]

A class file has the following code:

Imports System
Imports System.Data
Imports System.Data.SqlClient

Namespace LoginUsers
Public Class UserValidation
Public Function Validate(ByVal UserName As String, ByVal
Password As String) As Integer
Dim iID As Integer
Dim sqlCmd As SqlCommand
Dim sqlConn As SqlConnection

sqlConn = New SqlConnection(".......")
sqlCmd = New SqlCommand("LoginUser", sqlConn)
sqlCmd.CommandType = CommandType.StoredProcedure

With sqlCmd
.Parameters.Add("@UserID", SqlDbType.Int, 4).Direction
= ParameterDirection.ReturnValue
.Parameters.Add("@UserName", SqlDbType.VarChar,
50).Value = UserName
.Parameters.Add("@Password", SqlDbType.VarChar,
50).Value = Password
End With

sqlConn.Open()
iID = CType(sqlCmd.ExecuteScalar, Integer)
sqlConn.Close()

Return iID
End Function
End Class
End Namespace

Using vbc, I compiled the above into a DLL named LoginUsers.dll. This
is the simple stored procedure:

ALTER PROCEDURE dbo.NETLoginUser
@UserName varchar(50),
@Password varchar(50)
AS
DECLARE
@ID int

IF EXISTS(SELECT UserID FROM Users WHERE UserName = @UserName AND
Password = @Password)
BEGIN
SET @ID = (SELECT UserID FROM Users WHERE UserName = @UserName
AND Password = @Password)
END
ELSE
BEGIN
SET @ID = 0
END
RETURN @ID

Finally this is the ASPX page:

<%@ Import Namespace="LoginUsers" %>

<script runat="server">
Sub LoginUser(ByVal obj As Object, ByVal ea As EventArgs)
Dim boUserValidation As UserValidation
Dim iUID As Integer

boUserValidation = New UserValidation

iUID = boUserValidation.Validate(txtUserName.Text,
txtPassword.Text)
Response.Write(iUID)
</script>
<form runat="server">
<asp:TextBox ID="txtUserName" runat="server"/>
<asp:TextBox ID="txtPassword" TextMode="password" runat="server"/>
<asp:Button ID="btnSubmit" OnClick="LoginUser" runat="server"/>
</form>

Asuume that one of the records in the DB table named Users has the
UserName & Password value as "ron" & "nor" respectively (both without
the quotes). Assume that the UserID of this user is 10.

When I run the above ASPX page & enter the UserName & Password as "ron"
& "nor", then the Response.Write(iUID) says 0 where as it should be 10.
Why?

If I replace ExecuteScalar in the class file (the first code snippet)
with this:

sqlCmd.ExecuteNonQuery()
iID = CInt(sqlCmd.Parameters(0).Value)

then Response.Write(iUID) correctly shows the ID value as 10 but using
ExecuteScalar shows the ID value as 0! Why so?

 

[Mohamed Mosalem]

Hi,
The value is not returned in the ExecueteScalar function because you are not
selecting the @ID in the stored procedure, so you have to change the last
statement in the stored procedure to select the @ID instead of returing it,
as follows
ALTER PROCEDURE dbo.NETLoginUser
@UserName varchar(50),
@Password varchar(50)
AS
DECLARE
@ID int

IF EXISTS(SELECT UserID FROM Users WHERE UserName = @UserName AND
Password = @Password)
BEGIN
SET @ID = (SELECT UserID FROM Users WHERE UserName = @UserName
AND Password = @Password)
END
ELSE
BEGIN
SET @ID = 0
END
Select @ID