Updating record using a Query

Hi guys,

I am a bit confused and a tad frustrated with the following scenario, and I am hoping you can point me in the right direction or at least tell me I am barking up the wrong red herring!

Simple scenario, I have a window/form which has a table on it and a few edit controls, usual save, edit buttons etc.

The Table & Edit controls are linked or bound to a parameterised query. The intention is to do all the edits on this query then write back to the file/table in SQL Server back end.

I am using HExecuteQuery as I have built the query using the Windev Query Editor, I read somewhere in the help that the Query if it's linked to a file should update said file, I am doing doing an HAdd and HModify in the right places I think, then executing the HExecuteQuery but
it's not happening!

Hi Mark,

Isn't there a 'hmodifyfile' option when you run your query that you need to use for this to work?

Cheers,

Bob

Bob,

Yes there is thanks, I thought in my desparation I might have tried that but I'll go back and have a look again.

Mark

"On non-Hyper File files, the hModifyFile constant is useless: the files involved in the query are automatically modified when the query result is modified.
" according to the help.

You have to make sure your record pointer is pointing to the required record inside your queryresult, every time you need to change some value.
i.e. use HReadSeek() in "Row Selection" of your browsing table.
Then you can modify some fields and use HModify() to save those changes.

another example (without error handling)
HExecuteQuery(yourquery)
HReadFirst(yourquery)
WHILE NOT HOut(yourquery)
yourquery.SomeField = "some value"
HModify(yourquery)
HReadNext(yourquery)
END
HCancelDeclaration(yourquery)

Arie

Hi Mark,

The HAdd command works fine for me when using a query to popoulate a screenview and then to write back to the original SQL table. I have shown an example below. I am connecting via OLEDB and have the correct permissions. Is it a permissions issue in your case?

HOpen(TblNationalTransferCostBrands,"",hOReadWrite)
HExecuteQuery(EvaNtcExclusionQuery,hModifyFile,gStyID)
HReadFirst(EvaNtcExclusionQuery,EvaNtcExclusionQuery.evaID)
WHILE NOT HOut(EvaNtcExclusionQuery)
TblNationalTransferCostBrands.ntbNtcID=EvaNtcExclusionQuery.ntcID
TblNationalTransferCostBrands.ntbActive=True
TblNationalTransferCostBrands.ntbBrnID=gBrnID
TblNationalTransferCostBrands.ntbDateCreated=Today()+Now()
TblNationalTransferCostBrands.ntbDateUpdated=Today()+Now()
HAdd(TblNationalTransferCostBrands,hCheckIntegrity)
Error("Added one record")
HReadNext(EvaNtcExclusionQuery,EvaNtcExclusionQuery.evaID)
END


HTH

Milton

Arie,

Thanks for your input I'll give that a go.

Mark

Milton,

Thanks for your suggestion, and another way of looking at it I will try your solution out also.

Mark