Decimal Place issue - unable to insert into Database

[JasonWilliams]

I have a really simple problem and I've tried a couple of solutions found elsewhere but just can't seem to get this right.

I have two values, which are initially imported via a Database query (SQL), then the value written into a DBL Control.  Once altered, I then want to put it back using Database Insert and create a new record.  My problem lies with once I've changed the value, and pressed the button, the value sent to the Database isn't the formatted value I need (and I have tried converting to a string, setting the width, setting the display format of the control - nothing seems to work).

Screenshot attached shows the resultant data, it errors as my SQL column is set to Decimal(6,2).

All I want is a value like 1234.56

Am I just being really stupid here?!

[aputman]

What is the error message?

[JasonWilliams]

Possible reason(s):
ADO Error: 0x80040E57
Exception occured in Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error converting float to data type numeric. in NI_Database_API.lvlib:Rec Create - Command.vi->NI_Database_API.lvlib:Cmd Execute.vi->NI_Database_API.lvlib:DB Tools Insert Data.vi->Update CdV Factor.vi

[aputman]

Are your column names [1] and [10]?  I doubt that special characters are allowed.  Give your columns an actual name with alpha characters. 

[JasonWilliams]

Yes they are called 1 & 10, I've used the square brackets to ensure they a relative name, not an actual value, but I will test that theory, but I usually write data back to this table by other methods and have no issues.

[JasonWilliams]

---

@aputman wrote:

Are your column names [1] and [10]?  I doubt that special characters are allowed.  Give your columns an actual name with alpha characters. 

---

Having amended the table and changed the column names to the words "One" and "Ten", I can confirm this still doesn't resolve the issue.  It was worth a try but was 99.9% sure this wasn't the issue, but having learned from LabVIEW experience, sometimes expect the unexpected!

[JÞB]

This is one of "Those Times" you need to use "Bundle By Name" rather than "Bundle"

[aputman]

With a datatype of decimal(6,2), the maximum number of digits you can have is 4 on the left side and 2 on the right side of the decimal point. The 2 forces every value to have 2 decimal places, even though they may be zeros.  You won't be able to insert 12345 because it assumes there are two decimal places.  Your number becomes 12345.00 and you've now exceeded the 6 digit limit.  Check your data to make sure you don't have any numbers with 5 or more digits on the left side of the decimal. 

[JasonWilliams]

---

@aputman wrote:

With a datatype of decimal(6,2), the maximum number of digits you can have is 4 on the left side and 2 on the right side of the decimal point. The 2 forces every value to have 2 decimal places, even though they may be zeros.  You won't be able to insert 12345 because it assumes there are two decimal places.  Your number becomes 12345.00 and you've now exceeded the 6 digit limit.  Check your data to make sure you don't have any numbers with 5 or more digits on the left side of the decimal. 

---

That is correct and what I'm expecting to see; my usual, largest value would be around 5755.25 - so no wider than 6 digits, I have a feeling when I'm going through the Data to Variant function, that's having the undesired effect I'm seeing, but that's what the Database Insert VI wants from the source data.

[Michael_Munroe]

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.