External data access w/ Service Tier

njhansen
Member Posts: 37
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:
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
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:
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:
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
0
Answers
-
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.
Thanksaav!o0 -
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 R20 -
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...0
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 320 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions