Accès BDDRésolu

Question

Bonjour a tous
S'il vous j'ai besoin que vous m'aider concernant ce code ci-dessous
car jai creer deux requettes dans ma base de donnees que je voudrais lire dans ma datagridview de mon windows form mais il ne s'affiche pas et j'ai 3 erreurs qu'ils m'affiche quelqu'un pourra t'il m'aider svp
Imports System.DataImports System.Data.OleDbImports System.Data.SqlClientPublic Class FrmROUGRAPH    'Etablissement de connexion    Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\V_Techno_DB_be.mdb")    'Déclaration de quelques variables    Public rSQL As String    Public i As Integer    Public arrimage() As Byte    Dim cmd As New OleDbCommand    Dim MyCn As New SqlConnection    Dim MyDatAdp As New SqlDataAdapter    Dim MyCmdBld As New SqlCommandBuilder    Dim MyDataTblDonnees As New DataTable    Private Sub FrmROUGRAPH_Load(sender As Object, e As EventArgs) Handles MyBase.Load        remplirEmployers()    End Sub    '---Affichage de données de la Table Employers par la DataGridView1    Sub remplirEmployers()        Try            Dim cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\V_Techno_DB_be.mdb")            Dim cmd As New OleDbCommand("select * from Requête SommeTotalHeures", cn)            cn.Open()            Dim da As New OleDbDataAdapter(cmd)            Dim ds As New DataSet            da.Fill(ds, "V_Techno_DB_be")            DataGridView1.DataSource = ds            DataGridView1.DataMember = "V_Techno_DB_be"            cn.Close()        Catch ex As Exception            'MsgBox(ex.Message)        End Try    End Sub    Private Sub SelectTotalHoraireEmployé(requete As String, connectionString As String) As DataTable        'connexion a la bd        MyCn.ConnectionString = connectionString        Try            'ouverture de la connexion            MyCn.Open()            Try                'execution de la requête et remplissage du DataTable avec le resultat de celle-ci                MyDatAdp = New SqlDataAdapter(requete, MyCn)                MyCmdBld = New SqlCommandBuilder(MyDatAdp)                MyDatAdp.Fill(MyDataTblDonnees)            Catch ex As Exception                MsgBox(ex.ToString())            Finally                MyCn.Close()            End Try        Catch ex As Exception            MsgBox("erreur_connexion", MsgBoxStyle.Critical, "Erreur")        End Try        Return MyDataTblDonnees    End SubEnd Class

cs_JMO · Answer

Bonjour wayne2017,
Pour accéder à la table Access, il faut déjà remplacer 
"select * from Requête SommeTotalHeures"
par"Select * From [Requête SommeTotalHeures]"
Essaie ces simples lignes
Option Strict On
Option Explicit On
Imports System.Data.OleDb

Public Class FrmROUGRAPH
    Private pathbase As String = Application.StartupPath & "\V_Techno_DB_be.mdb"
    Private connStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & pathbase

Private dts As New DataSet
    Private dtt As DataTable

Private Sub FrmROUGRAPH_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Using conn As New OleDbConnection(connStr)
            Dim query As String = "Select * From [Requête SommeTotalHeures]"
            Dim cmd As OleDbCommand = New OleDbCommand(query)
            Dim dta As OleDbDataAdapter = New OleDbDataAdapter(cmd)

conn.Open()
            cmd.Connection() = conn
            dta.Fill(dts, "TableHeures")
            dtt = dts.Tables("TableHeures")
            conn.Close()
        End Using

MessageBox.Show(CStr(dtt.Columns.Count), "columns")
        MessageBox.Show(CStr(dtt.Rows.Count), "rows")
    End Sub
End Class
jean-marc

cs_JMO · Answer

Est-ce ta table ressemble à cela

cs_JMO · Answer

Pour exemple
Ajoute une classe
Public Class Personnel
    Public Property Employe As String
    Public Property Code As String
    Public Property Ref_Projet As String
    Public Property SommeHeure_Journalier As String
    Public Property Date_demarrage_Projet As Date
    Public Property Total_jours As String
End Class
et
Option Strict On
Option Explicit On
Imports System.Data.OleDb

Public Class FrmROUGRAPH
    Private pathbase As String = Application.StartupPath & "\Database11.accdb"
    Private connStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & pathbase

Private dts As New DataSet
    Private dtt As DataTable

Private ListePersonnel As List(Of Personnel)

Private Sub FrmROUGRAPH_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Using conn As New OleDbConnection(connStr)
            Dim query As String = "Select * From [table requête]"
            Dim cmd As OleDbCommand = New OleDbCommand(query)
            Dim dta As OleDbDataAdapter = New OleDbDataAdapter(cmd)

conn.Open()
            cmd.Connection() = conn
            dta.Fill(dts, "Table")
            dtt = dts.Tables("Table")
            conn.Close()
        End Using

ListePersonnel = (
            From row In dtt.AsEnumerable()
            Order By row.Field(Of String)("Nom"), row.Field(Of String)("Prenom"), row.Field(Of String)("Code") Ascending
            Select New Personnel With {
                .Employe = row.Field(Of String)("Nom") & New String(" "c, 1) & row.Field(Of String)("Prenom"),
                .Code = row.Field(Of String)("Code"),
                .Ref_Projet = row.Field(Of String)("Ref_Projet"),
                .SommeHeure_Journalier = row.Field(Of String)("SommeHeure_Journalier"),
                .Date_demarrage_Projet = row.Field(Of Date)("Date_demarrage_Projet"),
                .Total_jours = row.Field(Of String)("Total_jours")
            }).ToList

' 
        Dim NameEmploye = (From y In ListePersonnel.GroupBy(Function(x) x.Employe)).ToList
        For Each value In NameEmploye.Where(Function(x) x.Key <> Nothing)
            'MessageBox.Show(value.Key.ToString, "Nom-Prénom du Personnel")
        Next

' Initialisation DataGridView1
        With Me.DataGridView1
            .ColumnCount = 6
            .Columns(0).Name = "Personnel"
            .Columns(1).Name = "Code"
            .Columns(2).Name = "Projet"
            .Columns(3).Name = "Total Heures"
            .Columns(4).Name = "Date démarrage Projet"
            .Columns(5).Name = "Total jours"
            .Columns(0).Width = 140
            .Columns(4).Width = 140
        End With

' Exemple : sélection d'une personne
        Dim exempleEmploye As String = NameEmploye.Item(0).Key
        For Each item In ListePersonnel.Where(Function(x) x.Employe = exempleEmploye)
            With Me.DataGridView1.Rows
                .Add(item.Employe, item.Code, item.Ref_Projet, item.SommeHeure_Journalier, _
                     item.Date_demarrage_Projet.ToString("dd/MM/yyyy"), item.Total_jours)
            End With
        Next
    End Sub
End Class

wayne2017 · Answer

Bonjour a tous , bonjour cs_JMO svp j'ai un souci , pourriez-vous me dire comment remédier a cette phrase ,j'ai rajouter des champs a la base et lorsque je veux revenir a celle de depart il m'affiche ceci constamment : <> Select New Personnel With { .Employe = row.Field(Of String)("Nom") & New String(" "c, 1) & row.Field(Of String)("Prenom"), .Code = row.Field(Of String)("Code"), .Ref_Projet = row.Field(Of String)("Ref_Projet"), .SommeHeure_Journalier = row.Field(Of String)("SommeHeure_Journalier"), .Date_demarrage_Projet = row.Field(Of Date)("Date_demarrage_Projet"), .Total_jours = row.Field(Of String)("Total_jours") }).ToList NB: je n'ai pas modifier les champs de départ merci pour les idees

cs_JMO · Answer

Bonsoir le Forum, bonsoir wayne2017,
 
Ton code, déposé le 28 mars 2017 à 12:13, peut être simplifié.

Exemple ci-dessous dans lequel :
- ouverture de ta base Access;
- extraction des tables "[table requête]" et "[Employers]";
- fermeture de ta base Access;
- load DataGridView1;
- load ComboBoxCode.

Class1 modifiée
Public Class Personnel
    Public Property Employe As String
    Public Property Code As String
    Public Property Ref_Projet As String
    Public Property SommeHeure_Journalier As String
    Public Property Date_demarrage_Projet As Date
    Public Property Total_jours As Integer
End Class

Public Class Employers
    Public Property Code As String
End Class
Class FrmROUGRAPH
Option Strict On
Option Explicit On
Imports System.Data.OleDb

Public Class FrmROUGRAPH
    Private pathbase As String = Application.StartupPath & "\V_Techno_DB_be.mdb"
    Private connStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & pathbase

Private ListePersonnel As List(Of Personnel)
    Private CodeEmployers As List(Of Employers)

Private da As OleDb.OleDbDataAdapter
    Private ds As DataSet = New DataSet
    Private dtPersonnel As DataTable = New DataTable("Personnel")
    Private dtEmployers As DataTable = New DataTable("Employers")

Private Sub FrmROUGRAPH_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Initialize()
    End Sub

Sub Initialize()
        ' OUverture de la base Access et extraction des deux tables
        Using conn As New OleDbConnection(connStr)
            conn.Open()
            Dim querypersonnel As String = " Select * From [table requête] Where [Date_demarrage_Projet] Is Not Null "
            da = New OleDb.OleDbDataAdapter(querypersonnel, conn)
            da.Fill(ds, "Personnel")

Dim queryemployers As String = " Select Distinct Code From [Employers] "
            da = New OleDb.OleDbDataAdapter(queryemployers, conn)
            da.Fill(ds, "Employers")
            conn.Close()
        End Using
        ' Extraction data de la base Access terminée ... Fermeture de la base

dtPersonnel = ds.Tables("Personnel")
        dtEmployers = ds.Tables("Employers")

' Initialisation DataGridView1
        With Me.DataGridView1
            .ColumnCount = 6
            .Columns(0).Name = "Personnel"
            .Columns(1).Name = "Code"
            .Columns(2).Name = "Projet"
            .Columns(3).Name = "Total Heures"
            .Columns(4).Name = "Date démarrage Projet"
            .Columns(5).Name = "Total jours"
            .Columns(0).Width = 140
            .Columns(4).Width = 140
        End With

ListePersonnel = (
            From row In dtPersonnel.AsEnumerable()
            Order By row.Field(Of String)("Nom"), row.Field(Of String)("Prenom"), row.Field(Of String)("Code") Ascending
            Select New Personnel With {
                .Employe = row.Field(Of String)("Nom") & New String(" "c, 1) & row.Field(Of String)("Prenom"),
                .Code = row.Field(Of String)("Code"),
                .Ref_Projet = row.Field(Of String)("Ref_Projet"),
                .SommeHeure_Journalier = row.Field(Of String)("SommeHeure_Journalier"),
                .Date_demarrage_Projet = row.Field(Of Date)("Date_demarrage_Projet"),
                .Total_jours = row.Field(Of Integer)("Total_jours")
            }).ToList

For Each item In ListePersonnel
            With Me.DataGridView1.Rows
                .Add(item.Employe, item.Code, item.Ref_Projet, item.SommeHeure_Journalier, _
                     item.Date_demarrage_Projet.ToString("dd/MM/yyyy"), item.Total_jours)
            End With
        Next

CodeEmployers = (
            From row In dtEmployers.AsEnumerable()
            Order By row.Field(Of String)("Code") Ascending
            Select New Employers With {
                .Code = row.Field(Of String)("Code")
            }).ToList

Me.ComboBoxCode.Items.Add("Code")
        For Each item In CodeEmployers
            Me.ComboBoxCode.Items.Add(item.Code)
        Next
        Me.ComboBoxCode.SelectedIndex = 0
    End Sub
End Class

Il serait souhaitable que tu valides ce post et ouvrir une nouvelle discussion si problème ou question (genre ComboBox_Change).

jean-marc

wayne2017 · Answer

SUITE PROGRAMME AVEC LE CALCUL D'HEURES

CREER UNE CLASSE

ublic Class Personnel    Public Property Employe As String    Public Property Code As String    Public Property Ref_Projet As String    Public Property Date_de_demarrage_Projet As Date    Public Property Heures_Depart As String    Public Property Date_de_Fin_Projet As Date    Public Property Heures_FIN As String    Public Property Total_Heures As StringEnd Class




ENSUITE ENTRE CODE SUIVANT DANS LE PROGRAMME:

ption Strict OnOption Explicit OnImports System.Data.OleDbPublic Class FrmROUGRAPH    Private pathbase As String = Application.StartupPath & "\V_Techno_DB_be.mdb"    Private connStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & pathbase    Private ListePersonnel As List(Of Personnel)    Private da As OleDb.OleDbDataAdapter    Private ds As DataSet = New DataSet    Private dtPersonnel As DataTable = New DataTable("Personnel")    Private Sub FrmROUGRAPH_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load        Me.Text = "Mon application en cours de développement"        ExtractDataBase()        Initialize()    End Sub    Sub ExtractDataBase()        Using conn As New OleDbConnection(connStr)            conn.Open()            Dim query As String            query = " Select Nom, Prenom, Code, Ref_Projet, Date_de_demarrage_Projet, " & _                    "        Heures_Depart, Date_de_Fin_Projet, Heures_FIN" & _                    "        From Employers " & _                    "        Where Date_de_demarrage_Projet Is Not Null And " & _                    "              Date_de_Fin_Projet Is Not Null "            da = New OleDb.OleDbDataAdapter(query, conn)            da.Fill(ds, "Personnel")        End Using        dtPersonnel = ds.Tables("Personnel")    End Sub    Sub Initialize()        ListePersonnel = (            From row In dtPersonnel.AsEnumerable()            Order By row.Field(Of String)("Nom"), row.Field(Of String)("Prenom"), row.Field(Of String)("Ref_Projet") Ascending            Select New Personnel With {                .Employe = row.Field(Of String)("Nom").ToUpper & New String(" "c, 1) & row.Field(Of String)("Prenom"),                .Code = row.Field(Of String)("Code"),                .Ref_Projet = row.Field(Of String)("Ref_Projet"),                .Date_de_demarrage_Projet = row.Field(Of Date)("Date_de_demarrage_Projet"),                .Heures_Depart = row.Field(Of String)("Heures_Depart"),                .Date_de_Fin_Projet = row.Field(Of Date)("Date_de_Fin_Projet"),                .Heures_FIN = row.Field(Of String)("Heures_FIN"),                .Total_Heures = returnTotalHeures(row.Field(Of Date)("Date_de_demarrage_Projet") & New String(" "c, 1) & _                                                  row.Field(Of String)("Heures_Depart"), _                                                  row.Field(Of Date)("Date_de_Fin_Projet").ToString("yyyy/MM/dd") & New String(" "c, 1) & _                                                  row.Field(Of String)("Heures_FIN"))            }).ToList        ' Initialistion ComboBox_NomProjet        Me.ComboBox_NomProjet.Items.Add("Codes Projet")        For Each item In ListePersonnel.Select(Function(c) c.Code).Distinct.OrderBy(Function(c) c.ToString)            Me.ComboBox_NomProjet.Items.Add(item.ToString)        Next        Me.ComboBox_NomProjet.SelectedIndex = 0        ' Initialisation ComboBox_Personnel        Me.ComboBox_Personnel.Items.Add("Personnel")        For Each item In ListePersonnel.Select(Function(c) c.Employe).Distinct.OrderBy(Function(c) c.ToString)            Me.ComboBox_Personnel.Items.Add(item.ToString)        Next        Me.ComboBox_Personnel.SelectedIndex = 0        ' Initialisation DataGridView1        With Me.DataGridView1            .ColumnCount = 6            .Columns(0).Name = "Personnel"            .Columns(1).Name = "Code"            .Columns(2).Name = "Projet"            .Columns(3).Name = "Début Projet"            .Columns(4).Name = "Fin Projet"            .Columns(5).Name = "Total Heures"            .Columns(0).Width = 140            .Columns(3).Width = 100            .Columns(4).Width = 100            .Columns(5).Width = 100        End With        For Each item In ListePersonnel            With Me.DataGridView1.Rows                .Add(item.Employe, _                     item.Code, _                     item.Ref_Projet, _                     item.Date_de_demarrage_Projet.ToString("yyyy/MM/dd") & New String(" "c, 1) & item.Heures_Depart, _                     item.Date_de_Fin_Projet.ToString("yyyy/MM/dd") & New String(" "c, 1) & item.Heures_FIN, _                     item.Total_Heures)            End With        Next    End Sub    Function returnTotalHeures(ByVal debut As String, ByVal fin As String) As String        Dim days As Long = DateDiff(DateInterval.Minute, CDate(debut), CDate(fin))        Dim iSpan As TimeSpan = TimeSpan.FromMinutes(days)        Dim iHour As String = CStr(((iSpan.Days * 24) + iSpan.Hours)).PadLeft(2, "0"c) & " h " & _            iSpan.Minutes.ToString.PadLeft(2, "0"c)        Return iHour    End Function    Private Sub ButtonClose_Click(sender As Object, e As EventArgs) Handles Button_Close.Click        Me.Close()    End SubEnd Class


et le tout est  ok !



   

      REMERCIEMENT A CS_JMO

Accès BDD

6 réponses

Votre réponse

Discussions similaires