Update values to the database

[gnshmrthy]

Hi friends,

             I`m trying to update the values to the data base, I get the following error

 

"NI_Database_API.lvlib:Cmd Execute.vi->NI_Database_API.lvlib:DB Tools Update Data.vi->Insert_Update Table and Data to the Database.vi<ERR>ADO Error: 0x80040E14

Exception occured in Microsoft Office Access Database Engine: Syntax error in UPDATE statement. in NI_Database_API.lvlib:Rec Create - Command.vi->NI_Database_API.lvlib:Cmd Execute.vi->NI_Database_API.lvlib:DB Tools Update Data.vi->Insert_Update Table and Data to the Database.vi"

 

I didn`t connect the condition terminal, I`m not sure what should be done with the condition terminal at the Update VI. I tried connecting the terminal with the empty string or string constant still the same error.

 

 

Thanks,

Ganesh

[LogMAN]

The condition terminal is well described in the help:

 

condition specifies an SQL clause that this VI uses to filter the selection criteria. This VI appends this clause to the end of an update statement. For example, where (col1 > 10). If you do not specify a value for this input or if the input is an empty string, this VI updates all rows in table.

 

EDIT: Should describe anyways:

Let's say your table consists of a pair of ID + name ( 0 | bill, 1 | john, 2 | pa_l, etc..) now you want to update the name of the row in which the ID is 2 ('pa_l' should be 'paul'), your condition would be 'WHERE ID = 2'.

 

Your current data is of variant type. What data are you trying to send: cluster, string, numeric, ...? -> I guess you get the error because the data terminal is not correctly wired.

 

The table input specifies the table name and if your table has for example three columns (id, owner, description) and you only want to update the description, you should set the first element of the columns terminal to 'description'. (And only provide the new value of description to the data terminal!)

 

Could you provide some more information about your actual data type & table layout?

Edited November 25, 2013 by LogMAN

[gnshmrthy]

Hi Logman,

                I just want to update all the value to the database. I`m passing a numeric values of (Long and double), in some cases I also use string. The images of my screen are 

 

 

 

 

 

 

Sorry I really didn`t get the theme of the description described in help menu.

Edited November 25, 2013 by gnshmrthy

[LogMAN]

I can only guess based on the help, since I generally use pure SQL syntax... The update statement will try to update all columns in your table which is impossible as you need a primary key (a column that is the unique identifier). This would be 'speedo_ok' in your example I guess. You could now either update element-by-element using the condition input WHILE speedo_ok={speedometer values.select} where you have to replace {speedometer values.select} with the actual value of the particular element. The second option is to present a new primary key to your table that is set to auto-increment; and always define the column names for your cluster whenever you use the cluster to insert / update / etc...

 

The update statement with all cluster elements makes no sense as far as I understand, as this would cause all rows to carry the exact same information for each column. As I mentioned before, you need a primary key that is unique. Try to update a certain element of your cluster using the condition I mentioned before.

 

Maybe someone else could give an actual example how to solve this based on the DB VIs?

 

Sorry I really didn`t get the theme of the description described in help menu.

 

No problem, I had to learn all that stuff too

A good source for SQL has always been http://www.w3schools.com/sql/ or MSDN of course for MSSQL.

 

 

Just for my own satisfaction , but this would be the SQL syntax (I did not test it, but it should work): You have to replace all elements in {} by their representatives in the cluster. Green would be valid, red is what you try to do (I still assume speedo_ok is your primary key):

 

UPDATE speedometer SET speed={max speed}, frequency={speedo max freq}, amplitude={speedo amplitude}, pulse_rev_n={speedo pulse/rev}, ratio={speedo ratio}, pulse_type={pulse type} WHERE speedo_ok={select};

UPDATE speedometer SET speedo_ok={select}, speed={max speed}, frequency={speedo max freq}, amplitude={speedo amplitude}, pulse_rev_n={speedo pulse/rev}, ratio={speedo ratio}, pulse_type={pulse type};

 

I hope that helps.

[gnshmrthy]

Hi Logman, You are correct the column names were the primary key for the error. I`ve updated the column names and also added the S.No for the tables which has more than one row. I used the S.NO as the particular element to update the values in the corresponding rows.

 

 

 

 

Kudos....

 

 

The link for SQL is great thanks for the knowledge sharing.

Edited November 26, 2013 by gnshmrthy