CODEWITHSUNDEEP
c#sqldatareader
Zied.M
Zied.M

Reputation: 215

IndexOutOfRange Exception in sqldatareader using c#

I create an application using c# , In my authentification interface , i have a test control , i want to know profile user .

My database contains table named user which contains 4 columns 

(id_user,name ,mail, profile)

Here is my code 

public string profil_user(string login)
    {
        SqlConnection conn = new database().connect_user();
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandText = "select profile from user where name = '" + login + "';";
        SqlDataReader s = cmd.ExecuteReader();
        if (s.Read())
        {
           return ( s.GetString(3));


        }
        else{return ("false"); }
    }

but i have an exception in s.GetString(3)

system.IndexOutOfRange : index was outside the bounds of the array

Upvotes: 0

Views: 162

Answers (5)

anae
anae

Reputation: 21

Because you do not have all the fields in your select list

Change the SQL to:

select id_user,name ,mail, profile from user where name = '" + login + "';

Upvotes: 1

Tim Schmelter
Tim Schmelter

Reputation: 460028

So you want the fourth row, not the fourth column which you try to access with s.GetString(3):

int rowNum = 0;
while(s.Read())
{
   if(++rowNum == 4)
   {
      return s.GetString(0);
   }
}
return "false";

However, it is a bit strange to access the fourth row when you don't use an Order By. You should also only return the row that you want with the correct sql query.

You are also open for sql injection if you use string concatenation here:

cmd.CommandText = "select profile from user where name = '" + login + "';";

Use sql parameters:

cmd.CommandText = "select profile from user where name = @login";
cmd.Parameters.Add("@login", SqlDbType.VarChar).Value = login;

have 4 columns not rows

Ok, so you instead want the fourth column. Why don't you use the name instead?

Since you only select the profile-column(the fourth), you could simply use GetString(0). But you could also select all columns and then determine the correct index with GetOrdinal:

int profileColumnIndex = s.GetOrdinal("profile");
return s.GetString(profileColumnIndex);

This is useful if you don't control the query or it might be changed in future.

Upvotes: 2

Jon Skeet
Jon Skeet

Reputation: 1499800

You're only selecting a single field (profile) but then you're trying to select the 4th field (index 3) here:

return ( s.GetString(3));

In addition to just returning s.GetString(0) I would strongly advise you to:

  • Use parameterized SQL - always do this, to prevent SQL injection attacks, make your code more readable, and prevent unexpected text conversion problems
  • Either throw an exception or return null if the profile isn't found, instead of returning the string "false"
  • Use using statements for disposable things like SqlCommand, SqlConnection and SqlDataReader to ensure that you clean up resources appropriately
  • Start following .NET naming conventions to make your code more idiomatic

So something like:

public string GetUserProfile(string login)
{
    string sql = select profile from user where name = @login";
    // I assume Connect() returns an *open* connection?
    using (var conn = new Database().Connect())
    {
        using (var command = new SqlCommand(sql, conn))
        {
            command.Parameters.Add("@login", SqlDbType.NVarChar).Value = login;
            using (var reader = command.ExecuteReader())
            {
                // If it's an error (code failure) for there to be no matching profile,
                // you may want to throw an exception instead.
                return s.Read() ? s.GetString(0) : null;
            }
        }
    }
}

Upvotes: 2

rockymcclamrock
rockymcclamrock

Reputation: 21

The parameter for SqlDataReader.GetString should be the column index. You're only selecting one column so you get an exception.

Upvotes: 1

apomene
apomene

Reputation: 14389

You are selecting only 1 field, thus index 3 is out of bounds. It also very important to Use parameters. Try:

cmd.CommandText = "select profile from user where name = @login;";
cmd.Parameters.Add("@login, SqlDbType.NVarChar).Value = login;
SqlDataReader s = cmd.ExecuteReader();
while (s.Read())
{   
  return  s[0].ToString();
}

Upvotes: 1

Related Questions