473,395 Members | 1,969 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,395 software developers and data experts.

Help with a DataGridView

Hi all,
I'm loading a datagridview from an Excel file using a dataadapter.fill()
method. A few questions:

1) Is there any way to control the column datatypes? The columns are
defined by the .fill method based on the excel file, and the data is read in
during the .file method, so everything happens with this one command. If I
try to define a column.datatype before calling .fill I get an error because
the column doesn't exist yet, if I try to define the column.datatype after
..fill I get an error because you can't change a columns datatype once data
exists in the datatable.

2) Is there any way to have a progress bar monitor the progress of the .fill
method. Again everything happens with this single command so I don't have
anywhere to place code.

Heres my code:

Dim connString As String = "Driver={Microsoft Excel Driver (*.xls)};DBQ=" &
Me.OpenFileDialog1.FileName
Dim da As New System.Data.Odbc.OdbcDataAdapter

Dim ds As DataSet = New DataSet

Dim myRecords As Integer

Using cn As New System.Data.Odbc.OdbcConnection(connString)

cn.Open()

da.SelectCommand = New System.Data.Odbc.OdbcCommand("Select * from
[Sheet1$]", cn)

myRecords = da.Fill(ds)

Me.DataGridViewCoFile.DataSource = ds.Tables(0)

End Using

Thanks,

Ryan
Jan 31 '07 #1
2 3389
Hi Ryan,

As for your first question, you could create columns with the data types
you'd like and add them to the DataTable before you call the Fill method of
the DataAdapter.

If each column you add into the DataTable has the same name as the
corresponding column in the source table does in the Excel file, you could
just pass the DataTable's name as the second parameter in the Fill method.
For example, if the DataTable's name is DataTable1, the statement should
look like blow:

da.Fill(ds,"DataTable1")

Note that you should ensure the data in the source table could be converted
to the data type you specify in the DataTable.

In addition, if all the names of the columns you create and add into the
DataTable are not same as those in the source table, you should use
DataColumn mappings to specify the mapping between the columns in the
source table and those in the DataTable.

For more information on DataTableMapping and DataColumn Mapping, you may
visit the following link.
'Setting Up DataTable and DataColumn Mappings'
http://msdn2.microsoft.com/en-us/ks92fwwh.aspx

As for your second question, since you'd like a progress bar to monitor the
progress of filling the DataTable, you'd better do the work of 'filling' in
a separate thread. I suggest that you use a BackgroundWorker component to
do it, which is very suitable to execute an operation on a separate thread
and provides a good mechanism to report the progress of the underground
work.

For more information on BackgroundWorker class and how to use it, you may
visit the following link.
'BackgroundWorker Class'
http://msdn2.microsoft.com/en-us/lib....backgroundwor
ker.aspx

There remains another question,i.e. how to know the progress of filling the
DataTable. I suggest that you use a timer component to do that. Start the
timer just before the filling work begins, and then check the progress of
the work in the Tick event handler of the timer (you may get the total row
count in the source table in advance and check the current row count in the
DataTable, then return the percent). When the underground work finishes,
stop the timer.

Hope this helps.
If you have anything unclear, please feel free to let me know.

Sincerely,
Linda Liu
Microsoft Online Community Support

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

Feb 1 '07 #2
Thanks Linda! This works :)

"Linda Liu [MSFT]" <v-****@online.microsoft.comwrote in message
news:K1****************@TK2MSFTNGHUB02.phx.gbl...
Hi Ryan,

As for your first question, you could create columns with the data types
you'd like and add them to the DataTable before you call the Fill method
of
the DataAdapter.

If each column you add into the DataTable has the same name as the
corresponding column in the source table does in the Excel file, you could
just pass the DataTable's name as the second parameter in the Fill method.
For example, if the DataTable's name is DataTable1, the statement should
look like blow:

da.Fill(ds,"DataTable1")

Note that you should ensure the data in the source table could be
converted
to the data type you specify in the DataTable.

In addition, if all the names of the columns you create and add into the
DataTable are not same as those in the source table, you should use
DataColumn mappings to specify the mapping between the columns in the
source table and those in the DataTable.

For more information on DataTableMapping and DataColumn Mapping, you may
visit the following link.
'Setting Up DataTable and DataColumn Mappings'
http://msdn2.microsoft.com/en-us/ks92fwwh.aspx

As for your second question, since you'd like a progress bar to monitor
the
progress of filling the DataTable, you'd better do the work of 'filling'
in
a separate thread. I suggest that you use a BackgroundWorker component to
do it, which is very suitable to execute an operation on a separate thread
and provides a good mechanism to report the progress of the underground
work.

For more information on BackgroundWorker class and how to use it, you may
visit the following link.
'BackgroundWorker Class'
http://msdn2.microsoft.com/en-us/lib....backgroundwor
ker.aspx

There remains another question,i.e. how to know the progress of filling
the
DataTable. I suggest that you use a timer component to do that. Start the
timer just before the filling work begins, and then check the progress of
the work in the Tick event handler of the timer (you may get the total row
count in the source table in advance and check the current row count in
the
DataTable, then return the percent). When the underground work finishes,
stop the timer.

Hope this helps.
If you have anything unclear, please feel free to let me know.

Sincerely,
Linda Liu
Microsoft Online Community Support

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
==================================================

This posting is provided "AS IS" with no warranties, and confers no
rights.

Feb 6 '07 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: sapkal.manish | last post by:
Question : How can I find actual row position of DataTable in DataGridView when DataTable is sorted / My source code like this Private WithEvent dgvInwDet as new DataGridView Private...
1
by: Riley | last post by:
Need some help. I am using datagridview in 2005 with C#. I am calling a web service and filling the datagridview with the data returned. This all works fine. Next step is to take a value from one...
0
by: MKBender | last post by:
Hello everyone, What I'm currently trying to do is allow a user to add a row to my datagridview. I have AllowUserToAddRows set to true. And the empty now does appear at the bottom of my data. ...
14
by: giddy | last post by:
ok i have a datagridview in my app and i have a function defined like so: public void FillColumns(ref DataGridView theDGV) when i call it ... like this : FillColumns(ref dataGridView2); i...
9
by: trint | last post by:
How can I retrieve all of the data from a datagridview? Any help is appreciated. Thanks, Trint
1
by: lbelkova | last post by:
Hello, I've created a custom DataGridViewColumn. Everything work well, except for some reason the column doesn't accept some of the chars: "q", "." and "'". Did anybody have a similar problem?...
1
by: slikrik98 | last post by:
Greetings, I believe I have narrowed down my issues to one simple question, and I'm hoping someone with async events experience can help me out. My question is: how is using...
6
by: Simon Harvey | last post by:
Hi all, I'm really hoping someone can help me with this as it's causing me some serious problems. I have a Windows Forms application using the gridview control. When the user selects a row,...
9
by: Miro | last post by:
My current headache is proper is with the datagridview I am starting to realize that a DataGridView within vs2008 is not as 'robust' as a 'textboxfield' by default for example. Example: A...
3
by: Andrus | last post by:
I have DataGridView in virtual mode containing 3500 rows. In code below, assigning to RowCount value to 3500 takes 8 seconds. CPU usage goes high at this time. Stepping by F11 into user code shows...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.