[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

if no matches, then

Posted on 2006-03-28
37
Medium Priority
?
384 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:net_susan
  • 21
  • 14
  • +1
37 Comments
 
LVL 25

Accepted Solution

by:
nauman_ahmed earned 880 total points
ID: 16315841
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
 
LVL 25

Assisted Solution

by:nauman_ahmed
nauman_ahmed earned 880 total points
ID: 16315856
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
 
LVL 12

Assisted Solution

by:craskin
craskin earned 720 total points
ID: 16316170
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:net_susan
ID: 16316413
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
 
LVL 1

Author Comment

by:net_susan
ID: 16316425
Nauman,

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

Author Comment

by:net_susan
ID: 16316434
Craskin,

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

Thanks!

Susan
0
 
LVL 25

Expert Comment

by:nauman_ahmed
ID: 16317038
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
 
LVL 26

Assisted Solution

by:Rejojohny
Rejojohny earned 400 total points
ID: 16318336
>>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
 
LVL 1

Author Comment

by:net_susan
ID: 16322518
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
 
LVL 25

Assisted Solution

by:nauman_ahmed
nauman_ahmed earned 880 total points
ID: 16322653
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
 
LVL 25

Expert Comment

by:nauman_ahmed
ID: 16322662
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
 
LVL 1

Author Comment

by:net_susan
ID: 16323034
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
 
LVL 25

Expert Comment

by:nauman_ahmed
ID: 16323114
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
 
LVL 25

Expert Comment

by:nauman_ahmed
ID: 16323144
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
 
LVL 1

Author Comment

by:net_susan
ID: 16323157
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
 
LVL 25

Expert Comment

by:nauman_ahmed
ID: 16323340
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
 
LVL 1

Author Comment

by:net_susan
ID: 16323635
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
 
LVL 25

Assisted Solution

by:nauman_ahmed
nauman_ahmed earned 880 total points
ID: 16323693
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
 
LVL 1

Author Comment

by:net_susan
ID: 16323870
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
 
LVL 25

Assisted Solution

by:nauman_ahmed
nauman_ahmed earned 880 total points
ID: 16324095
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
 
LVL 1

Author Comment

by:net_susan
ID: 16324538
Why did you take away my data reader?

This code says:

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

Author Comment

by:net_susan
ID: 16324584
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
 
LVL 25

Assisted Solution

by:nauman_ahmed
nauman_ahmed earned 880 total points
ID: 16324978
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
 
LVL 1

Author Comment

by:net_susan
ID: 16325146
Ok, I'll try the new code.

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

Yes.
0
 
LVL 1

Author Comment

by:net_susan
ID: 16325199
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
 
LVL 1

Author Comment

by:net_susan
ID: 16325206
so, it failed on this line: da.Fill(dt)
0
 
LVL 1

Author Comment

by:net_susan
ID: 16325227
If I just enter whatever, it fails on the same line:

Invalid column name 'sdfdsf'.
0
 
LVL 25

Assisted Solution

by:nauman_ahmed
nauman_ahmed earned 880 total points
ID: 16325502
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
 
LVL 1

Author Comment

by:net_susan
ID: 16325976
>>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
 
LVL 1

Author Comment

by:net_susan
ID: 16326009
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
 
LVL 25

Expert Comment

by:nauman_ahmed
ID: 16326524
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
 
LVL 1

Author Comment

by:net_susan
ID: 16326724
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
 
LVL 25

Expert Comment

by:nauman_ahmed
ID: 16326845
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
 
LVL 1

Author Comment

by:net_susan
ID: 16327011
I did it exactly like that
300, 300, 300.
0
 
LVL 1

Author Comment

by:net_susan
ID: 16327101
I've got it, though. I'll post back.
0
 
LVL 1

Author Comment

by:net_susan
ID: 16327187
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
 
LVL 1

Author Comment

by:net_susan
ID: 16327192
Anyway, it works now. I still need a data reader, but I'll post a new question. Thanks!
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In an ASP.NET application, I faced some technical problems. In this article, I list them out and show the solutions that I found.  I hope it will be useful. Problem: After closing a pop-up window, the parent page should be refreshed automaticall…
IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question