Wrap it in a try catch and output the message

Try adding:
cm.CommandType = CommandType.StoredProcedure;
(befoer the ExecuteScalar())

You might also need to check whether your proc *selects* the value or
*prints* the value; ExecuteScalar() will only work for a SELECT - but
if this was the problem I would expect a different error message.

Marc

Not sure if this is the problem but if you copied and paste the code from
your project to the post, it looks like you have an extra apostrophe
character at the end of sp_Collect.

First of all, you really haven't showed how the stored procedure is defined.
Are you using a RETURN statement to return the string in the stored
procedure?  Or are you using a SELECT statement to return the string as a
record?

Stored procedures only return INT values.  If you need a string returned you
can use a parameter in the stored procedure with the OUTPUT tag.  Short
example below:

CREATE PROCEDURE sp_Collect
   @ID INT,
   @ReturnValue VARCHAR(255) OUTPUT
AS
   SELECT @ReturnValue = stringfield FROM sometable WHERE idfield = @ID
GO

C# code:
public string GetID(int ID)
{
   SqlCommand cm = new SqlCommand("sp_Collect", sqlCon);
   cm.Parameters.Add(new SqlParameter("@ID", ID));
   SqlParameter paramReturnValue = cm.Parameters.Add("@ReturnValue",
SqlDbType.VarChar, 14);
   paramReturnValue.Direction = ParameterDirection.Output;
   cm.ExecuteNonQuery();
   return (string) paramReturnValue;
}