?
Solved

Find and Replace Routine not working

Posted on 2007-11-16
3
Medium Priority
?
177 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

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

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…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

777 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