none
how to load an Access Database into a DataSet object in vb.net? RRS feed

  • Question

  • hi all

    how to load an Access Database into a DataSet object in vb.net?

    and i want show data into datagridview.


    Name of Allah, Most Gracious, Most Merciful and He created the human

    Sunday, November 19, 2017 4:54 PM

Answers

  • Take a look at the following example I did on MSDN.

    When looking at the code notice I separate database operations into a class project. I created the columns in the DataGridView designer and set the DataProperty there also.

    There is a lot of code yet all you need is (a slimmed down version from the code sample)

    Public Class Operations 
        ''' <summary> 
        ''' Create our database connection string smartly 
        ''' </summary> 
        Private Builder As New OleDbConnectionStringBuilder With 
            { 
                .DataSource = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "CustomersDatabase.accdb"), 
                .Provider = "Microsoft.ACE.OLEDB.12.0" 
            } 
    
        Public Function ReadCustomers() As DataTable 
     
            Dim dt As New DataTable 
            Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString} 
                Using cmd As New OleDbCommand With {.Connection = cn} 
     
                    cmd.CommandText = 
                        <SQL> 
                            SELECT  
                                Identifier,  
                                CompanyName,  
                                ContactName,  
                                ContactTitle,  
                                Address,  
                                City,  
                                PostalCode,  
                                Country  
                            FROM  
                                Customers 
                            ORDER BY CompanyName 
                        </SQL>.Value 
     
                    Try 
     
                        cn.Open() 
                        dt.Load(cmd.ExecuteReader) 
     
     
                    Catch ex As Exception 
    		    ' decide how to handle the error e.g. look how I did it in the code sample
                    End Try 
                End Using 
            End Using 
     
            Return dt 
     
        End Function 
    
    End Class 
    

    Form code

    ' Operations class
    Imports BackEnd 
    Public Class Form1 
        Private ops As New Operations 
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load 
            ' indicate we are taken care of managing columns, in this case 
            ' columns have been configured in the designer 
            dgvCustomers.AutoGenerateColumns = False 
    
            dgvCustomers.DataSource = ops.ReadCustomers 
        End Sub 
    End Class 
    
    The Operation class code could be taken out of the class and placed directly in the form yet it's best to keep them separated.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by sh 2020 Thursday, November 30, 2017 2:14 PM
    Monday, November 27, 2017 3:27 PM
    Moderator

All replies

  • Do you want to load a single table and display the information into a DataGridView for view only or do you want to add, edit, delete capabilities within the confines of the DataGridView or perhaps using a edit form which displays when say double-clicking a specific row in the DataGridView?

    What version of the .NET Framework are you targeting e.g. Framework 4.5 for instance?

    For the record, a DataSet is for relational data but can be used for a single table while a DataTable (which DataSet object have) is for (in this case) working with a single table from (in this case) your MS-Access database.

    In short, the more details you can provide the better I can answer your question.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Sunday, November 19, 2017 6:37 PM
    Moderator
  • Hi sh2015,

    Do you want to know how To Populate a DataSet Object from a Access Database by vb.ne, and then binding this dataset to datagridviewt? If yes, please refer to the :

    http://www.homeandlearn.co.uk/NET/nets12p5.html

    https://xldennis.wordpress.com/2010/11/16/populate-datagridview-with-data-from-an-access-database/

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, November 21, 2017 5:44 AM
    Moderator


  • Name of Allah, Most Gracious, Most Merciful and He created the human

    Karen,

    This OP shows already for a decade the same kind of questions. 


    Success
    Cor

    Tuesday, November 21, 2017 8:36 AM
  • thank dear Karen

    i used  Framework 4

    and i want show row into  the  dataset datatable   by  query


    Name of Allah, Most Gracious, Most Merciful and He created the human


    • Edited by sh 2020 Monday, November 27, 2017 3:10 PM
    Monday, November 27, 2017 2:08 PM
  • Take a look at the following example I did on MSDN.

    When looking at the code notice I separate database operations into a class project. I created the columns in the DataGridView designer and set the DataProperty there also.

    There is a lot of code yet all you need is (a slimmed down version from the code sample)

    Public Class Operations 
        ''' <summary> 
        ''' Create our database connection string smartly 
        ''' </summary> 
        Private Builder As New OleDbConnectionStringBuilder With 
            { 
                .DataSource = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "CustomersDatabase.accdb"), 
                .Provider = "Microsoft.ACE.OLEDB.12.0" 
            } 
    
        Public Function ReadCustomers() As DataTable 
     
            Dim dt As New DataTable 
            Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString} 
                Using cmd As New OleDbCommand With {.Connection = cn} 
     
                    cmd.CommandText = 
                        <SQL> 
                            SELECT  
                                Identifier,  
                                CompanyName,  
                                ContactName,  
                                ContactTitle,  
                                Address,  
                                City,  
                                PostalCode,  
                                Country  
                            FROM  
                                Customers 
                            ORDER BY CompanyName 
                        </SQL>.Value 
     
                    Try 
     
                        cn.Open() 
                        dt.Load(cmd.ExecuteReader) 
     
     
                    Catch ex As Exception 
    		    ' decide how to handle the error e.g. look how I did it in the code sample
                    End Try 
                End Using 
            End Using 
     
            Return dt 
     
        End Function 
    
    End Class 
    

    Form code

    ' Operations class
    Imports BackEnd 
    Public Class Form1 
        Private ops As New Operations 
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load 
            ' indicate we are taken care of managing columns, in this case 
            ' columns have been configured in the designer 
            dgvCustomers.AutoGenerateColumns = False 
    
            dgvCustomers.DataSource = ops.ReadCustomers 
        End Sub 
    End Class 
    
    The Operation class code could be taken out of the class and placed directly in the form yet it's best to keep them separated.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by sh 2020 Thursday, November 30, 2017 2:14 PM
    Monday, November 27, 2017 3:27 PM
    Moderator