Share
ReportHi
I just want to update the record in the external database connected through ADO using X++ code.
here is my code anyone please answer me how can I change a record in the external database.
i just want to change the transfer status in the exeternal database when record successfuly update or insert
private void insertVendors()
{
str sqlQuery, updateQuery, ConnectionString, serverName, catalogName;
System.Data.SqlClient.SqlConnectionStringBuilder connectionStringBuilder;
System.Data.SqlClient.SqlConnection connection;
System.Data.SqlClient.SqlCommand command, commandTraining, ExecuteCommand;
System.Data.SqlClient.SqlDataAdapter adapter, _adapterTraining;
System.Data.DataTableCollection DataTableCollection, _DataTableCollectionTraining;
System.Data.DataTable DataTable, DataTableTraining, DataTableEducation, _DataTableExperience;
System.Data.DataRowCollection DataRowCollection, _DataRowCollectionTraining;
System.Data.DataColumnCollection DataColumnCollection, _DataColumnCollectionTraining;
System.Data.DataRow DataRow, _DataRowTraining;
System.Data.DataColumn DataColumn, _DataColumn;
str PGCUser, PGCPassword;
int64 recordsProcessed;
int64 recordsFailed;
int64 FailedRecID;
int rowcount, rowindex, rowindexTraining, rowcountTraining;
System.Data.DataSet dataset, _datasetTraining;
System.Data.SqlClient.SqlParameterCollection parameterCollection;
System.Data.SqlClient.SqlDataReader dataReader;
//SL_VendorDC vendorDC;
//SL_ODBCConnector odbcConnector;
SL_ACVendTable acVendTable;
ListEnumerator enumeratorVendor;
List listVendor;
str sql;
AccountNum vendId;
;
try
{
sqlQuery = "select * from AX_Vendor where Transfer_Status = 'N' ";
sql = "Update AX_Vendor Set Transfer_Status = 'R' where VendorNo = 'vendId'";
serverName = "";
catalogName = "";
//odbcConnector = new SL_ODBCConnector();
connectionStringBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder();
connectionStringBuilder.set_DataSource(serverName);
connectionStringBuilder.set_IntegratedSecurity(true);
connectionStringBuilder.set_InitialCatalog(catalogName);
ConnectionString = connectionStringBuilder.get_ConnectionString();
connection = new System.Data.SqlClient.SqlConnection(ConnectionString);
command = new System.Data.SqlClient.SqlCommand(sqlQuery);
command.set_Connection(connection);
adapter = new System.Data.SqlClient.SqlDataAdapter(command);
new InteropPermission(InteropKind::ClrInterop).assert();
connection.Open();
try
{
dataset = new System.Data.DataSet();
adapter.Fill(dataset);
if (dataset!=null)
{
DataTableCollection = dataset.get_Tables();
DataTable = DataTableCollection.get_Item(0);
DataRowCollection = DataTable.get_Rows();
rowcount = DataRowCollection.get_Count();
for (rowindex = 0; rowindex < rowcount; rowindex++)
{
DataRow = DataRowCollection.get_Item(rowindex);
acVendTable.clear();
if(!DataRow.IsNull('VendorNo'))
vendId = DataRow.get_Item("VendorNo");
//vendorDC = DataRow.get_Item("VendorNo");
acVendTable = SL_ACVendTable::findByVendorId(vendId, true);
try
{
if(acVendTable)
{
ttsBegin;
if (DataRow !=null)
{
if(!DataRow.IsNull('AddressCode'))
acVendTable.AddressCode = DataRow.get_Item("AddressCode");
if(!DataRow.IsNull('AddressLine1'))
acVendTable.AddressLine1 = DataRow.get_Item("AddressLine1");
acVendTable.Status = SL_RecordStatus::New;
}
acVendTable.update();
ttsCommit;
}
else
{
acVendTable.insert();
}
sql = "update AX_Vendor set Transfer_Status = 'R' where Transfer_Status = 'N' and RecordType = 'M'";
recordsProcessed++;
}
catch
{
//error(SL_ACCommon::getInfoLogText(true));
FailedRecID = DataRow.get_Item("VendorNo");
info(strFmt("failed Vendor ID = %1",FailedRecID));
recordsFailed++;
}
}
if(recordsProcessed > 0)
///vendorMigrationSuccess = strFmt("%1 : Vendor(s) migrated from ETL_PROD to AX staging Table successfully", recordsProcessed);
info(strFmt("%1 Vendor(s) migrated :", recordsProcessed));
if(recordsFailed > 0)
//vendorMigrationFiald = strFmt("%1 : Vendor(s) failed during migration", recordsFailed);
info(strFmt("%1 : Vendoe(s)failed",recordsFailed));
}
}
catch
{
dataset.Dispose();
}
catch(Exception::CLRError)
{
dataset.Dispose();
}
connection.Dispose();
}
catch
{
connection.Dispose();
}
catch(Exception::CLRError)
{
connection.Dispose();
}
command.Dispose();
CodeAccessPermission::revertAssert();
// return employeeList;
*This post is locked for comments
I have the same question (0)
All responses (
Answers (
26
