web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Connecting to External...
Finance | Project Operations, Human Resources, ...
Unanswered

Connecting to External database X++

(0) ShareShare
ReportReport
Posted on by Community member 2
Hi, I'm trying to connect to an external database using the below code but it's not returning any results. The connection gets created but when I open the form the fields are not being populated. Is there perhaps something I need to tweek?
 
 
using System.Data.SqlClient;
class ExternalFields
 
     static public void Main(Args _args)
    {
        BBBEECertIssueDate               BBBEECertIssueDate;
        BBBEECertIssueExpDate            BBBEECertIssueExpDate;
        BBBEECertNo                      BBBEECertNo;
        BBBEECreatedBy                   BBBEECreatedBy;
        BBBEECreatedDate                 BBBEECreatedDate;
        BBBEEProReg                      BBBEEProReg;
        BBBEEStatLevelOfContributor      BBBEEStatLevelOfContributor;
        BlackOwned                       BlackOwned;
        BlackOwnedRural                  BlackOwnedRural;
        BlackOwnedWomen                  BlackOwnedWomen;
        DirectorAppDate                  DirectorAppDate;
        DirectorCellNo                   DirectorCellNo;
        DirectorCompInvolvedIn           DirectorCompInvolvedIn;
        DirectorCountry                  DirectorCountry;
        DirectorCreatedDate              DirectorCreatedDate;
        DirectorEditDate                 DirectorEditDate;
        DirectorEmailAddress             DirectorEmailAddress;
        DirectorEthnicGroup              DirectorEthnicGroup;
        DirectorGender                   DirectorGender;
        DirectorIDType                   DirectorIDType;
        DirectorLastVerDate              DirectorLastVerDate;
        DirectorNames                    DirectorNames;
        DirectorOwner                    DirectorOwner;
        DirectorOwnership                DirectorOwnership;
        DirectorSAIDNo                   DirectorSAIDNo;
        DirectorStatus                   DirectorStatus;
        DirectorSurname                  DirectorSurname;
        DirectorType                     DirectorType;
        IncomeTaxNumber                  IncomeTaxNumber;
        SARSRegistered                   SARSRegistered;
        TaxCreatedDate                   TaxCreatedDate;
        TaxEditDate                      TaxEditDate;
        PurchRFQTmpCompareRFQReply       PurchRFQReply;
        SqlDataReader                    reader;
        SqlCommand                       command;
 
 
 
 
   //LoginProperty loginProperty;
        OdbcConnection odbcConnection;
        Statement statement;
        ResultSet resultSet;
        LoginProperty myLoginProperty;
        str sql, criteria;
        int output;
        SqlStatementExecutePermission perm;
        str myUserName=/Admin/;
        str myPassword=/Admin12345/;
        str myConnectionString;
        ;
 
  // Set the information on the ODBC.
        myConnectionString=strfmt(/UID=%1;PWD=%2/,myUserName,myPassword);
        myLoginProperty = new LoginProperty();
        myLoginProperty.setServer(/Demo/);
        myLoginProperty.setDatabase(/ExternalDB/);
        myLoginProperty.setOther(myConnectionString);
        //Create a connection to external database.
        odbcConnection = new OdbcConnection(myLoginProperty);

        if (odbcConnection)
        {
            Sql = strFmt(/select CertificateIssueDate,CertificateExpiryDate,CertificateNumber,CertificateSignedBy,BlackOwnership,BlackWomanOwnership,[MDDirectorStatus].name,IncomeTaxNumber,SupplierDirectors.CountryTypeCode,SupplierDirectors.CreatedDate,SupplierDirectors.EditDate,SupplierDirectors.Email,SupplierDirectors.EthnicGroupCode,SupplierDirectors.GenderCode,SupplierDirectors.LastVerificationDate,SupplierDirectors.Name,SupplierDirectors.IsOwner,SupplierDirectors.IsOwner,SupplierDirectors.OwnershipPercentage,SupplierDirectors.SAIDNumber,SupplierDirectors.Surname,SupplierContacts.CellphoneNumber,IsRegistered,SupplierDetailsTax.EditDate from dbo.SupplierDetailsBBBEE left join dbo.MDBBBEEStatusLevelOfContributor on SupplierDetailsBBBEE.StatusLevelOfContributorCode = MDBBBEEStatusLevelOfContributor.Code left join dbo.SupplierContacts on SupplierDetailsBBBEE.SupplierNumber = SupplierContacts.SupplierNumber left join dbo.SupplierDirectors  on SupplierDirectors.SupplierNumber = SupplierContacts.SupplierNumber left join [MDDirectorStatus]  on [MDDirectorStatus].Code = SupplierDirectors.DirectorStatusTypeCode left join SupplierDetailsTax on SupplierDirectors.SupplierNumber = SupplierDetailsTax.SupplierNumber/);
 
 
//Assert permission for executing the sql string.
            perm = new SqlStatementExecutePermission(sql);
            perm.assert();
            //Prepare the sql statement.
            statement = odbcConnection.createStatement();
            resultSet = statement.executeQuery(sql);
            //Cause the sql statement to run,
            //then loop through each row in the result.
       
              reader = command.ExecuteReader();
              while(reader.Read())
            {
                //It is not possible to get field 3 and then 1.
                //Always get fields in numerical order, such as 1 then 2 the 3 etc.
              //  print resultSet.getString(1);
             //   print resultSet.getString(3);
                PurchRFQReply.initValue();
                PurchRFQReply.BBBEECertIssueDate = reader.get_Item(/CertificateIssueDate/);
                PurchRFQReply.BBBEECertIssueExpDate = reader.get_Item(/CertificateExpiryDate/);
                PurchRFQReply.BBBEECertNo = reader.get_Item(/CertificateNumber/);
                PurchRFQReply.BBBEECreatedBy = reader.get_Item(/CertificateSignedBy/);
                PurchRFQReply.BBBEECreatedDate = reader.get_Item(/CreatedDate/);
                PurchRFQReply.BlackOwned = reader.get_Item(/BlackOwnership/);
                PurchRFQReply.BlackOwnedWomen = reader.get_Item(/BlackWomanOwnership/);
                PurchRFQReply.BBBEEStatLevelOfContributor = reader.get_Item(/Name/);
                PurchRFQReply.DirectorAppDate = reader.get_Item(/AppointmentDate/);
                PurchRFQReply.DirectorCountry = reader.get_Item(/CountryTypeCode/);
                PurchRFQReply.DirectorCreatedDate = reader.get_Item(/CreatedDate/);
                PurchRFQReply.DirectorEditDate = reader.get_Item(/EditDate/);
                PurchRFQReply.DirectorEmailAddress = reader.get_Item(/Email/);
                PurchRFQReply.DirectorEthnicGroup = reader.get_Item(/EthnicGroupCode/);
                PurchRFQReply.DirectorGender = reader.get_Item(/GenderCode/);
                PurchRFQReply.DirectorLastVerDate = reader.get_Item(/LastVerificationDate/);
                PurchRFQReply.DirectorNames = reader.get_Item(/Name/);
                PurchRFQReply.DirectorOwner = reader.get_Item(/IsOwner/);
                PurchRFQReply.DirectorOwnership = reader.get_Item(/OwnershipPercentage/);
                PurchRFQReply.DirectorSAIDNo = reader.get_Item(/SAIDNumber/);
                PurchRFQReply.DirectorSurname = reader.get_Item(/Surname/);
                PurchRFQReply.DirectorCellNo = reader.get_Item(/CellphoneNumber/);
                PurchRFQReply.DirectorIDType = reader.get_Item(/Name/);
                PurchRFQReply.DirectorStatus = reader.get_Item(/Name/);
                PurchRFQReply.DirectorType = reader.get_Item(/Name/);
                PurchRFQReply.IncomeTaxNumber = reader.get_Item(/IncomeTaxNumber/);
                PurchRFQReply.SARSRegistered = reader.get_Item(/IsRegistered/);
                PurchRFQReply.TaxCreatedDate = reader.get_Item(/CreatedDate/);
                PurchRFQReply.TaxEditDate = reader.get_Item(/EditDate/);
                PurchRFQReply.insert();
            }
            
           

            //Close the connection.
       
            reader.Close();
            statement.close();
        }
        
        else
        {
            error(/Failed to log on to the database through ODBC./);
        }
    }
}
I have the same question (0)
  • Martin Dráb Profile Picture
    Martin Dráb 237,880 Most Valuable Professional on at
    I see you're utilizing the 'using' statement, which suggest to me that you're actually using F&O and not AX. Am I right?
     
    Looking at the form isn't the right approach to debugging. It could mean that data is fetched correctly and the problem is just with the form. Please use the debugger to see what happens in your code.
     
    If ExecuteReader() runs without any exception but Read() returns false, it seems that the query executed successfully and just didn't find any data. If you get an exception, look at the type and the error message.
     
    I'm surprised that your code compiles at all, because get_Item() returns Object and you're trying to assign it to fields (with primitive data types). You should use methods like GetString() and GetInt32() instead.
     
    By the way, the API you're using is quite cumbersome and I really don't recommend using it. Instead, you can you use of the .NET frameworks to generates classes for your table and get code completion, compile-time validation of types and names, get typed values from columns and so on. It makes things much easier and safer.
     
    Also note that you call Close() only when everything succeeds but not if an exception gets thrown. The easiest approach to handle both cases is utilizing a 'using' block. An alternative is using try/finally.
  • Community member Profile Picture
    Community member 2 on at
    Hi Martin,
     
    I'm using F&O.
     
    I've made changes to the code but the form times out when I open. 
     
    Please see code below:
     
     
    class CSDFieldsIntegration
    {
        /// <summary>
        ///
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        [FormEventHandler(formStr(CSDInformation), FormEventType::PostRun)]
        public static void CSDInformation_OnPostRun(xFormRun sender, FormEventArgs e)
        {
            BBBEECertIssueDate               bBBEECertIssueDate;
            BBBEECertIssueExpDate            bBBEECertIssueExpDate;
            BBBEECertNo                      bBBEECertNo;
            BBBEECreatedBy                   bBBEECreatedBy;
            BBBEECreatedDate                 bBBEECreatedDate;
            BBBEEProReg                      bBBEEProReg;
            BBBEEStatLevelOfContributor      bBBEEStatLevelOfContributor;
            BlackOwned                       blackOwned;
            BlackOwnedRural                  blackOwnedRural;
            BlackOwnedWomen                  blackOwnedWomen;
            DirectorAppDate                  directorAppDate;
            DirectorCellNo                   directorCellNo;
            DirectorCompInvolvedIn           directorCompInvolvedIn;
            DirectorCountry                  directorCountry;
            DirectorCreatedDate              directorCreatedDate;
            DirectorEditDate                 directorEditDate;
            DirectorEmailAddress             directorEmailAddress;
            DirectorEthnicGroup              directorEthnicGroup;
            DirectorGender                   directorGender;
            DirectorIDType                   directorIDType;
            DirectorLastVerDate              directorLastVerDate;
            DirectorNames                    directorNames;
            DirectorOwner                    directorOwner;
            DirectorOwnership                directorOwnership;
            DirectorSAIDNo                   directorSAIDNo;
            DirectorStatus                   directorStatus;
            DirectorSurname                  directorSurname;
            DirectorType                     directorType;
            IncomeTaxNumber                  incomeTaxNumber;
            SARSRegistered                   sARSRegistered;
            TaxCreatedDate                   taxCreatedDate;
            TaxEditDate                      taxEditDate;
            PurchRFQTmpCompareRFQReply       purchRFQReply;
            
            PurchRFQReply = sender.datasource().cursor();
            //LoginProperty loginProperty;
            OdbcConnection odbcConnection;
            Statement statement;
            ResultSet resultSet;
            LoginProperty myLoginProperty;
            str sql, criteria;
            int output;
            SqlStatementExecutePermission perm;
            str myUserName="axdbadmin";   
            str myPassword="gI8fkaEMpqdzcru9"; 
            str myConnectionString;
            ;
            // Set the information on the ODBC.
            myConnectionString=strfmt("UID=%1;PWD=%2",myUserName,myPassword);
            myLoginProperty = new LoginProperty();
            myLoginProperty.setServer("ISDONEBOX");   
            myLoginProperty.setDatabase("CSD_prod_Dev"); 
            myLoginProperty.setOther(myConnectionString);
            //Create a connection to external database.
            odbcConnection = new OdbcConnection(myLoginProperty);

            if (odbcConnection)
            {
                Sql = strFmt("select CertificateIssueDate,CertificateExpiryDate,CertificateNumber,CertificateSignedBy,BlackOwnership,BlackWomanOwnership,[MDDirectorStatus].name,IncomeTaxNumber,SupplierDirectors.CountryTypeCode,SupplierDirectors.CreatedDate,SupplierDirectors.EditDate,SupplierDirectors.Email,SupplierDirectors.EthnicGroupCode,SupplierDirectors.GenderCode,SupplierDirectors.LastVerificationDate,SupplierDirectors.Name,SupplierDirectors.IsOwner,SupplierDirectors.OwnershipPercentage,SupplierDirectors.SAIDNumber,SupplierDirectors.Surname,SupplierContacts.CellphoneNumber,IsRegistered,SupplierDetailsTax.EditDate from dbo.SupplierDetailsBBBEE left join dbo.MDBBBEEStatusLevelOfContributor on SupplierDetailsBBBEE.StatusLevelOfContributorCode = MDBBBEEStatusLevelOfContributor.Code left join dbo.SupplierContacts on SupplierDetailsBBBEE.SupplierNumber = SupplierContacts.SupplierNumber left join dbo.SupplierDirectors  on SupplierDirectors.SupplierNumber = SupplierContacts.SupplierNumber left join [MDDirectorStatus]  on [MDDirectorStatus].Code = SupplierDirectors.DirectorStatusTypeCode left join SupplierDetailsTax on SupplierDirectors.SupplierNumber = SupplierDetailsTax.SupplierNumber");
                //Assert permission for executing the sql string.
                perm = new SqlStatementExecutePermission(sql);
                perm.assert();
                //Prepare the sql statement.
                statement = odbcConnection.createStatement();
                resultSet = statement.executeQuery(sql);

                //Cause the sql statement to run,
                //then loop through each row in the result.
                 while (resultSet.next())
                {
                    ttsbegin;
                    purchRFQReply.clear();
                    purchRFQReply.bBBEECertIssueDate = resultSet.getString(1);
                    purchRFQReply.bBBEECertIssueExpDate = resultSet.getString(2);
                    purchRFQReply.bBBEECertNo = resultSet.getString(3);
                    purchRFQReply.bBBEECreatedBy = resultSet.getString(4);
                    purchRFQReply.blackOwned = resultSet.getString(5);
                    purchRFQReply.blackOwnedWomen = resultSet.getString(6);
                    purchRFQReply.directorStatus = resultSet.getString(7);
                    purchRFQReply.incomeTaxNumber = resultSet.getString(8);
                    purchRFQReply.directorCountry = resultSet.getString(9);
                    purchRFQReply.taxCreatedDate = resultSet.getString(10);
                    purchRFQReply.taxEditDate = resultSet.getString(11);
                    purchRFQReply.directorEmailAddress = resultSet.getString(12);
                    purchRFQReply.directorEthnicGroup = resultSet.getString(13);
                    purchRFQReply.directorGender = resultSet.getString(14);
                    purchRFQReply.directorLastVerDate = resultSet.getString(15);
                    purchRFQReply.directorNames = resultSet.getString(16);
                    purchRFQReply.directorOwner = resultSet.getString(17); 
                    purchRFQReply.directorOwnership = resultSet.getString(18);
                    purchRFQReply.directorSAIDNo = resultSet.getString(19);
                    purchRFQReply.directorSurname  = resultSet.getString(20);
                    purchRFQReply.directorCellNo = resultSet.getString(21);
                    purchRFQReply.sARSRegistered = resultSet.getString(22);
                    purchRFQReply.directorEditDate = resultSet.getString(23);
                    purchRFQReply.insert();
                    ttscommit;
                    
                }
                //Close the connection.
                resultSet.close();     
                statement.close();
            }
            else
            {
                error("Failed to log on to the database through ODBC.");
            }
        }
    }
     
     
     
  • Martin Dráb Profile Picture
    Martin Dráb 237,880 Most Valuable Professional on at
    Please use the debugger to find out where your code get stuck - I can't do it for you. Even if I was willing, I don't have your data and your form.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
Martin Dráb Profile Picture

Martin Dráb 611 Most Valuable Professional

#2
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 529 Super User 2025 Season 2

#3
Sohaib Cheema Profile Picture

Sohaib Cheema 285 User Group Leader

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans