How to dynamically sorting in form datasource in ax 2012

Description:-
 
Here i have given simple demonstration for how to sort dynamically in form datasource in ax 2012.
I did some more research and finally found how to set the sort.

In your executeQuery method add the below code and change your table and field name.

TableName_ds.query().dataSourceNo(1).addSortField(fieldNum(TableName,FieldName),SortOrder::Ascending);

How to use Join in AX 2012

Description:-

In this Example we will see How to use join in ax table. For your information I have created table “A_Marks” and Count Each person Subject marks and join with different table object which one I have created in Job. Also I’m using sum () function for counting total of particular subject marks in table. Let’s create job and understand how to use join or many more function we can use in ax. i have here took join example for working with it.

Create table and Name it “A_Marks”
 

Create Job from AOT

• In the AOT, click Job.
• Right-click the Job node, and then select New Job.

static void CheckJoin(Args _args)
{
    A_Marks _Marks,_Marks1,_Marks2,_Marks3,_Marks4,_Marks5,_Marks6;
    A_Student _Student,_Student1,_Student2,_Student3;
    str name,name1;
    select sum(Maths) from _Marks;
    info("----------------Maths-------------------");
    while Select _Student
    join _Marks1
    where _Marks1.Mark_Student == _Student.Student_ID
    {
        name = (_Student.Student_Name);
        if(_Marks1.Maths)
        {
            info(strFmt(" %1 Has Maths Marks- %2",name,_Marks1.Maths));
        }
    }
    info("-----------------Total-------------------");
    info(strfmt("The total Maths Marks is %1", _Marks.Maths));   

    select sum(Phygics) from _Marks4;
    info("---------------Phygics------------------");
    while Select _Student2
    join _Marks3
    where _Marks3.Mark_Student == _Student2.Student_ID
    {
        name1 = (_Student2.Student_Name);
        if(_Marks3.Phygics)
        {
            info(strFmt(" %1 Has Phygics Marks- %2",name1,_Marks3.Phygics));
        }
    }
    info("-----------------Total-------------------");
    info(strfmt("The total Phygics Marks is %1", _Marks4.Phygics));
    info("-----------------------------------------");   

    select sum(Chemistry) from _Marks6;
    info("---------------Chemistry------------------");
    while Select _Student3
    join _Marks5
    where _Marks5.Mark_Student == _Student3.Student_ID
    {
        name1 = (_Student3.Student_Name);
        if(_Marks5.Chemistry)
        {
            info(strFmt(" %1 Has Chemistry Marks- %2",name1,_Marks5.Chemistry));
        }
    }
    info("-----------------Total-------------------");
    info(strfmt("The total Chemistry Marks is %1", _Marks6.Chemistry));
    info("-----------------------------------------");
}

Here you can Understand I have taken little Example to Understand it. So Now run your job and check info Log what Information we are getting.

Pass Container one form to another form in ax 2012

Description:

Here I have given simple demonstration to pass container one form to another in ax 2012.

Here I have given example to pass container in one form to another form in ax 2012. On your button click event create declare container and store you value.

Form-1 Button click event.

FormRun formRun;
Args    args = new Args();
str         multiSelectString;
container   con;
super();

con = conIns(con,1, strFmt("%1",Value1));
con = conIns(con,2, strFmt("%1", Value2));
con = conIns(con,3, Value3);
con = conIns(con,4, Value4);
con = conIns(con,5, Value5);
con = conIns(con,6, Value6);
con = conIns(con,7, Value7);

multiSelectString = con2Str(con,',');

args.name(formstr(FormName));
args.caller(this);
args.parm(multiSelectString);

//Which DataSource you want to pass
args.record(DataSourceName);
formRun = classfactory.formRunClass(args);
formRun.init();
formRun.run();
formRun.wait();
formRun.detach();

Form-2 Form init method.

container   con;
str         multipleRecords;
int         i1;
super();

//dataset start
if(element.args().dataset())
{
   //parmObject start
   if(element.args().parm())
   {
      //Getting multipleRecords Value start
      multipleRecords = element.args().parm();
      con = str2con(multipleRecords,",");
      for(i1 = 1;i1<= conLen(con) ;i1++)
      {
         if(i1==1){Value1 = strFmt("%1",conPeek(con,i1));}
         if(i1==2){ Value2 = strFmt("%1",conPeek(con,i1));}
         if(i1==3){ Value3= conPeek(con,i1);}
         if(i1==4){ Value4 = conPeek(con,i1);}
         if(i1==5){ Value5 = conPeek(con,i1);}
         if(i1==6){ Value6 = conPeek(con,i1);}
         if(i1==7){ Value7 = conPeek(con,i1);_TransDate = str2Date(_Getdate,123); }
      }
      //Getting multipleRecords Value end
   }
   //parmObject end
}
//dataset end

In form init method also you can validate for DataSource like below. Which DataSource you are pass as argument from Form-1.

public void init()
{
    //if not from InventDim dataset
    if( element.args().dataset() == tableNum(TableName))
    {
        super();
    }
    //if directly open
    if(!element.args().caller())
    {
        throw error("Cannot Run Directly.");
    }
}

How To Pass a container via args.parm() functionality

Description:-
 
In AX 2012 (and previous versions), there is something called an args() which is short for arguments. It primarily comes into play where an object (like a form) opens up another object.

"The Args class is used to pass arguments such as a name, a caller, and parameters between application objects."

For example, if a form opens up from another form (think ListPage form to non-list page), information about that first form can be passed to the second form that is being opened. Usually this is data like what form the second form is being opened from, any special data parameters, etc.

Anyways, with that prefacing out of the way... 

NOTE: Italics below indicate an update to the original post. I think it will make more sense to what the post is showing.

The Problem: There is a requirement to pass a container from one form to another form and we cannot use args.parmObject() functionality as its already taken. For this example, we are trying to pass a set of record Ids. While containers can have other values, we're just going to use recIds so I can show a few other features.

The Issue: There is no way to pass a container through other means. There are options to pass a string, enum, record set, static string value, or object instance, but no container. The parmObject instance is unavailable but args.parm() which only takes a string and is available.

The Resolution: Here is how it was solved:

1. Take the container and convert it a string with commas separating the variables
2. Pass the string to the args().parm() call in the first object
3. Retrieve the string from args().parm() in the second object
4. Convert the string to a container

For point 1, we need to use the con2str function. con2str(container c [, str sep] ).  For example, "con2Str(conRecIds,',')" would convert the container into a string using a comma as a separator.

For point 4, we need to use the str2con function. str2con(str_value [, str 10 _sep, boolean _convertNumericToInt64] ).  For an example, "str2con(strRecIds, ',', true)" would convert a string with commas as what distinguishes the individual components. The third parameter '_convertNumericToInt64' is really handy. It will take the string values and auto-convert them to an int64 (e.g. recId) when set to true.

Some code example: 

IN FORM 1:
[Action: Highlighted a few records and clicked a button] 

container conRecIds;

str       strRecIds;

tmpTable = dataSource_DS.getFirst(1);

while (tmpTable.RecId != 0)

{

   conRecIds += int642str(tmpTable.omRecID);

   tmpTable = dataSource_DS.getNext();

}

strRecIds = con2Str(conRecIds, ',');

element.args().parm(strRecIds);

IN FORM 2:

container conRecIds;

str       strRecIds;

strRecIds = args.parm();

conRecIds = str2con(strRecIds, ',', true);

How to read data from excel and bind into gridview in asp.net OR How to read data from excel-sheet in asp.net

Description:-

In this article we will Read data from excel sheet and bind in Gridview control to see. To open excel sheet we will use File-upload control and check by filename, extension, and if we want header or not in Gridview.  And by Changing the Page index of Gridview control we will call method for reading data from excel sheet to read another data.

Default.aspx:-

<div>
  <asp:FileUpload ID="FileUpload1" runat="server" />
  <asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="btnUpload_Click" />
  <br />
  <asp:Label ID="Label1" runat="server" Text="Has Header ?"></asp:Label>
  <asp:RadioButtonList ID="rbHDR" runat="server">
    <asp:ListItem Text="Yes" Value="Yes" Selected="True"></asp:ListItem>
    <asp:ListItem Text="No" Value="No"></asp:ListItem>
  </asp:RadioButtonList>
  <asp:GridView ID="GridView1" runat="server" OnPageIndexChanging="PageIndexChanging" AllowPaging="true"></asp:GridView>
</div>

Default.aspx.cs:-
Now Generate button click event to upload file and Check filename, Extension or etc…

protected void btnUpload_Click(object sender, EventArgs e)
{
  if (FileUpload1.HasFile)
  {
    string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
    string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
    string FolderPath = ConfigurationManager.AppSettings["FolderPath"];

    string FilePath = Server.MapPath(FolderPath + FileName);
    FileUpload1.SaveAs(FilePath);
    Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text);
  }
}

Now create method for read data from excel sheet and bind in Gridview control.

private void Import_To_Grid(string FilePath, string Extension, string isHDR)
{
  string conStr = "";
  switch (Extension)
  {
    case ".xls": //Excel 97-03
      conStr = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
      break;
    case ".xlsx": //Excel 07
      conStr = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
      break;
  }
  conStr = String.Format(conStr, FilePath, isHDR);
  OleDbConnection connExcel = new OleDbConnection(conStr);
  OleDbCommand cmdExcel = new OleDbCommand();
  OleDbDataAdapter oda = new OleDbDataAdapter();
  DataTable dt = new DataTable();
  cmdExcel.Connection = connExcel;

  //Get the name of First Sheet
  connExcel.Open();
  DataTable dtExcelSchema;
  dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
  string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
  connExcel.Close();

  //Read Data from First Sheet
  connExcel.Open();
  cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
  oda.SelectCommand = cmdExcel;
  oda.Fill(dt);
  connExcel.Close();

  //Bind Data to GridView
  GridView1.Caption = Path.GetFileName(FilePath);
  GridView1.DataSource = dt;
  GridView1.DataBind();
}

Web.Config:-
For Connection String you can use in web.config file for Secure connection like below and you can use in your webpage.

<connectionStrings>
  <add connectionString="Data Source=ServerName;Initial Catalog=DatabaseName;Persist Security Info=True;User ID=UserName;Password=PassWord" name="DBCS" providerName="System.Data.SqlClient"/>
</connectionStrings>

Or I have used here my AppSetting for Getting Data for Save Data in this Folder.

<appSettings>
  <add key="FolderPath" value="Files/"/>
</appSettings>

Now Generate OageIndexChanged event of Gridview Control to read another data from excel sheet.

protected void PageIndexChanging(object sender, GridViewPageEventArgs e)
{
  string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
  string FileName = GridView1.Caption;
  string Extension = Path.GetExtension(FileName);
  string FilePath = Server.MapPath(FolderPath + FileName);

  Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text);
  GridView1.PageIndex = e.NewPageIndex;
  GridView1.DataBind();
}

Now run your Application and you can select your ExcelSheet from FileUpload and Bind by Clicking on Button to Display Data in Gridview Control.

GridView RowCommand Event in Asp.Net

Description:-

In this article we will see about Gridview_RowCommand Event in dot net. Here I have created sample demo Project to Understand How to Work on Gridview_RowCommand Event. In this Demo Example I have bind Gridview from Database and Perform Action on that Gridview like Edit and Delete so user can Edit that item and Delete that Item. To Create Edit and Delete Inside Gridview you have to Create Columns in Gridview and from that Create Template Fields and Inside that Create Itemtemplate and Now Add Two Buttons for Edit and Delete.

Note: Here in Itemtemplate you can Bind Data Dynamically from Database like how I Done below. And For Increment Row Index I have used Container for Gridview row Index Generate.

Using the Command argument you can Get the Gridview selected Row Index Id and from that what action you have to Perform you can. For that you have to select RowCommandEventArgs to get ID for selected row.

Now Generate Gridview RowCommand from property and get ID from GridviewCommandEventArgs when you Select Gridview Row that it will select row Index from that Gridview for that you have to Add Command Argument and CommanName In Button Control. So when you Select Edit Button than it will fire Command Argument and Select what you have Bind in Command Argument. After what Action you have to Perform that you can Perform from ID. Here you can see Gridview RowCommand event Example What I have Dome.

Create Table and Name it "Item_Master".

CREATE TABLE [dbo].[Item_Master](
 [Item_ID] [int] IDENTITY(1,1) NOT NULL,
 [Item_Name] [varchar](20) NOT NULL,
 [Item_Rate] [real] NOT NULL,
 CONSTRAINT [PK_Item_Master] PRIMARY KEY CLUSTERED 
(
 [Item_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Create Store Procedure for Edit Item_Master Data:

CREATE Procedure [dbo].[SP_EditItemDetails]
@ID int
as
begin
 select * from ItemDetails_Master where ID=@ID;
end
GO

Create Store Procedure for Delete Item_Master Data:

Create procedure [dbo].[SP_DeleteItemDetailsItem]
@ID int
as
begin
 delete from ItemDetails_Master where ID=@ID;
end

HTML Code:-

<div  style="margin:10px;">
  <asp:GridView ID="GridViewItem" runat="server" BackColor="LightGoldenrodYellow" AutoGenerateColumns="False"
  BorderColor="Tan" BorderWidth="1px" CellPadding="2" GridLines="None" Height="10px"
  Width="507px" ForeColor="Black" HorizontalAlign="Left" OnRowCommand="GridViewItem_RowCommand"
  OnRowDataBound="GridViewItem_RowDataBound" OnRowDeleting="GridViewItem_RowDeleting"
  OnRowEditing="GridViewItem_RowEditing" ShowFooter="true" PageSize="5">
  <AlternatingRowStyle BackColor="PaleGoldenrod" />
  <Columns>
    <asp:TemplateField HeaderText="Action" HeaderStyle-BorderStyle="Groove" ItemStyle-BorderStyle="Groove">
      <ItemTemplate>
        <asp:Button ID="btnedit" runat="server" CommandName="Edit" CommandArgument='<%#Eval("ID") %>' Text="Edit" />
        <asp:Button ID="btndelete" runat="server" Text="Delete" CommandArgument='<%#Eval("ID") %>' CommandName="Delete" />
      </ItemTemplate>
    </asp:TemplateField>
    <asp:TemplateField HeaderText="ID">
      <ItemTemplate>
        <%#Container.DataItemIndex+1 %>
      </ItemTemplate>
    </asp:TemplateField>
    <asp:TemplateField HeaderText="Item Name">
      <ItemTemplate>
        <asp:Label ID="lbliname" runat="server" Text='<%#Eval("Item_Name") %>'></asp:Label>
      </ItemTemplate>
    </asp:TemplateField>
    <asp:TemplateField HeaderText="Item Rate">
      <ItemTemplate>
        <asp:Label ID="lblirate" runat="server" Text='<%#Eval("Item_Rate") %>'></asp:Label>
      </ItemTemplate>
    </asp:TemplateField>
    <asp:TemplateField HeaderText="Item Quantity">
      <ItemTemplate>
        <asp:Label ID="lbliqty" runat="server" Text='<%#Eval("Item_Quantity") %>'></asp:Label>
      </ItemTemplate>
    </asp:TemplateField>
  </Columns>
  <FooterStyle BackColor="Tan" />
  <HeaderStyle BackColor="Tan" Font-Bold="True" />
  <PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue" HorizontalAlign="Center" />
  <SelectedRowStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" />
  <SortedAscendingCellStyle BackColor="#FAFAE7" />
  <SortedAscendingHeaderStyle BackColor="#DAC09E" />
  <SortedDescendingCellStyle BackColor="#E1DB9C" />
  <SortedDescendingHeaderStyle BackColor="#C2A47B" />
  </asp:GridView>
</div>

Code Behind:-

protected void GridViewItem_RowCommand(object sender, GridViewCommandEventArgs e)
{
  Int32 ID = Convert.ToInt32(e.CommandArgument.ToString());
  if (e.CommandName == "Edit")
  {
    using (con = new SqlConnection(CS))
    {
      cmd = new SqlCommand();
      cmd.CommandText = "SP_EditItemDetails";
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.Connection = con;
      cmd.Parameters.AddWithValue("@ID", ID);
      con.Open();
      da = new SqlDataAdapter(cmd);
      dt = new DataTable();
      da.Fill(dt);
      if (dt.Rows.Count > 0)
      {
        itemdropdown.SelectedItem.Text = dt.Rows[0]["Item_Name"].ToString();
        txtitemrate.Text = dt.Rows[0]["Item_Rate"].ToString();
        txtitemquantity.Text = dt.Rows[0]["Item_Quantity"].ToString();
        txtitemtotalamount.Text = dt.Rows[0]["Item_Total"].ToString();
      }
    }
  }
  else if (e.CommandName == "Delete")
  {
    using (con = new SqlConnection(CS))
    {
      cmd = new SqlCommand();
      cmd.CommandText = "SP_DeleteItemDetailsItem";
      cmd.Parameters.AddWithValue("@ID", ID);
      cmd.Connection = con;
      cmd.CommandType = CommandType.StoredProcedure;
      con.Open();
      Int32 DeleteID = cmd.ExecuteNonQuery();
      if (DeleteID > 0)
      {
        lblmsg.Text = "Deleted !";
        lblmsg.ForeColor = System.Drawing.Color.Green;
        BindItemDetailsGrid();
      }
      else
      {
        lblmsg.Text = "Error : ID Not Found !";
      }
    }
  }
}

Now you are done for Gridview_RowCommand event for Perform any action from Gridview RowCommand event.

Automatic Database Backup in Sql

DECLARE

       @BaseFolder nvarchar(128)

      ,@DatabaseFolder nvarchar(256)

      ,@DBNAME sysname

      ,@path VARCHAR(256) -- path for backup files 

     ,@fileName VARCHAR(256) -- filename for backup 

     ,@fileDate VARCHAR(20) -- used for file name

 

-- Only one input parameter needed from the user.

 

SET @BaseFolder = 'D:\SQLBackup\'; 

DECLARE Db CURSOR FOR

SELECT name FROM sys.databases WHERE name <> 'tempdb';

OPEN Db;

FETCH NEXT FROM Db INTO @DBNAME;

WHILE @@FETCH_STATUS = 0

BEGIN

      -- Dynamically create a folder for each database.

      SET @DatabaseFolder = @BaseFolder + @DBNAME;

      EXEC master.sys.xp_create_subdir @DatabaseFolder;

      FETCH NEXT FROM Db INTO @DBNAME;

END

-- Clean up

CLOSE Db;

DEALLOCATE Db;

GO 

DECLARE @dateString CHAR(12), @dayStr CHAR(2), @monthStr CHAR(2), @hourStr CHAR(2), @minStr CHAR(2)

--month variable

IF (SELECT LEN(CAST(MONTH(GETDATE()) AS CHAR(2))))=2

   SET @monthSTR=CAST(MONTH(GETDATE()) AS CHAR(2))

ELSE

   SET @monthSTR= '0' + CAST(MONTH(GETDATE()) AS CHAR(2))

--day variable

IF (SELECT LEN(CAST(DAY(GETDATE()) AS CHAR(2))))=2

   SET @daySTR=CAST(DAY(GETDATE()) AS CHAR(2))

ELSE

   SET @daySTR='0' + CAST(DAY(GETDATE()) AS CHAR(2))

--hour variable

IF (SELECT LEN(DATEPART(hh, GETDATE())))=2

   SET @hourStr=CAST(DATEPART(hh, GETDATE()) AS CHAR(2))

ELSE

   SET @hourStr= '0' + CAST(DATEPART(hh, GETDATE()) AS CHAR(2))

--minute variable

IF (SELECT LEN(DATEPART(mi, GETDATE())))=2

   SET @minStr=CAST(DATEPART(mi, GETDATE()) AS CHAR(2))

ELSE

   SET @minStr= '0' + CAST(DATEPART(mi, GETDATE()) AS CHAR(2))

--name variable based on time stamp

SET @dateString=CAST(YEAR(GETDATE()) AS CHAR(4)) + @monthStr + @dayStr + @hourStr + @minStr

 

--=================================================================

DECLARE @IDENT INT, @sql VARCHAR(1000), @DatabaseFolder nvarchar(256),@DBNAME VARCHAR(200)

SELECT @IDENT=MIN(database_id) FROM SYS.DATABASES WHERE [database_id] > 0 AND NAME NOT IN ('TEMPDB')

WHILE @IDENT IS NOT NULL

BEGIN

   SELECT @DBNAME = NAME FROM SYS.DATABASES WHERE database_id = @IDENT

/*Change disk location here as required*/

   SELECT @SQL = 'BACKUP DATABASE '+@DBNAME+' TO DISK = ''D:\SQLBackup\'+ @DBNAME +'_db_' + @dateString +'.BAK'' WITH INIT'

   EXEC (@SQL)

   SELECT @IDENT=MIN(database_id) FROM SYS.DATABASES WHERE [database_id] > 0 AND database_id>@IDENT AND NAME NOT IN ('TEMPDB')

END

How to repeat Static Row on each page in SSRS Report

If you have static row in SSRS Report and you have to display header row in each page then you have to set below properties for that textbox in SSRS Report.

• First expand Advance properties window from Columns Group inside you SSRS Report Table Grouping.
• After select you textbox form Row Group which you have to repeat on each page. 

1. Set the KeepWithGroup property to After
2. Set the RepeatOnNewPage property to true

For each header row you have to do that.

 

Exception has been thrown by the target of invocation in dynamics Ax 2012

Exception has been thrown by the target of an invocation

Solution 1:-

AOT Data dictionary is not synchronize with Sql Server. I perform following steps to resolve this error.

• Right click on data Dictionary and synchronize the data Dictionary.
• Right click on AOT and compile it, it step take longer time. In the case of any error in any class, fix the issue and compiled.
• Generate full CIL.

Solution 2:-

For every user, go to “Tools > Options > Development > General” and UNCHECK the “Execute business operations in CIL” box.

Unchecking "Execute Business Operations in CIL" means that you stopped testing whether the logic works in CIL.

Solution 3:-

1. Catch the exception (by catch (Exception::CLRError)). I didn't get enough information to tell you where, but I described how to find it.
2. Get the CLR exception and look into its InnerException property. AifUtil::getClrErrorMessage() is usually the easiest way to do that.

How to write a simple select statement in Ax

Description:-

A select statement can be written specifically to return only one record or to return many records. If we expect the select statement to return multiple records and we would like to loop through these records, we simply embed the select statement within a while loop.

The following examples will demonstrate how to write simple select statements that return different data from the same table.
The first example will select all columns from all records in the Cartable:

CustTable
The following list shows the test data for the CustTable:

AccountNum	Name	CustGroup	Blocked
1101	Forest Wholesales	10	No
1102	Sunset Wholesales	20	No
1103	Cave Wholesales	10	No
1104	Desert Wholesales	30	Yes

Code:-

static void selectAllRecordsStatic(Args _args)
{
    A_PurchaseOrder objA_PurchaseOrder;
    int records;
    info("------------------START-------------------");
    while select objA_PurchaseOrder
    {
      info("--------------NEW RECORD--------------");
      info (strfmt("CarId: %1", objA_PurchaseOrder.Purchase_ID));
      info (strfmt("CarBrand: %1", objA_PurchaseOrder.Vender_Code));
      info (strfmt("Model: %1", objA_PurchaseOrder.Purchase_Date));
      info (strfmt("ModelYear: %1", objA_PurchaseOrder.Status));
      info (strfmt("Mileage: %1", objA_PurchaseOrder.Purchase_Amount));
      records++;
    }
    info("------------------END-------------------");
    info(strfmt("%1 records was selected", records));
}

Output:-