Transfering login information between ASP.netand SQLServer2008

Question

0.00/5 (No votes)

See more:

hi my stored procedure is:

SQL

GO
/****** Object:  StoredProcedure [dbo].[SelectUserPassExist]    Script Date: 01/26/2015 12:28:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<author,,name>
-- Create date: <create>
-- Description:	<description,,>
-- =============================================
ALTER PROCEDURE [dbo].[SelectUserPassExist] 
	@Usr nvarchar,
	@Pass nvarchar
AS
BEGIN

if not exists( select [UserName],[Passcode] from Users where ([UserName]=@Usr and [Passcode]=@Pass))
   Begin
    return -3
    end
   else
         Begin
              return 2
          end
end

And my Log in Button code is:

C#

protected void Button1_Click(object sender, EventArgs e)
    {
        int userExists;
    //    string strUserExists,strUser;
        //SelectFullName objSelectFullName = new SelectFullName();
        //string strUserName;
        //DataRow drFullname;
        //drFullname = objSelectFullName.SelectName(txtUsername.Text, txtPassword.Text);
        //strUserName = drFullname.ToString();
        userExists= CheckUserPass(txtUsername.Text,txtPassword.Text);
        //strUser= DRow["Username"].ToString();
        if (userExists == -3)
        {
            lblMsg.Text = "Username or Password is wrong.";
               }
        else
        {
            {
                Session["Usr"] = txtUsername.Text;
                FormsAuthentication.RedirectFromLoginPage(txtUsername.Text, chkRememberMe.Checked);
         
            }
        }   
    }
    private int CheckUserPass(string strUsr,string strPass)
    {
        int retVal;
        string strConnectionString = ConfigurationManager.ConnectionStrings["GarnetWebsiteConnectionString"].ConnectionString;
        SqlConnection con = new SqlConnection(strConnectionString);
        SqlCommand com = new SqlCommand();
        com.Connection = con;
        com.CommandType = CommandType.StoredProcedure;
        
        com.CommandText = "SelectUserPassExist";
       
        SqlParameter retParam = new SqlParameter("@Return", SqlDbType.Int);
        retParam.Direction = ParameterDirection.ReturnValue;
        com.Parameters.Add(retParam);
        com.Parameters.AddWithValue("@Usr", strUsr);
        com.Parameters.AddWithValue("@Pass", strPass);
        con.Open();
        int intRows = com.ExecuteNonQuery();
        retVal = (int)com.Parameters["@Return"].Value;
        con.Close();
        return retVal;
}

Every time i enter my username and password and I click on log in button I receive the return value -3 even if my username and password has properly entered please help me , thank you.

Posted 25-Jan-15 22:12pm

maysam_p82

Updated 25-Jan-15 22:25pm

Kornfeld Eliyahu Peter

v3

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2015-01-25T22:44:00

Never store passwords in clear text - it is a major security risk. There is some information on how to do it here: Password Storage: How to do it.[^]

And what you should be doing is using ExecuteScalar instead of ExecuteNonQuery, and looking at the return value it presents:

C#

retVal = com.ExecuteScalar();

sashje- · Answer 2 · 2015-01-25T22:48:00

My solution is to alter your stored procedure..

ALTER PROCEDURE [dbo].[SelectUserPassExist] 
	@Usr varchar,
	@Pass varchar
AS
BEGIN
 Print @Usr -- Will only prompt first character
 Print @Pass -- Will only prompt first character
if not exists( select FirstName,LastName from [User] where (FirstName=@Usr and LastName=@Pass))
   Begin   
    return -3
    end
   else
         Begin         
              return 2
          end
end

Alter to f.ex

ALTER PROCEDURE [dbo].[SelectUserPassExist] 
	@Usr varchar(max), -- To allow more than one character
	@Pass varchar(max)  -- To allow more than one character
if not exists( select FirstName,LastName from [User] where (FirstName=@Usr and LastName=@Pass))
   Begin   
    return -3
    end
   else
         Begin         
              return 2
          end
end

maysam_p82 · Answer 3 · 2015-01-26T01:27:00

Solution 3

I changed the code and I swapped "Executenonquery()" whith "ExecuteScalar()" method,now in every situation I receive"0" from the Stored Procedure , I think you didnot understand my problem. In fact I dont know that why the return value from stored procedure always is vice versa!(ofcourse with executenonquery() method). If I enter usr and pass properly I receive the return value with value "-3",means wrong password. I dont know where is the problem occurred :(. In stored procedure or in codebehind?
I think somewher of mycode has a little problem,because I dont properly understood the mechanism of how C# receive return values from sql server stored procedures.

Posted 26-Jan-15 1:27am

maysam_p82

Updated 26-Jan-15 1:32am

v2

Comments

sashje- 27-Jan-15 3:15am

Check my solution.. Solution 2.. i've tested it myself.. you need to specify nvarchar(max) in you procedure, or you will only get the first character(nvarchar = 1 character, nvarchar(max) = String

maysam_p82 3-Feb-15 5:00am

I changed the code with nvarchar(max) but now stored procedure returns the value "0" instead "-3" or if exists "2" but doesn't do that!

maysam_p82 3-Feb-15 5:11am

So you know I want that check the username and password user entered, store username in a session and redirect the user to a specific page , and check weather username and password exist in the database or not please insert a sample that I find how could I do that myself.

maysam_p82 3-Feb-15 5:13am

Is my code behind is error free? I think I've made some mistake in my code would you check it?

maysam_p82 3-Feb-15 9:26am

This is my solution:
storedprocedure:
USE [D:\PROGRAM FILES\MAYSAM\CSHARP PROJECTS\TRAINING\GARNETGALLERYCOLLECTION\APP_DATA\GARNETGALLERYWEBSITE.MDF]
GO
/****** Object: StoredProcedure [dbo].[SelectUserPassExist] Script Date: 02/03/2015 17:10:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <author,,name>
-- Create date: <create date,,="">
-- Description: <description,,>
-- =============================================
ALTER PROCEDURE [dbo].[SelectUserPassExist]
@Usr nvarchar(max),
@Pass nvarchar(max),
@Results int Output
AS
BEGIN
set @Results=(select count(*)from Users where ([UserName]=@Usr and [Passcode]=@Pass))
end

and my codebehind:

protected void Button1_Click(object sender, EventArgs e)
{
int intResult;

if ((intResult >1)||(intResult<1))
 {
lblMsg.Text = "Invalid Username or Password.";
}
else
{
{
Session["Usr"] = txtUsername.Text;
FormsAuthentication.RedirectFromLoginPage(txtUsername.Text, chkRememberMe.Checked);

}
}
}
private int CheckUserPass(string strUsr, string strPass)
{
int retVal;
string strConnectionString = ConfigurationManager.ConnectionStrings["GarnetWebsiteConnectionString"].ConnectionString;
SqlConnection con = new SqlConnection(strConnectionString);
SqlCommand com = new SqlCommand();
com.Connection = con;
com.CommandType = CommandType.StoredProcedure;
com.CommandText = "SelectUserPassExist";

com.Parameters.Add("@Results", SqlDbType.Int, 4);
com.Parameters.AddWithValue("@Usr", strUsr);
com.Parameters.AddWithValue("@Pass", strPass);
com.Parameters["@Results"].Direction = ParameterDirection.Output;

con.Open();
com.ExecuteNonQuery();
retVal = (int)com.Parameters["@Results"].Value;

con.Close();

return retVal;