Start Free Trial

asked on

if no matches, then

How can I do

if Email != @VEemail
then, tell user "this email is not registered"

else if
VGPassword != @Vpassword
then, "the password is wrong"

in the below code?

*************************************************************************

Private Sub verifycus()
Dim objConn As New SqlConnection("Server=myhost; Initial Catalog=mydb; User ID=myid; Password=mypw")
objConn.Open()
try
Dim VEemail As String = Request.QueryString("VEemail")
Dim VPassword As String = Request.QueryString("VPassword")
Dim cmd As New SqlCommand("SELECT * FROM CustomerInfo WHERE Email= @VEemail AND VGPassword = @Vpassword", objconn)
cmd.Parameters.Add(New SqlParameter("@email", VEemail))
cmd.Parameters.Add(New SqlParameter("@VGpassword", VPassword))
Dim dr As SqlDataReader
dr = cmd.ExecuteReader()
While dr.Read
Response.Write(dr.Item("Email"))
End While
dr.Close
finally
objConn.Close()
objConn.Dispose()
end try
End Sub

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Nauman,

The = NULL part isn't quite right

It says:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

ASKER

Nauman,

Also, how could I make that solution write "Your email does not match".

ASKER

Craskin,

Would that create a new table every time? Would the old table be overwritten?

Thanks!

Susan

Susan,

You can only select one column in that query and you will need to call SqlCommand.ExecuteScalar() that will return the value from this stored procedure e.g:

Dim retValue As Integer
retValue = cmd.ExecuteScalar()

If retValue = 100 then
Response.Write("Your e-mail address does not match.")
else if retValue = 200 then
Response.Write("your password is not correct")
else if retValue = 300 then
'Valid user
end if

To learn more about returning values from stored proc. please read the following article:

Return Value from Stored Procedure using .NET
http://www.aspfree.com/c/a/ASP.NET-Code/Return-Value-from-Stored-Procedure-using-NET/

-Nauman.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Thanks, ReJoJohny.

Nauman,

I don't understand how to get my QueryString results to send to the SP?

Dim cmd As New SqlCommand("VerifyLogin", objconn)

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Following is a good VB.NET example on returning value from SP:

Return Value from Stored Procedure using .NET
http://www.aspfree.com/c/a/ASP.NET-Code/Return-Value-from-Stored-Procedure-using-NET/

-Nauman.

ASKER

Thank you. I read the article, but my retValue = 0. I must have missed some little thing.

My code:

Private Sub verify ()
Dim objConn As New SqlConnection("Server=mystring")
objConn.Open()
try
Dim cmd As New SqlCommand("VerifyCus" , objconn)
cmd.CommandType = CommandType.StoredProcedure
Dim retValue As Integer
cmd.Parameters.Add("@VEmail", SqlDbType.Varchar, 255).Value = Request.QueryString("VEmail")
cmd.Parameters.Add("@VPassword", SqlDbType.Varchar, 255).Value = Request.QueryString("VPassword")

retValue = Cint(cmd.ExecuteScalar())
response.write (retValue)
If retValue = 100 then
'Valid user
else if retValue = 200 then
Response.Write("your password is not correct")
else if retValue = 300 then
Response.Write("Your e-mail address does not match.")
end if

Dim dr As SqlDataReader
dr = cmd.ExecuteReader()
While dr.Read
Response.Write(dr.Item("FName"))
End While
dr.Close
finally
objConn.Close()
objConn.Dispose()
end try
End Sub

************************************************

CREATE PROCEDURE VerifyCus
@VEmail AS VARCHAR(255),
@VPassword AS VARCHAR(255)
AS
IF exists(SELECT * FROM CustomerInfo WHERE Email= @VEmail AND CPassword = @VPassword)
RETURN 100
ELSE IF exists(SELECT * FROM CustomerInfo WHERE Email= @VEmail)
RETURN 200
ELSE

Susan,

Please use the following sp:

IF (SELECT CustomerID FROM CustomerInfo WHERE Email= @VEmail AND CPassword = @VPassword) = NULL
RETURN 100
ELSE IF (SELECT CustomerID FROM CustomerInfo WHERE Email= @VEmail) = Null
RETURN 200
ELSE
RETURN 300

CustomerID is the ID field in CustomerInfo table.

-Nauman.

Susan,

You need to change the stored procedure: first we should verify the e-mail address and then we should verify both the e-mail address and password to confirm if password is valid for a valid e-mail address:

IF (SELECT CustomerID FROM CustomerInfo WHERE Email= @VEmail) = NULL
RETURN 100
ELSE IF (SELECT CustomerID FROM CustomerInfo WHERE Email= @VEmail AND CPassword = @VPassword) = Null
RETURN 200
ELSE
RETURN 300

-Nauman.

ASKER

That SP didn't work (see above). I had to change
this
(SELECT CustomerID FROM CustomerInfo WHERE Email= @VEmail AND CPassword = @VPassword) = NULL
to
IF exists (SELECT CustomerID FROM CustomerInfo WHERE Email= @VEmail AND CPassword = @VPassword)

Susan, I have tested a variant of this stored procedure and it is working perfectly. Lets test it :)

1. Create a new procedure, call it VerifyLogin, and add the following to it:

CREATE PROCEDURE spVerifyLogin
@VEmail AS VARCHAR(255),
@VPassword AS VARCHAR(255)

AS
IF (SELECT CustomerID FROM CustomerInfo WHERE Email=@VEmail) = NULL
RETURN 100
ELSE
IF (SELECT CustomerID FROM CustomerInfo WHERE Email=@VEmail AND CPassword=@VPassword) = NULL
RETURN 200
ELSE
RETURN 300
GO

2. Open SQL Server Enterprise Manager. Select your database. Select Tools -> SQL Query Analyzer from the menu.

3. Type the following:

DECLARE @RETURN_STATUS INT
EXEC @RETURN_STATUS = Verifylogin 'jdoe@jdoe.com','jdoe'
SELECT 'RETURN_STATUS' = @RETURN_STATUS

Please test it with valid and invalid e-mail address and password and hit F5 to execute the procedure. Please let me know what value is retrieved in RETURN_STATUS.

-Nauman.

ASKER

What is ExecuteScalar?

retValue = Cint(cmd.ExecuteScalar())

If retValue = 100 then
Response.Write("Your e-mail address does not match.")
else if retValue = 200 then
Response.Write("your password is not correct")
else if retValue = 300 then
'Valid user
end if

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

I'm just confused beyond recovery at this point. :) Please show me what you mean in the below code.

Private Sub verify ()
Dim objConn As New SqlConnection("Server=mystring")
objConn.Open()
try
Dim cmd As New SqlCommand("VerifyCus" , objconn)
cmd.CommandType = CommandType.StoredProcedure
Dim retValue As Integer
cmd.Parameters.Add("@VEmail", SqlDbType.Varchar, 255).Value = Request.QueryString("VEmail")
cmd.Parameters.Add("@VPassword", SqlDbType.Varchar, 255).Value = Request.QueryString("VPassword")

retValue = Cint(cmd.ExecuteScalar())
response.write (retValue)
If retValue = 100 then
'Valid user
else if retValue = 200 then
Response.Write("your password is not correct")
else if retValue = 300 then
Response.Write("Your e-mail address does not match.")
end if

Dim dr As SqlDataReader
dr = cmd.ExecuteReader()
While dr.Read
Response.Write(dr.Item("FName"))
End While
dr.Close
finally
objConn.Close()
objConn.Dispose()
end try
End Sub

****************************************************************

CREATE PROCEDURE VerifyCus
@VEmail AS VARCHAR(255),
@VPassword AS VARCHAR(255)
AS

IF (SELECT CustomerID FROM CustomerInfo WHERE Email= @VEmail) = NULL
RETURN 100
ELSE
IF (SELECT CustomerID FROM CustomerInfo WHERE Email= @VEmail AND CPassword = @VPassword) = NULL
RETURN 200
ELSE
RETURN 300
GO

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Why did you take away my data reader?

This code says:

BC30456: 'Tables' is not a member of 'System.Data.DataTable'.

ASKER

When I comment out these lines:

'da.Fill(dt)

'Get user name, the above query will return only one row if Email address is unique
' Response.Write(dt.Tables(0).Rows(0)("FirstName").ToString())

'catch Ex As Exception
' Response.Write ex.ToString()

my result is always 300

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Ok, I'll try the new code.

>>Does it give 300 even when you input a record that does not exist?

Yes.

ASKER

It's cutting off the .com part of my email (the .com part does show up in the querystring in my http address).

The column prefix 'myemail@gmail' does not match with a table name or alias name used in the query.

ASKER

so, it failed on this line: da.Fill(dt)

ASKER

If I just enter whatever, it fails on the same line:

Invalid column name 'sdfdsf'.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

>>Did you run the SP in Query Analyzer?

I did. How I didn't notice it only returns 300 is beyond me, but I just tried it again (valid and invalid) and it only returns 300.

This seems to work:

CREATE PROCEDURE VerifyCus
@VEmail AS VARCHAR(255),
@VPassword AS VARCHAR(255)
AS

IF exists (SELECT CustomerID FROM CustomerInfo WHERE Email= @VEmail AND CPassword = @VPassword)
RETURN 100
IF exists (SELECT CustomerID FROM CustomerInfo WHERE Email= @VEmail)
RETURN 200
ELSE
RETURN 300
GO

ASKER

So, that works, but it only display 100, or 200your password is not correct
and no other text.

Where there should be 300 it says:

There is no row at position 0.

Susan,

Why did you change the stored procedure to use EXIST in the SQL query? Please use the following SP. I have tested it again and it should work according to the sceanrio in the code:

CREATE PROCEDURE VerifyCus
@VEmail AS VARCHAR(255),
@VPassword AS VARCHAR(255)
AS

IF (SELECT CustomerID FROM CustomerInfo WHERE Email= @VEmail) = NULL
RETURN 100
IF (SELECT CustomerID FROM CustomerInfo WHERE Email= @VEmail AND CPassword = @VPassword) = NULL
RETURN 200
ELSE
RETURN 300
GO

This procedure will only return 300 when there is a valid customer and the query in

Dim da As New SqlDataAdapter("SELECT * FROM CustomerInfo WHERE Email= '" & VEemail & "'", objconn)

will have some value. You are recieving There is no row at position 0 error since the stored procedure you have used is not correct. Please update the stored procedure and try the code again.

-Nauman.

ASKER

I changed it because that SP wasn't working. :(

I changed it back, here are the results:

with a valid email and password:

300Susan

with a valid email and bad password:

300Susan

with a bad email:

There is no row at position 0.

Susan,

I have created the table "CustomerInfo" in my SQL Server database. This table has customerid=1, Email="jdoe@jdoe.com" CPassword='jdoe'

I ran the stored procedure:

Valid E-Mail Password
-------------------------------
DECLARE @RETURN_STATUS INT
EXEC @RETURN_STATUS = VerifyCus 'jdoe@jdoe.com','jdoe'
SELECT 'RETURN_STATUS' = @RETURN_STATUS

RETURN_STATUS = 300

Valid E-Mail Invalid Password
-------------------------------
DECLARE @RETURN_STATUS INT
EXEC @RETURN_STATUS = VerifyCus 'jdoe@jdoe.com','jdoe1'
SELECT 'RETURN_STATUS' = @RETURN_STATUS

RETURN_STATUS = 200

InValid E-Mail Invalid Password
-------------------------------
DECLARE @RETURN_STATUS INT
EXEC @RETURN_STATUS = VerifyCus 'jdoe@jdoe1.com','jdoe1'
SELECT 'RETURN_STATUS' = @RETURN_STATUS

RETURN_STATUS = 100

Please run the same test on your system and let me know the result.

-Nauman.

ASKER

I did it exactly like that
300, 300, 300.

ASKER

I've got it, though. I'll post back.

ASKER

I tried what you posted line for line, but I can only get it to work like this:

CREATE PROCEDURE VerifyCus
@VEmail AS VARCHAR(255),
@VPassword AS VARCHAR(255)
AS

IF exists (SELECT * FROM CustomerInfo WHERE Email= @VEmail AND CPassword = @VPassword)
RETURN 100
else IF exists (SELECT * FROM CustomerInfo WHERE Email= @VEmail)
RETURN 200
ELSE
RETURN 300
GO

ASKER

Anyway, it works now. I still need a data reader, but I'll post a new question. Thanks!