INSERT QUERY help!!!

[wendymelon]

I would like to insert these strings:
C:/Documents and Settings/wendysia/Desktop/logo.jpg

into my database(mysql) with the field type of VarChar(80)

However, when the insert is done. I found out that the "/" character has been eliminated into
Cocuments and SettingswendysiaDesktoplogo.jpg


Is there any problem with my Insert query?

insertPhoto = "UPDATE employee SET photo = '" & Picpth & "' WHERE Emp_ID ='" & ID & "'"

[mendhak]

Have you tried using Chr(47) instead of / ?

[wendymelon]

How should I use the Chr(47)??

The update query is

insertPhoto = "UPDATE employee SET photo = '" + CommonDialog1.FileName  + "' WHERE Emp_ID ='" & ID & "'"

The "/" character is in the string of CommonDialog1.FileName

[plenderj]

Try this :

Dim strPath As String: strPath = CommonDialog1.FileName
Dim strSQLStatement As String
If Not strPath = vbNullString Then
    strPath = Replace(strPath, "\", "\b")
    strSQLStatement = "UPDATE employee SET photo = '" & strPath & "' WHERE Emp_ID ='" & ID & "'"
End If

You need to use an escaped character to use a backslash.

[sahbasita]

Hi, Wendy ...

Try this one. It takes a little bit code on your project. First you must create a validation function in a module. This is the sample :

Public Function pc_ValidationStr(pi_strValue As Variant) As String
Dim StrValidation As String
Dim strValues As String
Dim lngCnt As Long
Dim lngStrLong As Long
On Error GoTo errH
StrValidation = ""
    If TypeName(pi_strValue) = "String" Then
        strValues = Trim(pi_strValue)
        lngStrLong = Len(strValues)
        If lngStrLong > 0 Then
            For lngCnt = 1 To lngStrLong
                If Mid(strValues, lngCnt, 1) = "'" Then
                    If Not Right(StrValidation, 1) = "'" Then
                        StrValidation = StrValidation & Mid(strValues, lngCnt, 1) & "'"
                    End If
                Else
                    StrValidation = StrValidation & Mid(strValues, lngCnt, 1)
                End If
            Next
        End If
    End If
pc_ValidationStr = StrValidation
End Function

After this one you can directly call the function in the query.
i.e :
insertPhoto = "UPDATE employee SET photo = " & pc_ValidationStr(picpth) & " WHERE ......"

Hope it solve your problem.

[wendymelon]

It does not work......

Do you guys have any other option?

[plenderj]

Did you try mine?

[mendhak]

Originally posted by wendymelon
It does not work......

Do you guys have any other option?

Show the code(s) you tried

[Static]

you do realize that your slashes are backwards??

C:/Documents and Settings/wendysia/Desktop/logo.jpg

should be

C:\Documents and Settings\wendysia\Desktop\logo.jpg

but...I believe that has to do with Unix? maybe? is your sql server running on unix?

anyway...

try replacing the \ with a : or a ^ then replace back when you read it back in

picPath = Replace(picPath,"\","^")

then reverse on read

picPath = Replace(picPath,"^","\")

[ahara]

if it is a backslash instead of the forward slashes shown in your example, the escape character is \ single backsash....so you would do \\ for each slash

http://dev.mysql.com/doc/mysql/en/String_syntax.html

It looks like there is no escape character for the forward slash, in which case you would want to swap it out as previously suggested in this thread.

[wendymelon]

This works for me

"UPDATE employee SET photo = 'C:/\Photo/\" & CommonDialog1.FileTitle & "' WHERE Emp_ID ='" & Id & "'"