db tools - insert into

[Parny]

Hi,

Please look into the attachment. I am inserting the data of an array and a constant into a MS access table(interval) as shown in the picture using insert_into vi. The array has 24 elements and hence the target table has 24+1(for constant)=25 columns, all elements are written at a time as single record . To make the work simple, I am avoiding wiring 'columns array', and wiring 25elements of data as a cluster(assuming 1st element as 1st colums and so). The shown arrangement works perfect.

change seeking:

Would like to include inserting a new element(means tagrget table now has 26 columns) 'date&time'. My work would be easier if I am able to include that in existing build array, however since 'date&time' is non-numeric type can't be included there. Is it possible to include that in that existing cluster some how? although, I may do all this by wiring 'columns array' with all the 26 columns in it and constructing a cluster seperately, which is a bit heavier compared to this!

Thanks in advance.

Message Edited by Parny on 07-07-2008 09:49 PM

[mikeporter]

Can you post a description of the table you are inserting the data into? When you say a MS Access database, do you mean Jet? MS Access technically isn't a database, it's a development environment for creating applications that use databases. You can link the Access front end to a variety of databases including SQL Server and Oracle. Also it will make things easier of you lose the database connectivity toolkit - it's an unnecessary complication. See here.

Mike...

[tst]

The DB toolkit has a helper VI which translates a U32 into a date\time string which the insert VI can use in its cluster. It should be in one of the palettes.

[Parny]

Thanks for your time Mike.

Yes, its Jet DB provider and enclosed here. I will be writing all 26 values as a single record to that table for every certain time. Indeed, mostly I use SQL_execute_query.vi(with string formatter, I know its heavy work) for inserting as well. But dbtool_insert_data.vi is easier at some times such as my figure in the first post. I had a look at executesqlcommand.vi from your attachment, the data type has to be 1D array of variant only? how can I achieve my requirement with that?

or can you please make an arrangement to write a record to the atatched db1.mdb into the given fields.

[Joachim082]

Hi Parny,

I don't know if this makes what you want with your date/time:

In my applications I always use the LV Timestamp Information and convert it to a double. When reading back in LV i convert the double back to a LV Timestamp. Unfortunately if you need the information readable in access you can't use my recommendation.

HTH
--
Joachim

[Parny]

Hi tst,

I am not really worried about converting into string, b'coz that can be achieved by timing palette functions(pls see my attachment) itself, but how I can insert that string(datetime) along other 25 elements of number data at a time into the table without wiring any column array in the insertinto vi?

Thanking you.

[tst]

Ah, I didn't notice that your cluster was originally an array. I don't think there's any way to do that other than bundling the entire array or using the timestamp as a DBL. What I usually myself is just handle all the bundling and unbundling in subVIs, so that at least the code is clean.

As for the string, the DB toolkit has a VI which formats the string in a way in which the insert VI can recognize it, but I don't think that will help you. You could try modifying the insert VI so that you can add another parameter, but it's not really worth it.

[Parny]

Hi Joachim,

that really looks closer to what I am looking, but the data in access is meant to be readable. So as per your hint it may not fit exactly

how are you converting string to double(is it using 'decimalstring to number'??)?

thanking you.

[Joachim082]

Hi Parny,

I am not converting the string to double - only the timestamp to double. which format does your time information have? You can use the Scan from string to convert your time information back to the LV timestamp and next to a double.

HTH
--
Joachim