Help with a DataGridView

[Ryan]

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

 

[Linda Liu [MSFT]]

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/library/system.componentmodel.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/subscriptions/managednewsgroups/default.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/subscriptions/support/default.aspx.
==================================================

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

 

[Ryan]

Thanks Linda! This works

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/library/system.componentmodel.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/subscriptions/managednewsgroups/default.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/subscriptions/support/default.aspx.
==================================================

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