?
Solved

"Find and Replace" a String in an Access Database

Posted on 2005-04-27
11
Medium Priority
?
243 Views
Last Modified: 2010-03-31
In fact I have posted the question in MS Access at http://www.experts-exchange.com/Databases/MS_Access/Q_21405311.html. But the solutions suggested does not work for Java. Hopefully someone from this channel can help me out. Please refer to the link for details about the question.

Thanks.
0
Comment
Question by:alex_ge
  • 3
  • 2
  • 2
  • +4
11 Comments
 
LVL 16

Expert Comment

by:suprapto45
ID: 13883190
Hi,

Can you elaborate more on your question please?

"MyColumn.replaceAll("Morning", "Afternoon")."
Do you want to replace all the morning with afternoon?

Regards
Dave
0
 
LVL 15

Expert Comment

by:aozarov
ID: 13883213
what about this generic sql statement (that should work in java as well):
Update MyTable Set MyColumn='Morning' where MYColumn='Afternoon'
0
 
LVL 15

Expert Comment

by:aozarov
ID: 13883245
Also, are you sure that if you take the suggestion provided in the linked thread
and then use it with jdbc statement by calling updateQuery on MS access DB that fails?
If so, what is the exception?
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 92

Accepted Solution

by:
objects earned 250 total points
ID: 13883400
you'll need to do a select, then replaceAll(), and update the column.
let me know if you have any questions :)
0
 

Author Comment

by:alex_ge
ID: 13883448
I only want to update a portion of the column, so it's definitely not going to be "Set MyColumn='Morning' where MYColumn='Afternoon'".

To "objects", is this the only way? Kind of tedious. I thought there might be some ready commands which I can just call.
0
 
LVL 92

Expert Comment

by:objects
ID: 13883467
> I thought there might be some ready commands which I can just call.

Not if your database doesn't support it sorry.
Youy could use an updateable result set (again if supported)
http://www.javaalmanac.com/egs/java.sql/UpdateRow.html
0
 
LVL 30

Expert Comment

by:Mayank S
ID: 13883736
Which driver are you using?

BTW, what's wrong in shooting a query directly like aozarov mentioned:

>> Update MyTable Set MyColumn='Morning' where MYColumn='Afternoon'

Why do you want to select those rows first?

>> I only want to update a portion of the column, so it's definitely not going to be "Set MyColumn='Morning' where
>> MYColumn='Afternoon'".

Can you explain a little more what you mean by portion of the column? Maybe an example will make it clearer.
0
 
LVL 21

Expert Comment

by:MogalManic
ID: 13884127
How about this (warning: this is Access specific code NOT ansi sql):

  update table set MyColumn=left(MyColumn, inStr(MyColumn,'Afternoon')) & 'Morning' & mid(MyCoumn, inStr(MyColumn,'Afternoon')+9)
  where MYColumn like '*Afternoon*'

This will replace the first occurance of 'Afternoon', with 'Morning' in the column.  To replace ALL, keep executing the statement until 0 rows are updated.
0
 

Author Comment

by:alex_ge
ID: 13884187
"left" and "inStr" are VBA functions or not? if they are, then I'm not able to use them in my Java app.
0
 
LVL 10

Expert Comment

by:edwardiii
ID: 13902053
Hi, alex_ge.

The following works for me one a Win2k Pro system running J2 v1.4.2_03 and interacting with an Access 2000 database table (table is "Table1").  The code that replaces all occurrences of "edwardiii" in my table's "Supplier" column with "ed" is:

     pStatement.execute("update Table1 set Supplier = 'ed' where Supplier ='edwardiii'");

I then pull all recordsets from the "Table1" table and display them in a JTextArea.  Here is the complete routine:

       Connection con;    
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            String MoreTextString;
            con = DriverManager.getConnection("jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=C:/MyDatabase.mdb","","");            
            Statement pStatement = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            pStatement.execute("update Table1 set Supplier = 'ed' where Supplier ='edwardiii'");
            pStatement.execute("select * from Table1 where Supplier = 'ed'");
           
            ResultSet rs = pStatement.getResultSet();    
            while (!rs.isLast())
            {
                rs.next();
                String Id = rs.getString("ID");
                String Supplier = rs.getString("Supplier");                
                moreJTextArea.setText(moreJTextArea.getText() + "\n" + Id + " " +Supplier);
                               
            }
            rs.close();
            pStatement.close();
            con.close();            
                   
        }
        catch (SQLException e)
        {
            System.out.println(e.getMessage());
           
        } catch (Exception e)
        {
            System.out.println(e.getMessage());
        }
0
 

Author Comment

by:alex_ge
ID: 13907885
Thanks all for your efforts. It seems there are no other solutions, other than select, replace and update, as suggested by "objects". So I have accepted his suggestion as the answer
0

Featured Post

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!

Question has a verified solution.

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

After being asked a question last year, I went into one of my moods where I did some research and code just for the fun and learning of it all.  Subsequently, from this journey, I put together this article on "Range Searching Using Visual Basic.NET …
Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.
Suggested Courses
Course of the Month15 days, 14 hours left to enroll

850 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