เคล็ดลับการทำ join form ใน Ax2012

ปุ่ม add นี้ไป control อะไรให้ใส่ใน ActionPane

รูปภาพ

ถ้าให้ insert แบบ join โดยใส่ FK ลงไป auto ให้ใส่ join source

รูปภาพ

ผลลัพธ์ น้ะจ้ะ

รูปภาพ

Advertisements

[ Microsoft Dynamics AX 2012 ] CIL generation: Source array was not long enough. Check srcIndex and length, and the array’s lower bounds

Error

CIL generation: Line Number nn - Line Number nnn - Source array was not long enough. Check srcIndex and length, and the array's lower bounds.    \Classes\WhateverClass\WhateverMethod    nn    whatever    Err:nnn

Solution

On the Internet, there’s always a suggest to do a magic trick ( `Generate Full CIL` ) that solved all the symptom of Dynamics AX.

However, In my case, the `Generate Full CIL` does not help.

Scenario 1

In my case, The AX throws error `Source array was not long enough. Check srcIndex and length` and AX point the red underline error to class A,

A — use —> B

AX

indicate error

However, the root cause of the error is in class B that is used by class A. The class B has compile error. ( I knew this by compile class B again, and found that some fields that is used in class B are missing from the table ).

After comment it out, recompile class B, and `generate increment CIL`. The error `Source array was not long enough. Check srcIndex and length, and the array’s lower bounds` is gone.

NOTE here that I don’t need to `generate full CIL` at all.

Scenario 2

I also found that, in some case AX indicates error in class A with `Source array was not long enough. Check srcIndex and length`, which class A use class B that extend from class C.

A —- use —-> B ( extends C )

Ax

indicates error

The root cause of the error is in class C. The class C has a compile error. The solution to this is like scenario 1, compile class C, and `generate increment CIL`. And again, no need to `generate full CIL`.

That’s it. Hope this article will help you, rather than waiting 4-5 hours doing nothing for the `full CIL generation` and ends up with same error as before. Try to check the modified class that the error class is used, they may contains error.

Steps to rules all of theses

In scenarios 1-2 we can knew the error class. However, in the case that we don’t know the error class. Following these steps to rule them all.

1. full compile AX  ( *** This is important, generate full CIL alone does not solve the problem )

2. fix all the compile error (  AX don’t allow us to deploy the new assembly if we had a compile error )

3. full generate CIL ( Some AX service group uses the CIL.NET assembly, so we have to generate CIL)

Once we can deploy the new .NET assembly, the problem is gone.

[ mssql ] first OLAP cube

create cube

1. Choose FaceTable, and bring the related table using FK

Image

2. Get suggested measure group, Using Existing Table, Image

3. Create Measure, left the measure all selectedImage

4. select all dimensions

Image

NOTE : Physical table fields shown in picture below

Image

NOTICE :

– From Measure, there is `Count` Measures added

– From Dimensions,

Image

5. Create Date Hierarchy, DimDate always have a hierarchyImage

6. Build > Deploy cube

Browse cube

7. Browse a cube, add measure

Image

8. Slice and dice

Image

ref : developerfusion

[ udl extension ] test Data Link with windows

1. new File

2. test.udl

3. double click

4. we can select provider driver

รูปภาพ

NOTE : if in 64 bit machine, but the driver is 32 bit ( i.e. `Oracle Provider for OLE DB` ), try to execute UDL in 32 bit mode using the following command.

C:\Users\User>c:\Windows\SysWOW64\rundll32.exe "c:\Program Files (x86)\Common Files\System\Ole DB\oledb32.dll",OpenDSLFile C:\Users\User\Desktop\test.udl

32_64_bit_machineThe sourcecode of the UDL file is showing below.

[oledb]

; Everything after this line is an OLE DB initstring

Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=userIdNaja;Data Source=XXX.XXX.XXX.XXX

[ C#] MVC 2 101

Create New Project

In visual studio, File > New Project
`Visual C# Projects` and in the Templates pane, select `ASP.NET MVC 2 Web Application`.

The files will be generated.

SFA.sln
SFA\App_Data
SFA\bin
SFA\Content\Site.css
SFA\Controllers\AccountController.cs
SFA\Controllers\HomeController.cs
SFA\Global.asax
SFA\Global.asax.cs
SFA\Models\AccountModels.cs
SFA\obj\Debug
SFA\obj\Debug\DesignTimeResolveAssemblyReferencesInput.cache
SFA\obj\Debug\Package
SFA\obj\Debug\TempPE
SFA\Properties\AssemblyInfo.cs
SFA\Scripts\jquery-1.4.1-vsdoc.js
SFA\Scripts\jquery-1.4.1.js
SFA\Scripts\jquery-1.4.1.min.js
SFA\Scripts\jquery.validate-vsdoc.js
SFA\Scripts\jquery.validate.js
SFA\Scripts\jquery.validate.min.js
SFA\Scripts\MicrosoftAjax.debug.js
SFA\Scripts\MicrosoftAjax.js
SFA\Scripts\MicrosoftMvcAjax.debug.js
SFA\Scripts\MicrosoftMvcAjax.js
SFA\Scripts\MicrosoftMvcValidation.debug.js
SFA\Scripts\MicrosoftMvcValidation.js
SFA\SFA.csproj
SFA\SFA.csproj.user
SFA\SFA.Publish.xml
SFA\Views\Account
SFA\Views\Home
SFA\Views\Shared
SFA\Views\Web.config
SFA\Views\Account\ChangePassword.aspx
SFA\Views\Account\ChangePasswordSuccess.aspx
SFA\Views\Account\LogOn.aspx
SFA\Views\Account\Register.aspx
SFA\Views\Home\About.aspx
SFA\Views\Home\Index.aspx
SFA\Views\Shared\Error.aspx
SFA\Views\Shared\LogOnUserControl.ascx
SFA\Views\Shared\Site.Master
SFA\Web.config
SFA\Web.Debug.config
SFA\Web.Release.config
----------------------------------------------------------------- UnitTest
SFA.Tests\App.config
SFA.Tests\bin
SFA.Tests\Controllers
SFA.Tests\obj
SFA.Tests\Properties
SFA.Tests\SFA.Tests.csproj
SFA.Tests\bin\Debug
SFA.Tests\Controllers\AccountControllerTest.cs
SFA.Tests\Controllers\HomeControllerTest.cs
SFA.Tests\obj\Debug
SFA.Tests\obj\Debug\DesignTimeResolveAssemblyReferencesInput.cache
SFA.Tests\obj\Debug\TempPE
SFA.Tests\Properties\AssemblyInfo.cs

Build and Deploy

In Solution Manager, Right Click at the project > Build

Click `Start Debugging (F5)` icon

The default route

Global.asax.cs

routes.MapRoute(
"Default", // Route name
"{controller}/{action}/{id}", // URL with parameters
new { controller = "Home", action = "Index", id = UrlParameter.Optional } // Parameter defaults
);

i.e. http://localhost:52386/Home/About calls method About()

in SFA\Controllers\HomeController.cs

 

Passing The data from controller to view

Controller

ViewData["Message"] = "Welcome to ASP.NET MVC!";

View

<%: ViewData["Message"] %>

Action Anchor

View

<%: Html.ActionLink("Register", "Register") %>

Form

View : Account/LogOn.aspx

NOTE : submit to Account/LogOn.aspx

<% using (Html.BeginForm()) { %>
<%: Html.ValidationSummary(true, "Login was unsuccessful. Please correct the errors and try again.") %>
<div>
<fieldset>
<legend>Account Information</legend>

<div>
<%: Html.LabelFor(m => m.UserName) %>
</div>
<div>
<%: Html.TextBoxFor(m => m.UserName) %>
<%: Html.ValidationMessageFor(m => m.UserName) %>
</div>

<div>
<%: Html.LabelFor(m => m.Password) %>
</div>
<div>
<%: Html.PasswordFor(m => m.Password) %>
<%: Html.ValidationMessageFor(m => m.Password) %>
</div>

<div>
<%: Html.CheckBoxFor(m => m.RememberMe) %>
<%: Html.LabelFor(m => m.RememberMe) %>
</div>

<p>
<input type="submit" value="Log On" />
</p>
</fieldset>
</div>
<% } %>

Model : AccountModels.cs

#region Models

public class LogOnModel
{
[Required]
[DisplayName("User name")]
public string UserName { get; set; }

[Required]
[DataType(DataType.Password)]
[DisplayName("Password")]
public string Password { get; set; }

[DisplayName("Remember me?")]
public bool RememberMe { get; set; }
}

#endregion

Properties Message

Web.config

i.e. minRequiredPasswordLength

<membership>
<providers>
<clear/>
<add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider" connectionStringName="ApplicationServices"
enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="false"
maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10"
applicationName="/" />
</providers>
</membership>

 

 

winCE 5.0 in ARMV4I in a NutShell

– We can use Visual Studio 2005 or 2008 ( up to 2008 only ) in order to develop winCE 5.0 ( or 6.0 ).

– The screen below shown the C# development, in a nutshell

NOTE : BCD.net is the Barcode SDK ( BCD.net.dll ), the other API for winCE i.e. System.Data.SqlClient, or System.Data.SqlServerCe also available.รูปภาพ

How To Connect WinCE device to windows

– To connect Tohken PM250 Barcode Scanner, in Windows 7 (64 bits) we use Windows Mobile Device Center ( Similar to ActiveSync in Windows XP ).

– To solve the Universal serial bus ( USB ) controllers “Unknown Device”, when connecting the PM250 device to the computer, just go to WinCE, Settings > Network and Dial-up Connections, and at USB connection right click and untick ‘Set as Default’.

If this is no help, try unplug and plug USB in many times. There will be some times that windows can install driver. Let’s cross the finger. winCE_connection_remove_set_as_default_to_connect_to_windows_7_x64

Connect WinCE Device to MSSQL Server

First, I noted that I am using winCE 5.00 with Microsoft SQL Server 2012

Connect to mssql database using IP Address and Port i.e. from the example below connect to port 1234

string connectionString = "Data Source=192.168.2.2,1234;User ID=youruserid;Password=yourpassword;Initial Catalog=yourdatabasename;"

– Before that enable TCP/IP in the SQLSERVER Configuration Manager

SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for SQLEXPRESS

with this configuration, we can also change the port in the ‘IP All’, by replacing ‘TCP Dynamic Ports’ from 0 with ” ( blank ), and set TCP port instead ( in my case 1234 ).

TCP_IP_properties_for_SQLEXPRESS

NOTE : when we configure TCP properties, no need to state the instance ( e.g. 192.168.2.2\SQLEXPRESS ), because that particular IP and port has pointed to the instance already.

NOTE  about the connection string, if we connect to SQLEXPRESS instance on the local PC, the connection string can be

Data Source=.\SQLEXPRESS;Initial Catalog=yourdatabasenamenaja;Integrated Security=SSPI

Problem solved : SQLException : SQL Server does not exist or access denied

– Make sure your Windows firewall is disabled ( or you can configure the windows firewall to allow MSSQL Server, the method is shown below ). Otherwise you will get “SQLException : SQL Server does not exist or access denied” ( Before you will get this message, make sure your print out the SQLException using the below snippet code like in the example here, since “Console.WriteLine(e)” shows no clue. )

try
{
    comm.Connection.Open();
}catch (SqlException ex){
    StringBuilder errorMessages = new StringBuilder();
    for (int i = 0; i &lt; ex.Errors.Count; i++){
        errorMessages.Append("Index #" + i + "\n" +
                             "Message: " + ex.Errors[i].Message + "\n" +
                             "LineNumber: " + ex.Errors[i].LineNumber + "\n" +
                             "Source: " + ex.Errors[i].Source + "\n" +
                             "Procedure: " + ex.Errors[i].Procedure + "\n");
}
Console.WriteLine(errorMessages.ToString());
return;
}

How to configure windows firewall to allow the connection from MSSQL Server

1. go to Windows Firewall

2. Advanced settings

3. Inbound Rules

4. Add program : sqlservr.exe

WARNING : If you have multiple sqlservr.exe in your machine, make sure that you pick the right one. For example, in my case, it is at

C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\Binn\sqlservr.exe

NOTE : For me, I didn’t set allow port 1234, and Outbound Rules,

just Inbound rules program point to sqlservr.exe is OK.

Problem solved : PlatformNotSupportedException

– PlatformNotSupportedException may be occur, if the database codepage is not found in the Regional and Setting of the Device ( something like “PlatformNotSupportedException : at System.Data.SqlClient.TdsParser.GetCodePage(SqlCollation collation)”  error in the stacktrace ). I paste the full error below.

at System.Globalization.CultureInfo..ctor(Int32 culture, Boolean useUserOverride)
at System.Globalization.CultureInfo..ctor(Int32 culture)
at System.Data.SqlClient.TdsParser.GetCodePage(SqlCollation collation)
at System.Data.SqlClient.TdsParser.ProcessEnvChange(Int32 tokenLength)
at System.Data.SqlClient.TdsParser.Run(RunBehavior run, SqlCommand cmdHandler, SqlDataReader dataStream)
at System.Data.SqlClient.TdsParser.Run(RunBehavior run)
at System.Data.SqlClient.SqlInternalConnection.Login(Int32 timeout)
at System.Data.SqlClient.SqlInternalConnection.OpenAndLogin()
at System.Data.SqlClient.SqlInternalConnection..ctor(SqlConnection connection, Hashtable connectionOptions)
at System.Data.SqlClient.SqlConnection.Open()

For example, in my case, my MSSQL database using Thai_CI_AS ( You can know it by using the query “select SERVERPROPERTY(‘Collation’)” ) , but “Thai” Language pack is not installed in the WinCE device ( You can verify whether it is installed in Control Panel > Regional and Language Setting > Your Locale. “Thailand” is not there ).

And the screenshot below shows one way how to solve by changing Database properties in MSSQL Server management Studio. Change the Collation of the database to whatever your winCE support.

NOTE : you can see the description of each Collation from executing SQL Query “select * from fn_helpcollations();” and this is ref)

how_to_change_codepage_mssqlserverProblem solved : SQLException : General network error. Check your network documentation.

When I try “cmd.ExecuteReader()” from winCE 5.0, to fetch the data from the MSSQL 2012 Server, I face “SQLException, General network error”.

I try to look at the query log using “SQL Server Profiler” program, which show the connection coming from “.Net SqlClient Data Provider”. First it starts with “Audit Login”, then follow with “RPC:Completed” ( which execute the function like “exec sp_executesql N’SELECT …'”), then “Audit Logout” when the system throws the error.

It have no problem with command.ExecuteNonQuery() the system can update the record in the database, only when I query using the comm.ExecuteReader() that has the problem.

Solution :

There is a lot of the solution on the internet regarding “General Network Error”, since microsoft randomly thrown this error to the one which is not related with the Network at all. I try a lot of solution from the internet, but it does not help in my case, til I found this article. The article suggest me to use the query that query from the information_schema view instead. The query is below.

SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

and it works ! Therefore, something is wrong with my table.

After inspect my table, the problem is found

general_network_error_mssql_2012When we create new column in MSSQL 2012, the default datatype that popup is nchar(10) ( like column xxx in the picture above ). However, this causes the “General Network Error” ( WTF ! ). The solution is change it to use other datatype, i.e. int, or nvarchar() instead.

OK now ! FINALLY !!!!