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
LVL 1
net_susanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nauman_ahmedCommented:
You need to do that in a stored procedure like this:

REATE PROCEDURE spVerifyLogin
@EMAIL_ADDRESS AS VARCHAR(255),
@PASSWORD  AS VARCHAR(255)
AS
IF (SELECT ACCOUNT_ID FROM FP_ACCOUNT WHERE EMAIL_ADDRESS=@EMAIL_ADDRESS) = NULL
 RETURN 100
ELSE
  IF (SELECT ACCOUNT_ID  FROM FP_ACCOUNT WHERE EMAIL_ADDRESS=@EMAIL_ADDRESS AND PASSWORD=@PASSWORD) = NULL
    RETURN 200
  ELSE
   RETURN 300
GO

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

This stored procedure will return 100 when e-mail address is wrong, will return 200 when password is wrong and will return 300 when both of the values are correct.

-Nauman.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
nauman_ahmedCommented:
First line of sp shold be:

CREATE PROCEDURE spVerifyLogin

Another way for this is to run the SQL query first for E-Mail Address match and if email address is valid, match both e-mail address and password.

-Nauman.
0
craskinCommented:
Private Sub verifycus()
Dim objConn As New SqlConnection("Server=myhost; Initial Catalog=mydb; User ID=myid; Password=mypw")


Dim VEemail As String = Request.QueryString("VEemail")
Dim VPassword As String = Request.QueryString("VPassword")
Dim da As New SqlDataAdapter("SELECT * FROM CustomerInfo WHERE Email= " & VEemail & " AND VGPassword = " & Vpassword, objconn)

dim dt as New DataTable
da.Fill(dt)
If dt.Rows(0).Items(0) Is Nothing Then
    Response.Write("Username or password incorrect")
Else
    Response.Write(dt.Rows(0).Items("Email").ToString)
 End Sub

try something like that
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

net_susanAuthor Commented:
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.
0
net_susanAuthor Commented:
Nauman,

Also, how could I make that solution write "Your email does not match".
0
net_susanAuthor Commented:
Craskin,

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

Thanks!

Susan
0
nauman_ahmedCommented:
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.
0
RejojohnyCommented:
>>The = NULL part isn't quite right

instead of
IF (SELECT ACCOUNT_ID FROM FP_ACCOUNT WHERE EMAIL_ADDRESS=@EMAIL_ADDRESS) = NULL

try
IF exists (SELECT ACCOUNT_ID FROM FP_ACCOUNT WHERE EMAIL_ADDRESS=@EMAIL_ADDRESS)

same for the other SQL too ...
0
net_susanAuthor Commented:
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)
0
nauman_ahmedCommented:
You pass the parameters like this:

Dim cmd As New SqlCommand("VerifyLogin",objConn)
Dim retValue As Int32
cmd.CommanDType = CommandType.StoredProcedure
cmd.Parameters.Add("@EMAIL_ADDRESS", SqlDbType.Varchar, 255).Value = Request.QueryString("VEemail")
cmd.Parameters.Add("@PASSWORD", SqlDbType.Varchar, 255).Value = Request.QueryString("VPassword")

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

-Nauman.
0
nauman_ahmedCommented:
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.
0
net_susanAuthor Commented:
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
0
nauman_ahmedCommented:
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.
0
nauman_ahmedCommented:
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.
0
net_susanAuthor Commented:
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)
0
nauman_ahmedCommented:
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.
0
net_susanAuthor Commented:
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
0
nauman_ahmedCommented:
susan,

My bad, I tested the Sp but didnt test it in ASPX. One update in code :$:

The stored procedure is returning  value, to retrieve the value we have to add one more paramter to the SqlCommand object like this:

Dim retParam As New SqlParameter("@RETURN_VALUE", SqlDbType.Int)
retParam.Direction = ParameterDirection.ReturnValue
cmd.Parameters.Add(retParam)
cmd.ExecuteScalar()
retValue = Cint(cmd.Parameters("@RETURN_VALUE").Value))

The ExecuteScalar method execute an SQL query or sp and return the no of rows effected or value from sp. Here is the code that I used for testing:

Dim retValue As Int32
        Dim conn As New SqlConnection("packet size=4096;user id=user1;data source=localhost;persist security info=True;initial catalog=MY_DB;password=pass")
        conn.Open()
        Dim cmd As New SqlCommand("spVerifyLogin", conn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add("@EMAIL_ADDRESS", SqlDbType.VarChar, 255).Value = "jdoe@jdoe.com"
        cmd.Parameters.Add("@PASSWORD", SqlDbType.VarChar, 255).Value = "jdoe"

        Dim retParam As New SqlParameter("@RETURN_VALUE", SqlDbType.Int)
        retParam.Direction = ParameterDirection.ReturnValue
        cmd.Parameters.Add(retParam)
        cmd.ExecuteScalar()


        Response.Write(cmd.Parameters("@RETURN_VALUE").Value)

       

        cmd = Nothing
        conn.Close()
        conn = Nothing

-Nauman.
0
net_susanAuthor Commented:
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
0
nauman_ahmedCommented:
Susan,

Your SP is correct no changes needed. Your updated VB.NET code is given below:

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")

'Add a parameter to SQL Command to retrieve the value from stored procedure
Dim retParam As New SqlParameter("@RETURN_VALUE", SqlDbType.Int)
retParam.Direction = ParameterDirection.ReturnValue
cmd.Parameters.Add(retParam)

'Execute the SP
cmd.ExecuteScalar())

retValue = Cint(Cint(cmd.Parameters("@RETURN_VALUE").Value))
response.write (retValue)
If retValue = 100 then
 'Invalid user
else if retValue = 200 then
Response.Write("your password is not correct")
else if retValue = 300 then

'User is valid get the user detail
Dim da As New SqlDataAdapter("SELECT * FROM CustomerInfo WHERE Email= " & VEemail, objconn)

dim dt as New DataTable
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)("FName").ToString())

end if

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

finally
               objConn.Close()
               objConn.Dispose()
      end try
End Sub


Please post the exact statement that is causing confusion :)

-Nauman.
0
net_susanAuthor Commented:
Why did you take away my data reader?

This code says:

BC30456: 'Tables' is not a member of 'System.Data.DataTable'.
0
net_susanAuthor Commented:
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
0
nauman_ahmedCommented:
woops:

Response.Write(dt.Tables(0).Rows(0)("FirstName").ToString())

should be

Response.Write(dt.Rows(0)("FirstName").ToString())


>>Why did you take away my data reader?
I have to slightly modify your code since the Stored Procedure is returning only one value and not the whole resultset. Once we are sure the return value is 300 i.e. e-mail address and password are valid, then we need to retrieve the user information such as Name etc from another query.

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

-Nauman.
0
net_susanAuthor Commented:
Ok, I'll try the new code.

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

Yes.
0
net_susanAuthor Commented:
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.
0
net_susanAuthor Commented:
so, it failed on this line: da.Fill(dt)
0
net_susanAuthor Commented:
If I just enter whatever, it fails on the same line:

Invalid column name 'sdfdsf'.
0
nauman_ahmedCommented:
You need to put single quotes:

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

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

>>Yes.

Did you run the SP in Query Analyzer?

Also Can you tell me why you want to show specifically if the e-mail address is not correct or password? In standards practice we just match the e-mail address and password and if no record is return the following message is displayed:

"Either your e-mail address is wrong or password is not correct. Please try again."

I will recommnd this approach since it is more secure and it will make spoofing harder.

-Nauman.


0
net_susanAuthor Commented:
>>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
0
net_susanAuthor Commented:
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.
0
nauman_ahmedCommented:
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.
0
net_susanAuthor Commented:
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.
0
nauman_ahmedCommented:
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.
0
net_susanAuthor Commented:
I did it exactly like that
300, 300, 300.
0
net_susanAuthor Commented:
I've got it, though. I'll post back.
0
net_susanAuthor Commented:
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
0
net_susanAuthor Commented:
Anyway, it works now. I still need a data reader, but I'll post a new question. Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.