Overwriting Data in mdb file

[vivekde]

Hi,

   I have the requirement to fetch tha data from the mdb file through SQL operations provided in the teststand. I am successfully reading and writing the data into the mdb file but struggling with overwriting the data again i.e. one of the field say freature needs to be overwite with the new value captured from VI. The alternative method could be appending the new data and fetch the latest one. I am unwaware of the SQl statements and hence finding difficulty in judging the best one.

Please let me know if anyone could help me.

Thanks in Advance,

Vivek

[J_Stape]

Hi Vivek,

If you are successfully reading and writing data into your .mdb file using the database steps, I believe you are comfortable using the SELECT and INSERT statements.  To overwrite data, you can use the UPDATE statement. The syntax for an UPDATE statement is:

UPDATE table
SET column = expression
WHERE predicates;

You might want to open up MS Access to try running your SQL statements before using TestStand data steps.  This will help you get the syntax correct before running it in TestStand.

I have attached an example sequence that demonstrates how to use a SELECT query to get a dataset and then use an UPDATE query to modify the dataset.  You will not be able to run it as it isn't written for your udl, but it should be useful as a guide.


Message Edited by J_Stape on 04-15-2008 05:52 PM

[vivekde]

Thanks Jessica for your help. It's working Happily now. Thanks Again

[Shwets]

This query Works for Hardcoded values to be replaced as is also shown in your example but when i am using any variable for example

UPDATE table
SET column = "+FileGlobals.Variable+"
WHERE predicates

FileGlobals.Variable gets the value from previous statement.

This Query gives the error saying one or more values not given.

Can i know a way where instead of hardcoded values i can use variable in the update query like in select query we do.

Thanks in Advance

Message Edited by Shwets on 04-21-2008 08:39 AM

[Shwets]

Though through different technique i have found a way to update a table by using Open SQL statement and than Use data operation

statement With "data to operate on" set to SET AND PUT RECORDS and the values to be updated accessed from variables which are given in Coliumn/Parameter values Section of Data Operation statement.

But Still there must be a way out to update fields in TABLE with values which are dynamically updated from variables and using these varaibles, instead of hardcoded values in UPDATE Command of SQL.

[Dennis_Knutson]

You can't just place quote marks around the variable. You need to create the string by doing something like:

"UPDATE myTable SET myText = '" + FileGlobals.Variable + "' WHERE Key=1;"

[Shwets]

Yes i did the same but still it gives me error.

Thanks

Shweta

[Dennis_Knutson]

Post an example sequence with your query. I never did update commands but all other queries used a TestStand variable so I know it's possible.

[vivekde]

Hi Dennis, I attached a sequnce file that tries both the Update and Set and Put Option available in TestStand to update the database. I have added comments on those steps what exactly happening when we are using those operations. Please help me soon coz we have an urgent requirement. Thanks In Advance, Vivek

[Dennis_Knutson]

It looks to me like you have a problem with strings. When you do a query on a database and look for a string, you need to enclose the string with '. This is used in the earlier example but you are not doing that.