SQL Update To Access

[BradFromOz]

I am having trouble working out the correct way to update variables back into an access database.

I can insert a text string ok but I can not grab any of the variables.

The code I am using to insert the text string is:

UPDATE tblEmailAddresses
SET txtEmailAddress='test'
WHERE UniqueId=1

From everything I have read I should be able to use

UPDATE tblEmailAddresses
SET txtEmailAddress=$Dataset Column(3)
WHERE UniqueId=1

or

UPDATE tblEmailAddresses
SET txtEmailAddress=$txtEmailAddress$
WHERE UniqueId=1

Any help would be appreciated, the script is attached and the db field names match the variable names in the task.

Cheers

Brad

[forumsupport]

Hello,

The datatype of 'txtEmailAddress' field is text so we need to put quotes around variable value. Like this,

UPDATE tblEmailAddresses
SET txtEmailAddress='$Dataset Column(3)'
WHERE UniqueId=1

or

UPDATE tblEmailAddresses
SET txtEmailAddress='$txtEmailAddress$'
WHERE UniqueId=1

There is no attachment with your original post so if you could upload your task then we can amend it for your convenience.

[BradFromOz]

Thanks for the feedback.

I have given both of those options and still can't seem to get it to work.

I realised after I hit Submit last time that I forgot to attach but had to wait for the post to get approved.

Task and DB attached this time.

Thanks

Brad

[forumsupport]

Hello Brad,

The given task, extracts txtEmailAddress from Access table and updates the same column (i.e. txtEmailAddress) with same value.

We have attached amended task that extracts txtEmailAddress where UniqueId=1 and updates txtEmailAddress of UniqueId = 2.

Please copy attached task under location, 'C:\...\My Documents\Automation Anywhere\Automation Anywhere\My Tasks' folder.

[BradFromOz]

Thanks for your help, that did the job.

I was setting a default variable in the txtEmailAddress field in the example above. When I remove this default value the script works but if I leave the default value in there it doesn't.

This raises 2 questions for me.

1. Why doesn't the default value get passed
2. Why doesn't the default value get overwritten

I am happy to work without default values in 99% of cases but Murphy's Law will dictate that I will want to at some stage now.

Interested on your thoughts there.

Thanks for the quick response.

Brad

[forumsupport]

Hello,

Regarding your query,

1) This is very strange as we have never heard or seen such type of behaviour before. However, let us investigate. In the task, we are assigning value of DataColumn(5) to txtEmailAddress at line number 9 so ideally it overrides the default value. You can check the value of txtEmailAddress variable using msgbox command before and after line number 9.

2) We suggest clearing txtEmailAddress variable in the beginning as the loop starts so that it can ensure it is not carry forwarding the old value. Use Variable Operation to assign blank to any user variable.

Hope that helps.