?
Solved

String Replace using SQL/TADOQuery

Posted on 2006-11-21
10
Medium Priority
?
2,451 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 46

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
Technology Partners: 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 1000 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 46

Accepted Solution

by:
aikimark earned 1000 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 46

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses
Course of the Month11 days, 15 hours left to enroll

752 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