OpenView and SQL return different results 1

[iwells]

Hi,

I'm trying to understand why the following are returning 2 different results:

Set mDBLinkCmpRW = OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READWRITE)

mDBLinkCmpRW.OpenView "IC0280", vICHIST
vICHIST.Browse "ITEMNO = ""02250115-443""", True

And:

select * from ICHIST where ITEMNO = '02250115-443'

When I run the select statement I get 4 records, but when I create that view I'm only getting 2 records. I'm 100% sure I'm running it against the same database (I'm working on our test server only) and I really can't figure this out. Here's the results from SQL:

DAYENDSEQ ENTRYSEQ LINENO
597 32 1
108545 1 7000
125954 1 6000
147481 1 1

If I .browse I get DAYENDSEQ 108545 and 147481, but not the others and if i specific try to .browse for the other records I get a different item for dayend 597/32/1 and not record is found 125954/1/6000.

Help?

 

[Jay Converse]

That doesn't make any sense, they should be the same.

 

[iwells]

I agree, I can't figure it out.

I'm testing it with other items as well and getting the same results. I've triple checked the database I'm running the SQL statement against and the company I'm logged in to and they're definitely the same.

 

[iwells]

I see what it is ... if you use:

Do While vICHIST.GoNext
...
Loop

The code actually EXECUTES the method .GoNext, it doesn't test for it, so in my data the loop skips records 1/3 in the dataset that's being returned. Interested to see what Ettienne says since this seems to skip records ... I wonder if the first step in his loop is .GoPrevious?

 

[Jay Converse]

I always use .Fetch

 

[iwells]

Yea and I'll do the same, but I'm definitely interested to see Ettienne's response on how he's getting around this.

 

[ettienne]

I've never run into something like that.
But then I hardly ever open a view and do .Browse if I'm simply reading records, I always use CS0120 and a SQL statement.
FWIW .Fetch is deprecated and .GoNext (and the others) are the newer functions. I never do .GoPrevious (it is slower) or .GoTop or .GoBottom - they can be really slow.
It actually does not make a programmatic difference if you use .Fetch or .GoNext - they are interchangeable. Interested to see if you get 4 records returned when you use .Fetch above.

Sage 300 Certified Consultant

 

[iwells]

I'll play around with both, but for right now I find the API very, very frustrating regardless ...

Here's an expansion of the continued issues:

mDBLinkCmpRW.OpenView "CS0120", vET
vET.InternalSet 256

vET.Browse "SELECT * WHERE EQ_Cost.Location = '" & vICHIST.Fields("LOCATION").Value & "' AND EQ_Cost.ItemNo= '" & vICHIST.Fields("ITEMNO").Value & "' AND EQ_Cost.CostType = 'B' AND EQ_Cost.Qty <> 0 ORDER BY EQ_Cost.DayEndNo, EQ_Cost.EntryNo, EQ_Cost.DetailNo", True

If vET.Fetch Then
... do true
Else
vET.Browse "INSERT INTO EQ_Cost VALUES ('" & vICHIST.Fields("ITEMNO").Value & "', '" & vICHIST.Fields("LOCATION").Value & "', 'F', " & vICHIST.Fields("QUANTITY").Value & ", '" & strFCurrency & "', " & vICHIST.Fields("HOMEEXTCST").Value & ", '" & strRCurrency & "', " & dblRCost & ", " & vICHIST.Fields("DAYENDSEQ").Value & ", " & vICHIST.Fields("ENTRYSEQ").Value & ", " & vICHIST.Fields("LINENO").Value & ", '" & vICHIST.Fields("DOCNUM").Value & "')", True
End if

'in my test, the scenario is false and executes the insert record fine.

vET.Browse "SELECT * FROM EQ_Cost WHERE CostType = 'F'", True

If vET.Fetch Then
... do true
Else
... do false
End if

The 3rd vET.Browse fails (Method 'Fetch' of object IAccpacView failed), but if I set a watch on vET.Browse, the value is false and then it doesn't fail. Do I need to DoEvents or something? It seems like the "view" or record set doesn't update properly? Is there some type of method you need to recall after Browsing to clear information related to CS0120? More importantly, there was a record inserted in to the table in a previous If statement, so there should actually be a record returned too ... that's why I'm assuming I need some type of commit transaction or update or etc (even though I've tested all those and they don't see to do the trick).

 

[Jay Converse]

I've never had any issues like this. FWIW, you should be using a read-only link if you're not updating records, it's faster. And don't use the same view object for both browsing and updating.

 

[iwells]

Ok, maybe that's my issue then because I keep using the same view over and over to perform select/insert/updates (though they're never nested and always performed sequentially where 1 is dependent on the outcome of the other). I'll try splitting them up to see if that's the issue.

I guess I'm wondering in the end if its worth it for me to put the effort in to learn this API or use ADO which I could have finished already.

 

[Jay Converse]

I _never_ use the same view. I create a new view as soon as the SQL statement is different.

 

[ettienne]

Jip, do not reuse the same view for different purposes, it does not like that.

Sage 300 Certified Consultant

 

[iwells]

Ok, then maybe I should use ADO anyway? Basically what I'm going is reading from ICHIST, checking in a custom table, inserting a record in to custom table, then reading from ICHIST, checking in custom table, updating or inserting in to custom table (which could be affected by the first or subsequent inserts/updates in the custom table) ... or should I .Close and .Open the views over and over?

I was really going with the API based on previous comments from you guys saying that it's better, but maybe not in this scenario?

 

[Jay Converse]

You can do what you want. But I wouldn't use ADO, it's less portable.

 

[iwells]

That came across wrong, I didn't mean that negatively, I more meant I'm trying to put in the effort to learn this based on your recommendations, but maybe this scenario is a weakness of the API? How can I get around the fact that I will constantly change the selects/inserts/updates using CS0120?

 

[Jay Converse]

I don't know what to say. I do inserts, updates, truncates, create views, basically any SQL statement I want without issue. It's not the _value_ of the statement that's important, it's the _structure_ of the statement. So, you can have "UPDATE MYTABLE SET FIELD = '" & sMyVal & "' WHERE OTHERFIELD = '" & sOtherVal & "'" in a loop, and use the same view each time. Just don't use that same view for "UPDATE ANOTHER_TABLE SET ANOTHERFIELD = '" & sMyVal & "' WHERE OTHERFIELD = '" & sOtherVal & "' AND THATFIELD = 2".

 

[DjangMan]

And to add to this:
1) Use CS0120 whenever you can. It means that your test environment and your live environment won't need separate connection strings, etc. You can do inserts and reads from other databases, not just the Sage database: "SELECT X, Y, Z FROM [OtherDatabase]..[OtherTable]". Very useful. Sometimes you need to do more complicated things. You could use a disconnected recordset and load records from CS0120 into the disconnected recordset and go from there.

2) if I'm using a loop with CS0120, I do this, otherwise if you try to reuse CS0120 you get errors.

Dim qry as AccpacCOMAPI.AccpacView
while <somereason>
[indent]mDBLink.Open "CS0120", qry[/indent]
[indent]qry.browse "<some SQL query>", True[/indent]
[indent]set qry = nothing[/indent]
wend

 

[iwells]

Thanks DjangMan! This is the solution I was looking for since I didn't realize that the query/view didn't refresh itself so to speak.

 

[vbdbcoder]

I have been using "select" sql statements directly, without using api. I only insert\update\delete using api. It has been working fine for years.