Get year form date in SSRS report

Get year form date in SSRS report

Description:-

In this article we will see about hoe to get only year in SSRS report. here I have display only year 2 digit in SSRS report. using expression how to get it. here is the solution to get year 2 digit. it could be current year or previous year or next year. here is the expression to get year 2 digit. Here my current year is 2016.

Expression:-

=Format(CDate(today()), "yy")
Output:- 16

=Year(today())
Output:- 2016

today().AddYears(-1).year
Output:- 2015

CStr(Year(Today)-1)
Output:- 2015

today().AddYears(+1).year
Output:- 2017

=Format(CDate(year(today())+1), "yy")
Output:- 17

Format(Year(today())+1).Substring(2)
Output:- 17

Format(Year(today())).Substring(2)
Output:-16

Remove unused windows update patch files

Description:-

In this tutorials we will see how to remove windows update patch file which file we does not need in system. Here is the solution to remove those files and keep your system fast. Follow this step to remove it.
Step 1:- open command prompt run as system administrator mode.

Step 2:- type below command in your command prompt to remove unused windows update patch files.

rmdir /q /s %WINDIR%\Installer\$PatchCache$

wait until process complete.

Step 3:- now open your My Computer and check in your C:\ Drive.

Note: - If you have removed your patch file once then again you cannot run this command again because already you have removes your patch file from that directory.

How to Create SysOperationFramework in Ax 2012

How to Create SysOperationFramework in Ax 2012

Description:- 

In AX 2012 the RunBaseBatch Framework has been replaced with SysOperation framework formally known as Business Operation Framework. We can still use the RunBaseBatch framework in AX 2012, but is recommended to use SysOperation Framework.

SysOperation Framework:- SysOperation Framework formerly known as (Business Operation Framework) provides a way to create and run our operations on a batch server. Previously in earlier versions of Ax 2009 RunBaseBatch framework was used.

The only reason that I have understand of the introduction of SysOperation in Ax 2012 is that, in previous version RunBaseBatch provides the implementation of the actions required for the operation to be run on server. But SysOperation provides the BASE implementation of all the implementation that were before. So it means that there is a fair chance of extending in future versions as well.

SysOperation Concept:

Data Contract Class
 This class is used for holding the input data. As the name indicates this class is used to take the inputs from the user and will store in to variables.
This class must be [DataContractAttribute] before declaration.
This class must contain same number of parm methods as the number of input fields.
All parm methods must start with [DataMemberAttribute] 
Eg: [DataMemberAttribute]

  
Server Operation Classes
This class is used to run the business logic for the data contract class, we can say it is similar to "run" method in the RunBaseBatch Framework.
This class must be extend "SysOperationServiceController".
It should override “New” method and also have ‘main’ and “construct” methods.


In the below example we will create a dialog to take inputs from user and display info log.
Inputs: First Name, Last Name, DOB and age.

Create Data Contract Class as below.

[DataContractAttribute]
class UmeshTestDataContract
{
    str         fName;
    str         lName;
    int         age;
    TransDate   dob;
    NoYesId     dobcheck;
    str title;
}

[DataMemberAttribute]
public int parmAge(int _age= age)
{
    age     = _age;
    return age;
}

[DataMemberAttribute]
public TransDate parmDOB(TransDate _dob= dob)
{
    dob     = _dob;
    return dob;
}

[DataMemberAttribute]
public NoYesId parmDobcheck(NoYesId    _dobcheck=dobcheck)
{
    dobcheck        = _dobcheck;
    return dobcheck;
}

[DataMemberAttribute]
public str parmfName(str _fname = fName)
{
    fName   = _fname;
    return  fName;
}

[DataMemberAttribute]
public str parmlName(str _lName=lName)
{
    lName   = _lName;
    return  lName;
}

Now we will create a serviceOperation class, which will extend SysOperationServiceController.

class UmeshTestBatchServiceOperation extends SysOperationServiceController
{
}

Override New method.

public void new(IdentifierName _className  = '', IdentifierName _methodName = '', SysOperationExecutionMode _executionMode = 0)
{
    super();
    this.parmClassName(_className);
    this.parmMethodName(_methodName);
    this.parmExecutionMode(_executionMode);
}

Create a method to run the business logic as Run method in the RunBaseBatch.

public void runMyLogic(UmeshTestDataContract _datacontract)
{
    VendTable       lVentTable;
    str             name;
    TransDate       ldob;
    int             lage;
    name            = _datacontract.parmfName() + " " + _datacontract.parmlName();
    ldob            = _datacontract.parmDOB();
    lage            = _datacontract.parmAge();
    info(strFmt("%1 : %2 : %3",name, ldob, lage));
}

Create a construct method

public static UmeshTestBatchServiceOperation construct()
{
    ClassName className;
    MethodName runMethodName;
    SysOperationExecutionMode exeMode = SysOperationExecutionMode::Synchronous;
    UmeshTestBatchServiceOperation UkServiceOperation;
    className = classStr(UmeshTestBatchServiceOperation);
    runMethodName = methodStr(UmeshTestBatchServiceOperation, runMyLogic);
    UkServiceOperation = new UmeshTestBatchServiceOperation(className,runMethodName,exeMode);
    UkServiceOperation.parmDialogCaption("SysOperation Batch");
    return UkServiceOperation;
}

Now we will create a main method which will call the SysOperationServiceController method “startOperation”.

public static void main(Args    _args)
{
    UmeshTestBatchServiceOperation UkServiceOperation;
    UkServiceOperation = UmeshTestBatchServiceOperation::construct();
    UkServiceOperation.startOperation();
}

Generate Incremental CIL. This is required as it will be executed on AOS server.
Run the above class and enter inputs and see output.
Export datagridview to pdf in windows form application in asp.net

Export datagridview to pdf in windows form application in asp.net

Description:-

In this example we explain that how to export DataGridView data to PDF file in Windows Forms (WinForms) Applications using ItextSharp PDF conversion library using C#. DataGridView cannot provide facility to export Griddata directally to pdf and so that we have to use one .dll file and give reference to your project and use the functionality export to pdf in table format by using the iTextSharp.dll Table for this solution.

To export DataGrid data or row to pdf format first download the iTextSharp.dll and the use it in your project. Here below is code to export DataGridView data to pdf with table format and Company logo or image logo with header footer in pdf writing data.

Create Table:-

CREATETABLE [dbo].[cardetail](
 [carno] [nvarchar](50)NOTNULL,
 [carname] [nvarchar](50)NOTNULL,
 [address] [nvarchar](50)NOTNULL,
 [personname] [varchar](50)NOTNULL,
 [cellno] [bigint] NOTNULL,
 [detail] [nvarchar](50)NOTNULL,
 [total] [bigint] NOTNULL,
 [id] [bigint] IDENTITY(1,1)NOTNULL,
CONSTRAINT [PK_cardetail] PRIMARYKEYCLUSTERED
(
 [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

Default.aspx.cs:-

SqlConnection cn = new SqlConnection("ConnectionString");
Int32 empid;
public Form1()
{
  //this.cardetail_Load();
  InitializeComponent();
}

//function for pass data to this function as a datareader and its display in pdf table format 
private void GenerateBillAndExport(SqlDataReader Reader)
{
if (Reader.HasRows)
{
  if (Reader.Read())
  {
    PdfPTablePdfPTable = newPdfPTable(4);
    PdfPTable.DefaultCell.Padding = 3;
    PdfPTable.WidthPercentage = 100;
    PdfPTable.HorizontalAlignment = Element.ALIGN_LEFT;
    PdfPTable.DefaultCell.BorderWidth = 1;

    PdfPTable.AddCell(new PdfPCell(iTextSharp.text.Image.GetInstance("D://Logo.jpg")) { Border = 0 });
    PdfPTable.AddCell(new PdfPCell(new Phrase("Name: " + Reader[3].ToString())) { Border = 0 });
    PdfPTable.AddCell(new PdfPCell(new Phrase("Date :" + DateTime.Now.ToShortDateString())) { Border = 0 });
    PdfPTable.AddCell(new PdfPCell(new Phrase("Mobile No  :" + Reader[4].ToString())) { Border = 0 });

    PdfPTable.AddCell(new PdfPCell(new Phrase("CarName")) { BackgroundColor = iTextSharp.text.BaseColor.GRAY });
    PdfPTable.AddCell(new PdfPCell(new Phrase("Detail")) { BackgroundColor = iTextSharp.text.BaseColor.GRAY });
    PdfPTable.AddCell(new PdfPCell(new Phrase("Total")) { BackgroundColor = iTextSharp.text.BaseColor.GRAY });
    PdfPTable.AddCell(new PdfPCell(new Phrase("CarNo")) { BackgroundColor = iTextSharp.text.BaseColor.GRAY });

    PdfPTable.AddCell(Reader[1].ToString());
    PdfPTable.AddCell(Reader[5].ToString());
    PdfPTable.AddCell(Reader[6].ToString());
    PdfPTable.AddCell(Reader[0].ToString());
    //add blank row 
    iTextSharp.text.pdf.PdfPCell c1 = new iTextSharp.text.pdf.PdfPCell(new Phrase(" "));
    iTextSharp.text.pdf.PdfPCell c2 = new iTextSharp.text.pdf.PdfPCell(new Phrase(" "));
    iTextSharp.text.pdf.PdfPCell c3 = new iTextSharp.text.pdf.PdfPCell(new Phrase(" "));
    iTextSharp.text.pdf.PdfPCell c4 = new iTextSharp.text.pdf.PdfPCell(new Phrase(" "));
    PdfPTable.AddCell(c1);
    PdfPTable.AddCell(c2);
    PdfPTable.AddCell(c3);
    PdfPTable.AddCell(c4);
    //Add Footer row 
    PdfPTable.AddCell(newPdfPCell(new Phrase("Name:- Umesh Adroja" + Environment.NewLine + "Contact No:- 8000762096"))
    {
      BackgroundColor = iTextSharp.text.BaseColor.LIGHT_GRAY
    });
    PdfPTable.AddCell(newPdfPCell(new Phrase("")));
    PdfPTable.AddCell(newPdfPCell(new Phrase("")));
    PdfPTable.AddCell(newPdfPCell(new Phrase("")));
    //PdfPTable.AddCell(new PdfPCell(new Phrase("Name:- Umesh Adroja" + Environment.NewLine + "Contact No:- 8000762096"))
    //{
    //    BackgroundColor = iTextSharp.text.BaseColor.LIGHT_GRAY
    //});
    //Exporting to PDF 
    stringfolderPath = "D:\\PDFs\\";
    if (!Directory.Exists(folderPath))
    {
      Directory.CreateDirectory(folderPath);
    }
    //Reader.GetValue(0) + "_" + DateTime.Now.ToShortDateString() 
    FileStream stream = new FileStream(folderPath + Reader.GetValue(0) + "_" + ".pdf", FileMode.Create);
    using (stream)
    {
      Document pdfDoc = new Document(PageSize.A2, 10f, 10f, 10f, 0f);
      PdfWriter.GetInstance(pdfDoc, stream);
      pdfDoc.Open();
      pdfDoc.Add(PdfPTable);
      pdfDoc.Close();
      stream.Close();
      System.Diagnostics.Process.Start(folderPath + Reader.GetValue(0) + "_" + ".pdf");
    }
  }
}
}

private void cardetail_Load(object sender, EventArgs e)
{
  DataTable dt = binddata();
  dataGridView2.DataSource = dt;
  dataGridView2.AutoGenerateColumns = false;
  dataGridView2.AllowUserToAddRows = false;
  DataGridViewLinkColumnEditlink = newDataGridViewLinkColumn();
  Editlink.UseColumnTextForLinkValue = true;
  Editlink.HeaderText = "Edit";
  Editlink.DataPropertyName = "lnkColumn";
  Editlink.LinkBehavior = LinkBehavior.SystemDefault;
  Editlink.Text = "Edit";
  dataGridView2.Columns.Add(Editlink);
  DataGridViewLinkColumnDeletelink = newDataGridViewLinkColumn();
  Deletelink.UseColumnTextForLinkValue = true;
  Deletelink.HeaderText = "delete";
  Deletelink.DataPropertyName = "lnkColumn";
  Deletelink.LinkBehavior = LinkBehavior.SystemDefault;
  Deletelink.Text = "Delete";
  dataGridView2.Columns.Add(Deletelink);
  DataGridViewLinkColumnPrintlink = newDataGridViewLinkColumn();
  Printlink.UseColumnTextForLinkValue = true;
  Deletelink.HeaderText = "Export to pdf";
  Printlink.DataPropertyName = "lnkColumn";
  Printlink.LinkBehavior = LinkBehavior.SystemDefault;
  Printlink.Text = "Export to pdf";
  dataGridView2.Columns.Add(Printlink);
}

//Binding Gridview <!--[if !supportLineBreakNewLine]--><!--[endif]-->
public DataTable binddata()
{
  DataTable dt = new DataTable();
  string query = "select * from cardetail";
  SqlCommand cmd = new SqlCommand(query, cn);
  SqlDataAdaptersa = new SqlDataAdapter();
  sa.SelectCommand = cmd;
  cn.Open();
  cmd.ExecuteNonQuery();
  sa.Fill(dt);
  dataGridView2.DataSource = dt;
  dataGridView2.AutoGenerateColumns = false;
  dataGridView2.AllowUserToAddRows = false;
  cn.Close();
  returndt;
}

//Edit Delete Event <!--[if !supportLineBreakNewLine]--><!--[endif]-->
private void dataGridView2_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
  SqlCommand cmd;
  SqlCommand Command;
  SqlDataReader Reader;
  try
  {
    empid = Convert.ToInt32(dataGridView2.Rows[e.RowIndex].Cells["id"].Value);
    switch (e.ColumnIndex)
    {
      case 0:
        cn.Open();
        Command = new SqlCommand("Select * from cardetail where id='" + empid + "'", cn);
        Reader = Command.ExecuteReader();
        if (Reader.HasRows)
        {
          if (Reader.Read())
          {
            textBox8.Text = Reader.GetValue(0).ToString();
            textBox9.Text = Reader.GetValue(1).ToString();
            textBox10.Text = Reader.GetValue(2).ToString();
            textBox11.Text = Reader.GetValue(3).ToString();
            textBox12.Text = Reader.GetValue(4).ToString();
            textBox13.Text = Reader.GetValue(5).ToString();
            textBox14.Text = Reader.GetValue(6).ToString();

            button4.Visible = true;
            button3.Visible = false;
          }
        }
        cn.Close();
      break;
      case 1:
        cn.Open();
        cmd = new SqlCommand("delete from cardetail where id = '" + empid + "'", cn);
        cmd.ExecuteNonQuery();
        cn.Close();
        MessageBox.Show("Deleted Successfully.....");
        binddata();
      break;
      case 2:
        cn.Open();
        Command = new SqlCommand("Select * from cardetail where id='" + empid + "'", cn);
        Reader = Command.ExecuteReader();
        GenerateBillAndExport(Reader);
        cn.Close();
      break;
    }
  }
  catch (Exception ex)
  {
    MessageBox.Show(ex.Message);
  }
}

//Update Button Click <!--[if !supportLineBreakNewLine]--><!--[endif]-->
private void button4_Click(object sender, EventArgs e)
{
  string a = DateTime.Now.ToShortDateString();
  string query = "update cardetail set carno='" + textBox8.Text + "',carname='" + textBox9.Text + "',address='" + textBox10.Text + "',personname='" + textBox11.Text + "',cellno='" + textBox12.Text + "',detail='" + textBox13.Text + "',total='" +   Convert.ToDouble(textBox14.Text) + "' where id='" + empid + "'";
  SqlCommand cmd = new SqlCommand(query, cn);
  cn.Open();
  cmd.ExecuteNonQuery();
  MessageBox.Show("Record Updates Successfully.....");
  cn.Close();
  //clear();
  binddata();
  button3.Visible = true;
  button4.Visible = false;
}

private void Form1_Load(object sender, EventArgs e)
{
  // TODO: This line of code loads data into the 'uHDataSet1.cardetail' table. You can move, or remove it, as needed.
  this.cardetailTableAdapter1.Fill(this.uHDataSet1.cardetail);
  // TODO: This line of code loads data into the 'uHDataSet.cardetail' table. You can move, or remove it, as needed.
  // this.cardetailTableAdapter.Fill(this.uHDataSet.cardetail);
  this.binddata();
}

//Insert button Code
private void button3_Click(object sender, EventArgs e)
{
  string a = DateTime.Now.ToShortDateString();
  string query = "insert into cardetail values(@carno,@carname,@address,@personname,@cellno,@detail,@total)";
  SqlCommand cmd = new SqlCommand(query, cn);
  cmd.Parameters.AddWithValue("@carno", textBox8.Text);
  cmd.Parameters.AddWithValue("@carname", textBox9.Text);
  cmd.Parameters.AddWithValue("@address", textBox10.Text);
  cmd.Parameters.AddWithValue("@personname", textBox11.Text);
  cmd.Parameters.AddWithValue("@cellno", textBox12.Text);
  cmd.Parameters.AddWithValue("@detail", textBox13.Text);
  cmd.Parameters.AddWithValue("@total", textBox14.Text);
  cn.Open();
  cmd.ExecuteNonQuery();
  MessageBox.Show("Record Inserted Successfully.....");
  cn.Close();
  //clear();
  binddata();
  button3.Visible = false;
  button4.Visible = true;
}

Expression Builder Lookup using X++ in dynamics ax

Expression Builder Lookup using X++ in dynamics ax

Description:-

I was just wondering if there is any way of popping a lookup form during the program execution.
We have seen many ways of creating lookups – EDT’s, relations, AutoLookup and through custom code [SysTableLookup]

Let us consider an example: I have a select query whose behaviour should be dynamic based on the User input. We might have used query Framework classes many a times to do this.
I was just going through the standard classes and found this code useful with minimum tweaking of code.

Try out the below example: This example will help you to select the custAccount and based on the selected customer account it brings you the last invoiced sales order.

Note: The columns in the form are built runtime using the AutoLookup fields.

Code:-

static void SR_expressionBuilderLookUp(Args _args)
{
  CustTable custTable;
  ExtendedDataTypeName extendedDataType = extendedtypestr(custAccount);
  CustAccount custAccount;
  Object expressionBuilderLookup;
  Args args = new Args(formstr(SysExpressionBuilderLookup));
  ;
  expressionBuilderLookup = new FormRun(args);
  expressionBuilderLookup.parmExtendedDataType(extendedDataType);
  expressionBuilderLookup.init();
  expressionBuilderLookup.run();
  expressionBuilderLookup.wait();
  custAccount = expressionBuilderLookup.parmSelectedValue();
  if(custAccount)
  {
    info(strfmt('Last SO of customer %1 is %2', custAccount, CustTable::find(custAccount).lastInvoice().SalesId));
  }
}


Remove temp data from computer

Description:-

In this tricks we will see how to remove temp file data to avoid files which files we want to use more. Here is the step to remove temp files data from your C:\ drive.

Step 1:-

Open your run window or else type "Win + r".


Ant type in run window "%temp%" and hit Enter.
You’re temp file location window will open. Select all and press "Shift + Del" and remove it.
CSS Animations and Transitions in asp.net

CSS Animations and Transitions in asp.net

Description:-

CSS animations are finally available in all major browsers, even in IE (since version 10). There are two ways to create CSS animations. The first is very easy, it is done through animating the changes of CSS properties with the transition declaration. With transitions, you can create hover or mouse down effects, or you can trigger the animation by changing the style of an element with JavaScript. You can see the transition below by hovering over the planet – this will cause the rocket to close in.

The second way for defining animations is a bit more complicated – it involves the description of specific moments of the animation with the @keyframe rule. This allows you to have repeating animations that don’t depend on user actions or JavaScript to get triggered. Hit the Edit button to see the code.

HTML:-

<div class="container">
  <div class="planet"></div>
  <div class="rocket"></div>
</div>

CSS:-

.container{
 width: 300px;
 height:300px;
 margin: 0 auto;
 position:relative;
 overflow:hidden;
}

.planet{
 position:absolute;
 top:0;
 left:0;
 width:100%;
 height:100%;
 background:url(http://demo.tutorialzine.com/2013/10/css3-features-you-can-finally-use/assets/img/planet.png) no-repeat center center;
}

.rocket{
 position:absolute;
 top:0;
 left:0;
 width:100%;
 height:100%;
 background:url(http://demo.tutorialzine.com/2013/10/css3-features-you-can-finally-use/assets/img/rocket.png) no-repeat 50px center;

 /* Chrome still requires the -webkit- prefix */
 -webkit-animation:orbit 2s linear infinite;
 animation:orbit 2s linear infinite;

 transition:background-position 0.8s;
}

.container:hover .rocket{
 background-position:80px center;
}

/* Define the keyframes of the animation */

@-webkit-keyframes orbit {
 from {
  -webkit-transform:rotate(0deg);}
 to {
  -webkit-transform:rotate(360deg);
 }
}

@keyframes orbit {
 from {
  transform:rotate(0deg);

  /* I am including the -webkit-transform properties, because
     Chrome might start supporting keyframe without prefix in the future,
     but we can't be certain whether it will support prefix-free transform
     at the same time */

  -webkit-transform:rotate(0deg);}
 to {
  transform:rotate(360deg);
  -webkit-transform:rotate(360deg);
 }
}

How to add multiple datasource in custom lookup in dynamics ax

How to add multiple datasource in custom lookup in dynamics ax

Description:-

In this article we will see about how to add multi-table in custom lookup. here is the job to add multi-table in custom lookup.

Job:-

public void lookup()
{
    Query                   query = new Query();
    QueryBuildDataSource    qbdsItem;
    QueryBuildDataSource    qbdsJoin,qbdsJoin1,qbdsJoin2,qbdsJoin3;
    //QueryBuildDataSource    qbdsInventItemLocation;
    QueryBuildRange         qbr,qbrModuleType;
    SysMultiTableLookup     sysTableLookup;
    ;
    qbdsItem = query.addDataSource(tableNum(InventTable));
    qbdsJoin= qbdsItem.addDataSource( tableNum(EcoResProduct),"EcoResProduct");
    qbdsJoin.relations( false);
    qbdsJoin.fields().dynamic(NoYes::Yes);
    qbdsJoin.addLink( fieldNum(InventTable, ItemId), fieldNum(EcoResProduct, DisplayProductNumber));
    qbdsJoin.joinMode(JoinMode::InnerJoin);

    qbdsJoin1= qbdsJoin.addDataSource( tableNum(EcoResProductMaster));
    qbdsJoin1.relations( false);
    qbdsJoin1.fields().dynamic(NoYes::Yes);
    qbdsJoin1.addLink( fieldNum(EcoResProduct, RecId), fieldNum(EcoResProductMaster, RecId));
    qbdsJoin1.joinMode(JoinMode::InnerJoin);

    qbdsJoin2= qbdsJoin1.addDataSource( tableNum(EcoResProductMasterSize));
    qbdsJoin2.relations( false);
    qbdsJoin2.fields().dynamic(NoYes::Yes);
    qbdsJoin2.addLink( fieldNum(EcoResProductMaster, RecId), fieldNum(EcoResProductMasterSize, SizeProductMaster));
    qbdsJoin2.joinMode(JoinMode::InnerJoin);

    qbdsJoin3= qbdsJoin2.addDataSource( tableNum(EcoResSize));
    qbdsJoin3.relations( false);
    qbdsJoin3.fields().dynamic(NoYes::Yes);
    qbdsJoin3.addLink( fieldNum(EcoResProductMasterSize, Size), fieldNum(EcoResSize, RecId));
    qbdsJoin3.joinMode(JoinMode::InnerJoin);
    //qbr=qbdsJoin.addRange(fieldNum(InventModelGroupItem,ModelGroupId));
    //qbr.value('FIFO');
    //qbr=qbdsJoin.addRange(fieldNum(InventTable,ItemId));
    //qbr.value(A_Test_ItemId.valueStr());


    sysTableLookup = SysMultiTableLookup::newParameters(this, query);

    sysTableLookup.addLookupField(fieldNum(InventTable, ItemId));
    //sysTableLookup.addLookupField(fieldNum(InventTable, ItemName));
    //sysTableLookup.addLookupMethod(tableMethodStr(InventTable, ConfigActive), 1, "Is configuration active?");
    sysTableLookup.addLookupField(fieldNum(EcoResSize,Name), 1, false, "Item Size");
    sysTableLookup.performFormLookup();
}

How to add range in multiple table lookup in dynamics ax

How to add range in multiple table lookup in dynamics ax

Description:-

In this article we will see about how to add range in multiple table lookup. here i have used multi table lookup in QueryBuildDataSource so we can add range in customer lookup. here i have given Job how to add range in multi-table lookup.

Job:-

public void lookup()
{
    Query                   query = new Query();
    QueryBuildDataSource    qbds;
    QueryBuildDataSource    qbdsJoin;
    SysTableLookup          sysTableLookup = sysTableLookup::newParameters( tableNum(InventTable), this);
    QueryBuildRange qbr;
    ;

    qbds= query.addDataSource( tableNum(InventTable));
    qbdsJoin= qbds.addDataSource( tableNum(InventModelGroupItem));
    qbdsJoin.relations( false);
    qbdsJoin.fields().dynamic(NoYes::Yes);
    qbdsJoin.addLink( fieldNum(InventTable, ItemId), fieldNum(InventModelGroupItem, ItemId));
    qbdsJoin.joinMode(JoinMode::InnerJoin);
    qbr=qbdsJoin.addRange(fieldNum(InventModelGroupItem,ModelGroupId));
    qbr.value('FIFO');
    qbr=qbdsJoin.addRange(fieldNum(InventModelGroupItem,ModelGroupDataAreaId));
    qbr.value(curext());
    //qbd.addRange(fieldnum(InventTable,ItemType)).value(SysQuery::value(enum2str(ItemType::Item)));
    //qbr.value('FIFO');
    sysTableLookup.parmQuery(query);
    sysTableLookup.addLookupfield( fieldNum(InventTable, ItemId),true);
    sysTableLookup.addLookupfield( fieldNum(InventTable,NameAlias));
    sysTableLookup.addLookupfield( fieldNum(InventTable,ItemType));
    sysTableLookup.addLookupfield( fieldNum(InventTable,Product));
    //sysTableLookup.addLookupMethod( tableMethodStr(InventTable,itemGroupId));
    //sysTableLookup.addLookupMethod( tableMethodStr(InventTable,defaultProductName));
   // sysTableLookup.addLookupfield( fieldNum(CustTable, VendGroup), true);
    //sysTableLookup.addLookupfield( fieldNum(InventTable, ItemId));
    sysTableLookup.performFormLookup();
}

dynamically change gridview cell color based on cell value in asp.net

dynamically change gridview cell color based on cell value in asp.net

Description:-
In this example we explain that how to change the color of the GridView cell based on some condition. Or dynamically change Gridview cell color based on cell value condition in asp.net. Or how to dynamically change the Background color of ASP.Net GridView Row based on some conditions

Sometime we have requirement like display data in grid in different color so end user can easily understand. Suppose we have one table student with column name age. If age <= 15 then we have to cell color="yellow". If age>=15 then we have to set GridView cell color ="Red”. To achieve this type of functionality we have to use RowDatabound event of the Gridview.

So to change the GridView row background dynamically based on some condition you have to write the below code.

Create Table:-

CREATE TABLE [dbo].[orders](
 [orderno] [int] NOTNULL,
 [OrderDate] [datetime] NULL,
 [ShipCountry] [varchar](10)NULL,
 [CustomerID] [int] NULL,
 [RequiredDate] [datetime] NULL,
 [ShipName] [varchar](10)NULL,
 [ShipCity] [varchar](10)NULL,
PRIMARY KEY CLUSTERED
(
 [orderno] 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

Default.aspx:-

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
  <title></title>
</head>
<body>
<form id="form1"runat="server">
  <div>
    <asp:GridView ID="grd_student" runat="server"Width="100%"AutoGenerateColumns="False" OnRowDataBound="grd_student_RowDataBound" BackColor="#DEBA84" BorderColor="#DEBA84" BorderStyle="None" BorderWidth="1px" CellPadding="3" CellSpacing="2">
    <FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510"/>
    <HeaderStyle BackColor="#A55129" Font-Bold="True" Font-Names="cambria" ForeColor="White"Height="25px"/>
    <PagerStyle ForeColor="#8C4510" HorizontalAlign="Center"/>
    <RowStyleFont-Names="Calibri"Height="25px"BackColor="#FFF7E7"ForeColor="#8C4510"/>
    <Columns>
      <asp:BoundField DataField="orderno" HeaderText="Order No" ItemStyle-Width="100px"/>
      <asp:BoundField DataField="OrderDate" HeaderText="Order Date" ItemStyle-Width="280px" ItemStyle-HorizontalAlign="Center"/>
      <asp:BoundField DataField="ShipCountry" HeaderText="Country" ItemStyle-Width="100px" ItemStyle-HorizontalAlign="Center"/>
      <asp:BoundField DataField="CustomerID" HeaderText="Customer Id" ItemStyle-Width="100px" ItemStyle-HorizontalAlign="Center"/>
      <asp:BoundField DataField="RequiredDate" HeaderText="Required Date" ItemStyle-Width="280px" ItemStyle-HorizontalAlign="Center"/>
      <asp:BoundField DataField="ShipName" HeaderText="Ship Name" ItemStyle-Width="100px" ItemStyle-HorizontalAlign="Center"/>
      <asp:BoundField DataField="ShipCity" HeaderText="City" ItemStyle-Width="100px" ItemStyle-HorizontalAlign="Center"/>
    </Columns>
    <SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White"/>
    <SortedAscendingCellStyle BackColor="#FFF1D4"/>
    <SortedAscendingHeaderStyle BackColor="#B95C30"/>
    <SortedDescendingCellStyle BackColor="#F1E5CE"/>
    <SortedDescendingHeaderStyle BackColor="#93451F"/>
    </asp:GridView>
  </div>
</form>
</body>
</html>

Default.aspx.cs:-

SqlConnection conn = new SqlConnection("your connection string");
protected void Page_Load(object sender, EventArgs e)
{
  if (!IsPostBack)
  {
    Populate_studGrid();
  }
}

protected void Populate_studGrid()
{
  DataSet ds = new DataSet();
  string cmdstr = "select * from orders";
  SqlCommand cmd = new SqlCommand(cmdstr, conn);
  SqlDataAdapter adp = new SqlDataAdapter(cmd);
  adp.Fill(ds);
  grd_student.DataSource = ds;
  grd_student.DataBind();
}

protected void grd_student_RowDataBound(object sender, GridViewRowEventArgs e)
{
  if (e.Row.RowType == DataControlRowType.DataRow)
  {
    string CountryName = e.Row.Cells[2].Text; //it cell represent a student age
    if (CountryName == "USA")
        e.Row.Cells[2].BackColor = System.Drawing.Color.AliceBlue;
    else if (CountryName == "India")
        e.Row.Cells[2].BackColor = System.Drawing.Color.Yellow;
    else
        e.Row.Cells[2].BackColor = System.Drawing.Color.Pink;
  }
}

Executescalar in asp.net

Executescalar in asp.net

Description:-

In this article we will see about Execute Scalar in Webpage. How to use it and to get data by using it in code behind. Using this we can get one data at a time from database like if we want total country from database then we can use Scalar function for reading single reading data from database. It use aggregate function to read it and get from database. Here is the demo example to get total country from database and display it on your webpage.

Create Table:-

CREATE TABLE [dbo].[Country](
 [CountryID] [bigint] IDENTITY(1,1)NOTNULL,
 [CountryName] [varchar](50)NULL,
CONSTRAINT [PK_Country] PRIMARYKEYCLUSTERED
(
 [CountryID] 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

Default.aspx:-

<div>
 <asp:Button ID="Button1" runat="server" Text="Count Country" OnClick="Button1_Click"/>
</div>
<asp:Label ID="Label1" runat="server"Text=""></asp:Label>

Default.aspx.cs:-

protected void Button1_Click(object sender, System.EventArgs e)
{
  string connectionString = ConfigurationManager.ConnectionStrings["DBCS_New"].ToString();
  SqlConnection connection = new SqlConnection(connectionString);
  string sql = "select count(*) from Country";
  try
  {
    connection.Open();
    SqlCommand cmd = new SqlCommand(sql, connection);
    int result = Convert.ToInt32(cmd.ExecuteScalar());
    connection.Close();
    Label1.Text = "Number of Countries - " + result;
  }
  catch (Exception ex)
  {
    Label1.Text = "Error in ExecuteScalar " + ex.ToString();
  }
}

Main Table Functionality in Ax 2012

Main Table Functionality in Ax 2012

How to get that functionality in our forms or table. This is done by three ways:

1) By EDT Relations
2) By using JumpRef method
3) By using FormRef property in Table

EDT Relations:
If you use an EDT in tables which have relation with some other table fields, that time you can able to navigate the main table or main form.

FormRef Property:
Select the Table and go to properties and select the required form in the FormRef property.

JumpRef method:
If you are not having that option, simply write an override the JumpRef method in a field of DataSource or in a Form Control. Here i show you a sample jumpRef method code:

public void jumpRef()
{
   Args            args;
   MenuFunction    menuFunction;
   ;
   args = new Args();
   menuFunction = new MenuFunction(menuitemDisplayStr(FormName), MenuItemType::Display);
   args = new Args(menuFunction.object());
   args.caller(element);
   args.record(RecordName); // to be a datasource which added in the current form
   menuFunction.run(args);
}
How to encrypt and decrypt xml file in asp.Net

How to encrypt and decrypt xml file in asp.Net

Description:-

In this example we explain that how to encrypt and Decrypt XML file in asp.net with C#.in previous we already explain that how to Encrypt and Decrypt Password when user is register to website then it's password are stored in SqlServer database in Encrypt format and when user is login to website then it will fetch the Encrypt password from the database and Decrypt it and match with user enter password.
So this is simple thing but now in this example we will explain how to encrypt and Decrypt whole XML file in asp.net. The main advantages to Encrypt and Decrypt XML file is that even user cannotsee data in XML file because data are stored in Encrypt format so it cannot be read by any user.
When any operation is performed in XML file then it will first Decrypt XML file and then perform operation. So this is the best facility for any application for better security.

Default.aspx:-

<div>
  <asp:FileUpload ID="flupld_xmlfile" runat="server"/>
  <asp:Button ID="EncryptFileButton" runat="server" Text="Encrypt" onclick="EncryptFileButton_Click"/>
  <asp:Button ID="DecryptFileButton" runat="server" Text="Decrypt" onclick="DecryptFileButton_Click"/>
</div>

Default.aspx.cs:-

protected void Page_Load(object sender, EventArgs e) { }
protected void EncryptFileButton_Click(object sender, EventArgs e)
{
  //Get the Input File Name and Extension. 
  string fileName = Path.GetFileNameWithoutExtension(flupld_xmlfile.PostedFile.FileName);
  string fileExtension = Path.GetExtension(flupld_xmlfile.PostedFile.FileName);
  //Build the File Path for the original (input) and the encrypted (output) file. 
  string input = Server.MapPath("~/Files/") + fileName + fileExtension;
  string output = Server.MapPath("~/Files/") + fileName + "_enc" + fileExtension;
  //Save the Input File, Encrypt it and save the encrypted file in output path. 
  flupld_xmlfile.SaveAs(input);
  this.Encrypt(input, output);
  //Download the Encrypted File. 
  Response.ContentType = flupld_xmlfile.PostedFile.ContentType;
  Response.Clear();
  Response.AppendHeader("Content-Disposition", "attachment; filename=" + Path.GetFileName(output));
  Response.WriteFile(output);
  Response.Flush();
  //Delete the original (input) and the encrypted (output) file. 
  File.Delete(input);
  File.Delete(output);
  Response.End();
}

private void Encrypt(stringinputFilePath, stringoutputfilePath)
{
  stringEncryptionKey = "MAKV2SPBNI99212";
  using (Aesencryptor = Aes.Create())
  {
    Rfc2898DeriveBytespdb = newRfc2898DeriveBytes(EncryptionKey, newbyte[] { 0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d, 0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76 });
    encryptor.Key = pdb.GetBytes(32); encryptor.IV = pdb.GetBytes(16);
    using (FileStreamfsOutput = newFileStream(outputfilePath, FileMode.Create))
    {
      using (CryptoStreamcs = newCryptoStream(fsOutput, encryptor.CreateEncryptor(), CryptoStreamMode.Write))
      {
        using (FileStreamfsInput = newFileStream(inputFilePath, FileMode.Open))
        {
          int data; while ((data = fsInput.ReadByte()) != -1)
          {
            cs.WriteByte((byte)data);
          }
        }
      }
    }
  }
}

protected void DecryptFileButton_Click(object sender, EventArgs e)
{
  //Get the Input File Name and Extension 
  string fileName = Path.GetFileNameWithoutExtension(flupld_xmlfile.PostedFile.FileName);
  string fileExtension = Path.GetExtension(flupld_xmlfile.PostedFile.FileName);
  //Build the File Path for the original (input) and the decrypted (output) file 
  string input = Server.MapPath("~/Files/") + fileName + fileExtension;
  string output = Server.MapPath("~/Files/") + fileName + "_dec" + fileExtension;
  //Save the Input File, Decrypt it and save the decrypted file in output path. 
  flupld_xmlfile.SaveAs(input);
  this.Decrypt(input, output);
  //Download the Decrypted File. 
  Response.Clear();
  Response.ContentType = flupld_xmlfile.PostedFile.ContentType;
  Response.AppendHeader("Content-Disposition", "attachment; filename=" + Path.GetFileName(output));
  Response.WriteFile(output);
  Response.Flush();
  //Delete the original (input) and the decrypted (output) file. 
  File.Delete(input);
  File.Delete(output);
  Response.End();
}

private void Decrypt(stringinputFilePath, stringoutputfilePath)
{
  string EncryptionKey = "MAKV2SPBNI99212";
  using (Aesencryptor = Aes.Create())
  {
    Rfc2898DeriveBytespdb = newRfc2898DeriveBytes(EncryptionKey, newbyte[] { 0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d, 0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76 });
    encryptor.Key = pdb.GetBytes(32); encryptor.IV = pdb.GetBytes(16);
    using (FileStreamfsInput = newFileStream(inputFilePath, FileMode.Open))
    {
      using (CryptoStreamcs = newCryptoStream(fsInput, encryptor.CreateDecryptor(), CryptoStreamMode.Read))
      {
        using (FileStreamfsOutput = newFileStream(outputfilePath, FileMode.Create))
        {
          int data; 
          while ((data = cs.ReadByte()) != -1)
          {
            fsOutput.WriteByte((byte)data);
          }
        }
      }
    }
  }
}

Substring in SSRS reprt

Substring in SSRS reprt

Description:-

In this article we will see how to use substring in ssrs report. Here I have used simple syntax to used substring in ssrs report. here is the expression to use substring in SSRS report. here my current year is 2016. you can set how you want see my previous post to set different expression to use in SSRS report.

Expression:- 

=Format(today()).Substring(2)
Output:-16
Get total sales of invoice customer using sql query in dynamics ax

Get total sales of invoice customer using sql query in dynamics ax

Description:-

In this query we will see about how to get invoice customer total sales based on parameter. Here I have I have used my custom table for map customer accounts and get totals invoice customer sales for that customer which is in my customer table. Based on invoice date I have pass parameter for getting particular range data.

SQL Query:-

select sum(SALESLINE.LINEAMOUNT),SALESTABLE.CUSTGROUP from SALESTABLE
join CUSTINVOICEJOUR on CUSTINVOICEJOUR.SALESID = SALESTABLE.SALESID and SALESTABLE.SALESSTATUS = 3 
join SALESLINE on SALESLINE.SALESID = SALESTABLE.SALESID
join CUSTOMTABLE on CUSTOMTABLE.CustAccount = SALESTABLE.CUSTACCOUNT
where CUSTINVOICEJOUR.INVOICEDATE >= '<FromDate>' and CUSTINVOICEJOUR.INVOICEDATE <= '<ToDate>'
and SALESLINE.DATAAREAID ='<Company>' and CUSTINVOICEJOUR.DATAAREAID = '<Company>' and SALESTABLE.DATAAREAID = '<Company>' and CUSTOMTABLE.DATAAREAID = '<Company>'
group by SALESTABLE.CUSTGROUP 
order by SALESTABLE.CUSTGROUP asc