Insert Into Command

I want to Insert data into external db table  from AX

 

how i can write insert into statement in ax batch job ?

i am writing like this but it is not inserting data into external db table

   sqlQuery = strFmt("INSERT INTO tblTrainingForWebSite([ExternalPlanID],[AccountNum],[PHCPHCRegistrationNumber],[FromDate],[ToDate],[trainername] ) VALUES ('%1','%2','%3','%4','%5','%6')",externalPlan.ExternalPlanID,customers.AccountNum,customers.PHCPHCRegistrationNumber,externalPlan.FromDate,externalPlan.ToDate,trainername);
    

  • It's seems that you're trying to use a direct SQL command, which you shouldn't be doing in the first place. There are many better options.
    Even if you keep using it, you may have a problem with the SQL code itself or with the X++ code where you're using the query. Unfortunately you didn't tell us anything about them, so it's hard to guess what could be wrong.
    I suggest you first explain what you're trying to achieve.
  • In reply to Martin Dráb:

    Thanks for response Martin,

    I am trying to insert data into external DB from AX and i am using SQLClinet class for this.

    }

    try
    {
    //SQL Query
    sqlQuery = strFmt("INSERT INTO tblTrainingForWebSite([ExternalPlanID],[AccountNum],[PHCPHCRegistrationNumber],[FromDate],[ToDate],[trainername] ) VALUES ('%1','%2','%3','%4','%5','%6')",externalPlan.ExternalPlanID,customers.AccountNum,customers.PHCPHCRegistrationNumber,externalPlan.FromDate,externalPlan.ToDate,trainername);
    // info(strFmt("%1 %2 %3 %4 %5 %6 %7 %8 %9 %10 ", externalPlan.ExternalPlanID,customers.AccountNum,customers.name(),customers.PHCOrganizationType,customers.PHCPHCRegistrationNumber,customers.PHCTehsil, customers.PHCDistrict,externalPlan.FromDate ,externalPlan.ToDate, trainername));
    select * from _phcSettings where _phcSettings.PHCSettings=="HCEFreeTextInvoiceDB";

    serverName =_phcSettings.PHCServerName;
    catalogName = _phcSettings.PHCCatalog;
    _PHCUser = _phcSettings.PHCUserName;
    _PHCPassword = _phcSettings.PHCUserPassword;
    connectionStringBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder();
    connectionStringBuilder.set_DataSource(serverName);
    connectionStringBuilder.set_UserID(_PHCUser);
    connectionStringBuilder.set_Password(_PHCPassword);
    connectionStringBuilder.set_IntegratedSecurity(false);
    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);
    new InteropPermission(InteropKind::ClrInterop).assert();
    connection.Open();

    try
    {
    command.ExecuteNonQuery();
    }
    catch
    {
    connection.Dispose();
    }
    catch(Exception::CLRError)
    {
    connection.Dispose();
    }
    connection.Dispose();
    }
    catch
    {
    connection.Dispose();
    }
    catch(Exception::CLRError)
    {
    connection.Dispose();
    }
    command.Dispose();
    CodeAccessPermission::revertAssert();
  • In reply to SalmanHafeez:

    First of all, let me format your code so it can be readable. Note that I dropped you exception-handling code, because it didn't make sense to me and it's not the core part:

    sqlQuery = strFmt("INSERT INTO tblTrainingForWebSite([ExternalPlanID],[AccountNum],[PHCPHCRegistrationNumber],[FromDate],[ToDate],[trainername] ) VALUES ('%1','%2','%3','%4','%5','%6')",externalPlan.ExternalPlanID,customers.AccountNum,customers.PHCPHCRegistrationNumber,externalPlan.FromDate,externalPlan.ToDate,trainername);
    
    connection = new System.Data.SqlClient.SqlConnection(this.buildConnectionString());
    command = new System.Data.SqlClient.SqlCommand(sqlQuery);
    command.set_Connection(connection);
    new InteropPermission(InteropKind::ClrInterop).assert();
    connection.Open();
    
    command.ExecuteNonQuery();

    I also extracted the connection string builder to a separate method, so it doesn't pollute the key part.

    str buildConnectionString()
    {
        // Imagine variable declarations here
        
        select * from _phcSettings
            where _phcSettings.PHCSettings=="HCEFreeTextInvoiceDB";
    
        serverName      =_phcSettings.PHCServerName;
        catalogName     = _phcSettings.PHCCatalog;
        _PHCUser        = _phcSettings.PHCUserName;
        _PHCPassword    = _phcSettings.PHCUserPassword;
    
        connectionStringBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder();
        connectionStringBuilder.set_DataSource(serverName);
        connectionStringBuilder.set_UserID(_PHCUser);
        connectionStringBuilder.set_Password(_PHCPassword);
        connectionStringBuilder.set_IntegratedSecurity(false);
        connectionStringBuilder.set_InitialCatalog(catalogName);
    
        return connectionStringBuilder.get_ConnectionString();
    }

  • In reply to Martin Dráb:

    Now please tell us what exactly happens. Do you say that ExecuteNonQuery() executes without any error? If you just execute your code and it doesn't do anything, it likely means that it fails on a CLR error, which is not caught by your (incorrect) exception handling. Please debug the code to be sure where it fails.

    Also, your usage of InteropPermission is suspicious. You assign the permission only after you use an CLR object; you either execute it in a context when InteropPermission isn't needed at all, or you should move above creating SqlConnection.
  • In reply to Martin Dráb:

    Ok Martin i understand.

    The issue is that when batch job run insert into command does not run because there is no data show in external DB table.
  • In reply to SalmanHafeez:

    Could you please elaborate what exactly you mean by "does not run" in this particular case? Debugging your code just by looking at data in the external database isn't likely the most efficient approach to debugging. I strongly suggest you use the debugger to understand what's wrong.

  • In reply to Martin Dráb:

    I have test the code. I have enter the dummy data into external DB table and by using

    sqlQuery = "Select * from tblTrainingForWebSite";

    get the data and show in info code is getting data correctly but when i use Insert Into SQL query it is not inserting data from AX to external table (tblTrainingForWebSite).

    which of following i need to used to insert data into external table.
    command.ExecuteNonQuery(); or command.ExecuteReader();
  • In reply to SalmanHafeez:

    ExecuteNonQuery() (or one of its asynchronous variants) is the right. If you want to learn more about methods of the class you're using, you can look into documentation.

    I still think you should fix your error handling and debug your code. Guessing isn't a very efficient approach, as you can see.

Related
Recommended