How to use a SQL statement with Business Connector

Hi,

I am a C# web developer and brand-new to AX. I want to use the business connector to fetch a row from the CustTable in AX. I cannot seem to find an example of how to construct a SQL Select statement with a WHERE statement. The only examples I find are simple statements like "SELECT * FROM %1. I need an example with a WHERE. Can anyone post an example?

Thanks

You may find useful to write your query in AX client first. Nevertheless, it’s quite straightforward - for example:

axRecord.ExecuteStmt(“select * from %1 where %1.DataAreaId == ‘dat’”);

Martin,

Thanks for the reply. I used your example but now get an error. Here is my C# code:

string strSQL2 = “SELECT * FROM %1 WHERE %1.CustAccount == ‘C999999’ AND %1.DataAreaId == ‘b01’”;

Axapta ax = new Axapta();

AxaptaRecord axRecord;

string tableName = “CustTable”;

ax.Logon("", “”, “”, “”);

using (axRecord = ax.CreateAxaptaRecord(tableName))

{

axRecord.ExecuteStmt(strSQL2);

}

while (axRecord.Found)

{

mstrName = Convert.ToString(axRecord.get_Field(“Name”));

}

But when I run this, I get the error below on this line, axRecord.ExecuteStmt(strSQL2);

[Microsoft.Dynamics.BusinessConnectorNet.BusinessConnectorException] {"Unable to compile "static void ComExecuteStmt(CustTable axTbl_0) { SELECT * FROM axTbl_0 WHERE axTbl_0.CustAccount == ‘C008038’ AND axTbl_0.DataAreaId == ‘b01’;} “.”} Microsoft.Dynamics.BusinessConnectorNet.BusinessConnectorException

Like I said, I am new to the AX world so I am probably doing something dumb.

If you have tried to run the query in AX client, as I suggested, you would find that there is no CustAccount field in (standard) CustTable (you probably meant AccountNum) and you have to use “&&” instead of “AND”.

You also don’t call axRecord.Next() in the while cycle and try to use a disposed variable (axRecord is disposed at the end of “using” block).

Yes, you are right: It’s AccountNum. I was using AccountNum but typed the wrong one when I posted my problem. I also changed the AND to &&. This all works and now no errors.

Another question: When I run this line,

axRecord.ExecuteStmt(strSQL2);

Can I populate a C# DataSet?

String strDTMeterQuery = "select * from %1 where %1.DTM_SerialNumber == " + DTM_SerialNumber.ToString();
Boolean insertFlag = false;
object DTM_SerialNumberObject;

using (McsStgDTMeterRecord = ax.CreateAxaptaRecord(strMcsStgDTMeter))
{
McsStgDTMeterFound = ax.CreateAxaptaRecord(strMcsStgDTMeter);
McsStgDTMeterFound.ExecuteStmt(strDTMeterQuery);

}

I was working on the same pattern and receive the same error as mentioned in the above post.
Can you suggest what mistake have I done??

Thanks

Hi,

Go thorugh this

http://msdn.microsoft.com/en-us/library/aa659581.aspx

Hai…,

can i use the ExecuteStmt method of AxaptaWrapper Class?

whether the following code are correct?
AxaptaWrapper axapta = SessionManager.GetSession();
table1= (AxaptaRecordWrapper)axapta.CreateAxaptaRecord(“Table1”);
table2= (AxaptaRecordWrapper)axapta.CreateAxaptaRecord(“Table2”);
axapta.ExecuteStmt(“select * from %1 join %2”,table1,table2);

becz i m getting the sameError:
Microsoft.Dynamics.BusinessConnectorNet.BusinessConnectorException: Unable to compile "static void ComExecuteStmt(…)