Cannot find columns from a stored procedure...

I have an application that I inherited, and I have a annoying problem. We're using stored procedures to return most of our data, and occasionally we receive errors stating that a particular column cannot be found in the resulting data table. When I run the stored procedure against SQL Server I receive the expected output. What would make this random act happen, any ideas?

Also, I keep receiving errors stating that a connection is already open and needs to be closed before an action to the database is performed. I'm explicitly closing each connection in a finally block for every method in my data access code, so a connection should always be closed, right?

Does the code the runs the stored procedure site inside a try...catch, if so make the catch log the parameter that were supplied to the stored procedure. Also I would log the number of rows in the datatable. This should allow the problem to be reproduced.

>before an action to the database is performed
What action(s) give rise to this message.

For completeness, which version of SQL Server and ASP.NET are you using?

|||

Here is an example of the code:

SqlDataAdapter da = new SqlDataAdapter("GetWFSAInfo",SQLcn);
DataSet ds = new DataSet();
da.SelectCommand.Parameters.Add("@.WFSAID",SqlDbType.Int).Value = WFSAID;
da.SelectCommand.CommandType = CommandType.StoredProcedure;

try {
da.Fill(ds,"GetWFSAInfo");
}
catch (Exception ex) {
NotifyOfException (ex);
}
finally {
SQLcn.Close();
}

return ds;

Originally, none of this code was in try/catch/finally blocks at all, so I had to weed through over 11,000 lines of data access code to put these constructs in. That seems to curb some of the problems. I prefer to use a class library that I created that takes care of all of this for me. I swear, object orientation, inheritance and the like and the previous programmer were never introduced. Aggravating to say the least...

NotifyOfException logs the exception to a log file. I am using SQL Server 2000/ASP.NET 1.1.

Thanks for all of your help...

|||

You will need to modify the catch

catch (Exception ex) {
NotifyOfException (ex);
}

to

catch (Exception ex) {
ex.Data = WFSAID;
NotifyOfException (ex);
}

This assumes of course that WFSAID is a variable.