Display Access table in Datagridview-VBForums
Results 1 to 25 of 25

Thread: Display Access table in Datagridview

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Nov 2005
    Posts
    66

    Display Access table in Datagridview

    Hello, I have a table in Access called 'tblRealTime' with a few rows of data and I want to display it in a DataGridView object on my form. When I run the program I get no errors but nothing shows up in the datagrid. My code is below:

    vb.net Code:
    1. Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    2.        
    3.         ConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\K0201227Project.accdb;Persist Security Info=False;"
    4.  
    5.         SQLStr = "SELECT * FROM tblRealTime"
    6.  
    7.  
    8.         Dim OleDBConn As New OleDbConnection() 'The OleDB Connection
    9.         Dim OleDBCmd As New OleDbCommand() 'The OleDB Command
    10.         Dim OleDBdr As OleDbDataReader        'The Local Data Store
    11.  
    12.         OleDBConn.ConnectionString = ConnString 'Set the Connection String
    13.         OleDBConn.Open() 'Open the connection
    14.  
    15.         OleDBCmd.Connection = OleDBConn
    16.         OleDBCmd.CommandText = SQLStr 'Sets the SQL String
    17.         OleDBdr = OleDBCmd.ExecuteReader 'Gets Data
    18.  
    19.  
    20.         'create new dataset
    21.         Dim ds As New DataSet
    22.  
    23.         Dim da As New OleDbDataAdapter(SQLStr, OleDBConn)
    24.         da.Fill(ds, "tblRealTime")
    25.      
    26.         DataGridView1.DataSource = ds.DefaultViewManager
    27.        
    28.  
    29.              OleDBdr.Close() 'Close the SQLDataReader        
    30.  
    31.              OleDBConn.Close() 'Close the connection

    Anybody know why it isn't working?

    Thanks
    Amy
    Last edited by Hack; Apr 7th, 2010 at 10:23 AM. Reason: Added Highlight Tags

  2. #2
    #28 for the Yanks coming GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    6,900

    Re: Display Access table in Datagridview

    Please use code tags when posting....

    Please post in correct area (i'll let the mods know) this has nothing to do with network programing.

    Why are you opening a datareader and a datatable?
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Nov 2005
    Posts
    66

    Re: Display Access table in Datagridview

    Hi Gary

    oops I didnt to post in that forum, i have reposted in the vb.net forum. i dont know why im opening a datareader and datatable, im sort of using bits and pieces of code and putting it together to try and get it to work! i am struggling to understand what each bit does.

  4. #4
    Super Moderator Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    Re: Display Access table in Datagridview

    Moved To VB.NET

    Thanks for the report Gary!
    Please use [Code]your code goes in here[/Code] tags when posting code.
    When you have received an answer to your question, please mark it as resolved using the Thread Tools menu.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    I dont answer coding questions via PM or EMail. Please post a thread in the appropriate forum section.

    Creating A Wizard In VB.NET
    Paging A Recordset
    What is wrong with using On Error Resume Next
    Good Article: Language Enhancements In Visual Basic 2010
    Upgrading VB6 Code To VB.NET
    Microsoft MVP 2005/2006/2007/2008/2009/2010/2011/2012/Defrocked

  5. #5
    #28 for the Yanks coming GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    6,900

    Re: Display Access table in Datagridview

    Try something like this:

    vb.net Code:
    1. Dim oCmd As System.Data.SqlClient.SqlCommand
    2.         Dim oDA As System.Data.SqlClient.SqlDataAdapter
    3.         Dim oDS As New DataSet
    4.  
    5.         oCmd = New System.Data.SqlClient.SqlCommand()
    6.         Try
    7.             oCmd.Connection = New System.Data.SqlClient.SqlConnection(ConnectionstringgoesHere)
    8.             oCmd.Connection.Open()
    9.             oCmd.CommandType = CommandType.Text
    10.             oCmd.CommandText = SQL that you want to run goes here
    11.             oCmd.CommandTimeout = intTimeOut
    12.             oCmd.CommandTimeout = 6000
    13.             oDA = New System.Data.SqlClient.SqlDataAdapter(oCmd)
    14.  
    15.             oDA.Fill(oDS)
    16.             oDA.Dispose()
    17.             grid.DataSource = oDS.Tables(0)
    18.         Catch ex As Exception
    19.             mdlGeneral.errMessage.intErrNum = Err.Number
    20.             mdlGeneral.errMessage.strMess = Err.Description
    21.            
    22.         Finally
    23.             oCmd.Connection.Close()
    24.             oCmd.Dispose()
    25.             oDS.Dispose()
    26.         End Try
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  6. #6
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    89,103

    Re: Display Access table in Datagridview

    Gary's code can actually be condensed down quite a bit:
    vb.net Code:
    1. Using adapter As New SqlDataAdapter("SQL query here", "connection string here")
    2.     Dim table As New DataTable
    3.  
    4.     Try
    5.         adapter.Fill(table)
    6.         grid.DataSource = table
    7.     Catch ex As Exception
    8.         '...
    9.     End Try
    10. End Using
    The adapter can handle a lot implicitly for you if you don't need to change the defaults, like creating and destroying the connection and the command and opening and closing the connection. The Using block takes care of destroying the adapter.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  7. #7
    Lively Member
    Join Date
    May 2009
    Location
    EG
    Posts
    84

    Re: Display Access table in Datagridview

    I'm sorry but can i display table contents into datagrid using Connected-Mode with no DataSet, using the DataReader ?!

  8. #8
    PowerPoster kevininstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    5,934

    Re: Display Access table in Datagridview

    Quote Originally Posted by evry1falls View Post
    I'm sorry but can i display table contents into datagrid using Connected-Mode with no DataSet, using the DataReader ?!
    Here is a bare-bones example which opens a local SQL-Server (Microsoft NorthWind database) database, selects data from two tables then populates a DataGridView where the columns have been created already in the DataGridView at design time. Remember that a DataReader is forward only.

    Code:
    Imports System.Data.SqlClient
    Public Class frmManualConnect
        Private Sub frmManualConnect_Load(
            ByVal sender As System.Object,
            ByVal e As System.EventArgs) _
        Handles MyBase.Load
    
            Using cn As New SqlClient.SqlConnection With
                {
                    .ConnectionString =
                    <T>
                        Data Source=.\SQLEXPRESS;
                        AttachDbFilename=|DataDirectory|\NORTHWND.MDF;
                        Integrated Security=True;
                        Connect Timeout=30;
                        User Instance=True
                    </T>.Value
                }
    
                Dim SQL = _
                <SQL>
                    SELECT     
                        Employees.LastName, 
                        Employees.FirstName, 
                        Employees.Title, 
                        Territories.TerritoryDescription
                    FROM         
                        Employees INNER JOIN
                            EmployeeTerritories ON 
                                Employees.EmployeeID = EmployeeTerritories.EmployeeID 
                    INNER JOIN Territories ON 
                        EmployeeTerritories.TerritoryID = Territories.TerritoryID
                    ORDER BY Employees.LastName
                </SQL>.Value
    
                Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand(SQL, cn)
    
                cn.Open()
    
                Dim reader = cmd.ExecuteReader
    
                If reader.HasRows Then
                    Do While reader.Read
                        DataGridView1.Rows.Add(New Object() _
                            {
                                reader.GetString(0),
                                reader.GetString(1),
                                reader.GetString(2),
                                reader.GetString(3)
                            }
                        )
                    Loop
                    reader.Close()
                End If
                cn.Close()
    
            End Using
        End Sub
    End Class

  9. #9
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    89,103

    Re: Display Access table in Datagridview

    Quote Originally Posted by evry1falls View Post
    I'm sorry but can i display table contents into datagrid using Connected-Mode with no DataSet, using the DataReader ?!
    Why would you want to? It takes more code to populate the grid manually and it's more error prone.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  10. #10
    Lively Member
    Join Date
    May 2009
    Location
    EG
    Posts
    84

    Re: Display Access table in Datagridview

    OK here is what i did, and it worked fine with me :
    Code:
    Private Sub DataG()
    Dim CMD As New OLEDB.OLEDBCommand
    Dim DataR As Oledb.OledbDataReader
     Dim MyTable As New DataTable
            CMD.Connection = CN
            CMD.CommandText = "Select * From TblNm"
            DataR = CMD.ExecuteReader
            Dim I As Integer
            For I = 0 To MyTable.Rows.Count - 1
                DataR.Read()
            Next
            MyTable.Load(DataR)
            DG1.DataSource = MyTable
            DG1.Refresh()
            DataR.Close()
             End Sub


    Actually besides it is a successful method to retrieve data from table into DataGridView, it also a beautiful way to display updates in DataGridView at runtime, i.e. (If you added new record to your database table through button1, and included SUB [DataG] at the end of your Button_Click it will display the new added record+the records already has been saved before.
    Last edited by evry1falls; May 22nd, 2012 at 12:02 PM.

  11. #11
    PowerPoster kevininstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    5,934

    Re: Display Access table in Datagridview

    Quote Originally Posted by evry1falls View Post
    OK here is what i did, and it worked fine with me :
    Code:
    Private Sub DataG()
    Dim CMD As New OLEDB.OLEDBCommand
    Dim DataR As Oledb.OledbDataReader
     Dim MyTable As New DataTable
            CMD.Connection = CN
            CMD.CommandText = "Select * From TblNm"
            DataR = CMD.ExecuteReader
            Dim I As Integer
            For I = 0 To MyTable.Rows.Count - 1
                DataR.Read()
            Next
            MyTable.Load(DataR)
            DG1.DataSource = MyTable
            DG1.Refresh()
            DataR.Close()
             End Sub


    Actually besides it is a successful method to retrieve data from table into DataGridView, it also a beautiful way to display updates in DataGridView at runtime, i.e. (If you added new record to your database table through button1, and included SUB [DataG] at the end of your Button_Click it will display the new added record+the records already has been saved before.
    There is no good reason to cycle thru data in the DataReader, all you need to do is work with the data loaded into the DataTable. For adding and editing data within the DataTable there are easier methods also. There are several ways to implement where JMC and Gary showed several in this thread.

  12. #12
    Lively Member
    Join Date
    May 2009
    Location
    EG
    Posts
    84

    Re: Display Access table in Datagridview

    Quote Originally Posted by kevininstructor View Post
    There is no good reason to cycle thru data in the DataReader, all you need to do is work with the data loaded into the DataTable. For adding and editing data within the DataTable there are easier methods also. There are several ways to implement where JMC and Gary showed several in this thread.
    When i worked with DataTable Data before, it loaded the table's data into the DataGrid always without the first record.If i had 3 records in my table it shows only 2 without the 1st one ... See this

  13. #13
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    89,103

    Re: Display Access table in Datagridview

    Quote Originally Posted by evry1falls View Post
    When i worked with DataTable Data before, it loaded the table's data into the DataGrid always without the first record.If i had 3 records in my table it shows only 2 without the 1st one ... See this
    That's probably because you called Read on the data reader, which advances the reader one record, which will probably then exclude that record from the DataTable. Just call ExecuteReader and then Load. Do that and it will work. Load will internally call Read on the data reader to get the records so, if you've already called Read, those records will be inaccessible to the DataTable.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  14. #14
    Lively Member
    Join Date
    May 2009
    Location
    EG
    Posts
    84

    Re: Display Access table in Datagridview

    Quote Originally Posted by jmcilhinney View Post
    That's probably because you called Read on the data reader, which advances the reader one record, which will probably then exclude that record from the DataTable. Just call ExecuteReader and then Load. Do that and it will work. Load will internally call Read on the data reader to get the records so, if you've already called Read, those records will be inaccessible to the DataTable.
    I've tried it, and it didn't work at all, the DataGrid did not even load any data, if you could provide an example to show i would appreciate it. Here is my example

  15. #15
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    89,103

    Re: Display Access table in Datagridview

    First, don't use a data reader and a data adapter. Either use a data reader and call Load or a data adapter and call Fill. Which one to use depends on whether you want to edit and save the data again.

    Also, most likely the reason that you grid didn't show anything is not because the DataTable wasn't populated but because you didn't tell the grid which DataTable to use. If you're going to use a DataSet then you need to set the DataMember as well as the DataSource.

    Follow the CodeBank link in my signature and check out my thread on Retrieving & Saving Data. Once you have a populated DataTable you simply assign that to the DataSource of the grid.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  16. #16
    Lively Member
    Join Date
    May 2009
    Location
    EG
    Posts
    84

    Re: Display Access table in Datagridview

    Quote Originally Posted by jmcilhinney View Post
    First, don't use a data reader and a data adapter. Either use a data reader and call Load or a data adapter and call Fill. Which one to use depends on whether you want to edit and save the data again.

    Also, most likely the reason that you grid didn't show anything is not because the DataTable wasn't populated but because you didn't tell the grid which DataTable to use. If you're going to use a DataSet then you need to set the DataMember as well as the DataSource.

    Follow the CodeBank link in my signature and check out my thread on Retrieving & Saving Data. Once you have a populated DataTable you simply assign that to the DataSource of the grid.
    WHY?
    I already know how to work with both (Online) and (Offline) modes using DataSet and DataReade. The question was "Is it possible to use the DataReader to populate data from access table into a DataGrid ?!!" . I know i can use the DataSet to do so ..

  17. #17
    PowerPoster kevininstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    5,934

    Re: Display Access table in Datagridview

    Quote Originally Posted by evry1falls View Post
    WHY?
    I already know how to work with both (Online) and (Offline) modes using DataSet and DataReade. The question was "Is it possible to use the DataReader to populate data from access table into a DataGrid ?!!" . I know i can use the DataSet to do so ..
    If you look at my reply it shows how to populate a DataGridView with a DataReader.

  18. #18
    Fanatic Member
    Join Date
    Aug 2006
    Location
    In my head
    Posts
    913

    Re: Display Access table in Datagridview

    WHY?
    I already know how to work with both (Online) and (Offline) modes using DataSet and DataReade. The question was "Is it possible to use the DataReader to populate data from access table into a DataGrid ?!!" . I know i can use the DataSet to do so ..
    This should hopefully help you to understand a little more about the differences and when to use each one. I personally rarely use datareader, only because I often need to filter, sort and redisplay the data I am returning from the database.

    Differences and Benefits

    Hope this helps!!

    D
    Platforms of choice: Visual Studio 2005/2008 Professional : Visual Studio 2010 Enterprise : PHP - Notepad++/WAMP

    Please Rate If I helped you.
    Please remember to mark threads as closed if your issue has been resolved.

    Reserved Words in Access | Connection Strings

  19. #19
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    89,103

    Re: Display Access table in Datagridview

    Quote Originally Posted by evry1falls View Post
    WHY?
    I already know how to work with both (Online) and (Offline) modes using DataSet and DataReade. The question was "Is it possible to use the DataReader to populate data from access table into a DataGrid ?!!" . I know i can use the DataSet to do so ..
    Why what? You quote a post of mine that contains three paragraphs and all you say is "Why"? How am I supposed to know what you're referring to?

    If you already know how to use a data reader then what's the problem? All a data reader does is read data from the result set of a command. That's it. What you do with the data is up to you. If you want to populate a grid directly then by all means go ahead, but that has nothing to do with the data reader. Your question is like saying that you already know how to use a tap but you want to know if you can use a tap to fill a glass. A tap just pumps out water. What you do with the water that comes out is up to you and nothing to do with the tap.

    What you cannot do is bind a data reader. The DataSource of the grid requires an IList or and IListSource object. A data reader doesn't implement either of those interfaces so it cannot be bound. That's why it's advisable to populate a DataTable, which is an IListSource and can be bound. If you want to populate the grid manually then you can do so but using a data reader is the same regardless of where the data goes to and populating a grid is the same regardless of where the data comes from.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  20. #20
    Lively Member
    Join Date
    May 2009
    Location
    EG
    Posts
    84

    Re: Display Access table in Datagridview

    Quote Originally Posted by jmcilhinney View Post
    Why what? You quote a post of mine that contains three paragraphs and all you say is "Why"? How am I supposed to know what you're referring to?

    If you already know how to use a data reader then what's the problem? All a data reader does is read data from the result set of a command. That's it. What you do with the data is up to you. If you want to populate a grid directly then by all means go ahead, but that has nothing to do with the data reader. Your question is like saying that you already know how to use a tap but you want to know if you can use a tap to fill a glass. A tap just pumps out water. What you do with the water that comes out is up to you and nothing to do with the tap.

    What you cannot do is bind a data reader. The DataSource of the grid requires an IList or and IListSource object. A data reader doesn't implement either of those interfaces so it cannot be bound. That's why it's advisable to populate a DataTable, which is an IListSource and can be bound. If you want to populate the grid manually then you can do so but using a data reader is the same regardless of where the data goes to and populating a grid is the same regardless of where the data comes from.
    Ok, so you are saying that i can use both DataSet and DataReader to populate a datagrid.

  21. #21
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    89,103

    Re: Display Access table in Datagridview

    What I'm saying is that a data reader reads data from a database and that's it. You cannot bind that data reader to WinForms controls so, if you want the data you read to populate a DataGridView, it's up to you to write code to populate it manually. That has NOTHING WHATSOEVER to do with the data reader. If you populate a DataTable though, whether using a data adapter or from a data reader, you can then simply bind that DataTable to the grid and the grid will be populated automatically.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  22. #22
    Lively Member
    Join Date
    May 2009
    Location
    EG
    Posts
    84

    Re: Display Access table in Datagridview

    OK, i won't disagree.
    last thing (True or False) question :

    - You can display data stored in MS Access 2007 database table into a datagridview control using OleDBDataReader and update the data displayed in the datagridview with no problems at run-time using VB .Net 2010 .

  23. #23
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    89,103

    Re: Display Access table in Datagridview

    May I ask why you are so determined to use a data reader? Given that the data adapter exists to retrieve and save data and that's what you want to do, why are you trying so hard not to use it? Do you bang nails in with a screwdriver?

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  24. #24
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    89,103

    Re: Display Access table in Datagridview

    The thing I've trying to get through is that how you move data between your app and the database and how you display data to the user and let them edit it have nothing whatsoever to do with each other. They are two completely separate operations. You use them both together in your app because you need to do both but they are two separate things. You can retrieve data with a data reader and you can save data. The fact that you display that data in a DataGridView in between has absolutely no impact on your ability to retrieve and save that data.

    The data reader is intended for when you want to read data row by row and use it row by row. It can also be used to populate a DataTable but it provides no way to save changes to that data. If that's not what you want to do then you shouldn't be using a data reader. The data adapter exists specifically to allow you to retrieve data, populate a DataTable and then save changes from that DataTable. Given that you want to display a table of data to the user in a grid, there's no reason for you to not use a DataTable. Given that you want to retrieve data and save changes, there's no reason for you not use a data adapter. Your determination to use the wrong tools for the job at hand makes no sense. If you can provide a logical explanation then I'm all ears but, otherwise, you're trying hard to do it wrong.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  25. #25
    Lively Member
    Join Date
    May 2009
    Location
    EG
    Posts
    84

    Re: Display Access table in Datagridview

    Sir,
    your words mean that YES i can retrieve data from the table using DataReader into DataGrid and Update it.

    Thanx

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.