-
Apr 7th, 2010, 08:58 AM
#1
Thread Starter
Lively Member
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:
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
ConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\K0201227Project.accdb;Persist Security Info=False;"
SQLStr = "SELECT * FROM tblRealTime"
Dim OleDBConn As New OleDbConnection() 'The OleDB Connection
Dim OleDBCmd As New OleDbCommand() 'The OleDB Command
Dim OleDBdr As OleDbDataReader 'The Local Data Store
OleDBConn.ConnectionString = ConnString 'Set the Connection String
OleDBConn.Open() 'Open the connection
OleDBCmd.Connection = OleDBConn
OleDBCmd.CommandText = SQLStr 'Sets the SQL String
OleDBdr = OleDBCmd.ExecuteReader 'Gets Data
'create new dataset
Dim ds As New DataSet
Dim da As New OleDbDataAdapter(SQLStr, OleDBConn)
da.Fill(ds, "tblRealTime")
DataGridView1.DataSource = ds.DefaultViewManager
OleDBdr.Close() 'Close the SQLDataReader
OleDBConn.Close() 'Close the connection
Anybody know why it isn't working?
Thanks
Amy
Last edited by Hack; Apr 7th, 2010 at 09:23 AM.
Reason: Added Highlight Tags
-
Apr 7th, 2010, 09:05 AM
#2
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
-
Apr 7th, 2010, 09:13 AM
#3
Thread Starter
Lively Member
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.
-
Apr 7th, 2010, 09:24 AM
#4
Re: Display Access table in Datagridview
Moved To VB.NET
Thanks for the report Gary!
-
Apr 7th, 2010, 09:36 AM
#5
Re: Display Access table in Datagridview
Try something like this:
vb.net Code:
Dim oCmd As System.Data.SqlClient.SqlCommand
Dim oDA As System.Data.SqlClient.SqlDataAdapter
Dim oDS As New DataSet
oCmd = New System.Data.SqlClient.SqlCommand()
Try
oCmd.Connection = New System.Data.SqlClient.SqlConnection(ConnectionstringgoesHere)
oCmd.Connection.Open()
oCmd.CommandType = CommandType.Text
oCmd.CommandText = SQL that you want to run goes here
oCmd.CommandTimeout = intTimeOut
oCmd.CommandTimeout = 6000
oDA = New System.Data.SqlClient.SqlDataAdapter(oCmd)
oDA.Fill(oDS)
oDA.Dispose()
grid.DataSource = oDS.Tables(0)
Catch ex As Exception
mdlGeneral.errMessage.intErrNum = Err.Number
mdlGeneral.errMessage.strMess = Err.Description
Finally
oCmd.Connection.Close()
oCmd.Dispose()
oDS.Dispose()
End Try
Sometimes the Programmer
Sometimes the DBA
Mazz1
-
Apr 8th, 2010, 12:42 AM
#6
Re: Display Access table in Datagridview
Gary's code can actually be condensed down quite a bit:
vb.net Code:
Using adapter As New SqlDataAdapter("SQL query here", "connection string here") Dim table As New DataTable Try adapter.Fill(table) grid.DataSource = table Catch ex As Exception '... End Try 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.
-
May 21st, 2012, 11:12 AM
#7
Lively Member
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 ?!
-
May 21st, 2012, 01:33 PM
#8
Re: Display Access table in Datagridview
Originally Posted by evry1falls
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
-
May 21st, 2012, 07:14 PM
#9
Re: Display Access table in Datagridview
Originally Posted by evry1falls
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.
-
May 22nd, 2012, 10:55 AM
#10
Lively Member
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 11:02 AM.
-
May 22nd, 2012, 11:11 AM
#11
Re: Display Access table in Datagridview
Originally Posted by evry1falls
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.
-
May 23rd, 2012, 08:54 PM
#12
Lively Member
Re: Display Access table in Datagridview
Originally Posted by kevininstructor
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
-
May 23rd, 2012, 09:16 PM
#13
Re: Display Access table in Datagridview
Originally Posted by evry1falls
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.
-
May 24th, 2012, 04:46 AM
#14
Lively Member
Re: Display Access table in Datagridview
Originally Posted by jmcilhinney
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
-
May 24th, 2012, 05:35 AM
#15
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.
-
May 24th, 2012, 09:33 AM
#16
Lively Member
Re: Display Access table in Datagridview
Originally Posted by jmcilhinney
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 ..
-
May 24th, 2012, 09:46 AM
#17
Re: Display Access table in Datagridview
Originally Posted by evry1falls
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.
-
May 24th, 2012, 10:00 AM
#18
Fanatic Member
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
-
May 24th, 2012, 08:12 PM
#19
Re: Display Access table in Datagridview
Originally Posted by evry1falls
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.
-
May 25th, 2012, 07:03 AM
#20
Lively Member
Re: Display Access table in Datagridview
Originally Posted by jmcilhinney
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.
-
May 25th, 2012, 07:25 AM
#21
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.
-
May 26th, 2012, 10:40 AM
#22
Lively Member
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 .
-
May 26th, 2012, 11:05 AM
#23
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?
-
May 26th, 2012, 11:15 AM
#24
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.
-
May 27th, 2012, 06:45 PM
#25
Lively Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|