Hi,
I m developing a web services with the use of .net business connector in dynamics ax.
I want to pass the date value in the query.But is giving me error.
Here’s the code.
AxaptaRecord smmActivity;
AxaptaRecord smmActivityParent;
AxaptaRecord custTable;
AxaptaRecord sma;
AxaptaRecord Temp;
DateTime strtdate,enddate;
Int32 count;
int i;
string actNum = “”;
string date;
Int32 dateyear, datemonth, datedt,date1;
double date2;
//string dt = “08/22/2008”;
strtdate = DateTime.Parse(stdt);
date = strtdate.ToShortDateString();
enddate = Convert.ToDateTime(enddt);
//Use of Activities class
//Declaration of Activities class object array
Activities[] data;
Activities[] error = new Activities[1];
string smmActivities = “smmActivities”;
string smmActivityParentLinkTable = “smmActivityParentLinkTable”;
string CustTable = “CustTable”;
string smaServiceOrderTable = “SMAServiceOrderTable”;
// Output variables for calls to AxRecord.get_Field
//As AxRecord.get_Field returns object
Object ActNum, ResEmp,TskPriority,StartDt,Purpose,Status,CustAddress;
Object[] custdata = new Object[2];
// String used to count the records query table.
string strQuery1 = “select count(ActivityNumber) from %1 join %2 where %2.ParentType == 6 || %2.ParentType == 15 && %2.Primarylink == 1 && %2.ActivityNumber == %1.ActivityNumber join %4 where %4.RecId == %2.RefRecId && %1.ResponsibleEmployee == '” + EmplId + “’&& %1.Status == " + status + “&& %1.TaskPriority ==” + taskprrt +” && %1.StartDateTime == str2date(‘8/11/2011’,213)";
// String used to get the records query table.
string strQuery = “select * from %1 join %2 where %2.ParentType == 6 || %2.ParentType == 15 && %2.Primarylink == 1 && %2.ActivityNumber == %1.ActivityNumber join %4 where %4.RecId == %2.RefRecId && %1.ResponsibleEmployee == '” + EmplId + “’ && %1.Status == " + status + " && %1.TaskPriority ==” + taskprrt;
// string strQuery = “select * from %1 join %2 where %2.ParentType == 6 && %2.ActivityNumber == %1.ActivityNumber && %1.ResponsibleEmployee == '” + EmplId + “’ && %1.Status == " + status + " && %1.TaskPriority ==” + taskprrt; join %3 where %3.RecId == %2.RefRecId
// Log on to Microsoft Dynamics AX.
ax = new Axapta();
ax.Logon(“dat”, “”, “”, “”);
//Create axapta record object
smmActivity = ax.CreateAxaptaRecord(smmActivities);
smmActivityParent = ax.CreateAxaptaRecord(smmActivityParentLinkTable);
custTable = ax.CreateAxaptaRecord(CustTable);
sma= ax.CreateAxaptaRecord(smaServiceOrderTable);
// Execute the query on the table to count the records.
ax.ExecuteStmt(strQuery1, smmActivity, smmActivityParent, custTable,sma);
//ax.ExecuteStmt(strQuery1, smmActivity, smmActivityParent);
//Setting the value of count variable.
count = Convert.ToInt32(smmActivity.get_Field(“ActivityNumber”));
//Initializing the size of the array.
data = new Activities[count];
// Execute the query on the table to get the records.
ax.ExecuteStmt(strQuery, smmActivity, smmActivityParent, custTable, sma);
//ax.ExecuteStmt(strQuery, smmActivity, smmActivityParent);
// custTable.ExecuteStmt(strQuery);
// Loop through the set of retrieved records.
i = 0;
while (smmActivity.Found)
{
//Setting the object variable value returned by the AxRecord.get_Field.
ActNum = smmActivity.get_Field(“ActivityNumber”);
actNum = ActNum.ToString();
TskPriority = smmActivity.get_Field(“TaskPriority”);
//Setting the object variable value returned by the AxRecord.get_Field.
StartDt = smmActivity.get_Field(“StartDateTime”);
//Setting the object variable value returned by the AxRecord.get_Field.
Purpose = smmActivity.get_Field(“Purpose”);
//Setting the object variable value returned by the AxRecord.get_Field.
Status = smmActivity.get_Field(“Status”);
//Setting the object variable value returned by the AxRecord.get_Field.
CustAddress = sma.get_Field(“ServiceAddress”);
//Setting the object variable value returned by the AxRecord.get_Field.
custdata = custName(actNum);
//Setting the object variable value returned by the AxRecord.get_Field.
////Setting the array value.
data[i] = new Activities(ActNum, TskPriority, StartDt, Purpose, Status, custdata[0], custdata[1], CustAddress);
// data[i] = new Activities(ActNum, TskPriority, StartDt, Purpose, Status);
i++;
smmActivity.Next();
}
// Dispose of the AxaptaRecord object.
smmActivity.Dispose();
// // Log off from Microsoft Dynamics AX.
ax.Logoff();
// return data;
return data;
I tried to pass string directly and then i tried datetime format and utc date time format.But it is not working also.
After that i m trying to write the logic in ax. In that i dont know how to return records from more that one table.And use in the web service.
I m able to write join query. But who to return record.
For ex.
If theres only one record then i can return it by following code.
queryRun = new QueryRun(query);
while(queryRun.next())
{
smmAct = queryRun.get(tablenum(smmActivities));
return smmAct;
}
But how to return all the data.