Vasanth Arivali's Blog

Hi,

Here i write a sample code to find the Table Name of Ax Tables using the TableId.

static void findTables(Args _args)

{

Dictionary            dictionary;

TableId                                  tableId;

tableName         tableName;

;

dictionary            =             new Dictionary();

// tableId             =             dictionary.tableNext(0);

tableId                  =             359;       //366; //62;

tableName         =             dictionary.tableName(tableId);

info(strfmt(“%1 – %2”,int2str(tableId), tableName));

//while (tableId)

//{

// tableId = dictionary.tableNext(tableId);

// tableName = dictionary.tableName(tableId);

//}

}

Thank You,

Vasanth Arivali.

Advertisements

Hi,

Here a sample code to pass argument to one form to another form and using of Args() class.

Steps:

1) Create two Forms named FormA and FormB

2)Use the EmplTable as the Datasource of both forms

3)Design FormA with one Grid and add 4 data fields to the Grid(EmplId,DEL_Name,Grade,EmplStatus…..)

4)Assign the datasource for the grid and the data fields

5)Add a Button in FormA

6)Override the Clicked() method and write the below code:

void Clicked()

{

Args    _args;

FormRun _formRun;

EmplId   _empId;

;
_empId = EmplTable.EmplId;  // Selected employee id in the Grid is assigned to the variable which is pass to the next form

_args = new Args(); // creating a object for args class

_args.name(formstr(VA_FormB));  // Form Menuitem

_args.caller(this);  // Form Caller(Current Form is mentioned as this)

_args.parm(_empId); // Employee Number is passed to next form[but parm() is not a best practise]

_args.record(EmplTable); // Table name is passed

_formRun = ClassFactory.formRunClass(_args); //new FormRun(_args);   // Creating object for FormRun

_formRun.init();   // Form Initialization for Load

_formRun.run();  // Form Run for process

_formRun.wait(); // Form Wait for Display

}

7) Open the Second Form – FormB

8) Add one Grid Control and set the Data Source is EmplTable

9) Add 4 data fields as same in the Form A

10)Now Override the Init() of Form

public void init()

{
parmid      _parmId;

EmplTable   _EmplTable;

//     DictTable   _dictTable;    FormBuildDataSource   _ds;    FormBuildGridControl frmGrid; // These are for dynamic Form creation so leave it

_parmId =  element.args().parm(); // Getting the argument value from the Caller
//info(int2str(element.args().record().TableId));

if(!element.args().caller())   // Check the form is called by caller or directly, if directly it throw error

throw error(“Cant Run Directly”);
if(element.args().record().TableId == tablenum(EmplTable))   // check if the sent Table and the Current form table are equal or not

{
//        _EmplTable = element.args().record();  // Assign the Received Table name to Local Variable

//_dictTable = new DictTable(element.args().record().TableId);  // leave it , is used for Dynamic Form Creation

//_ds = form.addDataSource(_dictTable.name());  // leave it , is used for Dynamic Form Creation

//_ds.table(_dictTable.id());   // leave it , is used for Dynamic Form Creation
//frmGrid = form.addControl(FormControlType::Grid, “Grid”);   // leave it , is used for Dynamic Form Creation

//frmGrid.dataSource(_ds.name());   // leave it , is used for Dynamic Form Creation
//info(strfmt(“%1     %2”,_EmplTable.EmplId,_EmplTable.DEL_Name));
//frmGrid.addDataField(_ds.id(),fieldnum(EmplTable, DEL_Name));  // leave it , is used for Dynamic Form Creation
//        EmplTable_EmplId.dataSource(_EmplTable);   // leave it , is used for Dynamic Form Creation

//        EmplTable_EmplId.dataField(fieldnum(EmplTable,EmplID));   // leave it , is used for Dynamic Form Creation

//        EmplTable_DEL_Name.dataSource(_EmplTable);   // leave it , is used for Dynamic Form Creation

//        EmplTable_DEL_Name.dataField(fieldnum(EmplTable,EmplId));  // leave it , is used for Dynamic Form Creation

//        EmplTable_DEL_Email.dataSource(_EmplTable);   // leave it , is used for Dynamic Form Creation

//        EmplTable_DEL_Email.dataField(fieldnum(EmplTable,EmplId));   // leave it , is used for Dynamic Form Creation

super();  // Form Initialization

}

else

{

info(“DataSet Not Received”);  // throw error

}

}

11)Override the Init() of the DataSource

public void init()

{

switch(element.args().dataset())// get the table id sent by caller

{

case tablenum(EmplTable):  // check the table if matches with this tableid

{

_EmplID  =   element.args().parm();  // get the argument value

query   = new Query();            queryBuildRangeProj =                                          query.addDataSource(tablenum(EmplTable)).addRange(fieldnum(EmplTable,EmplId));          // query build for the form to display

queryBuildRangeProj.value(_emplId); // Criteria for the form

EmplTable_ds.query(query); // execution of the query

break;

}

}
super(); //datasource  initialization on the form based on the criteria

}

12) Save it, and create two menu items for each.

13) It is important to change the runon property of the FormB as CalledFrom.

14)Run the FormA and select an Employee Record and click the button.

15)The FormB opens with the Related information of the Selected Employee on form.

Thanks & Regards,

Vasanth Arivali

This is the example to connect the External Database to Ax

void ODBCConnection()

{

LoginProperty LP                =  new LoginProperty();

OdbcConnection                  myConnection;

TableName                            TableName;

Statement                              myStatement;

ResultSet                                 myResult;

#define.Constring(“DSN=DSNNAME;UID=USERID;PWD=PASSWORD“)

try

{

LP.setOther(#Constring);

myConnection = new OdbcConnection(LP);

}

catch

{

info(“Check username/password.”);

return;

}
myStatement = myConnection.createStatement();

new SqlStatementExecutePermission(“SELECT * from TableNamewhere bImportflag = 0”).assert();

myResult = myStatement.executeQuery(“SELECT * from TableNamewhere bImportflag = 0”);

CodeAccessPermission::revertAssert();

while (myResult.next())

{

TableName.nMember                = Member;

TableName.tMemberName            = MembName;

TableName.tMemberAddress         = Address;

TableName.tRescountry            = Country;

TableName.tResState              = State;

TableName.insert();

}

}

Here i post code for Import Excel Data into Dynamics using X++ code.

Here the code written in Command Button clicked event, and also i  added the Excel format below of this post which i used.

void clicked()
{
SysExcelApplication             application;
SysExcelWorkbooks               workbooks;
SysExcelWorkbook                workbook;
SysExcelWorksheets              worksheets;
SysExcelWorksheet               worksheet;
SysExcelCells                   cells;
COMVariantType                  type;
System.DateTime                 ShlefDate;

FilenameOpen                    filename;
dialogField                     dialogFilename;

Dialog                          dialog;

//Table Declarations Starts

InventSize                      _InventSize;
InventBatch                     _InventBatch;
InventSerial                    _InventSerial;
InventTable                     _InventTable;
VendParameters                  _vendParameters;

//Table Declartions Ends

InventBatchId                   batchNumber;
InventBatchExpDate              expdate;
itemId                          itemid;
TransDate                       poddate;
CertificatesofSterilization  Certs;
CertificatesofAnalysis       CertiAnalysis;
InventSizeId                    InventSize;
ConfigId                        _ConfigId;
InventColorId                   _InventColorId;
InventSiteId                    _InventSiteId;
WMSLocationId                   _WMSLocationId;
InventLocationId                _InventLocationId;
WMSPalletId                     _WMSPalletId;
NoYesId                         ClosedTransactions;
NoYesId                         ClosedTransQty;
str                             pONo;
str                             srNo;
real                            quantity;
int                             row;
InventBatchExpDate              ShelfLifeDate;

#Excel

// convert into str from excel cell value

str COMVariant2Str(COMVariant _cv, int _decimals = 0, int _characters = 0, int _separator1 = 0, int _separator2 = 0)
{
switch (_cv.variantType())
{
case (COMVariantType::VT_BSTR):
return _cv.bStr();

case (COMVariantType::VT_R4):
return num2str(_cv.float(),_characters,_decimals,_separator1,_separator2);

case (COMVariantType::VT_R8):
return num2str(_cv.double(),_characters,_decimals,_separator1,_separator2);

case (COMVariantType::VT_DECIMAL):
return num2str(_cv.decimal(),_characters,_decimals,_separator1,_separator2);

case (COMVariantType::VT_DATE):
return date2str(_cv.date(),123,2,1,2,1,4);

case (COMVariantType::VT_EMPTY):
return “”;

default:
throw error(strfmt(“@SYS26908”, _cv.variantType()));
}
return “”;
}

;

dialog              =   new Dialog(“Excel Upoad”);
dialogFilename      =   dialog.addField(typeId(FilenameOpen));

dialog.filenameLookupFilter([“@SYS28576″,#XLSX,”@SYS28576”,#XLS]);
dialog.filenameLookupTitle(“Upload from Excel”);
dialog.caption(“Excel Upload”);

dialogFilename.value(filename);

if(!dialog.run())
return;

filename            =   dialogFilename.value();

application         =   SysExcelApplication::construct();
workbooks           =   application.workbooks();

try
{
workbooks.open(filename);
}
catch (Exception::Error)
{
throw error(“File cannot be opened.”);
}

workbook            =   workbooks.item(1);
worksheets          =   workbook.worksheets();
worksheet           =   worksheets.itemFromNum(1);
cells               =   worksheet.cells();

try
{
ttsbegin;

do
{
row++;

pONo                    =   COMVariant2Str(cells.item(row, 1).value());
itemid                  =   COMVariant2Str(cells.item(row,2).value());
InventSize         =   COMVariant2Str(cells.item(row, 3).value());
batchNumber             =   COMVariant2Str(cells.item(row, 4).value());
poddate                 =   cells.item(row, 5).value().date();
expdate                 =   cells.item(row, 6).value().date();
srNo                    =   COMVariant2Str(cells.item(row, 7).value());
Certs                =   str2enum(Certs,cells.item(row, 8).value().bStr());
CertiAnalysis        =   str2enum(CertiAnalysis,cells.item(row, 9).value().bStr());
quantity                =   cells.item(row, 10).value().double();
_ConfigId               =   COMVariant2Str(cells.item(row, 12).value());
_InventColorId          =   COMVariant2Str(cells.item(row, 13).value());
_InventSiteId           =   COMVariant2Str(cells.item(row, 14).value());
_WMSLocationId          =   COMVariant2Str(cells.item(row, 15).value());
_InventLocationId       =   COMVariant2Str(cells.item(row, 16).value());
_WMSPalletId            =   COMVariant2Str(cells.item(row, 17).value());
ClosedTransactions      =   str2enum(ClosedTransactions,cells.item(row, 18).value().bStr());
ClosedTransQty          =   str2enum(ClosedTransQty,cells.item(row, 19).value().bStr());

if(row > 1)
{

//Insert into InventSize Table

select firstonly _InventSize where _InventSize.ItemId == itemid && _InventSize.InventSizeId == InventSize;

if(!_InventSize)
{
_InventSize.InventSizeId     =      InventSize;
_InventSize.ItemId           =      itemid;
_InventSize.insert();
}
else
{
warning(strfmt(“Item Id and InventSize (%1   –   %2) already exists”,itemid,InventSize));
}

// Insert into InventBatch Table

_InventBatch.inventBatchId      =       batchNumber;
_InventBatch.itemId             =       itemid;
_InventBatch.prodDate           =       poddate;
_InventBatch.expDate            =       expdate;
_InventBatch.insert();

// Insert into InventSerial Table

_InventSerial.InventSerialId    = srNo;
_InventSerial.ItemId            = itemid;
_InventSerial.ProdDate          = poddate;
_InventSerial.insert();

info(strfmt(“Item(%1) uploaded successfully”,itemid));

}

type = cells.item(row+1, 1).value().variantType();

}while (type != COMVariantType::VT_EMPTY);

application.quit();

ttscommit;
}
catch
{
Error(“Upload Failed”);
}

}

 

Excel Format Image for My Import

My Excel Format

These are the details i used to import from Excel.

Just give DSN, Username, Password in the LoginProperty.

Hi, 

I connect sql server db(not a default one it is a another db) from Dynamics Axapta 2009.
I used odbc to connect and retrive data.

It was work fine in the server.
but when i was woked in ax client it show an error like this:

1) ODBC operation failed.Unable to log on to the database.

2) [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user ‘ASCENDERS\vasanth’.

3)Object ‘OdbcConnection’ could not be created

i am also given the user name as sqlserver username “sa” but it take my window authentication.
i don’t know how to solve the problem.plz help me with your valuable ideas..

Thanks in advance

Vasanth.A

Vasanth Arivali

Vasanth Arivali

This is my first Post

Welcome

What’s happening?

  • RT @HAJAMYDEENNKS: விஜய்யோட வாக்காளர் அடையாள அட்டையை அவர் அனுமதி இல்லாமல் H.ராஜா ட்விட்டரில் பதிவிட்டது சரியா ? ஒரு பிரபலத்துக்கே இப்படின்… 7 hours ago
  • RT @Enfielderstweet: பேருந்துகளிலே பஞ்சமர்கள் ஏறக்கூடாது என்பதை ஒழித்தவர் டபிள்யூ பி.ஏ. சவுந்திரபாண்டியன். அவர் பெயரால் ஆனது பாண்டி பஜார்.… 7 hours ago
  • RT @toptamilnews: அதிமுகவையும், இரட்டை இலையையும் மோடி காப்பாற்றுவார் எனில் எங்களை யார் காப்பாற்றுவார்கள்? : இயக்குநர் ராஜுமுருகன் #RajuMuru15 hours ago
  • RT @MersalSuganya: சரி முகலாய வரலாற்ற தூக்கிடுவோம், ஆரியன் வறலாற்றை போற்றிடுவோம். ஆமாம் ஆரியனுக்கு என்ன வரலாறு இருக்கு மணி ஆட்டுனத தவிர்த்… 15 hours ago
  • RT @PTTVOnlineNews: கொசுக்கள் உற்பத்தியாகும் வகையில் அரசு இடங்கள் இருந்தால் யாருக்கு அபராதம் விதிக்கப்படும்?: சீமான் #Seeman 19 hours ago
  • RT @maharaja_2020: எங்கன போனாலும் விடமாட்டேங்கிறாங்களே.!!!! சுத்தி சுத்தி அடிக்கிறாங்களே.,,!! ## பக்தாள் வாய்ஸ் ## https://t.co/ttNvF5YH… 21 hours ago
  • RT @Sukrancool: உண்மையான மெர்சல் அரசன் #INDIANSVsModi @RajasekarASho7 @maharaja_2020 @madhavaeshwaran @leochordia @viswarajantweet @anusya122 hours ago
  • RT @Mark2kali: பிஜேபியை கண்மூடித்தனமாக ஆதரிக்கும் பக்தர்களுக்கும் இது ஒருநாள் நடந்தே தீரும். #TamiliansVsModi #Mersal https://t.co/WbBMPbZ… 23 hours ago
  • RT @MSRajRules: தமிழ்பாலில் கலக்கப்பட்ட தண்ணீர். வடமொழி : சத்தம் தமிழ் : ஓசை, ஒலி #தமிழ்அன்னம் 23 hours ago
  • RT @firebirdborn: தமிழன் இந்தியனுக்கு முன்னோடி..... இனி ஹிந்தி மெல்லச் சாகும் twitter.com/ShakkrPara/sta… 23 hours ago

Categories

Join 13 other followers