INSERT QUERY help!!!

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
C:Documents and SettingswendysiaDesktoplogo.jpg


Is there any problem with my Insert query?

VB Code:

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

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

How should I use the Chr(47)??

The update query is

VB Code:

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

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

Try this :

VB Code:

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.

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 :

VB Code:

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.

It does not work...... :cry: :cry: :cry:

Do you guys have any other option?

Did you try mine?

Quote:

---

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

Do you guys have any other option?

---

Show the code(s) you tried

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,"^","\")

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.

This works for me
:)

VB Code:

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