[AccessD] Function for "scrubbing" strings before inserting t hem into SQLstatements?

I use this:

'add single quotes to beginning/end of a string
Public Function AddQuote(vData As Variant) As String

   If IsNull(vData) Then
      AddQuote = "NULL"
      Else
         AddQuote = "'" & SQLText(CStr(vData)) & "'"
      End If

End Function


Public Function SQLText(lstrIn As String) As String
Dim intStringLocation   As Integer
Dim strLeft             As String
Dim strRight            As String

intStringLocation = 0

intStringLocation = InStr(intStringLocation + 1, lstrIn, "'")

Do While intStringLocation > 0
    strLeft = Left(lstrIn, intStringLocation - 1)
    strRight = Mid(lstrIn, intStringLocation + 1)
    lstrIn = strLeft & "''" & strRight
    intStringLocation = intStringLocation + 2
    intStringLocation = InStr(intStringLocation, lstrIn, "'")
    Loop

SQLText = lstrIn

End Function


--------------------------------------------------
call with:

strSQL = "INSERT INTO tblEmployeeMonthlyTask ([TaskDescription]) VALUES "
strSQL = strSQL & "(" & AddQuote(Request.Form("txtTaskDescription")) & ")"


I make no claims for this code as it is kind of messy and I did not write
all of it.  LOL.  But it does work.  It can be optimized somewhat also.

HTH,
Bobby

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Michael Tucker
Sent: Friday, May 02, 2003 11:47 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] Function for "scrubbing" strings before inserting
them into SQLstatements?


Hi everyone,

I'm sure everybody has faced this situation, but I can't seem to figure out
an elegant solution.  I have a webpage interface to an Access database where
users can enter notes on various things.  Of course the user may enter
apostrophes or quotation marks in these notes, but of course that sends my
SQL statement into a tizzy.  A sample of my SQL statement...

strSQL = "INSERT INTO tblEmployeeMonthlyTask ([TaskDescription]) VALUES "
strSQL = strSQL & "('" & Request.Form("txtTaskDescription") & "')"

Is there a function that will convert my string so that it usable inside of
this SQL statement even if it contains apostrophes and quotes?

Thank you.

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com