External data access w/ Service Tier

njhansen

In several places, we've used ADO to retrieve data that we needed in the Classic client. Now, we're wanting to call some of that code from Web Services, and ran into the error:

The expression Variant cannot be type-converted to a String value.

Rashed describes our situation perfectly on his blog, so I won't type all the details myself.
https://community.dynamics.com/product/nav/navtechnical/b/navrashedamini/archive/2011/01/10/using-ado-on-rtc-in-nav.aspx

So, I started working down the road of using the dotNet objects, but am stuck with creating parameters. I've done it plenty of times in C#, so I figured it would be easy; but when I try to add parameters, the NAV compiler complains that

The call is ambiguous between the following methods:
'Parameters()'
and
'Parameters()'

Just to be sure, I deleted the way I'd typed it and had the F5 help for the SQLCommand object insert it into my code. No luck.

A toy sample:

SQLConnection := SQLConnection.SqlConnection(
  'Provider=SQLOLEDB;Data Source=theServer;' +
  'Initial Catalog=theDB;' +
  'Integrated Security=SSPI;');

SQLConnection.Open;

SQLCommand := SQLConnection.CreateCommand();
SQLCommand.CommandText :=
  'select count(*)' +
  '  from [' + COMPANYNAME + '$Sales Line] ss ' +
  ' where ss.DocType = @DocumentType ' +
  '   and ss.DocNo = @DocumentNo ';

SQLCommand.CommandTimeout := 7200;
SQLCommand.CommandType := 1;

//SqlParameterCollection := SQLCommand.Parameters;  //also doesn't work
SqlCommand.Parameters.AddWithValue('@DocumentType', "Document Type");
SqlCommand.Parameters.AddWithValue('@DocumentNo', "No.");
SQLReader := SQLCommand.ExecuteReader;

IF NOT SQLReader.Read THEN BEGIN
  LineCount := SQLReader.GetDecimal(0);
END;

SQLConnection.Close;
CLEAR(SQLReader);
CLEAR(SQLCommand);
CLEAR(SQLConnection);

Anyone have experience with this and know what to do? The only generic help I can find for the error involves changing the class to not have two overloads that the compiler can mistake for one another (obviously not an option since it is a base .Net class).

Local variables:

Name	DataType	Subtype	Length
SQLConnection	DotNet	'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Data.SqlClient.SqlConnection	
SQLCommand	DotNet	'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Data.SqlClient.SqlCommand	
SQLReader	DotNet	'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Data.SqlClient.SqlDataReader	
SQLParameter	DotNet	'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Data.SqlClient.SqlParameter	
SQLParameterCollection	DotNet	'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Data.SqlClient.SqlParameterCollection

aavio

HI,

Did you find any solution for this error.

i am using dotnet datatype SQLConnection variables.
Any other alternate way to execute store procedure from NAV

kindly help.

Thanks

njhansen

The solution was to upgrade to a later version of the NAV client.

See http://community.dynamics.com/nav/b/navericwauters/archive/2011/07/19/net-interop-calling-stored-procedures-on-sql-server-example-1.aspx#.UXbjzVfm82x which says:

upgrade to at least build 32198 of NAV 2009 R2

ESanabia

Hi,

I need some little help to read data from SqlDataReader using the new DotNet DataType Object. I am trying to assign a decimal value to a C/AL decimal variable using the method GetInt32(Int), but an error occur with the following message: Specified cast is not valid.

Down bellow is my code:


IF rPOSFuncProfile."No Sales Items With No Ivent." = TRUE THEN BEGIN

lSQLConnection := lSQLConnection.SqlConnection(GetConnectionString(rPOSFuncProfile."Sever Name", rPOSFuncProfile.Database,
rPOSFuncProfile.User,rPOSFuncProfile.Password));
lSQLConnection.Open;

lSQLCommand := lSQLConnection.CreateCommand();

SQLStr := 'SELECT SUM([Remaining Quantity]) AS Cantidad ' +
'FROM dbo. ' +
'WHERE [Item No_] = ' + rItem."No." + ' ' +
'AND [Location Code] = ' + SingleQuote + StoreSetup."Location Code" + SingleQuote;

lSQLCommand.CommandText := SQLStr;

lSQLDataReader := lSQLCommand.ExecuteReader();

WHILE lSQLDataReader.Read DO BEGIN
lQueryResult := lSQLDataReader.GetInt32(0);
END;

lSQLConnection.Close;

CLEAR(lSQLConnection);
CLEAR(lSQLCommand);
CLEAR(lSQLDataReader);

IF lQueryResult <= 0 THEN
EXIT(FALSE);

END;

Hopefully you can help me with this. Thanks in advance...