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?
