Sujet Précédent Sujet Suivant

DataAdapter dans DAL, un ou plusieurs des paramètres requis.

Résolu
Gwendal3 Messages postés 60 Date d'inscription samedi 4 mars 2006 Statut Membre Dernière intervention 15 août 2011 - 30 août 2007 à 08:16
Gwendal3 Messages postés 60 Date d'inscription samedi 4 mars 2006 Statut Membre Dernière intervention 15 août 2011 - 19 sept. 2007 à 19:11
Salut,


Voilà j'ai réalisé, avec des codes pris à droite et à gauche, une D.A.L.


Pour des essais, j'ai:

# Une base avec une table Tbl_Energie (ID_Energie (Auto), Energie)

# Un formulaire avec un DataGridview pour afficher, ajouter, modifier, supprimer, la table

# Un bouton charger,pour afficher les données dans le DataGridview

# Un bouton sauver, pour mettre à jour les données


Dans la DAL:
Pour charger la table
<hr size= "2" width="100%" />Public Sub RemplirDataSet(ByVal objDataSet As DataSet, ByVal strNomTable As String, _
                              ByVal strSelect As String, ByVal strInsert As String, _
                              ByVal strUpdate As String, ByVal strDelete As String)

        Dim objConnection As DbConnection = Nothing
        Dim objDataAdapter As OleDb.OleDbDataAdapter = Nothing

        Try

            ' Création de l'objet connexion
            objConnection = GetConnection()

            ' Ouverture de la connexion
            objConnection.Open()

            objDataAdapter = CreateDataAdapter(CommandType.Text, strSelect, strInsert, strUpdate, strDelete)
            objDataAdapter.FillSchema(objDataSet, SchemaType.Mapped, strNomTable)
            objDataAdapter.Fill(objDataSet, strNomTable)

        Finally
            If objConnection.State <> System.Data.ConnectionState.Closed Then
                objConnection.Close()
            End If

        End Try

    End Sub
<hr size="2" width="100%" /> Pour sauver les données <hr size ="2" width= "100%" />Public Sub MAJDataSet(ByVal objDataSet As DataSet, ByVal strNomTable As String, _
                          ByVal strSelect As String, ByVal strInsert As String, _
                          ByVal strUpdate As String, ByVal strDelete As String)

        Dim objConnection As DbConnection = Nothing
        Dim objDataAdapter As OleDb.OleDbDataAdapter = Nothing

        'Try

        objConnection = GetConnection()

        objDataAdapter = CreateDataAdapter(CommandType.Text, strSelect, strInsert, strUpdate, strDelete)

        objConnection.Open()

        ' UPDATE du dataset
        objDataAdapter.Update(objDataSet, strNomTable)

        objConnection.Close()

        'Catch ex As Exception
        'MessageBox.Show(ex.Message, ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Stop)

        'End Try

    End Sub
<hr size="2" width="100%" /> La fonction CreateDataAdapter <hr size ="2" width="100%" />Public Function CreateDataAdapter(ByVal commandType As CommandType, ByVal selectStoredProcedure As String, ByVal insertStoredProcedure As String, _
                                                                        ByVal updateStoredProcedure As String, ByVal deleteStoredProcedure As String) As DbDataAdapter

        'Déclarations
        Dim dbSelectCommand As DbCommand = Nothing
        Dim dbInsertCommand As DbCommand = Nothing
        Dim dbUpdateCommand As DbCommand = Nothing
        Dim dbDeleteCommand As DbCommand = Nothing
        Dim dbDataAdapter As DbDataAdapter = Nothing

        Try

            Dim dbConnection As DbConnection = GetConnection()

            'Paramétrage de la commande de sélection
            dbSelectCommand = _dbProviderFactory.CreateCommand()
            dbSelectCommand.CommandType = commandType
            dbSelectCommand.Connection = dbConnection

            If Not selectStoredProcedure Is Nothing Then
                dbSelectCommand.CommandText = selectStoredProcedure
            Else
                dbSelectCommand.CommandText = ""
            End If

            'Paramétrage de la commande d'insertion
            dbInsertCommand = _dbProviderFactory.CreateCommand()
            dbInsertCommand.CommandType = commandType
            dbInsertCommand.Connection = dbConnection

            If Not insertStoredProcedure Is Nothing Then
                dbInsertCommand.CommandText = insertStoredProcedure
            Else
                dbInsertCommand.CommandText = ""
            End If

            'Paramétrage de la commande de mise à jour
            dbUpdateCommand = _dbProviderFactory.CreateCommand()
            dbUpdateCommand.CommandType = commandType
            dbUpdateCommand.Connection = dbConnection

            If Not updateStoredProcedure Is Nothing Then
                dbUpdateCommand.CommandText = updateStoredProcedure
            Else
                dbUpdateCommand.CommandText = ""
            End If

            'Paramétrage de la commande de suppression
            dbDeleteCommand = _dbProviderFactory.CreateCommand()
            dbDeleteCommand.CommandType = commandType
            dbDeleteCommand.Connection = dbConnection

            If Not deleteStoredProcedure Is Nothing Then
                dbDeleteCommand.CommandText = deleteStoredProcedure
            Else
                dbDeleteCommand.CommandText = ""
            End If

            'Paramétrage de l'adaptateur
            dbDataAdapter = _dbProviderFactory.CreateDataAdapter()
            dbDataAdapter.InsertCommand = dbInsertCommand
            dbDataAdapter.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord
            dbDataAdapter.UpdateCommand = dbUpdateCommand
            dbDataAdapter.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord
            dbDataAdapter.DeleteCommand = dbDeleteCommand
            dbDataAdapter.SelectCommand = dbSelectCommand

        Catch ex As Exception
            MessageBox.Show(ex.Message, ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Stop)

        End Try

        'Retour
        Return dbDataAdapter

    End Function
<hr size="2" width="100%" />Le remplissage de la table dans le dataset fonctionne bien.


J'ajoute une (ou plusieurs) ligne dans le datagridview, et lorsque je clique sur Sauver, j'obtiens cette erreur:
Aucune valeur donnée pour un ou plusieurs des paramètres requis.

sur la ligne
objDataAdapter.Update(objDataSet, strNomTable)


Je vois pas ou sa cloche...


Merci d'avance pour le coup de main


Gdal

1 réponse

Réponse 1 / 1
Gwendal3 Messages postés 60 Date d'inscription samedi 4 mars 2006 Statut Membre Dernière intervention 15 août 2011
19 sept. 2007 à 19:11
Re, Tout le monde.

Voici une DAL que j'ai créé:
<hr size="2" width="100%" />Imports System
Imports System.Configuration            ' Récupérer les éléments contenus dans le fichier de configuration (notamment la chaine de connexion)
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common              ' Récupérer les classes abstraites d'ADO.net
Imports System.Collections
Imports System.String

Public Class ClassDb

#Region " Zone de réflexion "

    Private Sub fctDernierID(ByVal objConnection As DbConnection)

        Dim newID As Integer = 0

        Dim commandText As String
        commandText = "SELECT @@IDENTITY"

        Dim command As DbCommand
        command = CreateDbCommand(commandText, objConnection, CommandType.Text)

        newID = command.ExecuteScalar

        DernierID = newID

    End Sub

#End Region

#Region " Variables Privées "

    Private _DernierID As Integer

#End Region

    Public Property DernierID()
        Get
            Return _DernierID
        End Get
        Set(ByVal value)
            _DernierID = value
        End Set
    End Property

#Region "Propriétés Privées"

    '   "System.Data.Odbc"

    Private _strProviderName As String = "System.Data.OleDb"
    Private _strStringConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                             "Data Source =" & Application.StartupPath & "\Source\Parc Vehicule.mdb;"

    'Private _strStringConnection As ConnectionStringSettings
    ' Objet créant le Command en fonction du fournisseur
    Private _dbProviderFactory As DbProviderFactory

#End Region

#Region "Constructeur de la classe"

    '/ <summary>
    '/ Constructeur de la classe
    '/ </summary>
    Sub New()

        ' Récupération de la classe fabrique suivant le provider
        _dbProviderFactory = DbProviderFactories.GetFactory(_strProviderName)

    End Sub

#End Region

#Region "Gestion de la connexion récupérer depuis un Setting"

    '/ <summary>
    '/ Crée une connexion depuis le fichier de configuration
    '/ </summary>
    '/ <returns>Objet implementant DbConnection</returns>
    Public Function GetConnection() As DbConnection

        Dim objConnection As DbConnection
        objConnection = Nothing

        Try

            ' Création de la connection
            objConnection = _dbProviderFactory.CreateConnection()

            ' Affectation de la chaine de connexion
            objConnection.ConnectionString = _strStringConnection

        Catch ex As Exception
            MessageBox.Show(ex.Message, ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Stop)

        End Try

        ' retour de la connexion
        Return objConnection

    End Function

#End Region

#Region "Création d'un dbCommand"

    '/ <summary>
    '/ Créer un objet Command
    '/ </summary>
    ' TODO_1: Gestion des erreurs
    Public Function CreateDbCommand(ByVal commandText As String, ByVal connection As DbConnection, ByVal commandType As CommandType) As DbCommand

        Dim objCommand As DbCommand
        objCommand = Nothing

        Try

            objCommand = _dbProviderFactory.CreateCommand()
            objCommand.CommandType = commandType
            objCommand.CommandText = commandText
            objCommand.Connection = connection

        Catch ex As Exception
            MessageBox.Show(ex.Message, ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Stop)

        End Try

        Return objCommand

    End Function

    '/ <summary>
    '/ Créer un objet Command : Surcharge
    '/ </summary>
    Public Function CreateDbCommand(ByVal commandText As String, ByVal commandType As CommandType) As DbCommand

        Dim objCommand As DbCommand
        objCommand = Nothing

        Try

            objCommand = _dbProviderFactory.CreateCommand()
            objCommand.CommandType = commandType
            objCommand.CommandText = commandText
            objCommand.Connection = GetConnection()

        Catch ex As Exception
            MessageBox.Show(ex.Message, ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Stop)

        End Try

        Return objCommand

    End Function

#End Region

#Region "Récupération DataReader"

    '/ <summary>
    '/ Récupère un jeu d'enregistrement d'une base de donnée depuis un order T-SQL
    '/ </summary>
    Public Function GetReader(ByVal commandText As String) As DbDataReader

        Dim objDataReader As DbDataReader
        objDataReader = Nothing

        Dim objConnection As DbConnection
        objConnection = Nothing

        Try
            objConnection = GetConnection()

            Dim objCommand As DbCommand
            objCommand = CreateDbCommand(commandText, objConnection, CommandType.Text)

            objConnection.Open()

            objDataReader = objCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

        Catch
            If objConnection.State <> System.Data.ConnectionState.Closed Then
                objConnection.Close()
            End If

        End Try

        ' Retour du objDataReader;
        Return objDataReader

    End Function

#End Region

#Region "Exécution d'un ordre qui ne renvoit pas de données"
    '/ <summary>
    '/ Exécute un ordre T-SQL et renvoit le nombre de lignes affectées
    '/ </summary>
    Public Function ExecuteNonQuery(ByVal commandText As String) As Integer

        Dim resultat As Integer = 0
        Dim objConnection As DbConnection = Nothing

        Try
            objConnection = GetConnection()

            Dim objCommand As DbCommand
            objCommand = CreateDbCommand(commandText, objConnection, CommandType.Text)

            objCommand.Connection = objConnection

            objConnection.Open()

            resultat = objCommand.ExecuteNonQuery()

        Catch ex As Exception
            MessageBox.Show(ex.Message, ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Stop)

        Finally
            If objConnection.State <> System.Data.ConnectionState.Closed Then
                objConnection.Close()
            End If

        End Try

        ' Retour du résultat
        Return resultat

    End Function

#End Region

#Region "Exécution d'un ordre qui renvoit une seule valeur"

    '/ <summary>
    '/ Exécute un ordre T-SQL et renvoit une valeur
    '/ </summary>
    '/ Requète à éxécuter

    '/ <returns>Valeur de retour</returns>
    Public Function ExecuteScalar(ByVal commandText As String) As Object

        Dim resultat As Integer
        resultat = Nothing

        Dim objConnection As DbConnection
        objConnection = Nothing

        Try
            objConnection = GetConnection()

            Dim command As DbCommand
            command = CreateDbCommand(commandText, objConnection, CommandType.Text)

            objConnection.Open()

            resultat = command.ExecuteScalar()

            ' Récupérer le dernier ID
            fctDernierID(objConnection)

        Finally
            If objConnection.State <> System.Data.ConnectionState.Closed Then
                objConnection.Close()
            End If

        End Try

        ' Retour du résultat
        Return resultat

    End Function

#End Region

#Region "Gestion de la création d'un DataAdapter"

    '/ <summary>
    '/ Création d'un dataAdapter
    '/ </summary>
    Public Function CreateDataAdapter(ByVal commandType As CommandType, ByVal selectStoredProcedure As String, ByVal insertStoredProcedure As String, ByVal updateStoredProcedure As String, ByVal deleteStoredProcedure As String) As DbDataAdapter

        'Déclarations
        Dim dbSelectCommand As DbCommand = Nothing
        Dim dbInsertCommand As DbCommand = Nothing
        Dim dbUpdateCommand As DbCommand = Nothing
        Dim dbDeleteCommand As DbCommand = Nothing
        Dim dbDataAdapter As DbDataAdapter = Nothing

        Try

            Dim dbConnection As DbConnection = GetConnection()

            'Paramétrage de la commande de sélection
            dbSelectCommand = _dbProviderFactory.CreateCommand()
            dbSelectCommand.CommandType = commandType
            dbSelectCommand.Connection = dbConnection

            If Not selectStoredProcedure Is Nothing Then
                dbSelectCommand.CommandText = selectStoredProcedure
            Else
                dbSelectCommand.CommandText = ""
            End If

            'Paramétrage de la commande d'insertion
            dbInsertCommand = _dbProviderFactory.CreateCommand()
            dbInsertCommand.CommandType = commandType
            dbInsertCommand.Connection = dbConnection

            If Not insertStoredProcedure Is Nothing Then
                dbInsertCommand.CommandText = insertStoredProcedure
            Else
                dbInsertCommand.CommandText = ""
            End If

            'Paramétrage de la commande de mise à jour
            dbUpdateCommand = _dbProviderFactory.CreateCommand()
            dbUpdateCommand.CommandType = commandType
            dbUpdateCommand.Connection = dbConnection

            If Not updateStoredProcedure Is Nothing Then
                dbUpdateCommand.CommandText = updateStoredProcedure
            Else
                dbUpdateCommand.CommandText = ""
            End If

            'Paramétrage de la commande de suppression
            dbDeleteCommand = _dbProviderFactory.CreateCommand()
            dbDeleteCommand.CommandType = commandType
            dbDeleteCommand.Connection = dbConnection

            If Not deleteStoredProcedure Is Nothing Then
                dbDeleteCommand.CommandText = deleteStoredProcedure
            Else
                dbDeleteCommand.CommandText = ""
            End If

            'Paramétrage de l'adaptateur
            dbDataAdapter = _dbProviderFactory.CreateDataAdapter()
            dbDataAdapter.InsertCommand = dbInsertCommand
            dbDataAdapter.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord
            dbDataAdapter.UpdateCommand = dbUpdateCommand
            dbDataAdapter.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord
            dbDataAdapter.DeleteCommand = dbDeleteCommand
            dbDataAdapter.SelectCommand = dbSelectCommand

        Catch ex As Exception
            MessageBox.Show(ex.Message, ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Stop)

        End Try

        'Retour
        Return dbDataAdapter

    End Function

#End Region

#Region "Récupération d'un DataSet"

    ''' <summary>
    ''' Récupère un DataSet, sans schémas de table, de la base de donnée depuis un ordre T-SQL
    ''' </summary>
    ''' Dataset devant contenir la table

    ''' Nom de la table

    ''' Requête servant à récupérer la table

    ''' <remarks></remarks>
    Public Sub RemplirDataSetSsSchs(ByVal objDataSet As DataSet, ByVal strNomTable As String, _
                                    ByVal strCommandText As String)

        Dim objConnection As DbConnection = Nothing
        Dim objDbReader As DbDataReader = Nothing

        Try

            ' Création de l'objet connexion
            objConnection = GetConnection()

            Dim objCommand As DbCommand
            objCommand = CreateDbCommand(strCommandText, objConnection, CommandType.Text)

            ' Ouverture de la connexion
            objConnection.Open()

            objDbReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)
            objDataSet.Load(objDbReader, LoadOption.PreserveChanges, strNomTable)

        Finally
            If objConnection.State <> System.Data.ConnectionState.Closed Then
                objConnection.Close()
            End If

        End Try

    End Sub

    ''' <summary>
    ''' Récupère un DataSet, avec schémas de table, de la base de donnée depuis un ordre T-SQL
    ''' </summary>
    ''' Dataset devant contenir la table

    ''' Nom de la table

    ''' DataAdapter de la table

    ''' <remarks></remarks>
    Public Sub RemplirDataSetAvcSchs(ByVal objDataSet As DataSet, ByVal strNomTable As String, _
                                     ByVal objDataAdapter As OleDb.OleDbDataAdapter)

        Dim objConnection As DbConnection = Nothing

        Try

            ' Création de l'objet connexion
            objConnection = GetConnection()

            objDataAdapter.SelectCommand.Connection = objConnection
            objDataAdapter.InsertCommand.Connection = objConnection
            objDataAdapter.UpdateCommand.Connection = objConnection
            objDataAdapter.DeleteCommand.Connection = objConnection

            ' Ouverture de la connexion
            objConnection.Open()

            objDataAdapter.FillSchema(objDataSet, SchemaType.Mapped, strNomTable)
            objDataAdapter.Fill(objDataSet, strNomTable)

        Finally
            If objConnection.State <> System.Data.ConnectionState.Closed Then
                objConnection.Close()
            End If

        End Try

    End Sub

#End Region

#Region "Mise à jour du DataSet"

    Public Sub UpdateDataSet(ByVal objDataAdapter As DbDataAdapter, ByVal objDataSet As DataSet, ByVal strTable As String)

        Dim objConnection As DbConnection = Nothing

        Try

            ' Création de l'objet connexion
            objConnection = GetConnection()

            objDataAdapter.SelectCommand.Connection = objConnection
            objDataAdapter.InsertCommand.Connection = objConnection
            objDataAdapter.UpdateCommand.Connection = objConnection
            objDataAdapter.DeleteCommand.Connection = objConnection

            ' Ouverture de la connexion
            objConnection.Open()

            ' UPDATE du dataset
            objDataAdapter.Update(objDataSet, strTable)

        Catch ex As Exception
            'MessageBox.Show(ex.Message, ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Stop)

        Finally
            If objConnection.State <> System.Data.ConnectionState.Closed Then
                objConnection.Close()
            End If

        End Try

    End Sub

#End Region

End Class
<hr size="2" width="100%" />La classe Métier:
<hr size="2" width="100%" />' Couche Utilisateur (=Formulaire) <-> Couche Métier <-> Couche Accès aux données
' => Couche Métier

Imports System
Imports System.Data
Imports System.Data.OleDb

Public Class ClasseMarque

#Region " Variables Privées "

    Private _clClassDb As New ClassDb
    Private _NomTable As String = "TblMarque"
    Private _DernierID As Integer
    Private _IdMarque As Integer
    Private _Marque As String

#End Region

    Public Sub New()

    End Sub

#Region "Propriétés de la Marque"

    Public Property DernierID() As Integer
        Get
            Return _DernierID
        End Get
        Set(ByVal value As Integer)
            _DernierID = value
        End Set
    End Property

    Public Property IdMarque() As Integer
        Get
            Return _IdMarque
        End Get
        Set(ByVal value As Integer)
            _IdMarque = value
        End Set
    End Property

    Public Property Marque() As String
        Get
            Return _Marque
        End Get
        Set(ByVal value As String)
            _Marque = value
        End Set
    End Property

#End Region

#Region "Charger les données"

    ''' <summary>
    ''' Créer le DataAdapter
    ''' </summary>
    '''

    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function CreerDataAdapter(Optional ByVal strFiltreTrie As String = "") As OleDbDataAdapter

        Dim objDataAdapter As New OleDbDataAdapter
        Dim tableMapping As New Common.DataTableMapping

        Try

            ' Mapping de la table
            tableMapping.SourceTable = "Table"
            tableMapping.DataSetTable = "Tbl_Marque"
            tableMapping.ColumnMappings.Add("ID_Marque", "ID_Marque")
            tableMapping.ColumnMappings.Add("Marque", "Marque")
            objDataAdapter.TableMappings.Add(tableMapping)

            ' SELECTCOMMAND
            objDataAdapter.SelectCommand = New OleDb.OleDbCommand
            'objDataAdapter.InsertCommand.Connection = objConnexion
            objDataAdapter.SelectCommand.CommandText = "SELECT Tbl_Marque.* FROM Tbl_Marque " & strFiltreTrie
            objDataAdapter.SelectCommand.CommandType = CommandType.Text

            ' INSERTCOMMAND
            objDataAdapter.InsertCommand = New OleDb.OleDbCommand
            'objDataAdapter.InsertCommand.Connection = objConnexion
            objDataAdapter.InsertCommand.CommandText = "INSERT INTO Tbl_Marque (Marque) VALUES (?)"
            objDataAdapter.InsertCommand.CommandType = CommandType.Text
            objDataAdapter.InsertCommand.Parameters.Add(New OleDb.OleDbParameter("Marque", OleDb.OleDbType.VarWChar, 0, ParameterDirection.Input, CType(0, Byte), CType(0, Byte), "Marque", DataRowVersion.Current, False, Nothing))

            ' UPDATECOMMAND
            objDataAdapter.UpdateCommand = New OleDb.OleDbCommand
            'objDataAdapter.UpdateCommand.Connection = objConnexion            objDataAdapter.UpdateCommand.CommandText "UPDATE Tbl_Marque SET Marque ? WHERE ((ID_Marque = ?) AND ((? = 1 AND Marque IS NULL) OR (Marque = ?)))"
            objDataAdapter.UpdateCommand.CommandType = CommandType.Text
            objDataAdapter.UpdateCommand.Parameters.Add(New OleDb.OleDbParameter("Marque", OleDb.OleDbType.VarWChar, 0, ParameterDirection.Input, CType(0, Byte), CType(0, Byte), "Marque", DataRowVersion.Current, False, Nothing))
            objDataAdapter.UpdateCommand.Parameters.Add(New OleDb.OleDbParameter("Original_ID_Marque", OleDb.OleDbType.[Integer], 0, ParameterDirection.Input, CType(0, Byte), CType(0, Byte), "ID_Marque", DataRowVersion.Original, False, Nothing))
            objDataAdapter.UpdateCommand.Parameters.Add(New OleDb.OleDbParameter("IsNull_Marque", OleDb.OleDbType.[Integer], 0, ParameterDirection.Input, CType(0, Byte), CType(0, Byte), "Marque", DataRowVersion.Original, True, Nothing))
            objDataAdapter.UpdateCommand.Parameters.Add(New OleDb.OleDbParameter("Original_Marque", OleDb.OleDbType.VarWChar, 0, ParameterDirection.Input, CType(0, Byte), CType(0, Byte), "Marque", DataRowVersion.Original, False, Nothing))

            ' DELETECOMMAND
            objDataAdapter.DeleteCommand = New OleDb.OleDbCommand
            'objDataAdapter.DeleteCommand.Connection = objConnexion            objDataAdapter.DeleteCommand.CommandText "DELETE FROM Tbl_Marque WHERE ((ID_Marque ?) AND ((? = 1 AND Marque IS NULL) OR (Marque = ?)))"
            objDataAdapter.DeleteCommand.CommandType = CommandType.Text
            objDataAdapter.DeleteCommand.Parameters.Add(New OleDb.OleDbParameter("Original_ID_Marque", OleDb.OleDbType.[Integer], 0, ParameterDirection.Input, CType(0, Byte), CType(0, Byte), "ID_Marque", DataRowVersion.Original, False, Nothing))
            objDataAdapter.DeleteCommand.Parameters.Add(New OleDb.OleDbParameter("IsNull_Marque", OleDb.OleDbType.[Integer], 0, ParameterDirection.Input, CType(0, Byte), CType(0, Byte), "Marque", DataRowVersion.Original, True, Nothing))
            objDataAdapter.DeleteCommand.Parameters.Add(New OleDb.OleDbParameter("Original_Marque", OleDb.OleDbType.VarWChar, 0, ParameterDirection.Input, CType(0, Byte), CType(0, Byte), "Marque", DataRowVersion.Original, False, Nothing))

        Catch ex As Exception
            MessageBox.Show(ex.Message, ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Stop)

        End Try

        Return objDataAdapter

    End Function

    ''' <summary>
    ''' Charger un enregistrement
    ''' </summary>
    ''' ID de l'enregistrement à charger

    ''' <remarks></remarks>
    Public Sub ChargerUn(ByVal IdMarque As Integer)

        Try

            Dim intResultatRech As Int16
            Dim objDataReader As OleDb.OleDbDataReader
            Dim strRecherche As String

            strRecherche = "SELECT Tbl_Marque.* " & _
                           "FROM Tbl_Marque " & _
                           "WHERE Tbl_Marque.ID_Marque = " & IdMarque & ";"

            intResultatRech = _clClassDb.ExecuteNonQuery(strRecherche)

            'If intResultatRech > 0 Then

            objDataReader = _clClassDb.GetReader(strRecherche)

            Do While objDataReader.Read
                If objDataReader.IsDBNull(objDataReader.GetOrdinal("ID_Marque")) Then : _IdMarque "-1" : Else : _IdMarque objDataReader.GetInt32(objDataReader.GetOrdinal("ID_Marque")) : End If                If objDataReader.IsDBNull(objDataReader.GetOrdinal("Marque")) Then : _Marque "" : Else : _Marque objDataReader.GetString(objDataReader.GetOrdinal("Marque")) : End If

            Loop

            'Else
            ' MessageBox.Show("L'enregistrement n'existe pas")
            'End If

        Catch ex As Exception
            MessageBox.Show(ex.Message, ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Stop)
        End Try

    End Sub

    ''' <summary>
    ''' Liste permettant d'indiquer s'il faut charger le schémas de la table
    ''' </summary>
    ''' <remarks></remarks>
    Public Enum lstChargerSchemas

        AvecSchemas
        SansSchemas

    End Enum

    ''' <summary>
    ''' Charger toute ou une partie de la table dans un DataSet
    ''' </summary>
    ''' Objet DataSet devant contenir la table

    ''' Filtre "WHERE" et/ou trie "ORDER BY"

    ''' <returns>Retourne un objet DataTable</returns>
    ''' <remarks></remarks>
    Public Function ChargerTs(ByVal objDataSet As DataSet, _
                                   ByVal lstChargerSchemas As lstChargerSchemas, _
                                   Optional ByVal strFiltreTrie As String = "") As DataTable

        Dim objDataTable As DataTable = Nothing

        Try

            Select Case lstChargerSchemas

                Case lstChargerSchemas.AvecSchemas
                    _clClassDb.RemplirDataSetAvcSchs(objDataSet, _NomTable, CreerDataAdapter(strFiltreTrie))

                Case lstChargerSchemas.SansSchemas
                    Dim strRequete As String
                    strRequete = "SELECT Tbl_Marque.* FROM Tbl_Marque " & strFiltreTrie
                    _clClassDb.RemplirDataSetSsSchs(objDataSet, _NomTable, strRequete)

            End Select

            objDataTable = objDataSet.Tables(_NomTable)

            ' Renommer les colonnes du DataTable
            'objDataTable.Columns("ID_Marque").ColumnName = "ID_Marque"
            'objDataTable.Columns("Marque").ColumnName = "Marque"

        Catch ex As Exception
            MessageBox.Show(ex.Message, ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Stop)
        End Try

        Return objDataTable

    End Function

    ''' <summary>
    ''' Trier le DataSet
    ''' </summary>
    ''' Objet DataSet à Trier

    ''' Filtre Nom du champ = 'Valeur'

    ''' Type de trie ASC ou DESC

    ''' <returns>Retourne un objet DataView</returns>
    ''' <remarks></remarks>
    ''' <example>TrierChargerTs(objDataSet, "Marque = 'Peugeot'", "Marque ASC")</example>
    Public Function TrierChargerTs(ByVal objDataSet As DataSet, ByVal strFiltre As String, _
                                    Optional ByVal strChampASCDESC As String = "") As DataView

        Dim objDataView As New DataView

        Try

            With objDataView
                .Table = objDataSet.Tables("TblMarque")
                .Sort = strChampASCDESC
                .RowFilter = strFiltre
            End With

        Catch ex As Exception
            MessageBox.Show(ex.Message, ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Stop)
        End Try

        Return objDataView

    End Function

    ''' <summary>
    ''' Charger une combobox
    ''' </summary>
    ''' ComboBox à remplir

    ''' Nom du membre à afficher

    ''' Nom du membre à prendre pour valeur réelle

    ''' Filtre "WHERE" et/ou trie "ORDER BY"

    ''' <remarks></remarks>
    ''' <example>clMarque.ChargerComboBox(Me.cboMarque, "Marque", "ID_Marque", "ORDER BY Marque")</example>
    Public Sub ChargerComboBox(ByVal cboNameComboBox As ComboBox, _
                                ByVal strDisplayMember As String, _
                                ByVal strValueMember As String, _
                                Optional ByVal strFiltreTrie As String = Nothing)

        Try

            Dim objDataset As New DataSet

            cboNameComboBox.DisplayMember = strDisplayMember
            cboNameComboBox.ValueMember = strValueMember
            cboNameComboBox.DataSource = ChargerTs(objDataset, lstChargerSchemas.SansSchemas, strFiltreTrie)

        Catch ex As Exception
            MessageBox.Show(ex.Message, ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Stop)
        End Try

    End Sub

#End Region

#Region "Ajouter / Modifier / Supprimer / Mise à jour les données"

    Public Function AjouterUn() As Boolean

        Try

            ' Tester l'existence de la valeur
            Dim strRequeteTest As String
            strRequeteTest = "SELECT COUNT(Tbl_Marque.Marque) " & _
                             "FROM Tbl_Marque " & _
                             "WHERE Tbl_Marque.Marque = """ & _Marque & """;"

            Dim intResultat As Integer
            intResultat = _clClassDb.ExecuteScalar(strRequeteTest)

            ' Enregistrer la nouvelle valeur si elle n'existe pas
            If intResultat > 0 Then

                ' Indique l'enregistrement n'a pas été ajouté
                AjouterUn = False
                MessageBox.Show("Cette marque existe déjà.")

            Else

                Dim strRequeteAjo As String
                strRequeteAjo = "INSERT INTO Tbl_Marque(Marque) " & _
                                "VALUES(""" & _Marque & """)"

                ' Exécuter la requête d'ajout
                _clClassDb.ExecuteScalar(strRequeteAjo)

                ' Récupérer le dernier ID ajouté à la table
                DernierID = _clClassDb.DernierID

                ' Indique l'enregistrement a été ajouté
                AjouterUn = True

            End If

            Return AjouterUn

        Catch ex As Exception
            MessageBox.Show(ex.Message, ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Stop)

        End Try

    End Function

    Public Function ModifierUn() As Boolean

        Try

            ' Modifier la nouvelle valeur
            Dim strRequete As String
            strRequete = "UPDATE Tbl_Marque " & _
                         "SET Marque = """ & _Marque & """ " & _
                         "WHERE Tbl_Marque.ID_Marque = " & _IdMarque & ";"

            _clClassDb.ExecuteScalar(strRequete)

        Catch ex As Exception
            MessageBox.Show(ex.Message, ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Stop)

        End Try

    End Function

    Public Function SupprimerUn() As Boolean

        Try

            ' Supprimer la valeur
            Dim strRequete As String = Nothing

            strRequete = "DELETE Tbl_Marque.* " & _
                         "FROM Tbl_Marque  " & _
                         "WHERE Tbl_Marque.ID_Marque = " & _IdMarque & ";"

            _clClassDb.ExecuteScalar(strRequete)

        Catch ex As Exception
            MessageBox.Show(ex.Message, ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Stop)

        End Try

    End Function

    Public Sub UpdateDataSet(ByVal objDataSet As DataSet)

        Try

            ' UPDATE du dataset
            _clClassDb.UpdateDataSet(CreerDataAdapter, objDataSet, _NomTable)

        Catch ex As Exception
            MessageBox.Show(ex.Message, ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Stop)

        End Try

    End Sub

#End Region

End Class
<hr size="2" width="100%" />Quelques exemples:
+ Déclarer la classe lorsque l'on souhaite s'en servir
<hr size="2" width="100%" />    Private clMarque As New ClasseMarque
<hr size="2" width="100%" />+ Avant d'enregistrer ou de modifier les valeurs saisies dans le formulaire, il faut les tranférer dans la classe métier:
<hr size="2" width="100%" />#Region "Chargement Formulaire -> Classes // Classe Marque"

    Private Sub ChargerFrmClMarque()
        ' Marque
        clMarque.IdMarque = intIDTblMarque
        clMarque.Marque = Me.txtMarque.Text

    End Sub

#End Region
<hr size="2" width="100%" />Ensuite on enregistre ou modifie:
<hr size="2" width="100%" />                    ' Ajouter
                    clMarque.AjouterUn()
                    ' Modifier
                    clMarque.ModifierUn()
<hr size="2" width="100%" />Charger une combobox:
<hr size="2" width="100%" />        ' Remplissage de la ComboBox
        clMarque.ChargerComboBox(Me.cboMarque, "Marque", "ID_Marque", "ORDER BY Marque")
<hr size="2" width="100%" />
Voilà. Elle est très loin d'être parfaite, mais elle fonctionne.

Gdal
3

Discussions similaires

VBNET pause...sleep...pause...sleep...
bimbo777 -
eldim -

4 réponses
[VB.NET] OpenFileDialog, débutant .NET
el_teedee -
zippro4012 -

6 réponses
VB.Net Écrire dans un DataGridView
cs_MagDix -
radzo -

15 réponses
Format de Date sous VB.NET
bernardbouree -
limpsilver -

20 réponses
VB.Net Instr Ça marche en .Net???
cs_JeffC1977 -
Kristof_Koder -

1 réponse
Rejoignez-nous