How to select a record from a table to manipulate the data in a database?

[MechUnit]

Hello Community,

Using 32-bit Labview 2015.

I created a user interface that runs a query and fetches my table from a sql database.

I want to be able to manage one record at a time by selecting the record from my table and then manipulating the data as needed.

Everytime the user runs a query I want the first record in the table to be selected/highlighted.

I want an indicator showing which record is currently active.

Then by a button click, to be able to scroll through the actively selected record.

Once I have the selected record, I want a way to write a query to edit or delete the record.

Attached is a picture of my query to select all from my table and plugging the data into my table(results).

Attached is a picture of my table showing the records from my sql database.

What is the best way to go about selecting a record from a table and editing the data with a query?

Any help would be greatly appreciated.

Thank you 

[Alexander_Sobolev · Accepted solution]

I guess it is not a table, but multicolumn listbox.

Right click it, turn on Selection Mode -> Highlight entire row

The Value of the multicolumn listbox is the row index - you can write local variable to highlight row, use event structure to handle user clicking, etc.

If you turn on Editable Cells property of the listbox, you can allow user to edit listbox items.

If you want to get row data, you index ItemNames array and work with single row string array - convert it to cluster, etc.

I do not know, what kind of database it is, how you database structure looks like, I even do not know if you use wrapper to work with database, I can not write query for you.

Something like Update Tablica Set Serial='5' Where ID='22';

[MechUnit]

Hi Alexander,

Could you explain more about the get row data please. I am having trouble picturing it.

Attached is my table and column names.

[MechUnit]

I was able to get the row data and now have a single row string array that I can work with that I converted into a cluster.

I am currently having issues with getting the DB Tool Update to work.

I am unsure what I should be connecting to the Data terminal. I get the error below.

"Argument error; the number of columns does not equal the number of parameters."

It is possible that it could be my query. Do I need to update every column, even if no change occured, or can I just update the cells in a column that changed?

[Alexander_Sobolev]

I did not work with your database tool (I do not know what is it - LabVIEW database tool or not), so it is hard to say what should go into unknown VI.

With single SQL quety you can update multiple columns. Condition (WHERE) specifies which rows to update. If it is not used, it will update ALL rows.

If it is labview database tool, DB Tools Update Data VI

http://zone.ni.com/reference/en-XX/help/370016F-01/lvdatabase/db_tools_update_data/

it says that Data should be a cluster with number of elements, matching the size of  Columns array, first cluster element sets value of the column with first name, etc.

The row to update you specify with condition input WHERE (ID = 22).

If you are writing raw queries, it will be something like

UPDATE ProductTestView SET UUT_Serial_Number='5' WHERE ID='22'.