ADO method Questions

jversusj

hello
i have never worked with ADO before and have very little experience with SQL statements. i read through 7 pages of ADO forum search results along with numerous general concept webpages to familiarize myself a bit with the syntax and approach. i have been attempting to piece together some code that will accomplish my assignment. It is a very basic look-up and should not be complicated, so i want to try to do it myself without calling in our consultant. here is what i'm trying to do...

1. NAV should select all records from a Company table in an external SQL table.
2. NAV should check this recordset for existence of Customer No. in Company table (primary key, company no. = customer no.). I cannot get the Find method to work using ADORecSet.Find - it will not compile. ...so i comment this out for now and just try the next step..
3. IF NAV doesn't see a record, it should create one. i am trying ADORecSet.AddNew.
here is what i try to do:

      //ADORecSet.AddNew([VARIANT FieldList][, VARIANT Values])
      aFieldList[1] := 'Company';
      aFieldList[2] := 'Name';
      aFieldList[3] := 'Active';
      aFieldList[4] := 'Availability_Checking';
      aFieldList[5] := 'User_Stamp';
      aFieldList[6] := 'Date_Time_Stamp';

      aFieldValues[1] := 'LUC4422300';
      aFieldValues[2] := 'Test Customer Name';
      aFieldValues[3] := 'Y';
      aFieldValues[4] := 'Y';
      aFieldValues[5] := USERID;
      aFieldValues[6] := CURRENTDATETIME;

      ADORecSet.AddNew(aFieldList,aFieldValues);

i built the array to hold some temporarily hard-coded values (i just want to see if i can insert a record), using a code example from W3 schools tutorial on ADO methods. both arrays have the same datatype of VARIANT and both have 6 dimensions, but i get the following error when i attempt to compile my codeunit: "The array dimensions must be identical."

As far as i can tell, the dimensions are identical. i've confirmed it numerous times, even extended and contracted the dimensions to see if 6 was too much, too few. it will not compile. i don't see any threads discussing ADO Find or AddNew, so any tips would be appreciated. Thanks!

i am building my test using Miklos' downloadable ADO project as a basis.

kriki

Did you check also this : http://dynamicsuser.net/blogs/waldo/archive/2008/01/06/using-stored-procedures-in-c-side.aspx.

It has been a great help for me to start with ADO.

jversusj

i did read that, thank you.

one of my questions with that is, do i have to use a stored procedure? all i need to do is look up a number from NAV in an external SQL table and if i don't find it, create it. can that not be done directly with ADO?

i don't have much experience with working in SQL - i've never written a stored procedure, etc.

ara3n

You don't need to create stored procedure. You can simply call a select statement.

jversusj

thanks - that is where i am having trouble. i cannot find a solid example for syntax to work from. i tried a Find and couldn't compile. when i try an AddNew, i can't compile either. i'm a little lost...

most everything i found talks about SP - so, now i'm teaching myself how to create SP so i can try to use those syntax examples (such as Waldo's blog) to do what i need.

ara3n

Take a look at this example

http://mibuso.com/blogs/ara3n/2009/05/2 ... -with-ado/


You will see in example that it executes a sql statement and returns a recordset.


When the recordset is return you can loop through it

WHILE NOT ADORecordset.EOF DO BEGIN


ADORecordset.MoveNext;

END;

jversusj

thanks rashed, this could help me approach this using ADORecSet...

in the meantime, i was able to create my stored procedure and testing it locally, it worked.
(i'll copy /paste from the SP modify window in SSMS since i don't know how else to get at the query)

ALTER PROCEDURE [dbo].[usp_AddCompany]
@Company nvarchar(25),
@Name nvarchar(50),
@Active nchar(1),
@AvailCheck nchar(1),
@UserStamp nvarchar(30),
@DateTimeStamp datetime
AS
IF NOT EXISTS (SELECT * FROM COMPANY WHERE Company = @Company)

INSERT INTO COMPANY(COMPANY,NAME,ACTIVE,AVAILABILITY_CHECKING,USER_STAMP,DATE_TIME_STAMP)
VALUES (@Company,@Name,@Active,@AvailCheck,@UserStamp,@DateTimeStamp)

now i am writing my codeunit using Waldo's blog as a jumping off point. i cannot get past defining the Date-time stamp.
i have defined the parameter type as datetime (135), but i get an ADODB error that the type is incorrect.

..some code...
ADOParameter := ADOCommand.CreateParameter('@DateTimeStamp', 135, 1, 0,'20090929110830');
ADOCommand.Parameters.Append(ADOParameter);
..some code...

and i get this error

---

Microsoft Dynamics NAV

---

This message is for C/AL programmers:

The call to member CreateParameter failed. ADODB.Command returned the following message:
Application uses a value of the wrong type for the current operation.

---

OK

---

jversusj

okay - changed my code as follows:

ADOParameter := ADOCommand.CreateParameter('@DateTimeStamp', 135, 1, 0,CURRENTDATETIME);
ADOCommand.Parameters.Append(ADOParameter);

and it worked! \:D/

i was able to create the record from NAV, if it was not present already!

now i have to make my codeunit nice and pretty, but at least it works!

jversusj

i could not get the return value to work - so i stepped away from using the stored procedure and went back to attempting to do this with SQL statements in NAV.

i have the following issue when i attempt to INSERT

   sqlstring := STRSUBSTNO(sqlst,sCustomer,sCustName,USERID,CURRENTDATETIME);
   ADOCommand.CommandText := sqlstring;

   ADOCommand.CommandType := 1;
   ADOCommand.CommandTimeout := 0;
   ADOCommand.Execute;

sqlst is a text constant:
INSERT INTO COMPANY(COMPANY,NAME,ACTIVE,AVAILABILITY_CHECKING,USER_STAMP,DATE_TIME_STAMP) VALUES (%1,%2,Y,Y,%3,%4)

and here is the error

---

Microsoft Dynamics NAV

---

This message is for C/AL programmers:

The call to member Execute failed. Microsoft OLE DB Provider for SQL Server returned the following message:
The name "NOI4422300" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

---

OK

---

this is the value of sCustomer (my customer no.). i have tried a lot of different syntax but i always get this error.

Slawek_Guzek

SQL statement should read

INSERT INTO COMPANY(COMPANY,NAME,ACTIVE,AVAILABILITY_CHECKING,USER_STAMP,DATE_TIME_STAMP) VALUES ('%1','%2',Y,Y,'%3','%4');

(you forgot to quote parameters - they are passed as text and need ' character around)

You will probably need to play with %4 parameter to make date time format compatible with SQL, which might be tricky (may depend on SQL server settings)

That's one of reasons why stored procedures are better.

Regards,
Slawek

jversusj

thanks so much slawek.

i changed my text constant to include quotes around the values and it worked perfectly. Even the datetime was okay.

now i have two working pathways. whichever path gets me better error handling will be the path i implement with. i do like the idea of having all the code within NAV as opposed to calling the stored procedure in SQL.

Slawek_Guzek

Your welcome.

BTW: Even with stored procedures you still can have everything in C/AL

Just create 2 procedures:

1. Check if stored procedure exists, if yes drop it and re-create

your first SQL query (based on wha't you've done so far) should read something like:

'IF OBJECT_ID('dbo.usp_AddCompany') IS NOT NULL DROP PROCEDURE dbo."usp_AddCompany" ;GO ;CREATE PROCEDURE dbo."usp_AddCompany @Company nvarchar(25),...'

This could be called in OnPreReport.

2. In your report body call stored procedure using ADO and parameters

This might be simpler approach if you run into problems with date time format - each workstation could have different regional settings which may afect formatting datetime variable in STRSUBSTRNO

Regards,
Slawek

jversusj

thank you again, slawek.

i do have both options available to me - stored procedure and direct SQL calls through C/AL - your recommendation to use the SP to resolve Datetime issues is very strong. We are currently based in the US, so everyone 'should' have similar regional settings, but the possibility of failures may be enough to use the SP route and pass that as a parameter.

here is a follow-up question for anyone experienced with ADO...

what happens if the execute call times out? does this throw a NAV error and roll back within the NAV client? if not, how can i throw an error with failed automation?

i read a thread that said i could put all my code in the OnRun trigger of my Codeunit and then call my codeunit with IF codeunit.RUN THEN... ELSE ERROR. Did i interpret that correctly?

to give you the big picture, here is what my code needs to do.
During Order release, NAV checks if the order is a blind or double-blind shipment. if it is, it needs to connect to our warehouse management system (via ADO) and look up the customer in the company table and add it if it is not there. If this should fail for any reason, the order should not release - reverting back to the open status. This is required due to our WMS (Manhattan ILS) and how it is handling blind shipments. We need to be sure the company exists in ILS before we attempt to interface the sales order to it, or the interface file will fail.

i'm basically looking for suggestions for error handling with ADO connections. i don't know how to force a timeout in SQL to experiment with this.

Slawek_Guzek

Hi,

1. yes, "IF codeunit.RUN THEN" construction 'wraps' your code in one unit and in case of any error in your C/AL code inside called codeunit CODEUNIT.RUN function just returns false. This enables you to do further actions, in V5 you can even get error text using GETLASTERRORTEXT function

However you can't use IF CODEUNIT.RUN if there are any open transactions (any INSERT/MODIFY/DELETE in code before CODEUNIT.RUN is called, unless INSERT/MODIFY/DELETE is done on temporary table)

2. what happens if the execute call times out? - just like in case of any other error - all your transactions written since function started from user interface action, or since last COMMIT will be rolled back.

3. you don't have to force SQL to timeout your query to test your procedure for error handling - simple use RAISEERROR function instead of INSERT in your SQL query

4. I do option for stored procedures as they are usually faster, parameters are handled internally in their native forms (so no problem with data conversion), and you can easilly build much more logic/checks into stored procedure. However there also some drawbacks, like remembering to install them, keeping them up to date, etc.

If you writting this only for your company as internal stuff then probably you can assume you do have some level of control on your production environment (including computer settings) so probably going with direct TSQL queries instead of stored procedures is safe approach.

5. for checking only if cusomer exists in remote database I would rather configure linked server on local SQL server, then expose remote table from remote database as view on SQL server local to NAV, then link that view to NAV as a table. All checking routines would be then straightforward. However this configuration does not allow inserting anything to remote table.

Regards,
Slawek

jversusj

fantastic response, slawek. thank you.