CODEWITHSUNDEEP
c#.netsql-server-2008iis-7
Abdullah Darwish
Abdullah Darwish

Reputation: 223

.NET exception with SQL Server 2008

This code runs ASP.NET on IIS.7 in windows server 2008 R2 and SQL Server 2008 R2 Express.

This is a simple function which selects from the database based on 3 parameters and returns object containing the selected row.

This code runs normally in most time, but in some cases i get an exception called serial_number which is a column name in the database table.

This is the complete exception:

[IndexOutOfRangeException: serial_number]
System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName) +2674398
System.Data.SqlClient.SqlDataReader.GetOrdinal(String name) +249
System.Data.SqlClient.SqlDataReader.get_Item(String name) +23
PhoneSerialNumber.GetByPhoneNumber(String phoneNumber, String Country, String app) +423
UpdateMeClass.GenereteVersionTag(String Version, String PhoneNumber, String appName) +534
UpdateMe.ProcessRequest(HttpContext context) in c:\inetpub\wwwroot\me\Handlers\UpdateMe.ashx:63
System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +599
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +171

Although I logged the 3 parameters in case of the exception and they are not null, note that when I simulate the same request with the same parameters in the same server, it runs normally.

I think in the case of the exception the query runs fine but when getting values of the columns it through this exception, I might be wrong.

SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["meConnectionString"].ConnectionString);

connection.Open();
SqlCommand cmd;
using (connection)
{
    cmd = new SqlCommand("select * from serialnumber_table join countries on  country_id=countryid join applications on ApplicationID=App_ID where phone_number= @phoneNumber and  country_name=@Country and app_name=@app", connection);
    cmd.Parameters.AddWithValue("@phoneNumber", phoneNumber);
    cmd.Parameters.AddWithValue("@Country", Country);
    cmd.Parameters.AddWithValue("@app", app);

    using (SqlDataReader rdr = cmd.ExecuteReader())
    {
       while (rdr.Read())
       {
          sn = new PhoneSerialNumber();
          sn.SerialNumber = rdr["serial_number"].ToString();
          sn.PhoneNumber = rdr["phone_Number"].ToString();
          sn.PhoneLang = rdr["Lang"].ToString();
          sn.PhoneModel = rdr["ModelName"].ToString();
          sn.ApplicationVersion = rdr["App_Version"].ToString();
          sn.DealerCode = rdr["dealer_code"].ToString();
          sn.Size = rdr["size"].ToString();

          TimeSpan Time = DateTime.Parse(rdr["Renewal_Date"].ToString()) - new DateTime(1970, 1, 1, 0, 0, 0);
          sn._renewal_Date = (long)(Time.TotalMilliseconds);

          rdr.Dispose();
          connection.Dispose();
          return sn;
       }
   }

   sn = new PhoneSerialNumber();
   sn.SerialNumber = null;
   sn.PhoneNumber = null;
   sn.PhoneModel = null;
   sn.PhoneLang = null;
   sn.ApplicationVersion = null;
   sn.DealerCode = null;
   connection.Dispose();
   return sn;
}

Upvotes: 1

Views: 2041

Answers (5)

Anthony Shaw
Anthony Shaw

Reputation: 8166

The only thing that comes to mind, is that for some reason when you run your query, the serial_number column is not being returned. Rather than doing a SELECT *, try running doing a select on each individual columns that you're trying to return.

Is your serial number column duplicated across any of the other tables? Are any of the sources views?! If the serial_number comes from a view and you've changed an underlying table, it could need to be rebuilt in order to display the proper columns for the view.

the only time I've ever seen the exception as the column name is when the column specified doesn't exist in the current data reader, like it wasn't selected in the first place or simply didn't exist

Upvotes: 3

MethodMan
MethodMan

Reputation: 18863

TimeSpan Time "Time is a KEY WORD use more meaningful variable names also are you using all the fields from the Select query..? if not do not do Select *

Upvotes: 0

Cheesecake
Cheesecake

Reputation: 11

This is just a guess, but it's also possible you're disposing rdr twice. Once with the using statement, which translates into a try/finally block, and once explicitly before the return statement with your PhoneSerialNumber. .NET throws exceptions when you dispose something that's already disposing. Since no exception was posted, I'm just guessing here.

Upvotes: 1

JamieSee
JamieSee

Reputation: 13020

Odds are good that serial_number contains a null which comes back as DBNull.Value. Try using rdr["serial_number"] as string instead of rdr["serial_number"].ToString().

Also, avoid using SELECT *. It returns more data than necessary which increases network traffic and named columns in your SELECT produce better error messages.

Upvotes: 0

Mike Hofer
Mike Hofer

Reputation: 17022

Given that you've only provided "serial_number" as the cause of the exception (it can't possibly be the exception itself), I believe the culprit line is this one:

sn.SerialNumber = rdr["serial_number"].ToString();

Chances are really good that this value is null.

If the problem is that the column doesn't actually exist, I'd ask if this table was being generated dynamically, and someone isn't doing their job right.

Upvotes: 0

Related Questions