Need help stripping non-alphanumeric characters from fields.-Beginner

[ecugrad]

Need help stripping non-alphanumeric characters from fields. I'm parsing a text file and uploading data to a Sequel Server. The memo fields are giving me an error when I upload the data to the server if the string contains double quotes,apostiphies etc. I thought the code I have in the app would take care of the problem, but it has not. Any help would be appreciated.

Mike


Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

'retreive the file to parse and upload
OpenFileDialog1.InitialDirectory = "\\wil-lsvsql\AOOTemplate\"
OpenFileDialog1.Filter = "txt files (*.txt)|*.txt"
OpenFileDialog1.FilterIndex = 1
OpenFileDialog1.RestoreDirectory = True
If OpenFileDialog1.ShowDialog = DialogResult.OK Then
strFileName = OpenFileDialog1.FileName
End If
txtImportFile.Text = strFileName
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

'create variables for all fields to be parsed from file
Dim strDDate As String
Dim strRLName As String
Dim strRFName As String
Dim strRLogID As String
Dim strRTitle As String
Dim strROther As String
Dim strPhone As String
Dim strBankNo As String
Dim strCenter As String
Dim strRegion As String
Dim strCLName As String
Dim strCFName As String

Dim strCSSN As String
Dim strCSegment As String
Dim strCAccount As String
Dim strCDesc As String

Dim strRPLName As String
Dim strRPFName As String
Dim strRPTitle As String
Dim strRPOther As String
Dim strCatType As String
Dim strCatOther As String

Dim strFClientContact As String
Dim strSClientContact As String
Dim strCompDate As String
Dim strDescofResolution As String
Dim strTime As String
Dim strCSaved As String
Dim strCSat As String
Dim strCDisReason As String

'variables for upload to DisputeLog table
Dim strInsertSQL As String
Dim intCount As Integer
Dim intDisputeID As Integer
Dim intTotal As Integer
Dim strFileToMove As String
Dim intTotalDisputesNotUploaded As Integer

Try
'change cursor to hourglass
Cursor.Current = Cursors.WaitCursor

'determine if file selected
If Len(txtImportFile.Text) > 0 Then
'open the exceptions file to read in data
FileOpen(1, strFileName, OpenMode.Input)
intTotal = 0

While Not EOF(1)
Input(1, strDDate)
Input(1, strRLName)
Input(1, strRFName)
Input(1, strRLogID)
Input(1, strRTitle)
Input(1, strROther)
Input(1, strPhone)
Input(1, strBankNo)
Input(1, strCenter)
Input(1, strRegion)
Input(1, strCLName)
Input(1, strCFName)
Input(1, strCSSN)
Input(1, strCSegment)
Input(1, strCAccount)
Input(1, strCDesc)
Input(1, strRPLName)
Input(1, strRPFName)
Input(1, strRPTitle)
Input(1, strRPOther)
Input(1, strCatType)
Input(1, strCatOther)
Input(1, strFClientContact)
Input(1, strSClientContact)
Input(1, strCompDate)
Input(1, strDescofResolution)
Input(1, strTime)
Input(1, strCSaved)
Input(1, strCSat)
Input(1, strCDisReason)

'Replace any Apostrophe

strRLName = Replace(Trim(strRLName), "'", "''")
strRFName = Replace(Trim(strRFName), "'", "''")
strRTitle = Replace(Trim(strRTitle), "'", "''")
strROther = Replace(Trim(strROther), "'", "''")
strRegion = Replace(Trim(strRegion), "'", "''")
strCLName = Replace(Trim(strCLName), "'", "''")
strCFName = Replace(Trim(strCFName), "'", "''")
strCSegment = Replace(Trim(strCSegment), "'", "''")
strCDesc = Replace(Trim(strCDesc), "'", "''")
strRPFName = Replace(Trim(strRPFName), "'", "''")
strRPLName = Replace(Trim(strRPLName), "'", "''")
strRPTitle = Replace(Trim(strRPTitle), "'", "''")
strRPOther = Replace(Trim(strRPOther), "'", "''")
strCatOther = Replace(Trim(strCatOther), "'", "''")
strDescofResolution = Replace(Trim(strDescofResolution), "'", "''")
strCDisReason = Replace(Trim(strCDisReason), "'", "''")


strRLName = """" & Replace(strRLName, """", """""") & """"
strRFName = """" & Replace(strRFName, """", """""") & """"
strRTitle = """" & Replace(strRTitle, """", """""") & """"
strROther = """" & Replace(strROther, """", """""") & """"
strRegion = """" & Replace(strRegion, """", """""") & """"
strCLName = """" & Replace(strCLName, """", """""") & """"
strCSegment = """" & Replace(strCSegment, """", """""") & """"
strCDesc = """" & Replace(strCDesc, """", """""") & """"
strRPFName = """" & Replace(strRPFName, """", """""") & """"
strRPLName = """" & Replace(strRPLName, """", """""") & """"
strRPTitle = """" & Replace(strRPTitle, """", """""") & """"
strRPOther = """" & Replace(strRPOther, """", """""") & """"
strCatOther = """" & Replace(strCatOther, """", """""") & """"
strDescofResolution = """" & Replace(strDescofResolution, """", """""") & """"
strCDisReason = """" & Replace(strCDisReason, """", """""") & """"


strInsertSQL = "INSERT INTO dbo.Log (DDate, RLName,RFName,RLogID,RTitle,ROther,Phone,BankNo,Center,Region,CLName,CFName,CSSN,CSegment,CAccount,CDesc,RPLName,RPFName,RPTitle,RPOther,CatType,CatOther,FClientContact,SClientContact,CompDate,DescofResolution,Time,CSaved,CSat,CDisReason)"
strInsertSQL = strInsertSQL & "Values ('" & CDate(strDDate)
strInsertSQL = strInsertSQL & "', '" & strRLName
strInsertSQL = strInsertSQL & "', '" & strRFName
strInsertSQL = strInsertSQL & "', '" & strRLogID
strInsertSQL = strInsertSQL & "', '" & strRTitle
strInsertSQL = strInsertSQL & "', '" & strROther
strInsertSQL = strInsertSQL & "', '" & strPhone
strInsertSQL = strInsertSQL & "', '" & strBankNo
strInsertSQL = strInsertSQL & "', '" & strCenter
strInsertSQL = strInsertSQL & "', '" & strRegion
strInsertSQL = strInsertSQL & "', '" & strCLName
strInsertSQL = strInsertSQL & "', '" & strCFName
strInsertSQL = strInsertSQL & "', '" & strCSSN
strInsertSQL = strInsertSQL & "', '" & strCSegment
strInsertSQL = strInsertSQL & "', '" & strCAccount
strInsertSQL = strInsertSQL & "', '" & strCDesc
strInsertSQL = strInsertSQL & "', '" & strRPLName
strInsertSQL = strInsertSQL & "', '" & strRPFName
strInsertSQL = strInsertSQL & "', '" & strRPTitle
strInsertSQL = strInsertSQL & "', '" & strRPOther
strInsertSQL = strInsertSQL & "', '" & strCatType
strInsertSQL = strInsertSQL & "', '" & strCatOther
strInsertSQL = strInsertSQL & "', '" & strFClientContact
strInsertSQL = strInsertSQL & "', '" & strSClientContact
strInsertSQL = strInsertSQL & "', '" & strCompDate
strInsertSQL = strInsertSQL & "', '" & strDescofResolution
strInsertSQL = strInsertSQL & "', '" & strTime
strInsertSQL = strInsertSQL & "', '" & strCSaved
strInsertSQL = strInsertSQL & "', '" & strCSat
strInsertSQL = strInsertSQL & "', '" & strCDisReason & "')"
SqlCommand1.CommandText = strInsertSQL
SqlCommand1.ExecuteNonQuery()
intTotal = intTotal + 1

End While

FileClose(1)

'change cursor to default - pointer
Cursor.Current = Cursors.Default

If intTotal > 0 Then
MsgBox(intTotal & " Record(s) were successfully uploaded, and " & intTotalDisputesNotUploaded & " dispute(s) could not be uploaded due to data type errors.", MsgBoxStyle.OKOnly, "Upload Complete")
Else
MsgBox(intTotal & " Records(s) have been successfullly uploaded.", MsgBoxStyle.Information & MsgBoxStyle.OKOnly, "Upload Complete")
End If


txtImportFile.Clear()

'move file into 'ImportedFiles' folder
strFileToMove = ""
For intCount = 1 To Len(strFileName)
If Mid(strFileName, (Len(strFileName) + 1) - intCount, 1) <> "\" Then
strFileToMove = Mid(strFileName, (Len(strFileName) + 1) - intCount, 1) & strFileToMove
Else
Exit For
End If
Next
File.Move(strFileName, "\\wil-lsvsql\AOOTemplate\ImportedFiles\" & strFileToMove)
txtImportFile.Clear()

Else
MsgBox("Please select a file to import to continue.", MsgBoxStyle.Information & MsgBoxStyle.OKOnly, "File Selection")
End If


Catch sqlEx As SqlClient.SqlException
MessageBox.Show(sqlEx.Message)
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try

End Sub


Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.SqlConnection1.Open() 'open connection to sql
End Sub

Private Sub Label1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label1.Click

End Sub
End Class

 

[andrea96]

Do you have the option of using a stored procedure for your insert? If you use a stored procedure, it can accept quotes and apostrophes in input parameters.

Andrea

 

[ThatRickGuy]

You could try something like this:

public function StripNonAlphanumericChars(Value as string) as string
  dim OKCharList as string = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890"

  dim c as character
  dim OutputValue as string = string.empty
  for each c in Value
    if OKCharlist.indexof(c) > -1 then
      OutputValue &= c
    end if
  next

  return OutputValue
end function

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.