Solved

String Replace using SQL/TADOQuery

Posted on 2006-11-21
10
2,375 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

867 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now