Use datareader to get a value for UserNo

[Assimalyst]

Hi,

I'm relatively new to ADO, so forgive me if I am going about this in
the wrong way.

I have a login.aspx page, with a textbox 'usernameTxtBx'

I would like to extract a value from a single table in an SQL database.
The database has two columns of interest usrNo and usrName, they are in
the same table.

I would like to set the value from usrNo as an int where the value in
the usrName column matches that entered in the usernameTxtBx.

I have attempted to use a datareader in c#. Here's the relevent code:

// Declare conn from Web.Config
SqlConnection conn = new
SqlConnection(ConfigurationSettings.AppSettings["strConn"]);
conn.Open();

// Set username as a string
string Username = usernameTxtBx.Text;

//find usrNo
SqlCommand cmd = new SqlCommand("SELECT usrNo FROM tblUser WHERE
usrName = 'Username'", conn);
SqlDataReader dr = cmd.ExecuteReader();
dr.Read();

int UserNo = (int)dr["UserNo"];

conn.Close();

This gives the following error:

Description: An unhandled exception occurred during the execution of
the current web request. Please review the stack trace for more
information about the error and where it originated in the code.

Exception Details: System.IndexOutOfRangeException: UserNo

In debugging the value of UserNo appears to get set to 0 regardless of
the login used, where i would expect the value to equal 1 or 3 or
similar integer.

Any ideas what i'm doing wrong? am I even on the right track??

Thanks

 

[Mark Rae]

SqlCommand cmd = new SqlCommand("SELECT usrNo FROM tblUser WHERE

int UserNo = (int)dr["UserNo"];

You're selecting a field called *usrNo* and then trying to look for a field
called *UserNo*

 

[Guest]

Assimalyst,

You need to have the value of Username as part of the Select statement:

SqlCommand cmd = new SqlCommand("SELECT usrNo FROM tblUser WHERE
usrName = '" + Username + '", conn);

However, instead of building the Select statement this way, you should look
into using a parameterized query.

Kerry Moorman

 

[Assimalyst]

Thanks you both,

Kerry, I have tried your SQL statement, but the same error is being
produced, the UserNo value is still being read as 0 too.

Any more ideas?

Also could you ellaborate on how to implement the parameterised query
you mentioned if it's not too much trouble.

Thanks again.

 

[Mark Rae]

Kerry, I have tried your SQL statement, but the same error is being
produced, the UserNo value is still being read as 0 too.

Er, have you actually read my reply...?

"You're selecting a field called *usrNo* and then trying to look for a field
called *UserNo*"

You need to either change your SELECT statement to:

"SELECT usrNo AS UserNo FROM tblUser WHERE...

or change your variable population to:

int UserNo = (int)dr["usrNo"];

 

[Assimalyst]

Thank you Mark.

My appologies, was a bit tired last night it seems.

Just run through again this morning with you suggested change. All
sorted!

Thanks again.