CODEWITHSUNDEEP
c#sql-servervisual-studio-2008
Anna T
Anna T

Reputation: 1027

Stored Procedure with output parameters connected to method in C#

Do you see something blatantly wrong with this line: 

commaSepString1 = com.Parameters["@CommaSepString"].Value.ToString();

It shows an error like:

Object reference not set to an instance of an object.

For short, the C# method looks like:

public static DataTable GetFilmDetails(string filmId, out string
commaSepString1)

It takes the following stored procedure (that executes correctly on its own): 

com.CommandText = "CatalogGetFilmDetails2";

And since @CommaSepString is an output parameter in the stored procedure, in C# I have this:

param = com.CreateParameter();
param.ParameterName = "@CommaSepString";
param.Direction = ParameterDirection.Output;
param.DbType = DbType.String;
com.Parameters.Add(param);

commaSepString1 = com.Parameters["@CommaSepString"].Value.ToString();

If you are curious about the Stored Procedure, it starts like this: CREATE PROCEDURE CatalogGetFilmDetails2

(@FilmID int, @CommaSepString VARCHAR(50) OUTPUT) AS

The SP inserts some rows into a table variable based on some joins, it puts all values from column x from that table into a string with comma separated values: 

SELECT @CommaSepString 
= STUFF((SELECT ', ' + Categ  FROM @Filme1 FOR XML PATH('')), 1,1,'')

then it selects the table.

Thank you for the patience to read this. Would it be more clear if I published the entire code for the stored procedure and the C# method? Not sure if I am allowed that and worried it might be too lengthy to read.

PS: There must be something wrong about the commaSepString! The code worked perfectly before I added it in both places.

Upvotes: 1

Views: 1697

Answers (2)

Andomar
Andomar

Reputation: 238048

SQL Server doesn't know output, it only knows input/output. So when you specify 

param.Direction = ParameterDirection.Output;

SQL Server will actually read the string as well. Since you haven't supplied a value, SQL Server determines that that its length is zero.

Incredibly, if you specify the magic number -1, SQL Server will determine the size for you:

param.Size = -1;

This is perhaps the second worst gotcha about ADO.NET. Just behind the fact that SQL NULL translates to DBNull.Value instead of null.

Upvotes: 1

Steve
Steve

Reputation: 216243

You should set a breakpoint on the offending line, then you should examine your parameters collection to check if the parameter exists.
If it exists, then check if its value is null.
In this case the .ToString() will give you the error described.

Upvotes: 0

Related Questions