Updating Optional Fields With Values

[SMI168]

Hi There,
I've updated my customers on AccPac with 4 optional fields but now i need to add values to 1 of these options. I need my code to recognize the option i want to add my value to and insert the value under this option. I cant seem to get my code to locate the option that i want to add the value to. Please help, my code goes like this :

Imports System
Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Drawing
Imports System.Linq
Imports System.Text
Imports System.Windows.Forms
Imports ACCPAC.Advantage
Public Class Form1
'Initialise session to ACCPAC...
Public session As Session = Nothing
Public mDBLinkCmpRW As DBLink = Nothing

'Customers Insert/Update Views...
Dim ARCUSTOMER1header As ACCPAC.Advantage.View
Dim ARCUSTOMER1headerFields As ACCPAC.Advantage.ViewFields
Dim ARCUSTOMER1detail As ACCPAC.Advantage.View
Dim ARCUSTOMER1detailFields As ACCPAC.Advantage.ViewFields

Dim ARCUSTOMER20detail As ACCPAC.Advantage.View
Dim ARCUSTOMER20detailFields As ACCPAC.Advantage.ViewFields


Dim ARCUSTSTAT2 As ACCPAC.Advantage.View
Dim ARCUSTSTAT2Fields As ACCPAC.Advantage.ViewFields
Dim ARCUSTCMT3 As ACCPAC.Advantage.View
Dim ARCUSTCMT3Fields As ACCPAC.Advantage.ViewFields
Public Sub InitializeDBLink()
mDBLinkCmpRW = session.OpenDBLink(DBLinkType.Company, DBLinkFlags.ReadWrite)
End Sub
Public Sub OpenCustomer()

'Open Customers View...
ARCUSTOMER1header = mDBLinkCmpRW.OpenView("AR0024")
ARCUSTOMER1headerFields = ARCUSTOMER1header.Fields

ARCUSTOMER1detail = mDBLinkCmpRW.OpenView("AR0400")
ARCUSTOMER1detailFields = ARCUSTOMER1detail.Fields

ARCUSTOMER20detail = mDBLinkCmpRW.OpenView("AR0400")
ARCUSTOMER20detailFields = ARCUSTOMER20detail.Fields

Dim vd1 As ACCPAC.Advantage.View() = {ARCUSTOMER1header}
ARCUSTOMER20detail.Compose(vd1)

Dim vd2 As ACCPAC.Advantage.View() = {ARCUSTOMER1header}
ARCUSTOMER20detail.Compose(vd2)

Dim vd3 As ACCPAC.Advantage.View() = {ARCUSTOMER20detail}
ARCUSTOMER1header.Compose(vd3)


End Sub

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

lblDetails.Refresh()
lblDetails.BackColor = Color.AliceBlue
lblDetails.Text = "Updating Customer Optional Fields: "


'Create session to accpac...
session = New Session()

'Initialise session and login...
session.Init("", "XX", "XX1000", "55A")
session.Open("", "", "", System.DateTime.Now, 0)
'session.Open("", "", "", System.DateTime.Now, 0)

InitializeDBLink()
OpenCustomer()

'Dim temp As Boolean
'temp = ARCUSTOMER1header.Exists
ARCUSTOMER1header.Init()

'temp = ARCUSTOMER20detail.Exists
ARCUSTOMER20detail.Init()

ARCUSTOMER1detail.Init()

'temp = ARCUSTSTAT2.Exists
'ARCUSTSTAT2.Init()

Dim objconnection As New SqlConnection("=;user id=;password=")

Dim SQL As String

SQL = "select * from AI_Customers_Optional_test order by AccNumber"


objconnection.Open()

Dim cmd As New SqlCommand(SQL, objconnection)

Dim ds As New DataSet
Dim SqlDataAdapter = New SqlDataAdapter(cmd).Fill(ds, "AI_Customers_Optional_test")


'Try
On Error GoTo ACCPACErrorHandler

For Each dr As DataRow In ds.Tables(0).Rows

Application.DoEvents()
txtDetail3.Refresh()
txtDetail4.Refresh()
txtDetail3.Text = (dr("AccNumber"))
txtDetail4.Text = (dr("StoreBranch"))

Dim CurrentCust As String
Dim Field As String
CurrentCust = dr("AccNumber").ToString()
Field = dr("Field").ToString()

ARCUSTOMER1header.Browse("(IDCUST = " + CurrentCust + ")", True)
ARCUSTOMER1header.Fetch("0")
If (ARCUSTOMER1headerFields.FieldByName("IDCUST").Value) = CurrentCust Then

ARCUSTOMER20detail.GoBottom()
ARCUSTOMER20detail.RecordClear()
ARCUSTOMER20detail.RecordGenerate(False)

ARCUSTOMER1headerFields.FieldByName("IDCUST").SetValue(dr("AccNumber").ToString(), False)
If (ARCUSTOMER20detailFields.FieldByName("OPTFIELD").Value) = Field Then

ARCUSTOMER20detailFields.FieldByName("VALIFTEXT").SetValue(dr("StoreBranch").ToString(), False)
ARCUSTOMER20detail.Update()

Else
MessageBox.Show("not working", "ACCPAC IE Customers Update", MessageBoxButtons.OK, _
MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
End If

ARCUSTOMER1header.Update()

End If

Next

'Display a message that the record was added...
MessageBox.Show("Customers Updated", "Click OK to continue", MessageBoxButtons.OK, _
MessageBoxIcon.Information, MessageBoxDefaultButton.Button1)

ACCPACErrorHandler:
Dim lCount As Long
Dim lIndex As Long
Dim Errors As ACCPAC.Advantage.Errors

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

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

DisposeConnection()

End Sub
Public Sub DisposeConnection()

'End session to ACCPAC...
mDBLinkCmpRW.Dispose()
session.Dispose()

End Sub
Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
Me.Close()
End Sub
End Class

 

[Jay Converse]

You're using .Update, you need .Insert

 

[SMI168]

Hi tuba007, thanks for the reply, but if i use ARCUSTOMER20detail.Insert() it throws out an error saying that the record already exists.

 

[Jay Converse]

But you said you were adding new values. And with these statements:

ARCUSTOMER20detail.GoBottom()
ARCUSTOMER20detail.RecordClear()
ARCUSTOMER20detail.RecordGenerate(False)

You're adding a new record.

Maybe you should be using ARCUSTOMER20detail.Browse