Error - 2147217833 ocurred at NI_Database_API.lvlli: Cmd Execute.vi - >NI_Database_API.lvllib:DB Tools Insert Data.vi ->TESTDATABASE.vi

[JLuna]

This error appears when I run the respective VI (file attached). The whole following message appears:

Possible reason(s):

"ADO Error: 0x80040E57 Exception ocurred in Microsoft JET Database Engine: The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data in

NI_Database_API.lvllib:Rec Create - Command.vi - > NI_Database_API.lvlib:Cmd Execute.vi- >NI_Database_API.lvlib:DB Tools Insert Data.vi-> TESTDATABASE.vi"

I don't know if the cause could be when making the .udl file. But I doubt that.

I can list a series of factors that may impact on this error, since I don't know the possible cause. 

- The database has extension .mdb, its a access 2013 database but I saved it in .mdb

- When creating the .udl file, the provider selected is Microsoft JET 4.0 OLE DB Provider. 

- In the block diagram after the bundle function I used a variante function, since I've been reading its viable to use it. Thought I've already tried without variant. 

[jcarmody]

It seems more likely that the Toyota would pass, and the other two would fail.  Your error message makes me think your data types are causing your problem.

[JLuna]

I just found out the reason why the error appears. 

Let's say its the first time you create the VI. And in the column or field DEFECTS you input information such as "low air pressure". After sometime you want to add "high air pressure" to the DEFECT Field. This new defect has more characters, so the error will definitely show up. To avoid this error, just create another table with the new data added. 

Now my question is: 

Is there a way to update the new information? Avoiding (of course) this type of error. So that everytime I want to add larger character information, the error message won't show up. 

Many thanks in advance! 

[Dennis_Knutson]

You can use the SQL UPDATE command. This would require you to first read the existing value and append the new string to it. What have you set the field size to in your schema?

[JLuna]

What do you mean by that? By schema size? I'm using Office Access 2013, that's where Im using and creating the tables. But up to now, I haven't set a specific format to the information im gonna receive.. Is that what you mean by

defining the schema? What I did, is that when I was defining the structure of the .mdb file, the two fields have "Short Text" as data typ. Is that what you meant, by defining schema? The size?

Many thanks! I'll try the UPDATE command with "Execute Query" .vi

Best regards, 

[Sam_Sharp]

---

@JLuna wrote:

To avoid this error, just create another table with the new data added. 

---

Something doesn't add up here...your database/tables should be designed to accept the data you want to put in it. You should only need to create a table when you initialise/create the database and only need to update the schema (structure of the tables) during development or if you find out you later need to record additional fields with the data.

Do you have a diagram that shows the tables in your database?

[JLuna]

Here are the tables from the database I'm using at the moment. 

[Sam_Sharp · Accepted solution]

Your column names don't match - in the database you have underscores in your column names, in your VI, you don't.

You might also have a problem with your 'test number' field - depending on the field size you have configured, it might not accept double numeric floats:

That corresponds with the error you are seeing. If you are using an integer, you should wire an integer type of the appropriate size (e.g. I16/I32/I64) to the insert command.

[JLuna]

Thank you I've also notice that (for some reason)  in the text fields I had selected "short text" and size field: 14 and 6, for the respective columns (when it should be 255 max).  So also, that's why the error mentioned, small size in field. 

Many thanks!! I learned something new, feels awesome. 

Best regards!