executereader not retruning correct value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

executereader not retruning correct value

Author

Topic

stevenandler
Starting Member

42 Posts

Posted - 2012-09-13 : 14:57:06

I am running into a problem with one of my stored procedures not returning the correct value. When running the stored procedure from t-SQL when I enter a valid login and password, the CNT variable returns a "1" if the login and password don't match the stored procedure returns a 0. for Some Reason my ASP page always reads the CNT as "1".

Here is my stored procedure:

USE [ADLWEB]
GO
/****** Object: StoredProcedure [OGEN].[CAN_LOGIN] Script Date: 09/13/2012 14:50:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [OGEN].[CAN_LOGIN](
@P_USER_ID CHAR(8),
@P_PASSWORD CHAR(14))
AS
BEGIN
SELECT CAST(COUNT(*) AS NUMERIC(9)) AS CNT, OGEN.NEED_RESET_PWD(@P_USER_ID) AS NEED_RESET
FROM OGEN.GEN_M_USER
WHERE USER_ID = UPPER(@P_USER_ID)
AND LOGON_PASSWORD = UPPER(@P_PASSWORD)
AND STATUS = 1;
END;

Here is my code that calls and reads the return from the stored procedure:
protected void txtPassword_TextChanged(object sender, EventArgs e)
{
using (SqlConnection cnn = new SqlConnection("Data Source=RAY-PC;Database=ADLWEB;Integrated Security=SSPI"))
{
SqlDataReader rdr;
cnn.Open();
SqlCommand cmd = new SqlCommand("OGEN.CAN_LOGIN", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@P_USER_ID", txtUser.Text);
cmd.Parameters.AddWithValue("@P_PASSWORD", txtUser.Text);
rdr = cmd.ExecuteReader();
string results = "";
while (rdr.Read())
{
// this always returns a 1 why???
results = rdr["CNT"].ToString();
txtLogonError.Text = results;
}
rdr.Close();
}
}

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-13 : 15:06:32

Just by reading through your code, nothing jumps out. Although I don't expect that either of these will resolve the issue, you might try the following:

ALTER PROCEDURE [OGEN].[CAN_LOGIN](
@P_USER_ID CHAR(8),
@P_PASSWORD CHAR(14)) 
AS
SET NOCOUNT ON;
BEGIN
SELECT CAST(COUNT(*) AS NUMERIC(9)) AS CNT, OGEN.NEED_RESET_PWD(@P_USER_ID) AS 
....

And also

while (rdr.Read())
{
// this always returns a 1 why??? 
results = rdr["CNT"].ToString();
txtLogonError.Text = results;
break;
}
rdr.Close();

chadmat
The Chadinator

1974 Posts

Posted - 2012-09-13 : 15:07:46

You are passing in txtUser.Text for both parameters

-Chad

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-13 : 15:12:02

quote:
Originally posted by chadmat

You are passing in txtUser.Text for both parameters

-Chad

[LIKE] Very good eye, chadmat!!

stevenandler
Starting Member

42 Posts

Posted - 2012-09-13 : 15:24:04

quote:
Originally posted by chadmat

You are passing in txtUser.Text for both parameters

-Chad

stevenandler
Starting Member

42 Posts

Posted - 2012-09-13 : 15:26:55

Thank you Chad That was the problem! the second paramter was suppose to be txtPassword.text. This somewhat begs the question why I was always receiving 1 instead of a 0 but I already spent too much time on this silly thing.

Thanks again

chadmat
The Chadinator

1974 Posts

Posted - 2012-09-13 : 16:01:02

You are welcome!

-Chad

chadmat
The Chadinator

1974 Posts

Posted - 2012-09-13 : 16:01:47

quote:
Originally posted by sunitabeck

quote:
Originally posted by chadmat

You are passing in txtUser.Text for both parameters

-Chad
[LIKE] Very good eye, chadmat!!

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources