Today I was trying to debug this application. Where the reader is returning null even though I was passing the correct number of parameter and correct values.
the C# code looks something like this
CommonCommand cmd = new CommonCommand();
cmd.Command = GetSPCommand( GETCAREPROVIDERTYPEBYDESCRIPTION );
using( cmd.Command )
{
AddParameters( cmd.Command, pVarChar( CareProviderTypeBase.Property_Description ,20 ,description ) );
return GetObject(cmd);
}
where a storedprocedure expects Description parameter
DECLARE @RC int
DECLARE @DESCRIPTION varchar(50)
-- Set parameter values
EXEC @RC = [GetCareProviderTypeByDescription] @DESCRIPTION='Visiting Medical Officer'
I was running the query in Sql Analyzer and it was returning 1 row which I was expecting but in .Net I was getting null for the reader.
Finally I discovered my silly mistake:I was not looking at the length of the parameter which is 20... and the SP expecting 50. So when I was trying to find 'Visiting Medical Officer' which is 24 character long it as never maching as it was chopped to 20 characters by the .Net libraries.
This can also happen to you, someone might go and change the Table->Column length and SPs to cope with large length data. But you should never forget to change your c# codes where you have mentioned the parameter length.
Lessons Learned:
1. Check at the Length of the Parameter.
Some other tips:
2. Check whether data really exists in the db and the reader is supposed to return a row. You can do this by Executing the SP directly from SqlAnalyzer.
3. If you are dealing with multiple databases like me. Look at the correct DB.
This sort of bug do not throw any exception from the database so sometimes hard to investigate.