Take the 2-minute tour ×

I have the strangest issue. The following works most of the time, however "now and again" it fails to return data, even though I have put a breakpoint for the adapter.fill statement, checked the params and executed them via SQL Management Studio which definitely returns the data :)

So this is a standard c# SQL client call:

SqlCommand command = new SqlCommand();
command.CommandType = CommandType.StoredProcedure;
command.Connection = connection;

The connection is definitely being opened ok, and used because it is also being used via another call.. and actually subsequent calls work too.. however, again in my case the following "sometimes" fails:

command.CommandText = "myprocname";
command.Parameters.AddWithValue("@param1", param1value);
command.Parameters.AddWithValue("@param2", param2value);
command.Parameters.AddWithValue("@param3", param3value);
command.Parameters.AddWithValue("@param4", param4value);

and then to fill the dataset:

DataSet ds = new DataSet();
using (SqlDataAdapter adapter = new SqlDataAdapter(command))

So really nothing strange going on, and the above works most of the time.. on the occasion it didn't return what I expected, and put a breakpoint in just before the adapter.fill statement, checked the commandtext and each of the parameters/values being passed.. opened up sql management studio, ran the proc with those params, and sure enough the expected data was returned.

The proc is very simple, and again most of the time returned the data...

I cannot fault the proc in anyway, however this is driving my crazy as I have checked connection timeouts, timings of execution and so on.. I guess the craziest thing is that the proc name, and params are all correct (no extra params being passed by accident etc).. and running manually, it returns the data.. but via the fill statement it seems to NOT (but only occasionally!).

Any help with this madness much appreciated!!

share|improve this question
Maybe you're getting an ObjectDisposedException on command.Dispose();. I would use using for the connection, command and dataadapter. –  Tim Schmelter Feb 25 '13 at 12:51
There could be some static code removing/emptying the dataset or showing it before it has been filled. Can you add some debugging code, logging the number of lines loaded or replace the Fill with a datareader. Just changing the code can show the cause for this strange problem. –  CodingBarfield Feb 25 '13 at 12:52
Addition: the using on the SqlDataAdapter will also set it's SelectCommand to null which is command, that could cause an exception on command.Dispose();. –  Tim Schmelter Feb 25 '13 at 12:59
use a try-catch block around the dataset and log the exception. May be you will see something in the log that needs fixing. Some suggestions would be, whether any of your params are null during the process. So does your stored proc handles that? –  Azhar Khorasany Feb 25 '13 at 13:24
Hey thanks to all above.. certainly helpful pointers. So far I have ensured that all sqlconnection/command/adapter references are wrapped in USING statements. I have also confirmed again that just before the dataset is filled by the adapter, the command object does indeed contain the correct commandtext/parameters.. and again those params have been manually called in the sql management studio with results being returned.. only the dataset does not get filled by the adapter.fill command.. very strange... –  Dav.id Feb 25 '13 at 15:06

1 Answer 1

up vote 0 down vote accepted

RESOLVED: Okay well.. the strangest error, one that has really thrown me.

I didn't mention at the time of posting this question, that I was using RDLC/Reportviewer control to show the data within a set of dashboard charts.. i.e. you could then select a chart segment (a pie chart slice for example) to drilldown on that data.. the chart data was "pre-grouped" i.e. the procs for those charts didn't contain the detail to make it quick to show the dashboard..

Anyway, I digress, because I had some "bad data".. well data that had null dates, and the short story version was the grouping in the reportview chart was somehow causing the selected data to be incorrect "sometimes".. as in the params passed were/looked ok, but on occasion they were not..

So the end result is (which I had planned anyway) was to add checks into my procs for "date is not null" etc.. however it didn't harm to wrap the command object etc with "using" statements too! so many thanks for all the responses!

share|improve this answer

Your Answer


