none
VB.net receive response from a stored procedure in DB2 RRS feed

  • Question

  • I am trying to get the return of a stored procedure in a DB2 database. I followed samples I found on the 'net and created the code below. It does touch the SP, and creates a return. But.. I can't seem to be able to fill the catalog as needed. I know its Christmas eve, but this is driving me nuts! What is going wrong?

    Merry Christmas (to those that follow the holiday)..

    Julie

       Dim CMD = New OleDbCommand
    
            CMD.CommandType = CommandType.StoredProcedure
            CMD.CommandText = "STprocedure"
            CMD.Connection = MOHconn
    
    ‘************************************* Input
            CMD.Parameters.Add("P_CUSTOMER", OleDbType.Integer, 11).Value = Acct
            CMD.Parameters("P_CUSTOMER").Direction = ParameterDirection.Input
            CMD.Parameters.Add("P_ITEM", OleDbType.VarChar, 50).Value = Item
            CMD.Parameters("P_Item").Direction = ParameterDirection.Input
            CMD.Parameters.Add("P_REQ_QTY", OleDbType.Integer, 11).Value = Qty
            CMD.Parameters("P_REQ_Qty").Direction = ParameterDirection.Input
            CMD.Parameters.Add("P_ZIP", OleDbType.VarChar, 5).Value = Zip
            CMD.Parameters("P_Zip").Direction = ParameterDirection.Input
    
    ‘*************************************** Output
            Dim RetItem = CMD.Parameters.Add("OP_Item", OleDbType.VarChar, 11)
            RetItem.Direction = ParameterDirection.Output
    
            Dim RetCart_Qty = CMD.Parameters.Add("OP_Cart_Qty", OleDbType.VarChar, 11)
            RetCart_Qty.Direction = ParameterDirection.Output
    
            Dim RetUnit_Price = CMD.Parameters.Add("OP_Unit_Price", OleDbType.VarChar, 11)
            RetUnit_Price.Direction = ParameterDirection.Output
    
            Dim RetZIP = CMD.Parameters.Add("OP_ZIP", OleDbType.VarChar, 11)
            RetZIP.Direction = ParameterDirection.Output
    
            Dim RetCustomer = CMD.Parameters.Add("OP_Customer", OleDbType.VarChar, 11)
            RetCustomer.Direction = ParameterDirection.Output
    
                CMD.ExecuteNonQuery()
    
                PriceList.Add(New PriceReturn() With {
                                 .ZIP = Trim(CMD.Parameters("OP_ZIP").Value()),
                                 .ITEM = Trim(CMD.Parameters("OP_ITEM").Value()),
                                 .CART_QTY = CMD.Parameters("OP_CART_QTY").Value(),
                                 .UNIT_PRICE = CMD.Parameters("OP_UNIT_PRICE").Value(),
                                 .CUSTOMER = CMD.Parameters("OP_CUSTOMER").Value()
                                })
    

    Monday, December 25, 2017 9:31 AM

All replies

  • Julie,

    Merry Christmas too.

    First of all, this is a 24 hours active forum, so there is no morning or eve because that is for most contributors different. 

    However, to give a solution. Start first with a very simple windows form project, where you only run the Stored procedure with a command.ExecuteScalar and look then what kind of result you get. 

    https://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.executescalar(v=vs.110).aspx


    Success Cor



    Monday, December 25, 2017 12:02 PM
  • Hi juliemac,

    According to your description and your code, I don't find any error. I find that there are five value output, so have you define these five parameter output in Store Procedure? Can you please share your Store procedure here?

    I think  CMD.Parameters("parameter").Direction = ParameterDirection.Input
    is not needed, you can delete these.

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, December 26, 2017 5:30 AM
    Moderator