inserting arrays with DB Tools Insert Data

[labstew]

Hello,

I am having issues inserting data into a SQL database.  I don t have very much experience with SQL but all of the SELECT statements I made with labview worked perfectly.  However once I got to the INSERT portion of my code I can t get anything to work. 

I am sending an array into the "data" input of DB Tools Insert Data and I am sending in a list of elements in an array into the columns input.  I get an error that says "Argument error; the number of columns does not equal the number of parameters."  but my arrays are the same size.  I have been looking on the forums and have seen some information that leads me to believe that you can t send an array into the DATA input, but if you can t how will I sort the Columns?

I can attach the code if needed, but all of the data is gathered from select statements from our database so I am not sure it will be very helpful.

Thanks,

Andrew L

[Wensu]

Hi Andrew,

I don't know a lot about the DB Tools either, but I have been able to do select, insert, and update, using the LabVIEW DB Tools. 
If you can provide your code and sample data (txt file or something) maybe I can take a look.

Victof

[labstew]

Here is a copy of the VI.  It is in LV 8.2 but I can convert it to any other version if you need. 

[miguelc]

There are a couple problems with your code:

1. The DB Insert vi requires a cluster of elements, with each cluster element corresponding to a column of the 1D array of columns wired to column. To do this, its easiest to keep your data in non-variant form, then use the "Array to Cluster" thing (make sure to right click and set the number of elements, you have to do it manually). The only time you would wire a variant to the Insert vi is when you want to insert it into a single column. Note also the DB Insert vi will only allow you to do a 1D array at a time.

2. The number of elements in the variant data is one more than the columns (you have 8 columns but 9 elements in the Variant control, by my count). In the VI attached I simply set the "Array to Cluster" to have 8 elements, so it should work.

Why they require you to put the data in cluster format has been discussed here:

http://forums.ni.com/ni/board/message?board.id=170&thread.id=133738&view=by_date_ascending&page=3

Basically some people from the old SQL toolkit wanted it that way, probably to prevent broken code. Personally I think it should be changed, or the "array to cluster" should allow programmatic setting of element size.

[labstew]

Excellent! It worked perfectly, I had converted it to a cluster earlier and was wondering how the heck to remove that extra character 🙂 so it was a breeze to get it running (although I had one small hiccup because the code was wired to an indicator that had the extra element added at the end of the data still, removing the indicator and making a new one fixed this.).  The whole code sequence is now working and I can now auto create records for production test sheets, the old way used to be our techs logging in and manually creating sheets with an old access front panel.

Thank you very much Victof.

Andrew L