×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Returning Price Data From Sage 300 2012 view using vb.NET
3

Returning Price Data From Sage 300 2012 view using vb.NET

Returning Price Data From Sage 300 2012 view using vb.NET

(OP)
Can anyone help with the following code? I am trying to return the current pricing for a customer (to allow for sale pricing etc). The code below does not return the correct values. I have put a few MsgBox's to show values. When the code runs, the BILNAME is always correct. (In SAMINC 1200 will return Ronald Black and 1100 Bargain Mart - San Diago) However, the PRICELIST is WRONG (seems to just return the first it encounters) and so too is the price.

When I run the same VBA code in a macro, it's correct. Can you see where the code maybe wrong?

(The project needs to be done in vb.NET so using the MACRO is not an option.)

MY CODE

CODE -->

Public Sub CheckPrice()
        'This checks the PriceToCheck value = the Proper price for the customer and returns true if equals
        Dim mSession As New AccpacCOMAPI.AccpacSession
        mSession = CreateObject("Accpac.Session")
        mSession.Init("", "XY", "XY1000", "61A")

        mSession.Open("ADMIN", "ADMIN", "SAMLTD", Now.Date, 0, "")

        Dim mDBLinkCmpRW As AccpacCOMAPI.AccpacDBLink
        mDBLinkCmpRW = mSession.OpenDBLink(1, 0)

        Dim OEORD1header As AccpacCOMAPI.AccpacView
        Dim OEORD1headerFields As AccpacCOMAPI.AccpacViewFields
        mDBLinkCmpRW.OpenView("OE0520", OEORD1header)
        OEORD1headerFields = OEORD1header.Fields

        Dim OEORD1detail1 As AccpacCOMAPI.AccpacView
        Dim OEORD1detail1Fields As AccpacCOMAPI.AccpacViewFields
        mDBLinkCmpRW.OpenView("OE0500", OEORD1detail1)
        OEORD1detail1Fields = OEORD1detail1.Fields

        Dim OEORD1detail2 As AccpacCOMAPI.AccpacView
        Dim OEORD1detail2Fields As AccpacCOMAPI.AccpacViewFields
        mDBLinkCmpRW.OpenView("OE0740", OEORD1detail2)
        OEORD1detail2Fields = OEORD1detail2.Fields

        Dim OEORD1detail9 As AccpacCOMAPI.AccpacView
        Dim OEORD1detail9Fields As AccpacCOMAPI.AccpacViewFields
        mDBLinkCmpRW.OpenView("OE0502", OEORD1detail9)
        OEORD1detail9Fields = OEORD1detail9.Fields


        'vb.net code to compose arrays
        Dim icViews(4) As AccpacCOMAPI.AccpacView
        icViews(0) = OEORD1detail1
        icViews(1) = Nothing
        icViews(2) = Nothing
        icViews(3) = OEORD1detail2
        OEORD1header.Compose(icViews)

        ReDim icViews(4)
        icViews(0) = OEORD1header
        icViews(1) = Nothing
        icViews(2) = Nothing
        icViews(3) = OEORD1detail9
        OEORD1detail1.Compose(icViews)

        OEORD1detail2.Compose(OEORD1header)
        OEORD1detail9.Compose(OEORD1detail1)



        'enter customer and item
        OEORD1headerFields.FieldByName("CUSTOMER").Value = "1100"                         ' Set Customer Number
        MsgBox(OEORD1headerFields.FieldByName("BILNAME").Value)                           ' Returns Bilname correctly
        MsgBox(OEORD1headerFields.FieldByName("PRICELIST").Value)                         ' Does not return PRICELIST correctly
        OEORD1detail1Fields.FieldByName("ITEM").Value = "A1-105/0"                        ' Set Item
        MsgBox(OEORD1detail1Fields.FieldByName("UNITPRICE").Value)                        ' Return Value - is incorrect
        mDBLinkCmpRW.Close()
        mSession.Close()

        Exit Sub 

RE: Returning Price Data From Sage 300 2012 view using vb.NET

Either get the price from the IC price tables, or .Insert the item first.

RE: Returning Price Data From Sage 300 2012 view using vb.NET

(OP)
Thanks Tuba2007.

Am I going about this the correct way to get the price applicable to the customer? (Taking into account sale price etc)

Would it be better to...
  1. Check Customer Pricelist
  2. Then Check Unit Price for that Pricelist
Which view would give the UNITPRICE (taking into account any applicable SALE price)?

Much apprciated.

RE: Returning Price Data From Sage 300 2012 view using vb.NET

Do the .Insert

RE: Returning Price Data From Sage 300 2012 view using vb.NET

Going with the order entry view is the easiest way to get the correct price. OE will take all sorts of things into account such as contract prices, sale prices, expiry dates, quantity discounts, etc.

You can actually write a whole bunch of code to figure that out yourself, without OE. If your client doesn't have OE, for example, the code you've written isn't going to work.

RE: Returning Price Data From Sage 300 2012 view using vb.NET

(OP)

CODE -->

Thanks DjangMan,
The client does have OE.  However I am having some difficulty getting the .Insert done.  It keeps producing an error. (HRESULT E_FAIL has been returned from a call to a COM component.)  I've tried to mirror the VBA code in vb.NET but I'm missing something.

The code is the same as above except for the latter section which I've shown below

       'enter customer and item
        OEORD1headerFields.FieldByName("CUSTOMER").Value = "1100"                         ' Set Customer Number
        MsgBox(OEORD1headerFields.FieldByName("BILNAME").Value)                           ' Returns Bilname correctly
        MsgBox(OEORD1headerFields.FieldByName("PRICELIST").Value)                         ' Does not return PRICELIST correctly

        OEORD1header.Process()
        Dim temp = OEORD1detail1.Exists
        OEORD1detail1.RecordClear()
        temp = OEORD1detail1.Exists
        OEORD1detail1.RecordCreate(0)
        OEORD1detail1Fields.FieldByName("ITEM").Value = "A1-103/0"                        ' Item
        OEORD1detail1Fields.FieldByName("PROCESSCMD").PutWithoutVerification("1")         ' Process Command
        
        OEORD1detail1.Process()                                                           ' errors here                                    
        OEORD1detail1.Insert()
        OEORD1detail1Fields.FieldByName("LINENUM").PutWithoutVerification("-1")           ' Line Number
        OEORD1detail1.Read()
        temp = OEORD1detail1.Exists
        OEORD1detail1.RecordCreate(0)
        
        MsgBox(OEORD1detail1Fields.FieldByName("UNITPRICE").Value)                        ' Return Value - is incorrect
        
        mDBLinkCmpRW.Close()
        mSession.Close() 

I can't see where I've gone wrong. The equivelent VBA code works fine.

Ideas?

Thanks again.

RE: Returning Price Data From Sage 300 2012 view using vb.NET

1. Remove all "temp = " lines, they're junk from recording
2. LOL. You did a .Insert, then a .RecordCreate, which starts a new line! Check it after the .Insert.

RE: Returning Price Data From Sage 300 2012 view using vb.NET

(OP)
Tuba2007 Thanks!!

Too many late hours staring at nothing!

RE: Returning Price Data From Sage 300 2012 view using vb.NET

(OP)
Folks, I'm missing something here with this code - I know it has to be with the vb.NET difference - and I know you hate it, but if you have achieved what I am trying to do in vb.NET or see what I am still doing wrong I would be grateful.

See the relevant part of the cleaned up code below. (See above for the code that comes before this section) It is still failing on .insert !!

CODE -->

'vb.net code to compose arrays
        Dim icViews(4) As AccpacCOMAPI.AccpacView
        icViews(0) = OEORD1detail1
        icViews(1) = Nothing
        icViews(2) = Nothing
        icViews(3) = OEORD1detail2
        OEORD1header.Compose(icViews)

        ReDim icViews(4)
        icViews(0) = OEORD1header
        icViews(1) = Nothing
        icViews(2) = Nothing
        icViews(3) = OEORD1detail9
        OEORD1detail1.Compose(icViews)

        OEORD1detail2.Compose(OEORD1header)
        OEORD1detail9.Compose(OEORD1detail1)


        ORDERHEADER.Cancel()
        ORDERHEADER.Init()


        'enter customer and item
        ORDHEADERFIELDS.FieldByName("CUSTOMER").Value = "1200"                         ' Set Customer Number
        ORDDETAILFIELDS1.FieldByName("LINENUM").Value = 0
        ORDDETAILFIELDS1.FieldByName("ITEM").Value = "A1-320/0"                        ' Item
        ORDDETAILFIELDS1.FieldByName("QTYORDERED").Value = "1.0000"                    ' Quantity Ordered
        
        ORDDETAIL1.Insert()                                                            ' This errors in vb.NET
        
        MsgBox(ORDDETAILFIELDS1.FieldByName("UNITPRICE").Value)                        ' Return Value - is incorrect
        MsgBox(ORDHEADERFIELDS.FieldByName("BILNAME").Value)                           ' Returns Bilname correctly
        MsgBox(ORDHEADERFIELDS.FieldByName("PRICELIST").Value)                         ' Does not return PRICELIST correctly


        mDBLinkCmpRW.Close()
        mSession.Close() 

RE: Returning Price Data From Sage 300 2012 view using vb.NET

Throw in your error trapping code. OE is fairly good at telling you what the problem is.

However, just eyeballing you haven't created a detail record. Here's some old code I used to do the same:

CODE

OEORD1header.RecordClear
      OEORD1header.RecordCreate VIEW_RECORD_CREATE_DELAYKEY
      
      OEORD1header.Fields("CUSTOMER").value = aCustNo
      OEORD1header.Fields("PRICELIST").value = aPriceList
      OEORD1detail1.RecordClear
      OEORD1detail1.RecordCreate 0
    
      OEORD1detail1.Fields("ITEM").value = aItemNo
      OEORD1detail1.Process
      OEORD1detail1.Fields("QTYORDERED").value = CDbl(texQty.Text)
      GetCustomerPrice = FmtNumber(OEORD1detail1.Fields("UNITPRICE").value, OEORD1detail1.Fields("UNITPRCDEC").value) 

RE: Returning Price Data From Sage 300 2012 view using vb.NET

(OP)
Sorry, for complete clarity I should post the whole code now. I've worked on it a bit so here it is...

CODE -->

Public Sub CheckPrice()
        'This checks the PriceToCheck value = the Proper price for the customer and returns true if equals
        Dim mSession As New AccpacCOMAPI.AccpacSession
        mSession = CreateObject("Accpac.Session")
        mSession.Init("", "XY", "XY1000", "61A")

        mSession.Open("ADMIN", "ADMIN", "SAMLTD", Now.Date, 0, "")

        'On Error GoTo ACCPACErrorHandler


        Dim mDBLinkCmpRW As AccpacCOMAPI.AccpacDBLink
        mDBLinkCmpRW = mSession.OpenDBLink(1, 0)

        Dim ORDERHEADER As AccpacCOMAPI.AccpacView
        Dim ORDHEADERFIELDS As AccpacCOMAPI.AccpacViewFields
        mDBLinkCmpRW.OpenView("OE0520", ORDERHEADER)
        ORDHEADERFIELDS = ORDERHEADER.Fields

        Dim ORDDETAIL1 As AccpacCOMAPI.AccpacView
        Dim ORDDETAILFIELDS1 As AccpacCOMAPI.AccpacViewFields
        mDBLinkCmpRW.OpenView("OE0500", ORDDETAIL1)
        ORDDETAILFIELDS1 = ORDDETAIL1.Fields

        Dim ORDDETAIL2 As AccpacCOMAPI.AccpacView
        Dim ORDDETAILFIELDS2 As AccpacCOMAPI.AccpacViewFields
        mDBLinkCmpRW.OpenView("OE0740", ORDDETAIL2)
        ORDDETAILFIELDS2 = ORDDETAIL2.Fields

        Dim ORDDETAIL9 As AccpacCOMAPI.AccpacView
        Dim ORDDETAILFIELDS9 As AccpacCOMAPI.AccpacViewFields
        mDBLinkCmpRW.OpenView("OE0502", ORDDETAIL9)
        ORDDETAILFIELDS9 = ORDDETAIL9.Fields


        'vb.net code to compose arrays
        Dim icViews(4) As AccpacCOMAPI.AccpacView
        icViews(0) = ORDDETAIL1
        icViews(1) = Nothing
        icViews(2) = Nothing
        icViews(3) = ORDDETAIL2
        ORDERHEADER.Compose(icViews)

        ReDim icViews(4)
        icViews(0) = ORDERHEADER
        icViews(1) = Nothing
        icViews(2) = Nothing
        icViews(3) = ORDDETAIL9
        ORDDETAIL1.Compose(icViews)

        ReDim icViews(1)
        icViews(0) = ORDERHEADER
        ORDDETAIL2.Compose(icViews)

        ReDim icViews(1)
        icViews(0) = ORDDETAIL1
        ORDDETAIL9.Compose(icViews)

        ORDERHEADER.Cancel()
        ORDERHEADER.Init()


        'enter customer and item
        ORDHEADERFIELDS.FieldByName("CUSTOMER").Value = "1200"                         ' Set Customer Number
        ORDDETAILFIELDS1.FieldByName("ITEM").Value = "A1-320/0"                        ' Item
        ORDDETAILFIELDS1.FieldByName("QTYORDERED").Value = "1.0000"                    ' Quantity Ordered
       
        ORDDETAIL1.Insert()                                                            ' THIS IS WHERE IT ERRORS

        MsgBox(ORDDETAILFIELDS1.FieldByName("UNITPRICE").Value)                        ' Return Value - is incorrect
        MsgBox(ORDHEADERFIELDS.FieldByName("BILNAME").Value)                           ' Returns Bilname correctly
        MsgBox(ORDHEADERFIELDS.FieldByName("PRICELIST").Value)                         ' Does not return PRICELIST correctly


        mDBLinkCmpRW.Close()
        mSession.Close()

        Exit Sub

ACCPACErrorHandler:

        Dim lCount As Long
        Dim lIndex As Long
        Dim Errors As AccpacCOMAPI.AccpacErrors


        If Errors Is Nothing Then
            MsgBox(Err.Description)
        Else
            lCount = Errors.Count

            If lCount = 0 Then
                MsgBox(Err.Description)
            Else
                For lIndex = 0 To lCount - 1
                    MsgBox(Errors.Item(lIndex))
                Next
                Errors.Clear()
            End If
            Resume Next

        End If
    End Sub 

RE: Returning Price Data From Sage 300 2012 view using vb.NET

Price list and location?

RE: Returning Price Data From Sage 300 2012 view using vb.NET

And the order date. Some on the information, like Price List and Location could be defaulting from the customer. But they should be specified within the code.

And you still aren't creating a new detail line record.

Plus - don't skip on your compositions. Do all of them.

RE: Returning Price Data From Sage 300 2012 view using vb.NET

(OP)
Ok, thanks - I'll go back and redo and try, try again.
Many thx with your patience!!!

RE: Returning Price Data From Sage 300 2012 view using vb.NET

You're missing this line
ORDDETAILFIELDS1.RecordCreate(0)
before this line
ORDDETAILFIELDS1.FieldByName("ITEM").Value = "A1-320/0"

RE: Returning Price Data From Sage 300 2012 view using vb.NET

(OP)
A big thank you to all your help. Each one helped me get a solution, even if it was not the method I first started with. I just could not get the ACCPACCOMAPI to give me the correct values, even though the very same VBA version of the code would. So, in another leap forward for me (I know many of you may consider it in the other direction mad) was to try the Sage 300 ERP .Net API (Accpac.Advantage)

After modifying the code to suit, I am pleased to report it now works fine. For those that read this in the days and weeks to come, I present the working code below. Thanks again. (Until the next glitch)

CODE -->

Sub test()
      
        Dim mSession As New Session
        mSession.Init("", "XY", "XY1000", "62A")
        mSession.Open("ADMIN", "ADMIN", "SAMINC", DateTime.Today, 0)
        Dim mDBLinkCmpRW As DBLink = mSession.OpenDBLink(DBLinkType.Company, DBLinkFlags.ReadWrite)

        Dim ORDERHEADER As View = mDBLinkCmpRW.OpenView("OE0520")
        Dim ORDDETAIL1 As View = mDBLinkCmpRW.OpenView("OE0500")
        Dim ORDDETAIL2 As View = mDBLinkCmpRW.OpenView("OE0740")
        Dim ORDDETAIL3 As View = mDBLinkCmpRW.OpenView("OE0180")
        Dim ORDDETAIL4 As View = mDBLinkCmpRW.OpenView("OE0526")
        Dim ORDDETAIL5 As View = mDBLinkCmpRW.OpenView("OE0522")
        Dim ORDDETAIL6 As View = mDBLinkCmpRW.OpenView("OE0508")
        Dim ORDDETAIL7 As View = mDBLinkCmpRW.OpenView("OE0507")
        Dim ORDDETAIL8 As View = mDBLinkCmpRW.OpenView("OE0501")
        Dim ORDDETAIL9 As View = mDBLinkCmpRW.OpenView("OE0502")
        Dim ORDDETAIL10 As View = mDBLinkCmpRW.OpenView("OE0504")
        Dim ORDDETAIL11 As View = mDBLinkCmpRW.OpenView("OE0506")
        Dim ORDDETAIL12 As View = mDBLinkCmpRW.OpenView("OE0503")

        'vb.net code to compose arrays
        Dim icViews(6) As View
        icViews(0) = ORDDETAIL1
        icViews(1) = Nothing
        icViews(2) = ORDDETAIL3
        icViews(3) = ORDDETAIL2
        icViews(4) = ORDDETAIL4
        icViews(5) = ORDDETAIL5
        ORDERHEADER.Compose(icViews)

        ReDim icViews(6)
        icViews(0) = ORDERHEADER
        icViews(1) = ORDDETAIL8
        icViews(2) = ORDDETAIL12
        icViews(3) = ORDDETAIL9
        icViews(4) = ORDDETAIL6
        icViews(5) = ORDDETAIL7
        ORDDETAIL1.Compose(icViews)

        ReDim icViews(1)
        icViews(0) = ORDERHEADER
        ORDDETAIL2.Compose(icViews)


        ReDim icViews(2)
        icViews(0) = ORDERHEADER
        icViews(1) = ORDDETAIL1
        ORDDETAIL3.Compose(icViews)

        ReDim icViews(1)
        icViews(0) = ORDERHEADER
        ORDDETAIL4.Compose(icViews)

        ReDim icViews(1)
        icViews(0) = ORDERHEADER
        ORDDETAIL5.Compose(icViews)

        ReDim icViews(1)
        icViews(0) = ORDDETAIL1
        ORDDETAIL6.Compose(icViews)

        ReDim icViews(1)
        icViews(0) = ORDDETAIL1
        ORDDETAIL7.Compose(icViews)

        ReDim icViews(1)
        icViews(0) = ORDDETAIL1
        ORDDETAIL8.Compose(icViews)

        ReDim icViews(3)
        icViews(0) = ORDDETAIL1
        icViews(1) = ORDDETAIL10
        icViews(2) = ORDDETAIL11
        ORDDETAIL9.Compose(icViews)

        ReDim icViews(1)
        icViews(0) = ORDDETAIL9
        ORDDETAIL10.Compose(icViews)

        ReDim icViews(1)
        icViews(0) = ORDDETAIL9
        ORDDETAIL11.Compose(icViews)

        ReDim icViews(1)
        icViews(0) = ORDDETAIL1
        ORDDETAIL12.Compose(icViews)


        'set customer and item #
        ORDERHEADER.Fields.FieldByName("CUSTOMER").SetValue("1200", False)
        ORDDETAIL1.Fields.FieldByName("ITEM").SetValue("A1-320/0", False)

        'test output
        MsgBox(ORDDETAIL1.Fields.FieldByName("UNITPRICE").Value)
        MsgBox(ORDERHEADER.Fields.FieldByName("BILNAME").Value)
        MsgBox(ORDERHEADER.Fields.FieldByName("PRICELIST").Value)


        mDBLinkCmpRW.Dispose()
        mSession.Dispose()


    End Sub 

Again,
Thank you!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close