CS0120 1

[iwells]

Hi,

I'm trying, for the first time, to insert records in to a non-Sage table using CS0120 and getting the error message "Run-time error '2147467259 (80004005)' / Method 'Value' of object 'IAccpacViewField' failed".

Here's the code:

Private Const strEQCost As String = "SELECT EQ_Cost.ItemNo, EQ_Cost.Location, EQ_Cost.CostType, EQ_Cost.Qty, EQ_Cost.FunctionalCurrency, EQ_Cost.ReportingCurrency, EQ_Cost.DayEndNo, EQ_Cost.EntryNo, EQ_Cost.DetailNo, EQ_Cost.DocNo FROM EQ_Cost ORDER BY EQ_Cost.DayEndNo, EQ_Cost.EntryNo, EQ_Cost.DetailNo"

Set mDBLinkCmpRW = OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READWRITE)

mDBLinkCmpRW.OpenView "CS0120", vEQCost
vEQCost.Browse strEQCost, True
vEQCost.Fetch
vEQCost.InternalSet 256

vEQCost.Fields("DayEndNo").Value = 0
vEQCost.Insert

Obviously it's failing on the assignment of the value, but I don't understand why from other examples I've seen. I'm assuming my creation of vEQCost is missing something?

Thanks!

 

[iwells]

I should mention at the moment there are NO records in this table.

 

[ettienne]

Jip, it's missing a lot of somethings. What you are attempting makes no sense.
What exactly are you trying to achieve?

Sage 300 Certified Consultant

 

[iwells]

I'm trying to insert a new record in to a non-Sage table which is empty through the view using CS0120. I know a ton of fields are missing values and the view delcaration is missing (i forgot to copy that in), but i assume i 1) declare all the variables (which i think i have done) 2)assign values to them and open the view (which again I think i have done correctly) 3) assign values to insert (which is failing) and then 4) insert (which i can't get to).

 

[ettienne]

Your SQL query should have the form of "SELECT <fields> FROM <table>".
You're missing the <table>.

This part makes no sense:
vEQCost.Fields("DayEndNo").Value = 0
vEQCost.Insert

You cannot insert records in CS0120 using .Insert.
You need to do everything in an SQL query, something like:
INSERT INTO <table name> ("DayEndNo") VALUES (0)

Sage 300 Certified Consultant

 

[iwells]

Private Const strEQCost As String = "SELECT EQ_Cost.ItemNo, EQ_Cost.Location, EQ_Cost.CostType, EQ_Cost.Qty, EQ_Cost.FunctionalCurrency, EQ_Cost.ReportingCurrency, EQ_Cost.DayEndNo, EQ_Cost.EntryNo, EQ_Cost.DetailNo, EQ_Cost.DocNo FROM EQ_Cost ORDER BY EQ_Cost.DayEndNo, EQ_Cost.EntryNo, EQ_Cost.DetailNo"

That was the select to get the records, but now I see what you're saying that I can't insert with CS0120 and I wasn't aware of that. I thought once the view was created I could still manipulate like other views. That's the issue right there.

 

[ettienne]

Something else, if you are running data manipulation language statements (insert, update, etc) then you will use something like:

mDBLinkCmpRW.OpenView "CS0120", vEQCost
vEQCost.Browse "INSERT INTO TABLE ("DayEndNo") VALUES (0)", True

Or:

mDBLinkCmpRW.OpenView "CS0120", vEQCost
vEQCost.Browse "UPDATE TABLE Set "DayEndNo" = 0", True



Sage 300 Certified Consultant

 

[ettienne]

Another note, use these lines for SQL queries that return records:

mDBLinkCmpRW.OpenView "CS0120", vEQCost
vEQCost.Browse strEQCost, True
vEQCost.InternalSet 256

Do while vEQCost.GoNext

Loop

Note you need to lose vEQCost.Fetch

Sage 300 Certified Consultant

 

[ettienne]

Sorry I missed your FROM part above.

Sage 300 Certified Consultant

 

[iwells]

Ok, I've modified it as you suggested and when stepping over the code in the editor it isn't flagged with an error, but when I got to the table I'm not seeing the record inserted.

Is there something else that needs to be called after the .browse which contains the insert into statement? I tested the statement in SQL server and it does in fact insert the record in the table.

 

[ettienne]

.Browse will run the query.
Is the EQ_Cost table in the Sage 300 company database?

Sage 300 Certified Consultant

 

[iwells]

It is, it's the fact that it's pure SQL so double quotes aren't used for strings, single quotes are ... not sure why it still executed it though without throwing an error. Once I change the doubles to singles it works.

Thanks again for all the help.

 

[ettienne]

Sorry for the typo with the double quotes - it should have been single quotes.
Glad you figured it out.
CS0120 does not always return errors - sometimes it does nothing and other times it simply crashes.

Sage 300 Certified Consultant

 

[DjangMan]

As an aside you can use .fetch with CS0120 when working with the result set.

mDBLinkCmpRW.OpenView "CS0120", vEQCost
vEQCost.Browse strEQCost, True
vEQCost.InternalSet 256

while vEQCost.Fetch

wend

 

[ettienne]

I prefer .GoNext
.Fetch is the old syntax, but still functions.
GoNext, GoTop, GoBottom & GoPrevious was added later to improve flow.

Sage 300 Certified Consultant

 

[DjangMan]

Then I'll go with the flow, then.

 

[iwells]

Ettienne,

With .Browse how are the following treated:

1) is the "cursor" always at the first record or do you need to .GoTop?
2) how do you test for EOF/BOF/Record Count returned = 0?

Thanks.

 

[ettienne]

The cursor is at the first record.

This will loop through all records:
Do while vEQCost.GoNext
....
Loop


Sage 300 Certified Consultant

 

[iwells]

Ok I come the VBA world and when comparing this to .movenext in a loop you'd do while .eof = false, so how do you test for .eof? What's confusing me is .GoNext gets tested first and you can't .GoNext to EOF ... or can you?

 

[ettienne]

Don't overthink it.....

.GoNext returns TRUE if the next record is found, returns FALSE if not.

So "Do While vEQCost.GoNext" will loop through all records and will gracefully exit when it reaches EOF.

Sage 300 Certified Consultant