Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 185
  • Last Modified:

Find and Replace Routine not working

.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
Fordraiders
Asked:
Fordraiders
  • 2
1 Solution
 
Göran AnderssonCommented:
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
 
Göran AnderssonCommented:
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
 
FordraidersAuthor Commented:
Perfect ...Thanks a million !
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now