Send data to external DB from D365

(0) Share

Report

Posted on by RadekM

174

Hi,

I'm trying to send data by code into an external DB. Actually I try to expand a already created modification but I occoured problems (creator isn't avaliable).

This is a sample where the error occours. It catches a error by: exceptionTextFallThrough();

No data is going out into db using this but having the same code for custinvoicejour tables does work here. Debugging didnt tell me much here.

    private void insertProjInvoice()
    {
        System.Data.OleDb.OleDbCommand cmdInsertProjJour, cmdInsertProjTrans;

         this.setInsertProjInvoiceJour();


        cmdInsertProjJour = objConn.CreateCommand();
        cmdInsertProjJour.set_CommandText(sqlProjJour);
        cmdInsertProjJour.ExecuteReader();

        while select ProjInvoiceItem
            where Projinvoiceitem.projinvoiceid == ProjInvoiceJour.ProjInvoiceId
            && ProjInvoiceItem.InvoiceDate == ProjInvoiceJour.InvoiceDate

        {
            this.setInsertProjInvoiceTrans(ProjInvoiceItem);
            cmdInsertProjTrans = objConn.CreateCommand();
            cmdInsertProjTrans.set_CommandText(sqlTrans);
            cmdInsertProjTrans.ExecuteReader(); //

I have the same question (0)

All responses (15)

Answers (1)

Sort by

Gunjan Bhattachayya 35,429 on at

Like (0)

Report
Copy link

Link copied!

Hi,

What is the exact error you are getting on running this statement?

Have you tried executing the same statement using SSMS on the external DB?

Was this reply helpful? Yes No
Suggested answer

Martin Dráb 239,684 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

You're calling two different commands there (and we have no information about the first one - the one in sqlProjJour variable). Which one if failing?

You must pay attention to what error you get from the database. Just knowing that there was "an" error isn't typically sufficient for being able to fix it. By the way, using exceptionTextFallThrough() means that your code simply ignores the error.

Also note that you could use better APIs. For example, if you misspell a field name, your current code will compile and fails only at runtime. If you generated classes from the database schema (using Entity Framework, for example), this type of errors would get detected during compilation. It would also help you with data type checks and conversions, among other things.

Another problem of your code is that it's susceptible to SQL injection attacks. You should use Parameters property, if you stick to this API.

Was this reply helpful? Yes No
RadekM 174 on at

Like (0)

Report
Copy link

Link copied!

AH sorry! Actually it's almost the same thing. In both methods I build sql statements.

It breaks always on ExecuteReader().

About the exceptionTextFallThrough() didn't know it ignores the error. Actually it shows me just a custom label :error catched on invoiceid. nothing more.

This API is rewrited from AX4 the plan is to rewrite the complete API into webservices but for now I need to add something here and I don't have time to build it from scratch.

Was this reply helpful? Yes No
Martin Dráb 239,684 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
You really must look at the error returned from the database - you're unlikely to make any progress without finding what's going on.

Regarding exceptionTextFallThrough, you can simply review its implementation:

public static void exceptionTextFallThrough() { ; }

As you see, it does nothing. Therefore you catch an exception and do nothing with it.

My first step would be putting a breakpoint to the catch clause, running the code and reviewing the exception in the debugger.

Was this reply helpful? Yes No
RadekM 174 on at

Like (0)

Report
Copy link

Link copied!
Yeah I found some issues. Like missing '' ins sql statement. Now the data is send but. Date is different than what I send to with my statement.

Using this first variant of conversion gives me "Arithmetic overflow errir converting expression to data type int".

Using second conversion I let me send the whole data. But the year is always wrong there. I throw the whole string in info so I see that there is for ex. 2020-06-19 but in SSMS it shows 1905-06-19 and it there is no date in it should paste 1900-01-01 and I get 1905-03-14. Any idea here?

private str convertDateToSQLDate(Date _date) { str ret; if (_date) { ret = date2str(_date,321,2,0,2,0,4); } else { ret = "19000101"; } return ret; } private str convertDateToSQLDateRM(Date _date) { str ret; if (_date) { ret = date2str(_date,321,2,3,2,3,4); } else { ret = "1900-01-01"; } return ret; }

Was this reply helpful? Yes No
Suggested answer

Martin Dráb 239,684 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
This is exactly the kind of problem that you could have avoided by using a more robust API. :-)

Format '20200915' is what you should use, AFAIK. Just don't forget to handle time zones if needed; there may be another day in a different time zone.

You can convert it with System.String::Format(), for example:

System.String::Format('{0:yyyyMMdd}', _date);

Was this reply helpful? Yes No
RadekM 174 on at

Like (0)

Report
Copy link

Link copied!

The issue is the same. I can't execute it in SSMS if the date don't have a separator. THe moment I put it like this '2020-09-15' it proceed.

Was this reply helpful? Yes No
Martin Dráb 239,684 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
I'm 100% sure that's it's a supported format.

Please try this code and compare it with the code you're trying to use in SSMS:

select count(*) from SalesTable where CreatedDateTime > '20100112'

'2020-09-15' isn't culture-independent.

Was this reply helpful? Yes No
RadekM 174 on at

Like (0)

Report
Copy link

Link copied!

Well I got the same number of records for both examples. Anyway the original API is using dates as u said without separators. Only difference is that it was running on CustInvoiceJour and im trying it on ProjInvoiceJour.

Edit. Tried one more time thewriten standard. For custinvoiceJour its working good, and the year is ok. For projinvoicejour its like I said above.

Was this reply helpful? Yes No
Martin Dráb 239,684 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

There was a reply where you said you can't execute it; it seems you fixed that and deleted the reply I was reacting to.

Whether both formats are interpreted as the same date depends on the current culture. Therefore you get the same result in a certain context, but different results in another context.

Was this reply helpful? Yes No