System.Void' is not a valid return type for a mapped stored procedure method

Question

Am trying to execute a pro in SQL-server from LINQ in asp.net. It works fine when I execute the Proc in SQL,but when i try to call it from linq in asp.net it throws the following error "System.Void' is not a valid return type for a mapped stored procedure method" Here is my proc,

   ALTER PROCEDURE [dbo].[VIEW_COLUMN] 

   @REPORT_NO INT ,
   @TEMPLATE_NAME VARCHAR(100),
   @BODY_TEXT VARCHAR(500),
   @BODY_TEXT_O VARCHAR(500) OUTPUT

   AS
   BEGIN
  --DECLARE VARIABLES--
  DECLARE @BODY_TEXT VARCHAR(100)

   ----------- etc----
   ------etc etc-----
   SET @BODY_TEXT_O = @BODY_TEXT
   END

In my ASP.net Page,

    using (EHSIMSDataContext db = new EHSIMSDataContext(EHSIMSConnectionString.GetConnectionString()))
    {

        string MainString = _EmailTemp.TEMPLATE_TEXT;
        int? R = 000001;
        string b = "";
        string temname = _EmailTemp.VIEW_NAME;
        db.VIEW_COLUMN(R, temname, MainString, ref b);
    }

After the error i googled and changed my designer page to,(removed void and added string and added the last return line)

          public string VIEW_COLUMN ------etc
           {
             IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), rEPORT_NO, tEMPLATE_NAME, bODY_TEXT, bODY_TEXT_O);
        bODY_TEXT_O = ((string)(result.GetParameterValue(3)));
                    return ((string)(result.ReturnValue));
         }

And there is no return in the above code as well as the method as "Public Void VIEW_COLUMN", this still showed some different error, and I also tried to set string as return type in DBML file of my procedures properties still no use,don't know what am missing.

Additional Information Am having a Temp Table and table variable in my procedure,may is that the reason?

Some reply would be really helpful...Thanks

Answer 1

You are close; you need to change the return type for your stored procedure (within your dbml) to an int.

Linq in effect executes this sql command, which returns the result of execution as an int:

DECLARE @ReturnValue int

EXEC    @ReturnValue = [dbo].[VIEW_COLUMN]

SELECT  'ReturnValue' = @ReturnValue

Answer 2

Try changing your stored procedure so that it return the text directly instead of via output parameter.

Add one more row at the end:

SELECT @BODY_TEXT_O;