Solved

String Replace using SQL/TADOQuery

Posted on 2006-11-21
10
2,423 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

732 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