[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Find and Replace Routine not working

Posted on 2007-11-16
3
Medium Priority
?
182 Views
Last Modified: 2010-04-23
.net 2003
access 2003


What Iam Doing...

Taking a value from a field...fldMfgname
See if it exists in another table  txtFind...
If it does it is replaced with the txtReplace Value

Problem...
Ny Field value in fldMfgname keeps getting deleted to  "" or null


i.e.

What it should do:

tblVendNameFindReplace   =
txtFind         txtReplace
BUSS             BUSSMANN


tblData =
fldMfgname
BUSS


Replaced value  is  

BUSSMANN



iI keep getting the value in fldMfgname deleted...





Private Function AccessSandR()
        Dim conn As System.Data.OleDb.OleDbConnection
        Dim tblVendNameFindReplace As New DataTable
        Dim tblData As New DataTable

        conn = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Dm2007\SkuCat.mdb")
        'make dataadapters
        Dim dac As New System.Data.OleDb.OleDbDataAdapter("SELECT fldId, txtFind, txtReplace FROM tblVendNameFindReplace ", conn)
        Dim dad As New System.Data.OleDb.OleDbDataAdapter("SELECT fldID, fldDid, fldMfgname, fldMfrnum, fldDescription FROM tblData", conn)
        Dim cb As New System.Data.OleDb.OleDbCommandBuilder(dad)
        'get the datables from Access
        dac.Fill(tblVendNameFindReplace)
        dad.Fill(tblData)
        'send updated table back to Access

        For Each drc As DataRow In tblVendNameFindReplace.Rows
            For Each drd As DataRow In tblData.Rows
                If Not IsDBNull(drd("fldMfgname")) Then drd("fldMfgname") = ReplaceWordsTemplate(drd("fldMfgname"), drc("txtFind"), drc("txtReplace"))
            Next
        Next
        dad.Update(tblData)
    End Function
   
    Private Function ReplaceWordsTemplate(ByVal SearchText As String, ByVal Token As String, ByVal ReplaceText As String) As String
        Dim newtext As String = ""
        newtext = SearchText.Replace(" " & Token & " ", " " & ReplaceText & " ")
        'return result after removing space added at the end
        Return newtext.Substring(0, newtext.Length - 1)
    End Function


Thanks
fordraiders
0
Comment
Question by:fordraiders
[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
  • 2
3 Comments
 
LVL 29

Accepted Solution

by:
Göran Andersson earned 2000 total points
ID: 20304319
Here is your problem:

        'return result after removing space added at the end
        Return newtext.Substring(0, newtext.Length - 1)

Every time you call the method, it will remove the last character of the string, regardless if any replacement was done or not. If there are enough records in the tblVendNameFindReplace table, the string will be eaten up character by character until there is nothing left.

If you want to match whole words, you can make a regular expression using "\b" + Regex.Escape(Token) + "\b". That will match the token with a word break at each end, but it will not include the word breaks, so when you use the Regex.Replace method to do the replacement, you don't add spaces around the replacement word, so there is no spaces that you have to remove afterwards.

I suggest that you create the Regex object outside the tblData loop, so that you don't create a new Regex object for every single replacement. Something like:
For Each drc As DataRow In tblVendNameFindReplace.Rows
   Dim re As new Regex("\b" + drc("txtFind") + "\b", RegexOptions.Compiled)
   For Each drd As DataRow In tblData.Rows
      If Not IsDBNull(drd("fldMfgname")) Then
         drd("fldMfgname") = re.Replace(drd("fldMfgname"), drc("txtReplace"))
      End If
   Next
Next

Open in new window

0
 
LVL 29

Expert Comment

by:Göran Andersson
ID: 20304325
Correction:
Dim re As new Regex("\b" + drc("txtFind") + "\b", RegexOptions.Compiled)
 
should of course be:
 
Dim re As new Regex("\b" + Regex.Escape(drc("txtFind")) + "\b", RegexOptions.Compiled)

Open in new window

0
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 31409656
Perfect ...Thanks a million !
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
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

649 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