Base access et enregistrement VB2008

Bonjour,

Suggestion :

Première étape

Créer une(des) classe(s) représentant la(les) tables de ta BD.

ex:

Public Class EntAdherents 

#Region "--- Attributs ---" 
 Private mNom As String = String.Empty 
#End Region 

#Region "--- Propriétés ---" 
 Public Property Nom() As String 
  Get 
   Return Me.mNom 
  End Get 
  Set(ByVal value As String) 
   Me.mNom = value 
  End Set 
 End Property 
#End Region 

#Region "--- Méthodes ---" 
 Public Sub New() 

 End Sub 
 Public Sub New(ByVal pNom As String) 
  Me.mNom = pNom 
 End Sub 
#End Region 

End Class 

Public Class LstAdherents 
 Inherits List(Of Adherents) 
End Class 

Public Class CriAdherents 
#Region "--- Attributs ---" 
 Private mNom As String = String.Empty 
#End Region 

#Region "--- Propriétés ---" 
 Public Property Nom() As String 
  Get 
   Return Me.mNom 
  End Get 
  Set(ByVal value As String) 
   Me.mNom = value 
  End Set 
 End Property 
#End Region 

#Region "--- Méthodes ---" 
 Public Sub New() 

 End Sub 
#End Region 

End Class 

on retrouve dans ce fichier, une classe Entite (l'objet pricipal),
une classe Liste qui contiendra une liste d'objets (liste d,enregistrement),
ainsi qu'un classe Critères pour les requêtes SQL ( select, insert, update, delete ).

ensuite tu crée une classe que j'appellerai un controleur pour
accèder à la BD, voici un exemple d'un projet de base :

#Region "--- Importation des classes externes ---" 

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

    Imports TYP_ENT = PC_config_Entite.PC_config_Table 
    Imports TYP_COL = PC_config_Entite.PC_config_ListeTable 
    Imports TYP_CRI = PC_config_Entite.PC_config_Criteres 
    Imports TYP_COM = PC_config_Commun.PC_config_Communaute 
    Imports TYP_SQL = PC_config_Controleur.PC_config_ReqSQL 

#End Region 

Public Class PC_config_ExeSQL 

#Region "--- Attributs ---" 

    Private zInstanceSQL As TYP_SQL 

 Private zConnexion As OleDbConnection 

    Private zCheminBD As String = String.Empty 

#End Region 

#Region "--- Propriétés ---" 

    Private Property InstanceSQL() As TYP_SQL 
        Get 
            Dim oldInst As TYP_SQL = Me.zInstanceSQL 
            If (oldInst Is Nothing) Then 
                oldInst = New TYP_SQL 
            End If 
            Return oldInst 
        End Get 
        Set(ByVal value As TYP_SQL) 
            Me.zInstanceSQL = value 
        End Set 
    End Property 

 Private Property CheminBD() As String 
  Get 
   Return Me.zCheminBD 
  End Get 
  Set(ByVal value As String) 
   Me.zCheminBD = value 
  End Set 
 End Property 

    ''' <summary> 
    ''' Instance unique de la classe 
    ''' </summary> 
    ''' <value></value> 
    ''' <returns></returns> 
    ''' <remarks></remarks> 
    Private Property Connexion() As OleDb.OleDbConnection 

        Get 
            Try 
                If (Me.zConnexion Is Nothing) Then 
                    zConnexion = New OleDb.OleDbConnection() 
                End If 
            Catch ex As Exception 

            End Try 

            Return Me.zConnexion 

        End Get 
        Set(ByVal value As OleDb.OleDbConnection) 
            Me.zConnexion = value 
        End Set 
    End Property 

#End Region 

#Region "--- Méthodes ---" 

#Region "--- Constructeurs ---" 

    Public Sub New(ByVal pChemin As String) 

  Dim StrTexte As New System.Text.StringBuilder 
  CheminBD = pChemin 

  Try 
   ' Parametrage de la chaine de connection 
   Dim StringConnexion As String = _ 
    String.Format("Provider=Microsoft.Jet.OleDB.4.0;Data Source={0}{1};", CheminBD, "TableMembres.mdb") 
   Connexion.ConnectionString = StringConnexion 
   Connexion.Open() 

  Catch ex As Exception 
   StrTexte.AppendLine("Erreur de connexion !") 
   StrTexte.AppendLine("Modele") 
   StrTexte.AppendLine("Constructeur : PC_config_ExeSQL") 
            MsgBox(StrTexte.ToString) 
        Finally 

        End Try 

 End Sub 

#End Region 

#Region "--- Fonctions ---" 

#Region "--- Région Fonctions de suppression ---" 

 Public Function SupprimerEntite(ByVal pEnt As TYP_ENT) As TYP_ENT 

  Dim reqSQL As String = String.Empty 
  Dim Indice As Int32 = 0 

  Try 
   If (Connexion.State = ConnectionState.Closed) Then 
    Connexion.Open() 
   End If 

   reqSQL = InstanceSQL.Supprimer(pEnt) 

   Dim cmd As New OleDbCommand("Systeme", Connexion) 

   cmd.CommandText = reqSQL 
   Indice = cmd.ExecuteNonQuery() 

   pEnt.EstModifier = False 
   pEnt.EstNouveau = False 
   pEnt.EstSupprimer = False 
   pEnt.id = String.Empty 

  Catch ex As Exception 
   pEnt = Nothing 
  Finally 
   Connexion.Close() 
  End Try 

  Return pEnt 

 End Function 

#End Region 

#Region "--- Région Fonctions de Lectures ---" 

 Public Function Lecture(ByVal pCritere As TYP_CRI) As TYP_COL 

  Debug.WriteLine("Entree - PC_config - Lecture") 

  Dim MonAdapteur As OleDb.OleDbDataAdapter = Nothing 
  Dim MonDataTable As DataTable = Nothing 
  Dim MonDataSet As New DataSet() 
  Dim DataRowLocal As DataRow 
  Dim MaCollection As New TYP_COL 
  Dim MonEntite As New TYP_ENT 


  Try 
   If (Connexion.State = ConnectionState.Closed) Then 
    Connexion.Open() 
   End If 

   Dim reqSQL As String = InstanceSQL.Lecture(pCritere) 

   MonAdapteur = New OleDbDataAdapter(reqSQL, Connexion) 
   MonAdapteur.Fill(MonDataSet, "membres") 
   MonDataTable = MonDataSet.Tables("membres") 

   For Each DataRowLocal In MonDataTable.Rows 
    MonEntite = CreerInstance(DataRowLocal, pCritere.CheminBD) 
    MaCollection.Add(MonEntite) 
   Next 

  Catch ex As Exception 
   Debug.WriteLine("Erreur - PC_config - Lecture") 
   MaCollection = Nothing 
  Finally 
   Connexion.Close() 
   Debug.WriteLine("Sortie - PC_config - Lecture") 
  End Try 

  Return MaCollection 

 End Function 

#End Region 

#Region "--- Région Fonctions de Modifications ---" 

 ''' <summary> 
 '''  Permet d'enregistrer une collection d'entités selon les critères fournis 
 ''' </summary> 
 ''' <param name="pEntite"></param> 
 ''' <returns></returns> 
 ''' <remarks></remarks> 
 Public Function InsererEntite(ByVal pEntite As TYP_ENT) As TYP_ENT 

  Debug.WriteLine("Entree - PC_Config - InserererEntite") 

  Dim reqSQL As String = String.Empty 
  Dim Indice As Int32 = 0 
  Dim Reponse As Boolean = False 

  Try 
   If (Connexion.State = ConnectionState.Closed) Then 
    Connexion.Open() 
   End If 

   reqSQL = InstanceSQL.Insertion(pEntite) 

   Dim cmd As New System.Data.OleDb.OleDbCommand(reqSQL, Connexion) 
   cmd.CommandType = System.Data.CommandType.Text 
   cmd.CommandText = reqSQL 
   cmd.Connection = Connexion 
   Indice = cmd.ExecuteNonQuery() 

   pEntite.EstModifier = False 
   pEntite.EstNouveau = False 
   pEntite.EstSupprimer = False 

  Catch ex As Exception 
   Debug.WriteLine("Erreur - PC_config - InserererEntite") 
   pEntite = Nothing 
  Finally 
   Connexion.Close() 
   Debug.WriteLine("Sortie - PC_config - InserererEntite") 
  End Try 

  Return pEntite 

 End Function 

 Public Function SauvegarderEntite(ByVal pEnt As TYP_ENT) As TYP_ENT 

  Dim Flag As Boolean = False 

  Try 
   If (Connexion.State = ConnectionState.Closed) Then 
    Connexion.Open() 
   End If 

   If (Flag) Then 
    pEnt = Nothing 
   End If 
  Catch ex As Exception 

  Finally 
   Connexion.Close() 
  End Try 

  Return pEnt 

 End Function 

#End Region 

#Region "--- Région Fonctions de Conversions ---" 

 Private Function CreerInstance(ByVal objet As DataRow, ByVal pChemin As String) As TYP_ENT 

  Debug.WriteLine("Entree - PC_config : CreerInstance") 

  Dim clientEntite As TYP_ENT = New TYP_ENT 

  Try 

   With clientEntite 

    If (objet.Item(TYP_COM.iCol_id_Ind1) IsNot Nothing) Then 
     .id = Convert.ToString(objet.Item(TYP_COM.iCol_id_Ind1)) 
    End If 

    If (objet.Item(TYP_COM.iCol_pseudo_Ind2) IsNot Nothing) Then 
     .pass = Convert.ToString(objet.Item(TYP_COM.iCol_pseudo_Ind2)) 
    End If 

    If (objet.Item(TYP_COM.iCol_pass_Ind3) IsNot Nothing) Then 
     .pseudo = Convert.ToString(objet.Item(TYP_COM.iCol_pass_Ind3)) 
    End If 

    If (objet.Item(TYP_COM.iCol_email_Ind4) IsNot Nothing) Then 
     .email = Convert.ToString(objet.Item(TYP_COM.iCol_email_Ind4)) 
    End If 

    If (objet.Item(TYP_COM.iCol_date_inscr_Ind5) IsNot Nothing) Then 
     .date_inscription = Convert.ToDateTime(objet.Item(TYP_COM.iCol_date_inscr_Ind5)) 
    End If 

   End With 

  Catch ex As Exception 
   Debug.WriteLine("Erreur - PC_config : CreerInstance") 
  Finally 
   Debug.WriteLine("Sortie - PC_config : CreerInstance") 
  End Try 

  Return clientEntite 

 End Function 

#End Region 

#Region "--- Région Fonction Numero Suivant ---" 

 Public Function ObtenirNumeroSuivant(ByVal pChemin As String) As String 

  Debug.WriteLine("Entree - PC_config - ObtenirNumeroSuivant") 

  ' Parametrage des objets de manipulation des données 
  Dim MonAdapteur As OleDb.OleDbDataAdapter = Nothing 
  Dim MonDataTable As DataTable = Nothing 
  Dim MonDataSet As New DataSet() 
  Dim DataRowLocal As DataRow 
  Dim MaCollection As New TYP_COL 
  Dim MonEntite As New TYP_ENT 

  Dim Compteur As String = String.Empty 
  Dim Numero As Int32 = 0 

  Try 
   If (Connexion.State = ConnectionState.Closed) Then 
    Connexion.Open() 
   End If 

   Dim reqSQL As String = InstanceSQL.ObtenirNumeroUniqueSuivant 

   MonAdapteur = New OleDbDataAdapter(reqSQL, Connexion) 
   MonAdapteur.Fill(MonDataSet, "membres") 
   MonDataTable = MonDataSet.Tables("membres") 

   DataRowLocal = MonDataTable.Rows(0) 
   Dim Resultat As String = String.Empty 
   If (Not IsDBNull(DataRowLocal.Item(0))) Then 
    Compteur = CType(DataRowLocal.Item(0), String) 
    If Not (String.IsNullOrEmpty(Compteur)) Then 
     If ((Compteur > "100000") And (Compteur < "999999")) Then 
      Compteur = "100000" 
     End If 
    Else 
     Compteur = "100000" 
    End If 
   Else 
    Compteur = "100000" 
   End If 
   Int32.TryParse(Compteur, Numero) 
   Numero += 1 
   Compteur = Convert.ToString(Numero) 

  Catch ex As Exception 
   Debug.WriteLine("Erreur - PC_config - ObtenirNumeroSuivant") 
   Compteur = String.Empty 

  Finally 
   Debug.WriteLine("Sortie - PC_config - ObtenirNumeroSuivant") 
   Connexion.Close() 
  End Try 

  Return Compteur.ToString 

 End Function 

#End Region 

#End Region 

#End Region 

End Class 

Et une classe pour les requêtes SQL :

#Region "--- Importations des classes externes ---" 

    Imports TYP_ENT = PC_config_Entite.PC_config_Table 
    Imports TYP_CRI = PC_config_Entite.PC_config_Criteres 
    Imports TYP_COM = PC_config_Commun.PC_config_Communaute 

#End Region 

Public Class PC_config_ReqSQL 

#Region "--- Région Constantes SQL ---" 

#Region "--- Région Requête SQL Insertion ---" 

 ' TODO - Note la syntaxe peut changer selon le type de la BD 

    'INSERT INTO 'membre' ('id', 'pseudo', 'pass', 'email', 'date_inscription') VALUES 
    '(NULL, 'guillaume', '85ca51916e72aafad09b20409bbc43d538f4d791', 'guillaume.robier@hotmail.fr', 2012); 

    Public ReadOnly Property Insertion(ByVal pEnt As TYP_ENT) As String 
        Get 
            Dim ChaineSQL As New System.Text.StringBuilder 

            ChaineSQL.Append("INSERT INTO ") 
   ChaineSQL.Append(TYP_COM.BDNomTable) 
            ChaineSQL.Append(" (") 
            ChaineSQL.Append(TYP_COM.BD100Id) 
            ChaineSQL.Append(", ") 
            ChaineSQL.Append(TYP_COM.BD110Pseudo) 
            ChaineSQL.Append(", ") 
            ChaineSQL.Append(TYP_COM.BD120Pass) 
            ChaineSQL.Append(", ") 
            ChaineSQL.Append(TYP_COM.BD130Email) 
            ChaineSQL.Append(", ") 
            ChaineSQL.Append(TYP_COM.BD140DtInscr) 
            ChaineSQL.Append(") ") 
            ChaineSQL.Append(" VALUES (") 
            ChaineSQL.Append("'" & pEnt.id & "', ") 
            ChaineSQL.Append("'" & pEnt.pass & "', ") 
            ChaineSQL.Append("'" & pEnt.pseudo & "', ") 
            ChaineSQL.Append("'" & pEnt.email & "', ") 
            ChaineSQL.Append("'" & pEnt.date_inscription & "')") 


            Return ChaineSQL.ToString 
        End Get 
 End Property 

#End Region 

#Region "--- Région Requête SQL Sauvegarder ---" 

 Public ReadOnly Property Sauvegarder(ByVal pEnt As TYP_ENT) As String 
  Get 
   Dim ChaineSQL As New System.Text.StringBuilder 

   ChaineSQL.Append("UPDATE ") 


   Return ChaineSQL.ToString 
  End Get 
 End Property 

#End Region 

#Region "--- Région Requête SQL Supprimer ---" 

 Public ReadOnly Property Supprimer(ByVal pEnt As TYP_ENT) As String 
  Get 
   Dim ChaineSQL As New System.Text.StringBuilder 

   ChaineSQL.Append("DELETE (" & TYP_COM.BDNomTable & "." & TYP_COM.BD100Id & ")") 
   ChaineSQL.Append(" FROM (" & TYP_COM.BDNomTable & ")") 
   ChaineSQL.Append(" WHERE (((") 
   ChaineSQL.Append(TYP_COM.BDNomTable & "." & TYP_COM.BD100Id & ")=") 
   ChaineSQL.Append("""" & pEnt.id & """" & "));") 

   Return ChaineSQL.ToString 

  End Get 
 End Property 

#End Region 

#Region "--- Région Requête SQL Lecture ---" 

 ' TODO - Note la syntaxe peut changer selon le type de la BD 

 Public ReadOnly Property Lecture(ByVal pCri As TYP_CRI) As String 
  Get 
   Dim ChaineSQL As New System.Text.StringBuilder 

   ' TODO : A enlever après déboggage 
            pCri.id = String.Empty 

            If (String.IsNullOrEmpty(pCri.id)) Then 
                ChaineSQL.Append("SELECT ") 
                ChaineSQL.Append(TYP_COM.BD100Id) 
                ChaineSQL.Append(", ") 
                ChaineSQL.Append(TYP_COM.BD110Pseudo) 
                ChaineSQL.Append(", ") 
                ChaineSQL.Append(TYP_COM.BD120Pass) 
                ChaineSQL.Append(", ") 
                ChaineSQL.Append(TYP_COM.BD130Email) 
                ChaineSQL.Append(", ") 
                ChaineSQL.Append(TYP_COM.BD140DtInscr) 
                ChaineSQL.Append(" ") 
                ChaineSQL.Append(" FROM ") 
                ChaineSQL.Append(TYP_COM.BDNomTable) 
                ChaineSQL.Append(" ORDER BY ") 
    ChaineSQL.Append(TYP_COM.BD100Id) 
            Else 
                ChaineSQL = New System.Text.StringBuilder 
            End If 

   Return ChaineSQL.ToString 
  End Get 
 End Property 

#End Region 

#Region "--- Région Requête SQL Obtenir Numéro Suivant ---" 

 Public ReadOnly Property ObtenirNumeroUniqueSuivant() As String 
  Get 
   Dim ReqSQL As String = "SELECT MAX(" & TYP_COM.BDNomTable & "." & _ 
         TYP_COM.BD100Id & ")" & _ 
         " FROM " & TYP_COM.BDNomTable & ";" 
   Return ReqSQL 
  End Get 
 End Property 

#End Region 

#End Region 

End Class 

Ça peut paraitre complexe, mais c'est une architecture organique
bien monté.

Voici un modele de base complet que tu pourras aussi examiné :
https://www.cjoint.com/?BHqozsEEOxW

Je ne vais pas plus loin pour l'instant, examine ce code et si tu as des questions
j'irai plus loin :-)

Cdt

Lupin