Program against garbage charaters?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can I, and then how do I program in such a manner as to prevent the
introduction of any garbage (ESC, etc.) characters from the user interface to
be accepted by the db? I have seen ASCII? characters ... squares, fields that
look blank but are not, etc. Please help.
 
Andy, I'm not sure there is a single answer to your question. You might need
to figure out how the bad characters are getting into the database.

Examples:
a) Keyboard
=========
If there are specific characters you want to destroy when the user types
them, you could set each form's KeyPreview property to Yes, and in the
*form's* KeyPress, call the routine that kills those characters, e.g.:
Call NoDblQuote(KeyAscii)
The example (below) reassigns the double-quote character and the pipe
character. To destroy a keystroke, set KeyAscii to zero.

b) Import
======
Very often, these characters come in as the result of importing data that
contains line feeds (without Carriage Return), tabs, or other special
characters. To handle this, you would need to massage the input with
Replace() to handle each case.

c) Pasting
=======
Similar to import, a user can paste in bad characters. IME, this is not very
common, but there's not a simple way to handle it as it can happen anywhere.

d) Bad code
=========
Examples:
- API calls that fail to remove the terminating Null char from the string.
- Parsing words from a string/field, without trimming the leading/trailing
spaces.
- Character conversions.
- Conversions from other languages/code pages.
Can't blame the user for these ones. :-)

e) Access bugs
===========
Mostly it's a matter of being aware of these bugs, e.g.:
Concatenated fields yield garbage in recordset
at:
http://allenbrowne.com/bug-16.html


Public Sub NoDblQuote(KeyAscii As Integer)
On Error GoTo Err_NoDblQuote
'Purpose: Replace the double quote mark with a single quote mark.
'Usage: Set a form's KeyPreview to True. In form's KeyPress event:
' Call NoDblQuote(KeyAscii)
Select Case KeyAscii
Case 34
KeyAscii = 39
Case 124
KeyAscii = 92
End Select

Exit_NoDblQuote:
Exit Sub

Err_NoDblQuote:
MsgBox Err.Description
Resume Exit_NoDblQuote
End Sub
 
Can I, and then how do I program in such a manner as to prevent the
introduction of any garbage (ESC, etc.) characters from the user interface to
be accepted by the db? I have seen ASCII? characters ... squares, fields that
look blank but are not, etc. Please help.
Click to expand...

Hi Andy,

I don't know exactly how you intend to implement your idea but, I have an
application that pulls data from Word forms and the yo-yos that fill in these
forms do things that really leave me scratching my head and pondering the future
of our species. Anyway, below are a couple of functions I use to scrub input
strings. If you understand what they do you should be able to alter/adapt them
to work for you. They work fine in my application but I make no guarantees.
Watch out for word wrap.

Have fun,
RD

<code>
Function fExtractStr(ByVal sStringIn As String) As String
Dim lLen As Long, sStringOut As String
Dim i As Long, sTemp As String

lLen = Len(sStringIn)
sStringOut = ""
For i = 1 To lLen
sTemp = Left$(sStringIn, 1)
sStringIn = Right$(sStringIn, lLen - i)
' ASCII characters 65-90 are upper case, 97-122 are lower case and 32 =
Space
If (Asc(sTemp) >= 65 And Asc(sTemp) <= 90) Or _
(Asc(sTemp) >= 97 And Asc(sTemp) <= 122) Or _
(Asc(sTemp)) = 32 Then
sStringOut = sStringOut & sTemp
End If
Next i
fExtractStr = Trim(sStringOut)
fExtractStr = Left(fExtractStr, 50)

End Function

Function fExtractNum(ByVal sStringIn As String) As Currency
Dim lLen As Long, sStringOut As String
Dim i As Long, sTemp As String

lLen = Len(sStringIn)

sStringOut = ""
For i = 1 To lLen
sTemp = Left$(sStringIn, 1)
sStringIn = Right$(sStringIn, lLen - i)
' ASCII characters 48-57 = numbers 0-9
If Asc(sTemp) >= 48 And Asc(sTemp) <= 57 Then
sStringOut = sStringOut & sTemp
End If
Next i

If IsNumeric(sStringOut) Then
sStringOut = Trim(sStringOut)
sStringOut = Left(sStringOut, 10)
fExtractNum = CCur(Trim(sStringOut))
Else
fExtractNum = 0
End If

End Function

</code>
 
Back
Top