Can not insert AP invoice

[NewCCTech]

Hi everyone,

I am working on a program to create AP invoices in ACCPAC 54A with .net and SQL server 2008 developer edition. I got the following error when trying to insert a detail---System.Runtime.InteropServices.COMException (0x80004005): Error HRESULT E_FAIL has been returned from a call to a COM component.at AccpacCOMAPI.AccpacViewClass.Insert(). Can anyone give me suggestion and help me out here?

Thanks!

 

[Jay Converse]

Something basic is wrong with your code, that's a generic error. Show your code and explain more about your environment.

 

[NewCCTech]

Hi tuba2007,

Thanks for your quick response! Here is a piece of my code


//Open Accpac Session
AccpacCOMAPI.AccpacSession pSession;
AccpacCOMAPI.AccpacDBLink pDBLinkCmpRW;
AccpacCOMAPI.AccpacDBLink pDBLinkSysRW;
pSession = new AccpacCOMAPI.AccpacSessionClass();

pSession.Init("", "AP", "AP2100", VERSION);
pSession.Open(USER, PWD, storeID, DateTime.Now, 0, "");

try
{
if (pSession.IsOpened)
{
//Open DBLink
pDBLinkCmpRW = pSession.OpenDBLink(AccpacCOMAPI.tagDBLinkTypeEnum.DBLINK_COMPANY, AccpacCOMAPI.tagDBLinkFlagsEnum.DBLINK_FLG_READWRITE);
pDBLinkSysRW = pSession.OpenDBLink(AccpacCOMAPI.tagDBLinkTypeEnum.DBLINK_SYSTEM, AccpacCOMAPI.tagDBLinkFlagsEnum.DBLINK_FLG_READWRITE);

//Batch
bool temp;
AccpacCOMAPI.AccpacView APINVOICE1batch;
AccpacCOMAPI.AccpacViewFields APINVOICE1batchFields;
pDBLinkCmpRW.OpenView("AP0020", out APINVOICE1batch);
APINVOICE1batchFields = APINVOICE1batch.Fields;

//Header
AccpacCOMAPI.AccpacView APINVOICE1header;
AccpacCOMAPI.AccpacViewFields APINVOICE1headerFields;
pDBLinkCmpRW.OpenView("AP0021", out APINVOICE1header);
APINVOICE1headerFields = APINVOICE1header.Fields;

//Detail1
AccpacCOMAPI.AccpacView APINVOICE1detail1;
AccpacCOMAPI.AccpacViewFields APINVOICE1detail1Fields;
pDBLinkCmpRW.OpenView("AP0022", out APINVOICE1detail1);
APINVOICE1detail1Fields = APINVOICE1detail1.Fields;

//Detail2
AccpacCOMAPI.AccpacView APINVOICE1detail2;
AccpacCOMAPI.AccpacViewFields APINVOICE1detail2Fields;
pDBLinkCmpRW.OpenView("AP0023", out APINVOICE1detail2);
APINVOICE1detail2Fields = APINVOICE1detail2.Fields;

//Detail3
AccpacCOMAPI.AccpacView APINVOICE1detail3;
AccpacCOMAPI.AccpacViewFields APINVOICE1detail3Fields;
pDBLinkCmpRW.OpenView("AP0402", out APINVOICE1detail3);
APINVOICE1detail3Fields = APINVOICE1detail3.Fields;

//Detail4
AccpacCOMAPI.AccpacView APINVOICE1detail4;
AccpacCOMAPI.AccpacViewFields APINVOICE1detail4Fields;
pDBLinkCmpRW.OpenView("AP0401", out APINVOICE1detail4);
APINVOICE1detail4Fields = APINVOICE1detail4.Fields;

//APINVOICE1batch.Compose Array(APINVOICE1header)
//APINVOICE1header.Compose Array(APINVOICE1batch, APINVOICE1detail1, APINVOICE1detail2, APINVOICE1detail3)
//APINVOICE1detail1.Compose Array(APINVOICE1header, APINVOICE1batch, APINVOICE1detail4)
//APINVOICE1detail2.Compose Array(APINVOICE1header)
//APINVOICE1detail3.Compose Array(APINVOICE1header)
//APINVOICE1detail4.Compose Array(APINVOICE1detail1)

//Compose the views
AccpacCOMAPI.AccpacView[] arrBatchView = new AccpacCOMAPI.AccpacView[1];
arrBatchView[0] = APINVOICE1header;

AccpacCOMAPI.AccpacView[] arrHeaderView = new AccpacCOMAPI.AccpacView[4];
arrHeaderView[0] = APINVOICE1batch;
arrHeaderView[1] = APINVOICE1detail1;
arrHeaderView[2] = APINVOICE1detail2;
arrHeaderView[3] = APINVOICE1detail3;

AccpacCOMAPI.AccpacView[] arrDetailView1 = new AccpacCOMAPI.AccpacView[3];
arrDetailView1[0] = APINVOICE1header;
arrDetailView1[1] = APINVOICE1batch;
arrDetailView1[2] = APINVOICE1detail4;

AccpacCOMAPI.AccpacView[] arrDetailView2 = new AccpacCOMAPI.AccpacView[1];
arrDetailView2[0] = APINVOICE1header;

AccpacCOMAPI.AccpacView[] arrDetailView3 = new AccpacCOMAPI.AccpacView[1];
arrDetailView3[0] = APINVOICE1header;

AccpacCOMAPI.AccpacView[] arrDetailView4 = new AccpacCOMAPI.AccpacView[1];
arrDetailView4[0] = APINVOICE1detail1;

object szBuffer;
szBuffer = arrBatchView;
APINVOICE1batch.Compose(ref szBuffer);

szBuffer = arrHeaderView;
APINVOICE1header.Compose(ref szBuffer);

szBuffer = arrDetailView1;
APINVOICE1detail1.Compose(ref szBuffer);

szBuffer = arrDetailView2;
APINVOICE1detail3.Compose(ref szBuffer);

szBuffer = arrDetailView3;
APINVOICE1detail3.Compose(ref szBuffer);

szBuffer = arrDetailView4;
APINVOICE1detail4.Compose(ref szBuffer);

//Insert data
APINVOICE1batch.Browse("(BTCHSTTS = 1)", true);
temp = APINVOICE1batch.Exists;

//insert a new Batch
if (temp == false)
{
APINVOICE1batch.Init();
APINVOICE1batch.Read();

szBuffer = "DESC ---" + DateTime.Now.ToShortDateString();
APINVOICE1batch.Fields.get_FieldByName("BTCHDESC").PutWithoutVerification(ref szBuffer);
APINVOICE1batch.Update();

szBuffer = "00000";
APINVOICE1header.Fields.get_FieldByName("CNTITEM").PutWithoutVerification(ref szBuffer); // ' Entry Number
APINVOICE1header.Browse("", true);
APINVOICE1header.Fetch();

//Header
string sAcct = "", sInvNum = "", sInvDate = "", sAmt = "", sSubTotal = "", sGST = "", sHST = "";
//string sIDVend = "";
for (int i = 0; i < dtINV.Rows.Count - 1; i++)
{

APINVOICE1header.RecordClear();
APINVOICE1header.RecordGenerate(false);
szBuffer = "Header DESC";
APINVOICE1header.Fields.get_FieldByName("INVCDESC").PutWithoutVerification(ref szBuffer);
APINVOICE1header.Process();

sInvNum = dtINV.Rows.ItemArray[0].ToString();
sAcct = dtINV.Rows.ItemArray[1].ToString();
sInvDate = dtINV.Rows.ItemArray[2].ToString();
sAmt = dtINV.Rows.ItemArray[3].ToString();
sSubTotal = dtINV.Rows.ItemArray[4].ToString();
sGST = dtINV.Rows.ItemArray[5].ToString();
sHST = dtINV.Rows.ItemArray[6].ToString();

//Get Vendor ID
szBuffer = "169100";

APINVOICE1header.Fields.get_FieldByName("IDVEND").Equals(szBuffer);

szBuffer = "7";
APINVOICE1header.Fields.get_FieldByName("PROCESSCMD").PutWithoutVerification(ref szBuffer);
APINVOICE1header.Process();

szBuffer = sInvNum;
APINVOICE1header.Fields.get_FieldByName("IDINVC").Equals(szBuffer);

//First detail line
APINVOICE1detail1.GoBottom();

APINVOICE1detail1.RecordClear();
APINVOICE1detail1.RecordGenerate(false);

szBuffer = "-1";
APINVOICE1detail1.Fields.get_FieldByName("CNTLINE").PutWithoutVerification(ref szBuffer);

APINVOICE1detail1.Fields.get_FieldByName("IDGLACCT").Equals("5530-100");

szBuffer=sSubTotal;
APINVOICE1detail1.Fields.get_FieldByName("AMTDIST").Equals(szBuffer);

APINVOICE1detail1.Insert(); //ERROR OCCUR HERE

//Second detail line
APINVOICE1detail1.RecordClear();
APINVOICE1detail1.RecordGenerate(false);

szBuffer = "-2";
APINVOICE1detail1.Fields.get_FieldByName("CNTLINE").PutWithoutVerification(ref szBuffer);

szBuffer = "G";
APINVOICE1detail1.Fields.get_FieldByName("IDDIST").Equals(szBuffer);

szBuffer = sGST;
APINVOICE1detail1.Fields.get_FieldByName("AMTDIST").Equals(szBuffer);


APINVOICE1detail1.Insert();

//Insert header
szBuffer = sAmt;
APINVOICE1header.Fields.get_FieldByName("AMTGROSTOT").Equals(szBuffer);
APINVOICE1header.Insert();

}//end of loop dtINV

APINVOICE1batch.Update();
}
pSession.Close();
}//pSession Open
}

catch (Exception ex)
{


if (ex.ToString().Length >= 0)
{
string sError = "Error Occured! " + "\n" + ex.ToString() + "\n";

SendEmail(storeID, entryCount, balance, sError);
}
}

My task is to import a list of AP invoices into Accpac (54A) with VS 2008 and SQL 2008 (Developer Edition).

Appreciate your great help!

 

[ettienne]

My best advise is to get the basic program flow right in VBA after you have recoded a macro - I see you have recorded a macro. Step 1 after recording a macro is to clean it up, the macro recorder includes a lot of noise that is not required. Once you have the basic flow working in VBA you can easily migrate that to any other language.

 

[NewCCTech]

Hi ettienne,

I did what you said --Record a macro and clean the noise, then run it again and it created a batch with header and details, but when I migrate that to C#, it stop at setting IDVEND value and give the same error as before.

here is my macro

Dim mDBLinkCmpRW As AccpacCOMAPI.AccpacDBLink
Set mDBLinkCmpRW = OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READWRITE)

Dim mDBLinkSysRW As AccpacCOMAPI.AccpacDBLink
Set mDBLinkSysRW = OpenDBLink(DBLINK_SYSTEM, DBLINK_FLG_READWRITE)

Dim temp As Boolean
Dim APINVOICE1batch As AccpacCOMAPI.AccpacView
Dim APINVOICE1batchFields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "AP0020", APINVOICE1batch
Set APINVOICE1batchFields = APINVOICE1batch.Fields

Dim APINVOICE1header As AccpacCOMAPI.AccpacView
Dim APINVOICE1headerFields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "AP0021", APINVOICE1header
Set APINVOICE1headerFields = APINVOICE1header.Fields

Dim APINVOICE1detail1 As AccpacCOMAPI.AccpacView
Dim APINVOICE1detail1Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "AP0022", APINVOICE1detail1
Set APINVOICE1detail1Fields = APINVOICE1detail1.Fields

Dim APINVOICE1detail2 As AccpacCOMAPI.AccpacView
Dim APINVOICE1detail2Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "AP0023", APINVOICE1detail2
Set APINVOICE1detail2Fields = APINVOICE1detail2.Fields

Dim APINVOICE1detail3 As AccpacCOMAPI.AccpacView
Dim APINVOICE1detail3Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "AP0402", APINVOICE1detail3
Set APINVOICE1detail3Fields = APINVOICE1detail3.Fields

Dim APINVOICE1detail4 As AccpacCOMAPI.AccpacView
Dim APINVOICE1detail4Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "AP0401", APINVOICE1detail4
Set APINVOICE1detail4Fields = APINVOICE1detail4.Fields

APINVOICE1batch.Compose Array(APINVOICE1header)

APINVOICE1header.Compose Array(APINVOICE1batch, APINVOICE1detail1, APINVOICE1detail2, APINVOICE1detail3)

APINVOICE1detail1.Compose Array(APINVOICE1header, APINVOICE1batch, APINVOICE1detail4)

APINVOICE1detail2.Compose Array(APINVOICE1header)

APINVOICE1detail3.Compose Array(APINVOICE1header)

APINVOICE1detail4.Compose Array(APINVOICE1detail1)


temp = APINVOICE1batch.Exists
APINVOICE1batch.RecordCreate 1
APINVOICE1batch.Read
temp = APINVOICE1header.Exists
APINVOICE1header.RecordCreate 0
APINVOICE1detail1.Cancel
APINVOICE1batchFields("BTCHDESC").PutWithoutVerification ("Test VBA ---Run") ' Description
APINVOICE1batch.Update
APINVOICE1headerFields("IDVEND").Value = "169100" ' Vendor Number

APINVOICE1headerFields("PROCESSCMD").PutWithoutVerification ("7") ' Process Command Code
APINVOICE1headerFields("INVCDESC").PutWithoutVerification ("Header") ' Invoice Description

APINVOICE1header.Process

'APINVOICE1headerFields("PROCESSCMD").PutWithoutVerification ("4") ' Process Command Code

'APINVOICE1header.Process

APINVOICE1headerFields("IDINVC").Value = "234567893" ' Document Number
APINVOICE1headerFields("AMTGROSTOT").Value = "600.000" ' Document Total Including Tax

APINVOICE1detail1Fields("CNTLINE").PutWithoutVerification ("-1") ' Line Number
'APINVOICE1detail1.Read

APINVOICE1detail1Fields("TEXTDESC").Value = "AMT" ' Distribution Description

'temp = APINVOICE1detail1.Exists

APINVOICE1detail1Fields("AMTDIST").Value = "560.000" ' Distributed Amount

APINVOICE1detail1.Update
'APINVOICE1detail1.Browse "", 1

APINVOICE1detail1Fields("CNTLINE").PutWithoutVerification ("-1") ' Line Number

APINVOICE1detail1.Read
'temp = APINVOICE1detail1.Exists
APINVOICE1detail1.RecordCreate 0
APINVOICE1detail1.Process

APINVOICE1detail1Fields("IDDIST").Value = "G" ' Distribution Code
APINVOICE1detail1Fields("AMTDIST").Value = "40.000" ' Distributed Amount

APINVOICE1detail1.Insert
APINVOICE1detail1.Browse "", 1

APINVOICE1detail1Fields("CNTLINE").PutWithoutVerification ("-2") ' Line Number

APINVOICE1detail1.Read
APINVOICE1header.Insert

APINVOICE1batch.Update

====
my program stop at the following line code


szBuffer= "169100";
APINVOICE1header.Fields.get_FieldByName("IDVEND").PutWithoutVerification(ref szBuffer);

Don't know why. Could you please point out where is wrong?

Thanks a lot!

 

[ettienne]

This is what I mean by cleaned up code:

Dim mDBLinkCmpRW As AccpacCOMAPI.AccpacDBLink
Dim APbatch As AccpacCOMAPI.AccpacView
Dim APheader As AccpacCOMAPI.AccpacView
Dim APdetail1 As AccpacCOMAPI.AccpacView
Dim APdetail2 As AccpacCOMAPI.AccpacView
Dim APdetail3 As AccpacCOMAPI.AccpacView
Dim APdetail4 As AccpacCOMAPI.AccpacView

Set mDBLinkCmpRW = OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READWRITE)
mDBLinkCmpRW.OpenView "AP0020", APbatch
mDBLinkCmpRW.OpenView "AP0021", APheader
mDBLinkCmpRW.OpenView "AP0022", APdetail1
mDBLinkCmpRW.OpenView "AP0023", APdetail2
mDBLinkCmpRW.OpenView "AP0402", APdetail3
mDBLinkCmpRW.OpenView "AP0401", APdetail4

APbatch.Compose Array(APheader)
APheader.Compose Array(APbatch, APdetail1, APdetail2, APdetail3)
APdetail1.Compose Array(APheader, APbatch, APdetail4)
APdetail2.Compose Array(APheader)
APdetail3.Compose Array(APheader)
APdetail4.Compose Array(APdetail1)


APbatch.RecordCreate 1
APbatch.Fields("BTCHDESC").PutWithoutVerification ("Test VBA ---Run")   ' Description
APbatch.Update

APheader.RecordCreate 0
APheader.Fields("IDVEND").Value = "169100"                     ' Vendor Number
APheader.Fields("PROCESSCMD").PutWithoutVerification ("7")     ' Process Command Code
APheader.Fields("INVCDESC").PutWithoutVerification ("Header")   ' Invoice Description
APheader.Process
APheader.Fields("IDINVC").Value = "234567893"                  ' Document Number
APheader.Fields("AMTGROSTOT").Value = "600.000"                ' Document Total Including Tax

APdetail1.RecordCreate 0
APdetail1.Fields("IDDIST").Value = "G"                         ' Distribution Code
APdetail1.Fields("AMTDIST").Value = "40.000"                   ' Distributed Amount
APdetail1.Insert
APdetail1.Read

 

[ettienne]

Oops that last line APdetail1.Read should not be there.

 

[ettienne]

You need to add an Accpac error handler so that you can see the Accpac generated error messages.
If it is failing on IDVEND then my guess is that the vendor number is invalid.

 

[ettienne]

Correction:
The last line should be APheader.Insert instead of APdetail.Read

 

[NewCCTech]

Hi ettienne,

It says The IDVEND is on hold, I uncheck the "on Hold" box, but still same error-- Error HRESULT E_FAIL has been returned from a call to a COM component., BTW I have Error handler.

Is it because it is a lookup value, and I am not allowed to set value for IDVEND?


Thanks!

 

[ettienne]

If you have an Accpac error handler then you will see the Accpac error message, not a generic something crapped out message.
You need to uncheck the on hold box and save the record - I don't understand your question.

 

[DjangMan]

This looks odd:

APINVOICE1header.Fields.get_FieldByName("IDVEND").Equals(szBuffer);

I would expect something like:

APINVOICE1header.Fields.get_FieldByName("IDVEND").Put_Value(szBuffer);

.Equals doesn't make sense.

 

[NewCCTech]

Thanks DjanMan!

Yes, it should be set_value(szBuffer).

I am still having the problem (throw on line APINVOICE1header.Fields.get_FieldByName("IDVEND").set_Value(szBuffer), 169100 is a valid Vendor ID. I have tried many others Vendor IDs, all throw exception at the same line.

Any idea everyone? I am so desperate....

Thanks!

 

[DjangMan]

Run rvSpy at the same time. See what your code is putting into that field. rvSpy shows you most of the background work that goes on with Accpac and is a very useful tool for sorting out odd issues like this.

 

[NewCCTech]

Hi DjangMan,

The error occurs when open view APRTB (AP0012)(I guess it is to pull TERMs information), but I don't why it gives an error. Please help.

Thanks!

 

[DjangMan]

You don't show any code opening AP0012. Post code and we might be able to help.

 

[NewCCTech]

Hi DjangMan,

I ran RVSpy and it showed the error occured when Opening AP0012. I tried to put the code of RVSpy here, but don't know how?

 

[NewCCTech]

Sorry. It is AP0011 (APRTB)

 

[NewCCTech]

[1e3c] AP0011: APRTB [15:04:08.97]. Fetch(view=0x0240707c).

BTW, I am using Trial version of Accpac 54A. Would it be a matter?

Thanks!

 

[DjangMan]

Shouldn't matter. Do you have any terms set up in AP?

Also - post more of the rvSpy log.