DB Tools Insert Data VI

[DL84]

Hello all!

I'm working with one of the VI's in the Database toolkit (using LV8.5) and I'm able to open a connection to my access 2000 DB, but the insert data function that came with the toolkit isn't working.  I'm getting error "Error -2147217900 occurred at Cmd Execute.vi->DB Tools Insert Data.vi->DSPEC Database.vi"

Possible reason(s):
Exception occured in Microsoft JET Database Engine: Syntax error in INSERT INTO statement. in Rec Create - Command.vi->Cmd Execute.vi->DB Tools Insert Data.vi->DSPEC Database.vi

I'm not sure what the problem is, but I can say that with a cluster going into the data input (with the same number of elements as the columns in the DB) I'm not sure how it knows which control input from LV, goes into which column.  Just in case all of the columns are named identical to the control names....

I did find the following response in another post, but I'm not I understand it, or if its relevant to this case....
You can use the Insert Data vi which you would just have to specify the table name that you want to insert. Keep in mid that if you use this functiuon, you need to insert a cluster with THE SAME exact amount of fields as your table in the database. You would use this function if you want to insert a value to every single field in the database. If you dont, then you would use the Execute SQL Query vi, which you would wire a string with your query which in this case would be an INSERT statement.

It shouldn't be necessary since the example that came with the toolkit doesn't use this execute SQL query vi...

Bit confused here, any help would be great!

Thanks

[tst]

The Insert Data VI should have a fields input and there are two modes for using it:

• If you don't wire anything into it, the VI will use a cluster element for each column. The order will be the order of the cluster elements which by default is the order in which you placed the controls into cluster. If you have auto-populating columns or type mismatches, you will get an exception.
• If you wire an array of column names into the VI, each element of the cluster will be put into the column of the corresponding index in the array.
  For example, if you have a table with columns [ID, FirstName, LastName] and you wire a cluster which looks like ["Smith", "John"] and the fields input has [LastName, FirstName], the ID field will have a null or automatic value and the other two fields will have the relevant values. I hope this is understandable.

[Amanda_H]

Hi,

Thanks for the great response, tst. For further reference, please see this KnowledgeBase article. Thanks!

[DL84]

Thanks for the responses!

The first one helped with understanding what was going on in the background.  Amanda, your knowledge base entry resolved the problem though.  The program is now tripping up on error code: -2147217913.  This seems to imply that the field data in my DB doesn't match one of the controls data representation set in LV though.

Originally when I made each control I made them U8, but now that they are in the cluster, when I try to look at them the representation option is no longer listed.  In the database I use both string and numeric field types.  All numeric field types are set to "Single", and strings are set to field size: 50.

Why do you think I'm getting that error, the nomenclature is different but I thought u8 was the same thing as single?  And as far as LV goes, i'm not sure how to change the representation of a string to match that of access's field size of 50....

[Amanda_H]

Hi,

I'm glad I was able to help. This KnowledgeBase article should be helpful with regards to the new error you are seeing. Let me know if this helps!

[DL84]

Hi Amanda,

Thanks for the article, it confirmed pretty much what I suspected, but I can't seem to find inconsistancies in data type.  I noticed you list yourself as an applications engineer with NI.  Maybe you could clarify something for me.  My company recently purchased the premier support plan for me to use with the development of applications, and I'm not entirely sure where the line is drawn in what it covers.  There is documentation that came with it that I get "direct access to 'senior' AE for any registered NI products.

Obviously, they won't do programming for you, but if I was to send a copy of the DB and the VI I'm using to access and write data to it...do you know if it's covered to get some help tracking down the problem with a second pair of eyes? (I assume thats what its for, especially with what it cost for 1 year contract, otherwise what else would I use them for lol)

Thanks for your help!

[Amanda_H]

Hi leachdor,

Our Applications Engineers provide support for your technical needs regarding our hardware and software products. With the premier service plan, you will have access to senior engineers, with a typical turnaround time of one business day. While we are more than willing to help with general troubleshooting techniques, we typically do not construct full pieces of code for our customers. (Our partner program is intended for applications such as these.) However, the problem you are facing sounds perfect for an Applications Engineer to help out with. To create a service request, you may call in to 1-866-ASK-MY-NI (1-866-275-6964) or create one online at ni.com/ask. (Creating one online will place you at the front of the queue, so this is preferred by most of our customers.) We look forward to hearing from you soon, and I wish you luck with your application!