Problem with inserting into Accpac with VB.Net

[Shacktar]

Hi all,

I've figured out the syntax for using the COMAPI with Visual Basic.Net. But, I am having trouble actually inserting data into the detail (AR0042). When I call ".Insert", the program gives an "Unspecified error" (COMException) and crashes. Here is the code I'm using (I'm getting all the information from a data grid):

'Using COMAPI
Dim Session As New AccpacSession
Session.Init("", "AS", "AS1000", "52A")
Session.Open("MYUserName", "MYPassword", "TEST DBase", System.DateTime.Today, 0, "")

Dim link As AccpacCOMAPI.AccpacDBLink
link = Session.OpenDBLink(tagDBLinkTypeEnum.DBLINK_COMPANY, tagDBLinkFlagsEnum.DBLINK_FLG_READWRITE)

Dim ARInvoiceBatch As AccpacCOMAPI.AccpacView
Dim ARInvoiceBatchFields As AccpacCOMAPI.AccpacViewFields
link.OpenView("AR0041", ARInvoiceBatch)
ARInvoiceBatchFields = ARInvoiceBatch.Fields

Dim ARReceiptAdjustment As AccpacCOMAPI.AccpacView
Dim ARReceiptAdjustmentFields As AccpacCOMAPI.AccpacViewFields
link.OpenView("AR0042", ARReceiptAdjustment)
ARReceiptAdjustmentFields = ARReceiptAdjustment.Fields

Dim ARMiscellaneous As AccpacCOMAPI.AccpacView
Dim ARMiscellaneousFields As AccpacCOMAPI.AccpacViewFields
link.OpenView("AR0043", ARMiscellaneous)
ARMiscellaneousFields = ARMiscellaneous.Fields

Dim ARAppliedReceiptAdjustment As AccpacCOMAPI.AccpacView
Dim ARAppliedReceiptAdjustmentFields As AccpacCOMAPI.AccpacViewFields
link.OpenView("AR0044", ARAppliedReceiptAdjustment)
ARAppliedReceiptAdjustmentFields = ARAppliedReceiptAdjustment.Fields

Dim ARAdjustmentGL As AccpacCOMAPI.AccpacView
Dim ARAdjustmentGLFields As AccpacCOMAPI.AccpacViewFields
link.OpenView("AR0045", ARAdjustmentGL)
ARAdjustmentGLFields = ARAdjustmentGL.Fields

Dim openDocument As AccpacCOMAPI.AccpacView
link.OpenView("AR0061", openDocument)

Dim headerArray(2) As AccpacCOMAPI.AccpacView
headerArray(0) = ARInvoiceBatch
headerArray(1) = ARMiscellaneous
headerArray(2) = ARAppliedReceiptAdjustment

Dim appliedReceiptAdjustmentArray(1) As AccpacCOMAPI.AccpacView
appliedReceiptAdjustmentArray(0) = ARReceiptAdjustment
appliedReceiptAdjustmentArray(1) = ARAdjustmentGL


Dim openDocumentArray(4) As AccpacCOMAPI.AccpacView
openDocumentArray(0) = ARInvoiceBatch
openDocumentArray(1) = ARReceiptAdjustment
openDocumentArray(2) = ARMiscellaneous
openDocumentArray(3) = ARAppliedReceiptAdjustment
openDocumentArray(4) = ARAdjustmentGL

ARInvoiceBatch.Compose(ARReceiptAdjustment)
ARReceiptAdjustment.Compose(headerArray)
ARMiscellaneous.Compose(ARReceiptAdjustment)
ARAppliedReceiptAdjustment.Compose(appliedReceiptAdjustmentArray)
ARAdjustmentGL.Compose(ARAppliedReceiptAdjustment)
openDocument.Compose(openDocumentArray)

Dim newBatchNum As Integer = 3850
Dim foundBatch As Boolean = False

While Not foundBatch
newBatchNum = newBatchNum + 1
ARInvoiceBatch.Browse("CNTBTCH =" & newBatchNum, True)
foundBatch = Not ARInvoiceBatch.Fetch()
End While

ARInvoiceBatch.Fields.FieldByName("BATCHDESC").Value = "Post Dated Cheques for: " & ChequesDataGrid(0, 5)
ARInvoiceBatch.Fields.FieldByName("DATEBTCH").Value = System.DateTime.Today
'Set Batch Type = Imported
ARInvoiceBatch.Fields.FieldByName("BATCHTYPE").Value = 2
'Set Source Application (PDCS)
ARInvoiceBatch.Fields.FieldByName("SRCEAPPL").Value = "PDCS"

Dim index As Integer = 0

ARReceiptAdjustment.Fields.FieldByName("CODEPYMTYP").Value = "CA"
ARReceiptAdjustment.Fields.FieldByName("CNTBTCH").Value = newBatchNum
ARReceiptAdjustment.Fields.FieldByName("CNTITEM").Value = display_set.Tables(0).Rows.Count

For index = 0 To display_set.Tables(0).Rows.Count - 1

ARReceiptAdjustment.Fields.FieldByName("CODEPYMTYP").Value = "CA"
ARReceiptAdjustment.Fields.FieldByName("CNTBTCH").Value = newBatchNum
ARReceiptAdjustment.Fields.FieldByName("CNTITEM").Value = index + 1
ARReceiptAdjustment.Fields.FieldByName("IDCUST").Value = ChequesDataGrid(index, 2)

ARReceiptAdjustment.Fields.FieldByName("DATERMIT").Value = ChequesDataGrid(index, 5)
'not needed, these values are filled in automatically
'ARReceiptAdjustment.Fields.FieldByName("AMTRMITTC").Value = ChequesDataGrid(index, 6)
'ARReceiptAdjustment.Fields.FieldByName("SWRATETC").Value = 0

ARReceiptAdjustment.Fields.FieldByName("CODEPAYM").Value = "CHEQUE"
ARReceiptAdjustment.Fields.FieldByName("CODECURN").Value = "CAD"
ARReceiptAdjustment.Fields.FieldByName("RMITTYPE").Value = 3
ARReceiptAdjustment.Fields.FieldByName("CNTPAYMETR").Value = 1
ARReceiptAdjustment.Fields.FieldByName("AMTRMIT").Value = ChequesDataGrid(index, 6)
ARReceiptAdjustment.Fields.FieldByName("AMTPAYMTC").Value = ChequesDataGrid(index, 6)
ARReceiptAdjustment.Fields.FieldByName("DOCTYPE").Value = 1
ARReceiptAdjustment.Fields.FieldByName("CNTLSTLINE").Value = 1
ARReceiptAdjustment.Fields.FieldByName("FISCYR").Value = Regex.Match(ChequesDataGrid(index, 5), "\d+/\d+/(\d+)").Groups(1).ToString

Dim month As String = Regex.Match(ChequesDataGrid(index, 5), "(\d+)/\d+/\d+").Groups(1).ToString
If Len(month) = 1 Then
month = "0" & month
End If

ARReceiptAdjustment.Fields.FieldByName("IDRMIT").Value = getUniqueID(ChequesDataGrid(index, 0).ToString, index + 1)
ARReceiptAdjustment.Fields.FieldByName("FISCPER").Value = month
ARReceiptAdjustment.Fields.FieldByName("AMTRMITHC").Value = ChequesDataGrid(index, 6)

ARAppliedReceiptAdjustment.Fields.FieldByName("CODEPAYM").Value = "CA"
ARAppliedReceiptAdjustment.Fields.FieldByName("CNTBTCH").Value = newBatchNum
ARAppliedReceiptAdjustment.Fields.FieldByName("CNTITEM").Value = index + 1
ARAppliedReceiptAdjustment.Fields.FieldByName("CNTLINE").Value = 20
ARAppliedReceiptAdjustment.Fields.FieldByName("IDCUST").Value = ChequesDataGrid(index, 2)
ARAppliedReceiptAdjustment.Fields.FieldByName("IDINVC").Value = getInvoiceID(ChequesDataGrid(index, 5).ToString, index + 1)
ARAppliedReceiptAdjustment.Fields.FieldByName("TRXTYPE").Value = 2
ARAppliedReceiptAdjustment.Fields.FieldByName("PYMTRESL").Value = "10"
ARAppliedReceiptAdjustment.Fields.FieldByName("AMTPAYM").Value = ChequesDataGrid(index, 6)
ARAppliedReceiptAdjustment.Fields.FieldByName("CNTLASTSEQ").Value = 1
ARAppliedReceiptAdjustment.Fields.FieldByName("IDPPD").Value = getInvoiceID(ChequesDataGrid(index, 5).ToString, index + 1)
ARAppliedReceiptAdjustment.Fields.FieldByName("CDAPPLYTO").Value = 1
'get trouble here
ARAppliedReceiptAdjustment.Insert()
ARReceiptAdjustment.Insert()
Next

display_message(New Message("Data imported"))

Session.Close()

 

[jayconverse]

The simplest thing to do is record a VBA macro that does the same thing. ARAppliedReceiptAdjustment.Insert() may be out of order, or you may need a .Process() method before the .Insert().

Or, do some error trapping and inspect the Accpac Errors object.

Jay Converse
IT Director
Systemlink, Inc.

 

[JoeAtWork]

Hi,

I'm rather new to Accpac myself but for the last few weeks I've been converting VB/Macro code to C# and perhaps can offer a few of the things I've learned.

First of all, when using the COMApi through .NET, most of the methods seem to want everything passed as a reference to a generic object. Also, most methods return a generic object. That means I have to do a lot of casting back and forth.

I have also found that using the Value property directly does not work. The Exception message I got suggested I use the get_Value() and set_Value(ref object) methods instead. This worked. I also tend to use the PutWithoutVerification method more often than Value - I usually use whichever method the macro recorder used.

Keeping what I said in mind, here is a little snippet of my code:

	System.Object objCustNum = (Object)NewCustomer.CustNumber;
	fldsCustHeader.get_FieldByName("IDCUST").set_Value(ref objCustNum);

	vwCustStats.Init();
	fldsCustStats.get_FieldByName("IDCUST").PutWithoutVerification(ref objCustNum);
	obj = (Object)DateTime.Today.Year.ToString();
	fldsCustStats.get_FieldByName("CNTYR").PutWithoutVerification(ref obj);
	obj = (Object)strMonth;
	fldsCustStats.get_FieldByName("CNTPERD").set_Value(ref obj);

As you can see, I first cast any string (or any other variable data type) to a generic System.Object, which is what I pass by reference to the method. I even have to use casting with the Compose methods of the views, as below:

		obj = (Object)vwCustOptional;
		vwCustHeader.Compose(ref obj);
		obj = (Object)vwCustHeader;
		vwCustOptional.Compose(ref obj);

The one other difference I see between your code and mine is that when I use the OpenView method I have to specify that View parameter is an "out" parameter type. However, I don't know if that's required in VB.NET. Here's what mine looks like anyways:

	// Customer Statistics table: ARCSM
	AccpacCOMAPI.AccpacView vwCustStats = null;
	AccpacCOMAPI.AccpacViewFields fldsCustStats = null;
	dbLink.OpenView("AR0022", out vwCustStats);
	fldsCustStats = vwCustStats.Fields;

As Jay advised, I would check the Accpac session object's Errors collection in my error handler. It is especially useful when you get the unhelpful "Unspecified Error" exception. Here's the code in my central error handler where I gather up all the Accpac errors (which is just a collection of strings):

// Combine all AccPac errors into one string
string AccPacErrors = "";
if (AccPacSes != null)
{
	if (AccPacSes.Errors != null)
	{
		for(int j = 0; j < AccPacSes.Errors.Count; j++)
		{
			AccPacErrors = j.ToString() + ". " + AccPacSes.Errors.Item(j) + "\n\r";
		}
	}
}

Of course, you will need to translate my C# to VB.NET, but I think it's mostly a line per line translation.

Hope I have shed some pearls of wisdom (but a big disclaimer here I take no responsibility if that makes things worse).

 

[Shacktar]

Thanks guys for your replies. Unfortunately, it still doesn't work with the changes I've made. I've looked at the Accpac errors object right as I insert and it gives me lots of errors. Each error has something to do with the fields being blank or invalid. I have checked with the debugger right before the insert, and the views' fields do have the right values. Perhaps the actual fields aren't properly being set through VB. Any ideas?

 

[JoeAtWork]

In my code I always call the Init() method of the view before I try to put values in. So maybe you should add something like:

ARInvoiceBatch.Init()

..just before you start adding the new record. Same for when you start to add values for the ARReceiptAdjustment fields.

 

[Shacktar]

My program is now working! I used the Macro and I have to say, it's a really powerful tool. I just translated the VBA code to .Net and made some minor adjustments, then it worked perfectly! Thanks guys for your input.