How do I read data from database using SQL datareaders

Question

0.00/5 (No votes)

See more:

Hi, everyone

I have a stored procedure which logs in a user and retrieves some data from multiple table, When i test the procedure with test data it returns all the fields i need, But in my application when i use a datareader to retrieve these values i get an Index out of range exception.

I have used the "C#">reader.VisibleFieldCount property to count how many fields are being returned and it turns out i'm only getting back 3 of 8 fields.

Any assistance will be appreciated

Regards

What I have tried:

here is my stored procedure

SQL

ALTER PROCEDURE [dbo].[spCustomerLogin]

 @Email nvarchar(Max),
 @Password nvarchar(Max)

AS
SET NOCOUNT ON
SELECT Customer_name,CUSTOMER_PROFILE_DATA.Customer_email,Customer_password,Customer_phone,Card_number as 'crd_num',Card_expmnth,Card_expyear,Card_cvc
FROM CUSTOMER_PROFILE_DATA
join CARD_INFO
on CARD_INFO.Customer_email = CUSTOMER_PROFILE_DATA.Customer_email
WHERE (CUSTOMER_PROFILE_DATA.Customer_email =@Email) AND (Customer_password = @Password)
RETURN

My reader code

C#

while(reader.Read() && reader.VisibleFieldCount > 5)
              {


                  Response.Redirect("ProfileC.aspx", false);
                  Session["Cus_name"] = Convert.ToString(reader[0]);
                  Session["Cus_email"] = Convert.ToString(reader[1]);
                  Session["Cus_pass"] = Convert.ToString(reader[2]);
                  Session["Cus_phone"] = Convert.ToString(reader[3]).ToString();
                  Session["Card_num"] = Convert.ToString(reader[4]);
                  Session["Card_expmnth"] = Convert.ToString(reader[5]);
                  Session["Card_expyr"] = Convert.ToString(reader[6]);
                  Session["Card_cvv"] = Convert.ToString(reader[7]);

              }

Posted 18-Aug-16 2:21am

Garvin12

Updated 18-Aug-16 18:29pm

Andy Lanng

v4

Add a Solution

Comments

Richard MacCutchan 18-Aug-16 8:40am

Are you seriously storing userid and password in clear text in the database?

Garvin12 18-Aug-16 8:48am

no the password is encrypted.

Richard MacCutchan 18-Aug-16 9:22am

Just asking; we see so many people storing them in clear text.

Richard Deeming 18-Aug-16 11:57am

Encrypting passwords is almost as bad as storing them in plain text. If your encryption key is compromised, then so are all of your passwords.

You should only ever store a salted hash of the password, using a unique salt per record.

Secure Password Authentication Explained Simply[^]
Salted Password Hashing - Doing it Right[^]

Richard Deeming 18-Aug-16 11:58am

Also, it looks like you're storing credit-card details in your table. Hopefully those aren't stored in plain-text?

Andy Lanng 18-Aug-16 8:53am

that would indeed be blasphemy!

Garvin12 18-Aug-16 9:02am

sacrilegious

Garvin12 19-Aug-16 3:24am

I get what you mean, thanks for the links.

2 solutions

Solution 1

1. Don't use VisibleFieldCount. It is not what you think it is.
2. Reference your data by using the field name. That will make things much easier to debug. For example:

C#

Session["Cus_name"] = reader["Customer_name"].ToString();

Posted 18-Aug-16 3:44am

ZurdoDev

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)

Karthik_Mahalingam · Accepted Answer · 2016-08-18T18:29:00

try using SqlDataAdapter [^]

C#

SqlCommand sqlCommand = new SqlCommand();
           SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
           DataTable dt = new DataTable();
           da.Fill(dt);
           if (dt.Rows.Count == 1)
           {
               Session["Cus_name"] = Convert.ToString(dt.Rows[0][0]);
               Session["Cus_email"] = Convert.ToString(dt.Rows[0][1]);
               Session["Cus_pass"] = Convert.ToString(dt.Rows[0][2]);
               Session["Cus_phone"] = Convert.ToString(dt.Rows[0][3]);
               Session["Card_num"] = Convert.ToString(dt.Rows[0][4]);
               Session["Card_expmnth"] = Convert.ToString(dt.Rows[0][5]);
               Session["Card_expyr"] = Convert.ToString(dt.Rows[0][6]);
               Session["Card_cvv"] = Convert.ToString(dt.Rows[0][7]);
             

           }

Move this line at the last, so that all the necessary actions will perform before navigating to other page

C#

Response.Redirect("ProfileC.aspx", false);