locked
Data Access Layer & Return Values from Stored Procs RRS feed

  • Question

  • User121994837 posted

    Dear .NET Pros:

    In the Time Tracker program I liked the DAL that was used so I incorporated it into another project.

    It appears that when Executing Stored Procs the return value always returns -1 even if you return a ZERO in the stored proc.

    I wanted to find out if anyone else encounted this error and found the fix for it?

    Below is the code I am running.

    I find it a little strange that the Time Tracker does not use a return param from the stored proc for standard error
    checking.

    -------------------------------------------------
    VB.NET Code - Return Value always - 1?
    -------------------------------------------------

     Dim intRtnVal As Integer = 0
            Try
                intRtnVal = SqlHelper2.ExecuteNonQuery(ConfigurationSettings.AppSettings("CnnStr"), _
                                                                "AUSP_Update_ItemMaster", _
                                                                wItem.ItemNumber, _
                                                                wItem.Category, _
                                                                wItem.SubCategory, _
                                                                wItem.DescriptionShort, _
                                                                wItem.DescriptionLong, _
                                                                wItem.ImageThumbnail, _
                                                                wItem.ImageLarge, _
                                                                wItem.Price, _
                                                                wItem.Status, _
                                                                 0)
            Catch ex As Exception
                LblErrMsg.Text = ex.Message
                Exit Sub
            End Try

    -------------------------------------------------------------------------
    Stored Proc: Always Returns ZERO for a test, but the DAL gives -1
    -------------------------------------------------------------------------

    ALTER       PROCEDURE AUSP_Update_ItemMaster
     (
     @ItemNumber  varchar (20),
          @MainCategory  char (15),
     @SubCategory  char (2),
     @DescriptionShort varchar (150),
     @DescriptionLong        varchar (1024),
     @ImageThumbnail  image,
     @ImageLarge  image,
     @Price                  money,
            @Status   varchar (1),
     @Error   int OUTPUT
     )
    AS
    SET NOCOUNT ON

    /* See if the record exists */
    Select ItemNumber from AHS_ItemMaster where ItemNumber = @ItemNumber

      
    /* Insert New Record Else Update The Old Record */
    if @@ROWCOUNT = 0
      BEGIN
     INSERT INTO AHS_ItemMaster
                   (ItemNumber, MainCategory, SubCategory, DescriptionShort, DescriptionLong, ImageThumbnail, ImageLarge, Price, Status)
     VALUES (@ItemNumber, @MainCategory, @SubCategory, @DescriptionShort, @DescriptionLong, @ImageThumbnail, @ImageLarge, @Price, @Status)
      END
     Else
     Begin
     UPDATE AHS_ItemMaster
     SET  MainCategory = @MainCategory, SubCategory=@SubCategory, DescriptionShort=@DescriptionShort, DescriptionLong=@DescriptionLong, ImageThumbnail=@ImageThumbnail, ImageLarge=@ImageLarge, Price=@Price, Status=@Status
            WHERE ItemNumber = @ItemNumber
     END

    /*select @Error = @@Error
    Return @Error*/
    return 0
    -------------------------------------------------------------------------------------------------------------------
    Any help would be appreciated.

    Thank you,
    Patrick
    http://www.SURFThru.com

    Tuesday, November 22, 2005 9:22 AM

All replies

  • User1829916646 posted
    Patrick,

    The return value of the ExecuteNonQuery method will give you the number of rows affected by all statements in the stored proc.  However, since the stored proc uses SET NOCOUNT ON, the method's return value will always be -1.

    If you want the return value of the stored proc, you'll need to create a parameter object with Direction of ParameterDirection.ReturnValue, then get the value from that parameter after invoking the method.

    Regards,
    Jake
    Wednesday, November 23, 2005 2:32 AM
  • User121994837 posted

    Hi Jake,

    To clarify I am using the Data Access Blocks and not creating my own Execute.NonQuery. See Next Post.

    Regards,
    Patrick

    <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p> </o:p>

    <o:p> </o:p>

    Wednesday, November 23, 2005 10:48 AM
  • User121994837 posted

    Well now I know.  The return values using the Data Access Blocks is due to the fact you can call the methods using values or SQL Params.  If  you want a return value you have to pass the parms and not By Value to SQLHELPER.

    If you search on RETURN VALUES USING DATA ACCESS BLOCK you will soon see how many people are banging thier heads against the wall.

    Finally after a lot of reading and testing I have it.

    CREATE A PARAM Array
    Dim
    SQLp(1) As SqlParameter

    'My proc has two params Key, and Output

    'Key
    SQLp(0) =
    New SqlParameter("@CartID", strCartID)

    'Output Value
    SQLp(1) =
    New SqlParameter("@ItemsTotal", SqlDbType.Money)
    SQLp(1).Direction = ParameterDirection.Output

    'Forget about ReturnValue = SqlHelper. (THIS DOES NOT WORK!)

    'DO This
    SqlHelper.ExecuteNonQuery(ConfigurationSettings.AppSettings("CnnStr"), _
                                           CommandType.StoredProcedure, _
                                           "AUSP_Select_ShoppingCart_Totals", _
                                              SQLp)

    Guess what? The parm is returned back on the same param that was passed into the DAAB (SEE RED)
    Dim
    sCartTotal As Single = SQLp(1).Value


    Friday, November 25, 2005 1:32 AM
  • User-1145340147 posted

    hi every body....

    can u help me some topics on the subjuct....???

    i'm new to ASP and c#..

    plz help,.....

    help me plz...

    suggest a answer

    Tuesday, August 17, 2010 4:54 PM