Solved

String Replace using SQL/TADOQuery

Posted on 2006-11-21
10
2,398 Views
Last Modified: 2012-06-27
I am working on an application developed in Delphi 2006 (for Win32 platform) that uses MS Access database as a backend. I need to write a query along the lines of:

UPDATE tblPlans SET tblPlans.AccessList = REPLACE(AccessList,"OldValue","NewValue")
WHERE (tblPlans.AccessList LIKE "%OldValue%")

The query works perfectly in MS Access (ie after substituting '*' for the '%' etc), but when run from the Delphi app, results in a message like "Undefined function 'REPLACE' in expression." So it looks like TADOQuery doesn't support the REPLACE function.

Can anyone suggest an alternate approach for replacing text that will work with an UPDATE query (via TADOQuery)?

PS Stored procs aren't an option in the context of this particular application and the nature of it's deployment. (Otherwise I'd happily use them!)

PPS I can get around this problem to a degree with a long formula that uses LEFT, INSTR and RIGHT (ie to grab the strings to the left and right of the "OldValue" and join these with the "NewValue"), but I'd prefer something a little more efficient.



0
Comment
Question by:DavidLeeding
  • 4
  • 3
  • 3
10 Comments
 
LVL 15

Expert Comment

by:mikelittlewood
ID: 17994079
Ive just tried this using ADO, and it works fine for me.
0
 
LVL 15

Expert Comment

by:mikelittlewood
ID: 17994106
I better put into context, I am using Delphi 7 and MSSQL.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 17994955
I recommend you avoid TADOQuery for this.  Instantiate an MSAccess object.  You can then run a query through the .DoCmd method.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 15

Expert Comment

by:mikelittlewood
ID: 17995732
Sounds like its not an ADO problem to me, but the interaction with ADO and MSAccess.
Im using MSSQL so I cannot guarentee that what I have found is correct for MSAccess.

On a side note ... MSAccess ... yuck  :o)
0
 
LVL 1

Author Comment

by:DavidLeeding
ID: 18000174
Hi mikelittlewood
I'm afraid I'm stuck with MS Access for this project. Sigh. I agree: MSAccess = YUCK. Migrating to MSSQL right now is not possible/practical (and also brings with it a whole heap of corporate politics I can do with out for the moment). We intend to do so...eventually.

I suspected the problem has to do with the ADO-MSAcess interaction...I've come up against a few strange things in the past. Do you know of any reference/documentation on the net that highlights the ways in which ADO + MSAcess can go pear-shaped?

Hi aikimark
Thanks for your suggestion. However, for this app (and for a number of reasons), I'm required to stick with TADOQuery. Also, this particular query sits in the middle of a sequence of UPDATE queries (all using TADOQuery). I'd rather not treat this problematic query in a different way to the others.


Looks like I'll have to stick with my *ugly* query based on LEFT, INSTR and RIGHT. Sigh.
0
 
LVL 15

Assisted Solution

by:mikelittlewood
mikelittlewood earned 250 total points
ID: 18001702
Hi again David. You are correct about the ADO - MSAccess issue.
I did a quick test this morning with a new access database and ADO with the replace function, and I get the same error as you.
I think you are going to have to use your *ugly* query.
0
 
LVL 45

Accepted Solution

by:
aikimark earned 250 total points
ID: 18002542
Is there a way to prevent any syntax checking by the TADOQuery layer?  Maybe force a pass-thru condition.

Maybe you could add a public function to the database that does the Replace.

Are there any parameters you've set on your TADOQuery that might be more appropriate to a Select statement than an Update statement.  Your query doesn't return records.

You might try running this query in an .Execute method off the database connection object.
0
 
LVL 1

Author Comment

by:DavidLeeding
ID: 18004813
Thanks for your suggestions, mikelittlewood and aikimark.

I've checked the settings of the query; everything's as it should be. I tried ".Execute" on the database connection object and I get the same error message.  I'm requried to avoid using the backend for anything but data, so can't used stored procs or functions in the database. So I think I'm stuck with the inefficient "ugly" UPDATE query until we're ready to migrate to a "real" database. Oh well.

I'll split the points because you've both put time and thought into this...and you've confirmed for me that there's no "easy" way around this anomaly!
0
 
LVL 45

Expert Comment

by:aikimark
ID: 18015354
@DavidLeeding

If you create a separate MSAccess database with attached tables, you can store queries for use with your program.
0
 
LVL 1

Author Comment

by:DavidLeeding
ID: 18015590
Hi aikimark
Yes...I've done that with other applications. Unfortunately, it's not approriate to do that for this particular project for several long-winded and not entirely rational reasons (which I'll spare you!). But thanks for the suggestion.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question