CODEWITHSUNDEEP
c#sqlloopssqldatareadersqlcommand
ProgrammingDude
ProgrammingDude

Reputation: 597

DataReader Exception

I have a simple database that I am using. It contains two entries for users which is a user with UserID 1 and IsAdmin 0 and another with UserID 3041234567 and IsAdmin of 1. The only fields in the database is a string UserID and a bit IsAdmin. I am reading from the database with the following code:

SqlConnection conn = new SqlConnection(Properties.Settings.Default.Conn);
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM Users WHERE UserID = " + t.Text.ToString(), conn);
SqlDataReader reader = cmd.ExecuteReader();

if (reader.HasRows)
{

    while (reader.Read())
         {
                user.UserID = reader["UserID"].ToString();
                user.IsAdmin = Convert.ToBoolean(reader["IsAdmin"]);
         }
}

conn.Close();

If I enter the number 3041234567 as the UserID everything works perfectly, but If I enter the number 1 I get an exception saying that "The conversion of the nvarchar value '3041234567' overflowed an int column."

If I set a breakpoint and watch the while(reader.read()) loop the loop iterates through fine and sets the user.UserID = 1 and the user.IsAdmin = false. The exception is thrown when the loop begins to iterate a second time. I guess I have a couple of questions:

  1. Why is the loop iterating a second time?
  2. How is the ID 3041234567 being returned with the sql command "SELECT * FROM Users WHERE UserID = 1"
  3. What is the int column that is being overflowed?

Upvotes: 0

Views: 771

Answers (1)

Dmitrii Bychenko
Dmitrii Bychenko

Reputation: 186668

Well, since 

  3041234567 > int.MaxValue ( == 2147483647)

you've got an overflow; if you want some kind of integer value, however, try long (which is 64 bit long):

  long value = Convert.ToInt64(reader["UserID"]);

Something like this:

 // Wrap IDisposable into using 
 using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.Conn)) {
   conn.Open();

   // Make sql 
   //  1. Readable 
   //  2. Parametrized
   //  3. Avoid * in select
   String sql = 
     @"select UserID,
              IsAdmin
         from Users
        where UserID = @prm_UserId";

   // Wrap IDisposable into using 
   using (SqlCommand cmd = new SqlCommand(sql, conn)) {
     // Explicit data type will be better here (Add Parameter with type)
     // but I don't know it
     cmd.Parameters.AddWidthValue("prm_UserId", t.Text);  

     // Wrap IDisposable into using 
     using (SqlDataReader reader = cmd.ExecuteReader()) {
       // You don't want to iterate the whole cursor, but the first record 
       if (reader.Read()) {
         //TODO: Make UserID being "long"
         user.UserID = Convert.ToInt64(reader["UserID"]);
         user.IsAdmin = Convert.ToBoolean(reader["IsAdmin"]);
       } 
     }
   }
 }

Upvotes: 4

Related Questions