0

Good morning guys, I have a little problem with my code ... I wish my script update my "Mysql" database with new values only, without duplication the values already present in the database.

This is'the error that I get:

error message

Where am I wrong? 

  Sub conect()
    Dim cn As Object
    Dim rs As New ADODB.Recordset
    Dim sCon As String
    Dim Count As String
    Dim nMitt As String
    Dim eMitt As String
    Dim oGget As String
    Dim mEss As String
    Dim dEst As String
    Dim tIme As String
    Dim sSQL As String
    Dim oNameSpace As Outlook.NameSpace
    Dim oItem As Outlook.MailItem
    Dim oFolder As Outlook.MAPIFolder

    Set cn = CreateObject("ADODB.Connection")

    sCon = "Driver={MySQL ODBC 5.1 Driver};Server=192.168.27.249;Database=PROTOCOLLI;" _
    & "User=Riscoat;Password=Riscoat;Option=3;"

    cn.Open sCon

    Set oNameSpace = Application.GetNamespace("MAPI")
    Set oFolder = GetNamespace("MAPI").Folders("riscoat").Folders("1.PROTOCOLLI")
     rs.Open "SELECT PROT FROM RISCOAT ORDER BY PROT DESC LIMIT 1", sCon
     Count = rs.Fields("PROT")

    For Each oItem In oFolder.Items
        Count = Count + 1
        nMitt = ReplaceCharsForFileName(oItem.SenderName, " ")
        eMitt = ReplaceCharsForFileName(oItem.SenderEmailAddress, " ")
        oGget = ReplaceCharsForFileName(oItem.Subject, " ")
        mEss = ReplaceCharsForFileName(oItem.Body, " ")
        dEst = ReplaceCharsForFileName(oItem.To, " ")
        tIme = oItem.ReceivedTime

        cn.Execute = "IF NOT EXISTS (SELECT * FROM RISCOAT WHERE MESSAGGIO = '" & mEss & "' and DATA = '" & tIme & "') & INSERT INTO RISCOAT (PROT, NOME_MITT, MITTENTE, OGGETTO, MESSAGGIO, DESTINATARIO, DATA) VALUES ('" & Count & "','" & nMitt & "', '" & eMitt & "', '" & oGget & "', '" & mEss & "', '" & dEst & "', '" & tIme & "')"

                          -------only try ----------        
        ' cn.Execute = "INSERT INTO RISCOAT (PROT, NOME_MITT, MITTENTE, OGGETTO, MESSAGGIO, DESTINATARIO, DATA) VALUES ('" & Count & "','" & nMitt & "', '" & eMitt & "', '" & oGget & "', '" & mEss & "', '" & dEst & "', '" & tIme & "')ON DUPLICATE KEY UPDATE PROT = '" & Count & "', NOME_MITT = '" & nMitt & "', MITTENTE = '" & eMitt & "', OGGETTO = '" & oGget & "', MESSAGGIO = '" & mEss & "', DESTINATARIO = '" & dEst & "', DATA = '" & tIme & "'"
        ' cn.Execute "IF EXISTS (SELECT * FROM RISCOAT WHERE MESSAGGIO = '" & mEss & "' and DATA = '" & tIme & "') " & _
             "THEN UPDATE RISCOAT SET WHERE MESSAGGIO = '" & mEss & "' and DATA = '" & tIme & "' " & _
             "ELSE INSERT INTO RISCOAT (PROT, NOME_MITT, MITTENTE, OGGETTO, MESSAGGIO, DESTINATARIO, DATA) " & _
             "VALUES ('" & Count & "','" & nMitt & "', '" & eMitt & "', '" & oGget & "', '" & mEss & "', '" & dEst & "', '" & tIme & "')"

    Next

    End Sub

    Private Function ReplaceCharsForFileName(ByVal sName As String, sChr As String) As String
     sName = Replace(sName, "'", sChr)
     sName = Replace(sName, "*", sChr)
     sName = Replace(sName, "/", sChr)
     sName = Replace(sName, "\", sChr)
     '  sName = Replace(sName, ":", sChr)
     '  sName = Replace(sName, "?", sChr)
     sName = Replace(sName, Chr(34), sChr)
     sName = Replace(sName, "<", sChr)
     sName = Replace(sName, ">", sChr)
     sName = Replace(sName, "|", sChr)
     ReplaceCharsForFileName = sName
    End Function
Dmitry Streblechenko
  • 56,873
  • 3
  • 44
  • 75
Rufi0
  • 37
  • 9
  • If `Riscoat` is your *actual* username+password for your database, you might want to custom-flag your post so a moderator can remove these credentials from the post's content and edit history. – Mathieu Guindon Dec 13 '16 at 14:52
  • 1
    @Mat'sMug it's not my actual user and password, thank you. – Rufi0 Dec 13 '16 at 14:56
  • @Shadow I had already seen that post, but I can't use the solution proposed with vba or better don't know how to use ... – Rufi0 Dec 13 '16 at 15:00
  • It's an sql solution. Your question does not really have anything to do with vba or outlook. The duplicate topic outlines how the sql statement should look like. – Shadow Dec 13 '16 at 15:16
  • @Shadow I saw that post is similar but the post you linked is the update of unique values in two SQL tables. The difficulty I have found is that I have to compare the outlook array values with the table sql value and update the table only if these are not present. I tried to change the code in the post you indicated but I always make the same mistake ... – Rufi0 Dec 13 '16 at 15:25
  • cn.Execute = "INSERT INTO RISCOAT (PROT, NOME_MITT, MITTENTE, OGGETTO, MESSAGGIO, DESTINATARIO, DATA) VALUES ('" & Count & "','" & nMitt & "', '" & eMitt & "', '" & oGget & "', '" & mEss & "', '" & dEst & "', '" & tIme & "') AS tmp WHERE NOT EXISTS (SELECT * FROM RISCOAT WHERE MESSAGGIO = '" & mEss & "' and DATA = '" & tIme & "') LIMIT 1" – Rufi0 Dec 13 '16 at 15:25

0 Answers0