Decimal Place issue - unable to insert into Database

[JasonWilliams]

---

@Michael_Munroe wrote:

I should point out that the column labels in the image are reversed from the actual order of the data fields.

The DB toolkit is a set of unlocked VIs.  Drill down to DB Tools Insert Data.vi -> DB Tools Create Parameterized Query.vi and examine the parameters out array.  This will identify how the data has been interpreted.  You can expand all the SubVIs here until you can see the actual value being written.

---

Thanks; although it's a really simple wiring diagram, I didn't spot that I'd got them the wrong way around, both columns are identical however and wouldn't make a difference.  I haven't use the Create Parameterized Query VI before so I'll give that a look.  I currently have an open ticket for this with NI as I'm not sure it's actually as straightforward as it should be!

[aputman · Accepted solution]

Replace the Insert Data function with the Execute Query and build your own query with a Format Into String function.  

insert into [ProductTestData].[dbo].[CLS_cdV_factor] (One, Ten) values (%.2f, %.2f) 

[JasonWilliams]

---

@aputman wrote:

Replace the Insert Data function with the Execute Query and build your own query with a Format Into String function.  

insert into [ProductTestData].[dbo].[CLS_cdV_factor] (One, Ten) values (%.2f, %.2f) 

---

I'm going to accept this is the answer as it DOES solve the problem - but I suppose the question is still open as why doesn't it work via the Database Insert VI?

[aputman]

The only thing I can determine is there must be a value that has more than 4 digits.  I did some tests on my server with a decimal(6,2) datatype.  If I have more than 2 decimal digits, they get rounded off with no error.  If I insert a value in scientific notation, the number is inserted with no error.  If I insert a value with more than 4 integer digits, I get the same error that you posted.  

You might also try removing the To Variant VI and wire the cluster straight in.  

[JasonWilliams]

Just a quick update on this; I did report to NI and it appears to have been a bug, I have recently updated to 2019 and have just encountered the same scenario; a calculation with digits of precision, straight into an Insert into Database, but this time with no issue.