Browsing 2 different Fields in SQL using VB .NET to update Accpac

[SMI168]

Hi there, I'm currently inserting invoices from a staging table into accpac. The problem i have is that i need to check that if my custnumber field AND docnumber fields in my current datarow is the same as the record i've previously inserted. If it is, i must add just the detail to the previous header else i must create a new header. I make use of the Browse statement to do the read, but it only seems to be reading the custnumber and not the docnumber. Can someone please tell me how to browse for both custnumber and docnumber and fetch both values. Please note i do nat make use of the COMAPI dll. My code goes like this :

Private Sub btnInsert3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert3.Click

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

'Initialise session and login...
session.Init("", "XX", "XX0001", "55")
session.Open("USER1", "ACCPAC", "CLDDAT", System.DateTime.Now, 0)

InitializeDBLink()
OpenInvoice()

Dim temp As Boolean
temp = ARINVOICE1batch.Exists
ARINVOICE1batch.Init()

temp = ARINVOICE1header.Exists
ARINVOICE1header.Init()

temp = ARINVOICE1detail1.Exists
ARINVOICE1detail1.Init()

temp = ARINVOICE1detail2.Exists
ARINVOICE1detail2.Init()

temp = ARINVOICE1detail3.Exists
ARINVOICE1detail2.Init()

temp = ARINVOICE1detail4.Exists
ARINVOICE1detail2.Init()

Dim objconnection As New SqlConnection("server=mssql-fin.hosting.local;database=INT_TCC_INTEGRATION;user id=****;password=****")
Dim SQL As String

SQL = "select * from ai_invoices order by BatchDesc, HeaderCustNumber, HeaderDocNumber, HeaderOrderNumber"

objconnection.Open()

Dim cmd As New SqlCommand(SQL, objconnection)

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

Dim BatchDesc As String
BatchDesc = ""

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

ListBox1.Refresh()
ListBox2.Refresh()
ListBox1.Items.Add(dr("HeaderCustNumber"))
ListBox2.Items.Add(dr("HeaderDocNumber"))

If BatchDesc <> dr("BatchDesc").ToString() Then
'Create the batch
ARINVOICE1batchFields.FieldByName("PROCESSCMD").SetValue("1", False)
ARINVOICE1batch.RecordCreate(1)
ARINVOICE1batchFields.FieldByName("BTCHDESC").SetValue(dr("BatchDesc").ToString(), False)
ARINVOICE1batch.Update()

'Set the description to storage
BatchDesc = dr("BatchDesc").ToString()
End If


Dim CurrentCust As String
Dim CurrentDoc As String
CurrentCust = dr("HeaderCustNumber").ToString()
CurrentDoc = dr("HeaderDocNumber").ToString()

ARINVOICE1header.Browse("(IDCUST = " + CurrentCust + ")", True)
ARINVOICE1header.Browse("(IDINVC = " + CurrentDoc + ")", True)

If ARINVOICE1header.Fetch("0") = True Then
ARINVOICE1detail1.GoBottom()
ARINVOICE1detail1.RecordClear()
ARINVOICE1detail1.RecordGenerate(False)
ARINVOICE1detail1Fields.FieldByName("IDACCTREV").SetValue(dr("DetailRevAccount").ToString(), False)
ARINVOICE1detail1Fields.FieldByName("AMTEXTN").SetValue(dr("DetailExclValue").ToString(), False)
ARINVOICE1detail1Fields.FieldByName("TAXSTTS1").SetValue(dr("DetailTaxStatus").ToString(), False)
ARINVOICE1detail1Fields.FieldByName("SWDISCABL").SetValue("0".ToString(), False)
ARINVOICE1detail1.Insert()
ARINVOICE1header.Update()

Else

ARINVOICE1header.RecordClear()
ARINVOICE1header.RecordGenerate(False)
ARINVOICE1headerFields.FieldByName("INVCTYPE").SetValue(dr("HeaderInvType").ToString(), False)
ARINVOICE1headerFields.FieldByName("IDCUST").SetValue(dr("HeaderCustNumber").ToString(), False)
ARINVOICE1headerFields.FieldByName("IDINVC").SetValue(dr("HeaderDocNumber").ToString(), False)
ARINVOICE1headerFields.FieldByName("TAXSTTS1").SetValue(dr("DetailTaxStatus").ToString(), False)
ARINVOICE1detail1Fields.FieldByName("IDACCTREV").SetValue(dr("DetailRevAccount").ToString(), False)
ARINVOICE1detail1Fields.FieldByName("AMTEXTN").SetValue(dr("DetailExclValue").ToString(), False)
ARINVOICE1headerFields.FieldByName("SPECINST").SetValue(dr("HeaderInstructions").ToString(), False)
ARINVOICE1headerFields.FieldByName("ORDRNBR").SetValue(dr("HeaderOrderNumber").ToString(), False)
ARINVOICE1headerFields.FieldByName("CUSTPO").SetValue(dr("HeaderPO").ToString(), False)
ARINVOICE1headerFields.FieldByName("IDSHIPNBR").SetValue(dr("HeaderShipNumber").ToString(), False)

ARINVOICE1detail1.GoBottom()
ARINVOICE1detail1.RecordClear()
ARINVOICE1detail1.RecordGenerate(False)
ARINVOICE1detail1Fields.FieldByName("IDACCTREV").SetValue(dr("DetailRevAccount").ToString(), False)
ARINVOICE1detail1Fields.FieldByName("AMTEXTN").SetValue(dr("DetailExclValue").ToString(), False)
ARINVOICE1detail1Fields.FieldByName("TAXSTTS1").SetValue(dr("DetailTaxStatus").ToString(), False)
ARINVOICE1detail1Fields.FieldByName("SWDISCABL").SetValue("0".ToString(), False)
ARINVOICE1detail1.Insert()
ARINVOICE1header.Insert()

End If
End If
Next

Try

'Display a message that the record was added...
MessageBox.Show("Invoices Added", "Click OK to continue", MessageBoxButtons.OK, _
MessageBoxIcon.Information, MessageBoxDefaultButton.Button1)
Catch SqlExceptionErr As SqlException
MessageBox.Show("ACCPAR ERROR", "ACCPAC IE Customers", MessageBoxButtons.OK, _
MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
End Try


DisposeConnection()
End Sub

 

[ettienne]

The invoice number must be unique since you are creating AR invoices, so sort the records in your table by invoice number and then use a variable to store the last invoice no processed and if the current invoice no = last invoice no then add it to the current header, otherwise insert the header and create a new header.

 

[ettienne]

In your code line 2 overrides line 1, so the only .Browse is what is in line 2.

ARINVOICE1header.Browse("(IDCUST = " + CurrentCust + ")", True)
ARINVOICE1header.Browse("(IDINVC = " + CurrentDoc + ")", True)

You need to do

ARINVOICE1header.Browse("(IDCUST = " + CurrentCust + " AND IDINVC = " + CurrentDoc + ")", True)

 

[ettienne]

These lines serve no purpose, comment them out and see if it makes any difference.

Dim temp As Boolean
temp = ARINVOICE1batch.Exists
ARINVOICE1batch.Init()

temp = ARINVOICE1header.Exists
ARINVOICE1header.Init()

temp = ARINVOICE1detail1.Exists
ARINVOICE1detail1.Init()

temp = ARINVOICE1detail2.Exists
ARINVOICE1detail2.Init()

temp = ARINVOICE1detail3.Exists
ARINVOICE1detail2.Init()

temp = ARINVOICE1detail4.Exists
ARINVOICE1detail2.Init()

ARINVOICE1batchFields.FieldByName("PROCESSCMD").SetValue("1", False)

ARINVOICE1detail1.GoBottom()
ARINVOICE1detail1.RecordClear()

ARINVOICE1header.RecordClear()

ARINVOICE1detail1.GoBottom()
ARINVOICE1detail1.RecordClear()