Problème TableMapping, ColumnName, DataAdapaterUpdate ?

**Gdal** · 21/04/2007, 16h36

Salut,

J'ai une application 3 couches. Dans la couche métier (classe Véhicule), je charge dans un dataset une table.

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
38
39
40
41
42
43
44
45
46
47
Public Function ChargerTs(ByVal objDataSet As DataSet, Optional ByVal strFiltreTrie As String = Nothing) As DataTable
 
        Dim objDataTable As DataTable = Nothing
 
        'Try
 
        Dim strRequete As String
        strRequete = "SELECT Tbl_Vehicule.* " & _
                    "FROM Tbl_Vehicule " & _
                    strFiltreTrie & ";"
 
        _clClassDb.GetDataSet(objDataSet, "TblVehicule", strRequete)
 
        objDataTable = objDataSet.Tables("TblVehicule")
 
        ' Renommer les colonnes du DataTables
        'objDataTable.Columns("ID_Vehicule").ColumnName = "ID_Vehicule"
        'objDataTable.Columns("Immatriculation").ColumnName = "N° Immatriculation"
        'objDataTable.Columns("DateAcquisition").ColumnName = "Date Acquisition"
        'objDataTable.Columns("DateMiseEnCirculation").ColumnName = "1ère Mise en Circulation"
        'objDataTable.Columns("TypeVehicule").ColumnName = "Type"
        'objDataTable.Columns("NSerie").ColumnName = "N° Série"
        'objDataTable.Columns("NMoteur").ColumnName = "N° Moteur"
        'objDataTable.Columns("Puissance").ColumnName = "Puissance"
        'objDataTable.Columns("Kilometrage").ColumnName = "Kilométrage"
        'objDataTable.Columns("NbrePlace").ColumnName = "Nombre de place"
        'objDataTable.Columns("PoidsTC").ColumnName = "Poids T.C."
        'objDataTable.Columns("PoidsAV").ColumnName = "Poids A.V."
        'objDataTable.Columns("PoidsTR").ColumnName = "Poids T.R."
        'objDataTable.Columns("ID_TblRepertoire").ColumnName = "Propiétaire"
        'objDataTable.Columns("ID_TblMarque").ColumnName = "Marque"
        'objDataTable.Columns("ID_TblModele").ColumnName = "Modèle"
        'objDataTable.Columns("ID_TblGenre").ColumnName = "Genre"
        'objDataTable.Columns("ID_TblEnergie").ColumnName = "Energie"
        'objDataTable.Columns("ID_TblChauffeur").ColumnName = "Chauffeur"
        'objDataTable.Columns("ID_TblAssurance").ColumnName = "Assurance"
        'objDataTable.Columns("ID_TblAgence").ColumnName = "Agence"
 
        'End If
 
        'Catch ex As Exception
        '    MessageBox.Show(ex.Message, ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Stop)
        'End Try
 
        Return objDataTable
 
    End Function

J'ai volontairement mis les lignes ColumnName en commentaire, je vais y revenir dans le descriptif de mon soucis....

Ensuite, j'ai une fonction qui met à jour la table. (Lignes créées par le DataSetx.Designer.vb, que j'ai récupéré

)

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
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
 Public Sub UpdateDataSet(ByVal objDataSet As DataSet)
 
        Try
 
            Dim objDataAdapter = New OleDb.OleDbDataAdapter
            Dim tableMapping As Common.DataTableMapping = New Common.DataTableMapping
            tableMapping.SourceTable = "Tbl_Vehicule"
            tableMapping.DataSetTable = "TblVehicule"
            tableMapping.ColumnMappings.Add("ID_Vehicule", "ID_Vehicule")
            tableMapping.ColumnMappings.Add("Immatriculation", "N° Immatriculation")
            tableMapping.ColumnMappings.Add("DateAcquisition", "Date Acquisition")
            tableMapping.ColumnMappings.Add("DateMiseEnCirculation", "1ère Mise en Circulation")
            tableMapping.ColumnMappings.Add("TypeVehicule", "Type")
            tableMapping.ColumnMappings.Add("NSerie", "N° Série")
            tableMapping.ColumnMappings.Add("NMoteur", "N° Moteur")
            tableMapping.ColumnMappings.Add("Puissance", "Puissance")
            tableMapping.ColumnMappings.Add("Kilometrage", "Kilométrage")
            tableMapping.ColumnMappings.Add("NbrePlace", "Nombre de place")
            tableMapping.ColumnMappings.Add("PoidsTC", "Poids T.C.")
            tableMapping.ColumnMappings.Add("PoidsAV", "Poids A.V.")
            tableMapping.ColumnMappings.Add("PoidsTR", "Poids T.R.")
            tableMapping.ColumnMappings.Add("ID_TblRepertoire", "ID_TblRepertoire")
            tableMapping.ColumnMappings.Add("ID_TblMarque", "ID_TblMarque")
            tableMapping.ColumnMappings.Add("ID_TblModele", "ID_TblModele")
            tableMapping.ColumnMappings.Add("ID_TblGenre", "ID_TblGenre")
            tableMapping.ColumnMappings.Add("ID_TblEnergie", "ID_TblEnergie")
            tableMapping.ColumnMappings.Add("ID_TblChauffeur", "ID_TblChauffeur")
            tableMapping.ColumnMappings.Add("ID_TblAssurance", "ID_TblAssurance")
            tableMapping.ColumnMappings.Add("ID_TblAgence", "ID_TblAgence")
            objDataAdapter.TableMappings.Add(tableMapping)
 
            ' DELETECOMMAND
            objDataAdapter.DeleteCommand = New OleDb.OleDbCommand
            'objDataAdapter.DeleteCommand.Connection = objConnexion
            objDataAdapter.DeleteCommand.CommandText = "DELETE FROM Tbl_Vehicule" & _
                                                       " WHERE ((ID_Vehicule = ?)" & _
                                                       " AND ((? = 1 AND Immatriculation IS NULL) OR (Immatriculation = ?))" & _
                                                       " AND ((? = 1 AND DateAcquisition IS NULL) OR (DateAcquisition = ?))" & _
                                                       " AND ((? = 1 AND DateMiseEnCirculation IS NULL) OR (DateMiseEnCirculation = ?))" & _
                                                       " AND ((? = 1 AND TypeVehicule IS NULL) OR (TypeVehicule = ?))" & _
                                                       " AND ((? = 1 AND NSerie IS NULL) OR (NSerie = ?))" & _
                                                       " AND ((? = 1 AND NMoteur IS NULL) OR (NMoteur = ?))" & _
                                                       " AND ((? = 1 AND Puissance IS NULL) OR (Puissance = ?))" & _
                                                       " AND ((? = 1 AND Kilometrage IS NULL) OR (Kilometrage = ?))" & _
                                                       " AND ((? = 1 AND NbrePlace IS NULL) OR (NbrePlace = ?))" & _
                                                       " AND ((? = 1 AND PoidsTC IS NULL) OR (PoidsTC = ?))" & _
                                                       " AND ((? = 1 AND PoidsAV IS NULL) OR (PoidsAV = ?))" & _
                                                       " AND ((? = 1 AND PoidsTR IS NULL) OR (PoidsTR = ?))" & _
                                                       " AND ((? = 1 AND ID_TblRepertoire IS NULL) OR (ID_TblRepertoire = ?))" & _
                                                       " AND ((? = 1 AND ID_TblMarque IS NULL) OR (ID_TblMarque = ?))" & _
                                                       " AND ((? = 1 AND ID_TblModele IS NULL) OR (ID_TblModele = ?))" & _
                                                       " AND ((? = 1 AND ID_TblGenre IS NULL) OR (ID_TblGenre = ?))" & _
                                                       " AND ((? = 1 AND ID_TblEnergie IS NULL) OR (ID_TblEnergie = ?))" & _
                                                       " AND ((? = 1 AND ID_TblChauffeur IS NULL) OR (ID_TblChauffeur = ?))" & _
                                                       " AND ((? = 1 AND ID_TblAssurance IS NULL) OR (ID_TblAssurance = ?))" & _
                                                       " AND ((? = 1 AND ID_TblAgence IS NULL) OR (ID_TblAgence = ?)))"
            objDataAdapter.DeleteCommand.CommandType = CommandType.Text
            objDataAdapter.DeleteCommand.Parameters.Add(New OleDb.OleDbParameter("Original_ID_Vehicule", OleDb.OleDbType.[Integer], 0, ParameterDirection.Input, CType(0, Byte), CType(0, Byte), "ID_Vehicule", DataRowVersion.Original, False, Nothing))
            objDataAdapter.DeleteCommand.Parameters.Add(New OleDb.OleDbParameter("IsNull_Immatriculation", OleDb.OleDbType.[Integer], 0, ParameterDirection.Input, CType(0, Byte), CType(0, Byte), "Immatriculation", DataRowVersion.Original, True, Nothing))
            objDataAdapter.DeleteCommand.Parameters.Add(New OleDb.OleDbParameter("Original_Immatriculation", OleDb.OleDbType.VarWChar, 0, ParameterDirection.Input, CType(0, Byte), CType(0, Byte), "Immatriculation", DataRowVersion.Original, False, Nothing))
            objDataAdapter.DeleteCommand.Parameters.Add(New OleDb.OleDbParameter("IsNull_DateAcquisition", OleDb.OleDbType.[Integer], 0, ParameterDirection.Input, CType(0, Byte), CType(0, Byte), "DateAcquisition", DataRowVersion.Original, True, Nothing))
            objDataAdapter.DeleteCommand.Parameters.Add(New OleDb.OleDbParameter("Original_DateAcquisition", OleDb.OleDbType.[Date], 0, ParameterDirection.Input, CType(0, Byte), CType(0, Byte), "DateAcquisition", DataRowVersion.Original, False, Nothing))
            objDataAdapter.DeleteCommand.Parameters.Add(New OleDb.OleDbParameter("IsNull_DateMiseEnCirculation", OleDb.OleDbType.[Integer], 0, ParameterDirection.Input, CType(0, Byte), CType(0, Byte), "DateMiseEnCirculation", DataRowVersion.Original, True, Nothing))
            objDataAdapter.DeleteCommand.Parameters.Add(New OleDb.OleDbParameter("Original_DateMiseEnCirculation", OleDb.OleDbType.[Date], 0, ParameterDirection.Input, CType(0, Byte), CType(0, Byte), "DateMiseEnCirculation", DataRowVersion.Original, False, Nothing))
            Etc ...
 
            ' INSERTCOMMAND
            objDataAdapter.InsertCommand = New OleDb.OleDbCommand
            'objDataAdapter.InsertCommand.Connection = objConnexion
            objDataAdapter.InsertCommand.CommandText = "INSERT INTO Tbl_Vehicule (Immatriculation, DateAcquisition, DateMiseEnCirculation," & _
                                                       " TypeVehicule, NSerie, NMoteur, Puissance, Kilometrage, NbrePlace, PoidsTC, PoidsAV," & _
                                                       " PoidsTR, ID_TblRepertoire, ID_TblMarque, ID_TblModele, ID_TblGenre, ID_TblEnergie," & _
                                                       " ID_TblChauffeur, ID_TblAssurance, ID_TblAgence)" & _
                                                       " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
            objDataAdapter.InsertCommand.CommandType = CommandType.Text
            objDataAdapter.InsertCommand.Parameters.Add(New OleDb.OleDbParameter("Immatriculation", OleDb.OleDbType.VarWChar, 0, ParameterDirection.Input, CType(0, Byte), CType(0, Byte), "Immatriculation", DataRowVersion.Current, False, Nothing))
            objDataAdapter.InsertCommand.Parameters.Add(New OleDb.OleDbParameter("DateAcquisition", OleDb.OleDbType.[Date], 0, ParameterDirection.Input, CType(0, Byte), CType(0, Byte), "DateAcquisition", DataRowVersion.Current, False, Nothing))
            objDataAdapter.InsertCommand.Parameters.Add(New OleDb.OleDbParameter("DateMiseEnCirculation", OleDb.OleDbType.[Date], 0, ParameterDirection.Input, CType(0, Byte), CType(0, Byte), "DateMiseEnCirculation", DataRowVersion.Current, False, Nothing))
            Etc ...
 
            ' UPDATECOMMAND
            objDataAdapter.UpdateCommand = New OleDb.OleDbCommand
            'objDataAdapter.UpdateCommand.Connection = objConnexion
            objDataAdapter.UpdateCommand.CommandText = "UPDATE Tbl_Vehicule SET Immatriculation = ?, DateAcquisition = ?," & _
                                                       " DateMiseEnCirculation = ?, TypeVehicule = ?, NSerie = ?, NMoteur = ?, Puissance = ?," & _
                                                       " Kilometrage = ?, NbrePlace = ?, PoidsTC = ?, PoidsAV = ?, PoidsTR = ?," & _
                                                       " ID_TblRepertoire = ?, ID_TblMarque = ?, ID_TblModele = ?, ID_TblGenre = ?," & _
                                                       " ID_TblEnergie = ?, ID_TblChauffeur = ?, ID_TblAssurance = ?, ID_TblAgence = ?" & _
                                                       " WHERE ((ID_Vehicule = ?)" & _
                                                       " AND ((? = 1 AND Immatriculation IS NULL) OR (Immatriculation = ?))" & _
                                                       " AND ((? = 1 AND DateAcquisition IS NULL) OR (DateAcquisition = ?))" & _
                                                       " AND ((? = 1 AND DateMiseEnCirculation IS NULL) OR (DateMiseEnCirculation = ?))" & _
                                                       " AND ((? = 1 AND TypeVehicule IS NULL) OR (TypeVehicule = ?))" & _
                                                       " AND ((? = 1 AND NSerie IS NULL) OR (NSerie = ?))" & _
                                                       " AND ((? = 1 AND NMoteur IS NULL) OR (NMoteur = ?))" & _
                                                       " AND ((? = 1 AND Puissance IS NULL) OR (Puissance = ?))" & _
                                                       " AND ((? = 1 AND Kilometrage IS NULL) OR (Kilometrage = ?))" & _
                                                       " AND ((? = 1 AND NbrePlace IS NULL) OR (NbrePlace = ?))" & _
                                                       " AND ((? = 1 AND PoidsTC IS NULL) OR (PoidsTC = ?))" & _
                                                       " AND ((? = 1 AND PoidsAV IS NULL) OR (PoidsAV = ?))" & _
                                                       " AND ((? = 1 AND PoidsTR IS NULL) OR (PoidsTR = ?))" & _
                                                       " AND ((? = 1 AND ID_TblRepertoire IS NULL) OR (ID_TblRepertoire = ?))" & _
                                                       " AND ((? = 1 AND ID_TblMarque IS NULL) OR (ID_TblMarque = ?))" & _
                                                       " AND ((? = 1 AND ID_TblModele IS NULL) OR (ID_TblModele = ?))" & _
                                                       " AND ((? = 1 AND ID_TblGenre IS NULL) OR (ID_TblGenre = ?))" & _
                                                       " AND ((? = 1 AND ID_TblEnergie IS NULL) OR (ID_TblEnergie = ?))" & _
                                                       " AND ((? = 1 AND ID_TblChauffeur IS NULL) OR (ID_TblChauffeur = ?))" & _
                                                       " AND ((? = 1 AND ID_TblAssurance IS NULL) OR (ID_TblAssurance = ?))" & _
                                                       " AND ((? = 1 AND ID_TblAgence IS NULL) OR (ID_TblAgence = ?)))"
            objDataAdapter.UpdateCommand.CommandType = CommandType.Text
            objDataAdapter.UpdateCommand.Parameters.Add(New OleDb.OleDbParameter("Immatriculation", OleDb.OleDbType.VarWChar, 0, ParameterDirection.Input, CType(0, Byte), CType(0, Byte), "Immatriculation", DataRowVersion.Current, False, Nothing))
            objDataAdapter.UpdateCommand.Parameters.Add(New OleDb.OleDbParameter("DateAcquisition", OleDb.OleDbType.[Date], 0, ParameterDirection.Input, CType(0, Byte), CType(0, Byte), "DateAcquisition", DataRowVersion.Current, False, Nothing))
            objDataAdapter.UpdateCommand.Parameters.Add(New OleDb.OleDbParameter("DateMiseEnCirculation", OleDb.OleDbType.[Date], 0, ParameterDirection.Input, CType(0, Byte), CType(0, Byte), "DateMiseEnCirculation", DataRowVersion.Current, False, Nothing))
            Etc ...
 
            ' UPDATE du dataset
            _clClassDb.UpdateDataSet(objDataAdapter, objDataSet, "TblVehicule")
 
        Catch ex As Exception
            MessageBox.Show(ex.Message, ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Stop)
 
        End Try
 
    End Sub

Mon soucis.
Tout foncionne bien avec ces deux codes dans l'état actuel.
Par contre, dans le premier morceau, si je retire le commentaire des lignes ColumnName, j'obtiens l'erreur suivante:

Le paramètre ?_1 n'a pas de valeur par défaut

Pourquoi? Et comment résoudre mon problème?

Gwendal

**bidou** · 21/04/2007, 17h20

Je suppose que le paramètre n'arrive plus à mapper la colonne si tu changes son nom

**Gdal** · 21/04/2007, 19h58

re,

Envoyé par Bidou

Je suppose que le paramètre n'arrive plus à mapper la colonne si tu changes son nom

Je ne vois pas trop ce que tu veux dire....

Lorsque je charge le DataTable dans le DataSet sans modifier le nom des colonnes, je n'ai aucun problèmes.
Par contre, lorsque je charge le DataTable dans le DataSet en modifiant le nom des colonnes, alors j'obtiens le message indiqué plus haut....

Dans ma base (Access), les tables sont nommées ainsi Tbl_NomDeLaTable.
Dans le DataSet, les DataTables sont nommées ainsi TblNomDeLaTable.

Voilà

Gwendal

**bidou** · 22/04/2007, 08h03

tes paramètres sont définis par :
objDataAdapter.UpdateCommand.Parameters.Add(New OleDb.OleDbParameter("Immatriculation", OleDb.OleDbType.VarWChar, 0, ParameterDirection.Input, CType(0, Byte), CType(0, Byte), "Immatriculation", DataRowVersion.Current, False, Nothing))

Si tu modifie le nom local de la colonne de mappage, tu dois aussi le modifier dans la définition du paramètre