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

  • Question

  • I have stored procedure that when I run it from Management Studio it works fine, returning an output parameter as well as a return code.  The  code behind runs, but in the try catch code returns an error condition.  Interestingly, the record is added to the table, so I'm not sure what is going on.  If someone could help I would appreciate it.

    Thanks,
    Tom

    ALTER PROCEDURE [dbo].[AddHose]
    @ID int,
    @DeptID int,
    @HoseID varchar(20),
    @HoseSize int,
    @HoseLength int,
    @HoseLocation int,
    @Manufacturer varchar(50)=null,
    @ManufacturerCode varchar(50)=null,
    @ManufacturerDate smalldatetime=null,
    @ServiceDate smalldatetime=null,
    @DiscardDate smalldatetime=null,
    @Comment varchar(255)=null,
    @NewID int output

    AS
    BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.

       SET NOCOUNT ON
       SET FMTONLY OFF;

        if @ID = 0
           begin
             if exists(select ID from Hose where DeptID = @DeptID and HoseID = @HoseID)
                begin
                   select 99
                end
             else
                begin
                  insert into hose (DeptID,HoseID,HoseSize,HoseLength,HoseLocation,Manufacturer,ManufacturerCode,ManufacturerDate,
                  ServiceDate,DiscardDate,Comment)
                  values (@DeptID,@HoseID,@HoseSize,@HoseLength,@HoseLocation,@Manufacturer,@ManufacturerCode,@ManufacturerDate,
                  @ServiceDate,@DiscardDate,@Comment)
                  if @@Error = 0
                     begin
                       select @NewID = @@Identity
                     end
                  else
                     begin
                       select @@ERROR
                     end   
               end
           end
        else
           begin
             update hose
                set DeptID = @DeptID,
                    HoseID = @HoseID,
                    HoseSize = @HoseSize ,
                    HoseLength = @HoseLength ,
                    HoseLocation = @HoseLocation,
                    Manufacturer = @Manufacturer ,
                    ManufacturerCode = @ManufacturerCode ,
                    ManufacturerDate = @ManufacturerDate ,
                    ServiceDate = @ServiceDate ,
                    DiscardDate = @DiscardDate ,
                    Comment = @Comment
                where ID = @ID
                select @@Error
           end
    END

    Code behind:

     try
                 {
                     using (sfhsEntities context = new sfhsEntities())
                     {
                         System.Data.Objects.ObjectParameter SoutputParameter = new System.Data.Objects.ObjectParameter("NewID", typeof(int));
                         var rc = context.AddHose(Convert.ToInt32(this.txtCurID.Value),
                             clsDept.DeptID,
                             this.txtHoseID.Text,
                             Convert.ToInt32(this.cboHoseSize.SelectedValue),
                             Convert.ToInt32(this.cboHoseLength.SelectedValue),
                             Convert.ToInt32(this.cboHoseLocation.SelectedValue),
                             vManufacturer,
                             VManufacturerCode,
                             vManufacturerDate,
                             vServiceDate,
                             vDiscardDate,
                             vComment,
                             SoutputParameter).FirstOrDefault();
                         if (rc != null)
                         {
                             if (rc == 99)
                             {
                                 this.lblDuplicate.Text = "The Hose ID Entered Already Exists.";
                                 return false;
                             }
                         }
                         NewID = (int)SoutputParameter.Value;
                         if (NewID > 0)
                         {
                             rtnCode = AddHoseInspection();
                             if (!rtnCode)
                             {
                                 this.txtSuccess.Value = "N";
                                 this.lblErrMsg.Text = ErrorMessage + " Please Try Again.";
                                 this.lblErrMsg.Visible = true;
                                 return false;
                             }
                         }
                         this.lblErrMsg.ForeColor = System.Drawing.Color.White;
                         this.lblErrMsg.BackColor = System.Drawing.Color.Green;
                         this.txtSuccess.Value = "Y";
                         this.lblErrMsg.Text = "New Hose Added Without Problem...";
                         this.lblErrMsg.Visible = true;
                         Reset();
                         return true;
                     }
                 }
                 catch (Exception e)
                 {
                     this.txtSuccess.Value = "N";
                     this.lblErrMsg.Text = e.Message.ToString() + " Please Try Again.";
                     this.lblErrMsg.Visible = true;
                     return false;
                 }
             }

    Tuesday, September 18, 2012 1:36 AM

Answers

  • Hi Tom Loach,

    Welcome to MSDN Forum.

    Using output parameter in Entity Framework, you should set the return type of this imported function to 'None', not 'int'. : )

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, September 19, 2012 1:34 AM
    Moderator

All replies