Navision using ado Automation WITHEVENTS

Hi everyone, From a Navision 3.60 client, I am trying to code an ado Automation connection variable to do a MS SQL database connection and execute an SQL statement. I can do all this and it works. The issue I have is how to catch SQL generated errors. For example, when sending an INSERT statement I may generate “duplicate key” error. This causes a popup on screen and cancels the code execution. I want to catch these in the code. So, I created my ado variable with the WITHEVENTS property set to YES. Now when it runs, I immediately get a “fin.exe application error … Memory could not be read”. After clicking OK, the Navision client shuts down. I’ve also set the codeunit to SingeInstance = YES. I’m instanciating a … ‘Microsoft ActiveX Data Objects 2.7 Library’.Connection So, two questions. How can I catch various SQL raised errors without using the WITHEVENTS. Or, how should I get the code to run with the events (preferrable). Appreciate any comments and suggestions. Cheers, Eric

You’re limited to whatever is exposed to Navision, which is frustratingly little. If Navision was so kind to expose an event, a trigger with the event should appear automatically when you create the automation variable and set the WithEvents property to Yes. I have seen Navision crash when trying to change the WithEvents property on an already saved codeunit. My suggestion is to create a new dummy codeunit, declare the automation variable with events and see what happens. If that does the trick, you know what to do :). I doubt though that the error event is exposed. On a side note about catching errors in Navision… This is one of those examples where Navision failed to give us an error object to examine, and probably my number one pet peave about Navision development. Once Navision throws an error (which it does when it receives one from SQL Server), it just boots you out of the transaction. You could use the return value of the codeunit object to catch an error, but that would mean you’d have to write some pretty extensive code to give some feedback about the error itself. For instance, when a record variable causes the error, Navision destroys it, and you can’t get to the values anymore, so you’d have to write some preventive code to assume that stuff will error out so you can get the value from another variable. I’m starting to rant here, sorry. Let me know if you have any questions :).

Hi Daniel, Sorry for my delay in getting back, to this … I had an unexpected trip. Anyways, I’ve been playing with this and in the various incantations I can make, I cannot get away from either getting a memory error popup, or else getting the “SQL Error” popup. The main thing I want is to be able to issue the SQL statement and have no popup response from an SQL error. I can issue a SQL query to confirm my updates if need be, I’m just frustrated at not being able to issue the SQL UPDATE without getting a SQL error popup at such things as “duplicate key” or “null fields”. I know someone is going to say to be that I should “verify” before sending update, however in this instance, I am sending numerous records to the database and just want to get the good ones in, and report on the bad ones. I don’t want it to stop the process. I’m going to post another question asking how others are getting this done. Please feel free to respond to that, or here. Thanks for you comments. Cheers, Eric

Moved to SQL, Forums.