Discussion :

[LaventureEstivene]

Je souhaite renvoyé des données issue d'un tableau Excel vers une base SQL, mais le code SQL que j'utilise ne fonctionne pas et affiche un message d'erreur sur l'incompatibilité de type pour ma dernier requête.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
Sub test()
 
Dim I As Integer
 
    Const Server = "LocalHost", Port = "3306", User = "root", Password = "": Dim DataBase
With CreateObject("ADODB.Connection")
        .Open "Driver={MySQL ODBC 8.0 Unicode Driver};Server=" & Server & ";Port=" & Port & ";Database=" & DataBase & ";User=" & User & ";Password=" & Password & ";"
            requete = "CREATE DATABASE IF NOT EXISTS `vbamysql` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;"
            .Execute requete
        .Close
        DataBase = "vbamysql"
        .Open "Driver={MySQL ODBC 8.0 Unicode Driver};Server=" & Server & ";Port=" & Port & ";Database=" & DataBase & ";User=" & User & ";Password=" & Password & ";"
             requete = "CREATE TABLE IF NOT EXISTS  `voitures`" & vbCrLf & _
                       "(`id` INTEGER NOT NULL auto_increment,`marque` VARCHAR(25) NOT NULL,`modele` VARCHAR(25) NOT NULL ,`cv` INTEGER," & vbCrLf & _
                       "PRIMARY KEY (`id`),UNIQUE (`modele`)) ENGINE = InnoDB ;"
        .Execute requete
        .Close
 
         For I = 1 To 6
            a0 = ThisWorkbook.Sheets("traitement").Cells(I, 1).Value
            a1 = ThisWorkbook.Sheets("traitement").Cells(I, 2).Value
            a2 = ThisWorkbook.Sheets("traitement").Cells(I, 3).Value
            a3 = ThisWorkbook.Sheets("traitement").Cells(I, 4).Value
 
        DataBase = "vbamysql1"
        .Open "Driver={MySQL ODBC 8.0 Unicode Driver};Server=" & Server & ";Port=" & Port & ";Database=" & DataBase & ";User=" & User & ";Password=" & Password & ";"
        requete = "INSERT INTO voitures(id, marque, modele, cv) VALUES(" & _
                    a0 & ",'" & _
                    a1 & "', '" & _
                    a2 & "', " & _
                    a3 & ")"
        .Execute requete
        Next
 
        .Close
End With
End Sub

[Thumb down]

Bonjour,
Es tu certain que CV [a3] soit du numérique dans Excel ?