.NET exception with SQL Server 2008

Question

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;
}

There is no exception called `Serial_Number`, so what exception do you really get? — Tim Schmelter, Dec 20 '11 at 18:32
You may want to run this query against the DB and ensure that your data you are reading out is not null. rdr["serial_number"] may be null and calling .ToString() on it will cause a problem as with any of your other values. — ShelbyZ, Dec 20 '11 at 18:33
Serial_Number is a column name from the columns we get and this is the exception we get — Abdullah Darwish, Dec 20 '11 at 18:34
usually when the column name is the exception, it means the column doesn't exist in the data reader (see my answer below) — Anthony Shaw, Dec 20 '11 at 18:36
Why are you setting all those properties to null? Also your connection object should be managed and disposed of at the right time because you are using a `using` statement. You don't need to explicitly call it. — JonH, Dec 20 '11 at 18:37
once again how come for Database people are not using TimeStamp or DateTime what's with the time span.. are you tracking or storing some sort of Difference over time..?? — MethodMan, Dec 20 '11 at 18:40
Add [this extension](http://stackoverflow.com/a/813741/284240) and call it `rdr.HasColumn("serial_number")` to see if the column exists in the DataReader. — Tim Schmelter, Dec 20 '11 at 18:48
this is the exception trace, note that we are using ashx pages and more than 30K hits come to this function everyday : http://www.mebackendserver.com/Me/errorslogs/Error_12_20_2011_12_35_17_AM.html — Abdullah Darwish, Dec 20 '11 at 18:50

Anthony Shaw · Answer 1 · 2017-06-09T21:53:59.580

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

Also if the source is SQL Function (not only view), then you have also to rebuild the function. (Execute the function again) — Hakan Fıstık, Jun 09 '17 at 12:48

score 1 · Answer 2 · answered Dec 20 '11 at 18:39

1

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.

answered Dec 20 '11 at 18:39

Cheesecake

11
1

rdr.Dispose(); os being disposed twice.. @Abdullah look at MSDN or google search how and why using() is used your code could use a little bit of cleaning up.. also how you're reading from the config file too I would assign a private static variable up top somewhere in your code to get that value or create a property accessor – MethodMan Dec 20 '11 at 18:44
this is the exception trace, note that we are using ashx pages and more than 30K hits come to this function everyday : http://www.mebackendserver.com/Me/errorslogs/Error_12_20_2011_12_35_17_AM.html – Abdullah Darwish Dec 20 '11 at 18:48
Check to see if rdr["serial_number"] is null, like other have said so far. Also, I noticed you use + "\n" in your code... I would suggest using \r\n, or Environment.NewLine if you're trying to be fancy. – Cheesecake Dec 20 '11 at 18:53

score 0 · Answer 3 · answered Dec 20 '11 at 18:36

0

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.

answered Dec 20 '11 at 18:36

Mike Hofer

16,477
11
74
110

this is the exception trace, note that we are using ashx pages and more than 30K hits come to this function everyday : http://www.mebackendserver.com/Me/errorslogs/Error_12_20_2011_12_35_17_AM.html – Abdullah Darwish Dec 20 '11 at 18:47
Please find the stack trace of the exception in the original post – Abdullah Darwish Dec 20 '11 at 18:55
In this case, you're definitely trying to reference a column that doesn't exist on your source data set. Is the data set dynamically generated at run time? – Mike Hofer Dec 20 '11 at 19:02

score 0 · Answer 4 · answered Dec 20 '11 at 18:39

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.

score 0 · Answer 5 · answered Dec 20 '11 at 18:42

0

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 *

answered Dec 20 '11 at 18:42

MethodMan

18,625
6
34
52

.NET exception with SQL Server 2008

5 Answers5