Solved

Find and Replace Routine not working

Posted on 2007-11-16
3
173 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
vb.net checkbox 7 47
I need help viewing multiple page TIFF image in my WPF with VB.Net code-behind application 7 31
vb.net help 4 31
Write to a printer using vb.net 9 31
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…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

831 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