Solved

Find and Replace Routine not working

Posted on 2007-11-16
3
171 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
  • 2
3 Comments
 
LVL 29

Accepted Solution

by:
Göran Andersson earned 500 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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
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…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

743 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