Insert Statement

[byeadon]

I have been trying to come up with some sort of SQL statement or anthing for that matters that will allow me to type ANYTHING into a field in my Visual Basic Program and save it into an access 2000 database. I able to account for a singel quote but commas and others keep giving me the missing operator in query expression, I anyone knows how to account for anything entered into a field i would be greatly appericate it, Iam using VB6, Access 2000 and ADODB Connections.

Brad

[Magiaus]

I use this in asp pages. i don't see why it can't be used in a vb app.

Public Function HTMLDecodeChar( _
    ByVal lszEncodedChr As String _
)   '*****************************************************************************************
        Dim sRet As String
        
        sRet = Chr(Mid$(lszEncodedChr, 3, Len(lszEncodedChr)))
        HTMLDecodeChar = sRet
    '*****************************************************************************************
End Function
 
Public Function HTMLDecodeString( _
    ByVal lszEncoded As String, _
    ByRef lszEncodedChars, _
    ByVal cEncodedChars As Long _
)   '*****************************************************************************************
    Dim sRet As String, sReplace As String, i As Integer
    
    For i = 0 To cEncodedChars
        sReplace = HTMLDecodeChar(lszEncodedChars(i))
        If i = 0 Then
            sRet = Replace(lszEncoded, sReplace, lszEncodedChars(i))
        Else
            sRet = Replace(sRet, sReplace, lszEncodedChars(i))
        End If
    Next i  ' i = 0 To lcEncodedChars
    
    HTMLDecodeString = sRet
    '*****************************************************************************************
End Function
 
Public Function HTMLEncodeChar( _
    ByVal lszEncodeChr As String _
)   '*****************************************************************************************
        Dim sRet As String
        sRet = "&#" & Asc(lszEncodeChr)
        If Len(sRet) = 4 Then sRet = Mid$(sRet, 1, 2) & "0" & Mid$(sRet, 3, 4)
        HTMLEncodeChar = sRet
    '*****************************************************************************************
End Function
 
Public Function HTMLEncodeString( _
    ByVal lszEncode As String, _
    ByRef lszEncodeChars, _
    ByVal cEncodeChars As Long _
)   '*****************************************************************************************
    Dim sRet As String, sReplace As String, i As Integer
    
    For i = 0 To cEncodeChars
        sReplace = HTMLEncodeChar(lszEncodeChars(i))
        If i = 0 Then
            sRet = Replace(lszEncode, lszEncodeChars(i), HTMLEncodeChar(lszEncodeChars(i)))
        Else
            sRet = Replace(sRet, lszEncodeChars(i), sReplace)
        End If
    Next i  ' i = 0 To lszEncodeChars
    
    HTMLEncodeString = sRet
    '*****************************************************************************************
End Function

all you need to do to make use of it is loop through the contents of the field before insert and incode those nasty charecters and do the same upon redisply except decode

there is a better way though its called a stored proc i will make you an example and post it

[Magiaus]

i forgot i already made the looping code you can use HTMLEncodeString like so

sToInsert = HTMLEncodeString(theString,Array("'","&"),Len(Array("'","&")))

[Magiaus]

the real trick is knowing how to change the query type and setup the SQL in Access but it is in the help file.

[Magiaus]

that trick is good for almost all types of SQL and it can save you from all those repeated SQL statements in your code and from so other nasty things that can come up

and this is the vb.net forum not the db forum

[Magiaus]

here

look in db no vb changes