none
The data reader returned by the store data provider does not have enough columns for the query requested. RRS feed

  • Question

  • Hi,

    I am using EF4 and vs2010, c#.

    I have a stored proc, it like

    ALTER PROCEDURE [dbo].[DeleteInvByInvoiceId]
     -- Add the parameters for the stored procedure here
     @invoiceId int = 0
    AS
    BEGIN
        SET NOCOUNT ON;

        declare @err int
       
        begin transaction
     
     delete from Invoice where InvoiceId = @invoiceId;
     select @err = @@Error if @err <> 0 begin rollback transaction return @err end
     
        commit transaction 
     select @err = @@Error if @err <> 0 return @err else return 0
    END

    In my c# application, The sp is called as
    var result = db.DeleteInvSetupByInvoiceId(invoiceId, IsHead).Single();
    The rows in Invoice were deleted. But this line generates the error "The data reader returned by the store data provider does not have enough columns for the query requested."

     

    Tuesday, September 20, 2011 1:06 AM

Answers

  • Hi Peter,

    Thanks for your feedback.

    You should add "select @ err" at the end of your judgment,

    if @err = 0
      select @err = @@Error
     else
      select @err  
    

    EF won't treat it as return value, you should:

    if @err = 0
      select @err = @@Error
     else
      select @err  
    select @err
    

    and you should modify your import SP return value to "Int32"

      using (var context= new EFTestEntities())
                {              
                    int? rtvalue = context.DeleteInvByInvoiceId(3).First();       
                }
    

    Have a nice day.



    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by peter 9 Tuesday, September 27, 2011 11:33 PM
    Monday, September 26, 2011 5:52 AM
    Moderator

All replies

  • Hi peter,

    Welcome!

    Please try to change the return to None in model design, please let me know your feedback.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, September 20, 2011 7:27 AM
    Moderator
  • Hi Alan,

    Thank you for the help.

    After changing the return to None, the sp works fine, but the return value is wrong, it returned 0xffffffff. it should be 0.

    Tuesday, September 20, 2011 11:38 PM
  • Hi Peter,

    Thanks for your feedback.

    I test your stroed procedure, it returns -1 as result and sp works ok. When you set return none, the return value always -1.

    Have a nice day.

     


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, September 21, 2011 11:59 AM
    Moderator
  • Hi Alan,

    Thank u for the help again. To make the question simple, I simplified the sp in the question, but now I think it is better to give you the full version of the sp. I copied the sp at the end. In the Model browser, I changed the SP's return type to none under the EntityContainer, but I couldn't change it under the Model.Store, because it is disabled and the value is blank. I am wondering I got wrong setting?

    But, by the way, it is not good if SP always returns -1 because I won't know the SP succeeded or failed.

    If this is a problem that cannot be solved, is there any other way to call the SP in C#?

     

    USE [MyDatabase]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[DeleteInvByInvoiceId]
     @invoiceId int,
     @IsHeader bit
    AS
    BEGIN
        SET NOCOUNT ON;

        declare @err int
       
        begin transaction
     
     delete from Invoice where InvoiceId = @invoiceId;
     select @err = @@Error if @err <> 0 begin rollback transaction return @err end
     
     if @IsHeader = 1
      update site set HeadInvId = NULL where HeadInvId = @invoiceId
     else
      update site set FooterId = NULL where FooterId = @invoiceId
      
     select @err = @@Error if @err <> 0 begin rollback transaction return @err end 
     
        commit transaction 
     select @err = @@Error if @err <> 0 return @err else return 0
    END

    Thursday, September 22, 2011 5:55 AM
  • Hi Peter,

    Thanks for your feedback.

    After doing some research, I found the trick. when choose None as the return value, the method return a integer which indicate the affect rows. Why it alway is -1? the answer is "SET NOCOUNT ON;" please feel free to alter your stored procedure and move this link.

    The more information you can refer : http://msdn.microsoft.com/en-us/library/ms189837.aspx

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, September 22, 2011 7:11 AM
    Moderator
  • Hi Alan,

    Thank you.

    I removed the "SET NOCOUNT ON;" , and sp returns the total rows (from two sql) affacted. So the return statement in the SP is ignored. So what is the way to get the error code? Do I have to set the out goint parameter for the error code? or is there any other way I can still get the error coed as a return value of the SP?

     

    Friday, September 23, 2011 12:30 AM
  • Hi again,

    If you want to get the error code from the SP, you should use Select instead of return in your sp:

    ALTER PROCEDURE [dbo].[DeleteInvByInvoiceId] 
     -- Add the parameters for the stored procedure here
     @invoiceId int = 0
    AS
    BEGIN
        declare @err int
        begin transaction
     delete from Employees where EmployeeId = @invoiceId;
     select @err = @@Error if @err <> 0 begin rollback transaction return @err end
        commit transaction 
     select @err = @@Error if @err <> 0 return @err else return 0
     select @err
    END
    

    You can refer this link: http://devtoolshed.com/using-stored-procedures-entity-framework-scalar-return-values

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, September 23, 2011 6:05 AM
    Moderator
  • Hi Alan,

    Thank you for your help.

    Following your instruction, I changed my SP, removing all "return" and use "select".

    Now the rerturn value is still the number of the rows affacted. My new sp is as follows,

    ALTER PROCEDURE [dbo].DeleteInvByInvoiceId
     @invoiceId int,
     @IsHeader bit
    AS
    BEGIN
        declare @err int
       
        begin transaction
     
      delete from Invoice where InvoiceId = @invoiceId;
      select @err = @@Error
     
      if @err <> 0
       rollback transaction
      else
      begin
       if @IsHeader = 1
        update site set HeadInvId = NULL where HeadInvId = @invoiceId
       else
        update site set FooterId = NULL where FooterId = @invoiceId
      
       select @err = @@Error
       if @err <> 0
        rollback transaction
      end
     
     commit transaction 
     if @err = 0
      select @err = @@Error
     else
      select @err  
    END

    Peter

    Monday, September 26, 2011 2:16 AM
  • Hi Peter,

    Thanks for your feedback.

    You should add "select @ err" at the end of your judgment,

    if @err = 0
      select @err = @@Error
     else
      select @err  
    

    EF won't treat it as return value, you should:

    if @err = 0
      select @err = @@Error
     else
      select @err  
    select @err
    

    and you should modify your import SP return value to "Int32"

      using (var context= new EFTestEntities())
                {              
                    int? rtvalue = context.DeleteInvByInvoiceId(3).First();       
                }
    

    Have a nice day.



    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by peter 9 Tuesday, September 27, 2011 11:33 PM
    Monday, September 26, 2011 5:52 AM
    Moderator
  • Hi Alan,

     

    Thank you very much. Your answer solved my porblem.

     

     

    Peter

    Tuesday, September 27, 2011 11:33 PM
  • Hi Peter,

    You are welcome, I'm glad to help.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, September 28, 2011 2:32 AM
    Moderator