Solved

DataTable problem in VB.Net 2005

Posted on 2006-07-10
10
2,929 Views
Last Modified: 2008-10-07
Hi,

Im new to VB.NET 2005, so pls need your help.

I have got a Datable which fetches data from Excel file:

dt = Excel.Import.Query("C:\l.xls", "a2:d3")

I just want to know how can I link this "dt" - datable to DATAGRIDVIEW or DATASET.

Pls advice.

Regards
0
Comment
Question by:radhakrishan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 34

Expert Comment

by:Sancler
ID: 17072762
Linking (a) to a DataGridView and (b) to a Dataset are, conceptually, entirely different things.

A DataSet is a container for one or more DataTables.  If, for some reason, you want your DataTable to become part of a particular DataSet you can achieve that with

   MyDataSet.Tables.Add(dt)

A DataGridView is a display (and editing) mechanism for a DataTable.  At its simplest, you can link - in the sense of "binding" - your DataTable to a DataGridView by

   MyDataGridView.DataSource = dt

Roger
0
 

Author Comment

by:radhakrishan
ID: 17072846
Thanks for much.

Once you get data from xls file into DatGridView then can we save this to a table in SQL Server ?

Regards
0
 
LVL 34

Expert Comment

by:Sancler
ID: 17073630
You don't need to get it into a DataGridView to do that.  It is the DataTable itself that contains the data: the DataGridView only displays it (and provides a mechanism for editing it).  So any saving to SQL (or any other database) would be done from the DataTable.

Does the target table already exist in the SQL database?  Are its fields/columns identical to that you have in the table brought over from Excel: same columns, same names, in the same order, with the same datatypes?  If so, the easiest approach is probably to declare an SQLDataAdapter, use it to fill another DataTable, copy each element from each row in the Excel-based DataTable to a NewRow in the SQL-based DataTable, and use the SQLDataAdapter to .Update the SQL database.  If not, you will need to code your own Insert statement and parameters for an SQLCommand, loop through each row of the Excel-based DataTable filling the SQLCommand's parameters and then call .ExecuteNonQuery on it.

I can't help more than that without knowing much more about the details of your data and set-up.

Roger
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:radhakrishan
ID: 17073740
Target table already exist in the SQL Database.

Datatable has got info from EXCEL file.

I just want that it should pick up each row from DATATABLE and then insert it into SQL table.

U said to use SQLDataAdapter. Can u explain or provide bit of code for this ?

Thank u so much for ur quicky and excellent advise.

Regards
0
 

Author Comment

by:radhakrishan
ID: 17073776
SQL table which exists on the server is a temp table which required fields and all set to STRING.

So what I want is that it should pick the data from DATATABLE (which has got data from excel file) and insert into SQL SERVER table.#
0
 
LVL 34

Expert Comment

by:Sancler
ID: 17074935
Here's some code that will do the transfer with dataadapters if all necessary conditions are met.

        Dim ExcelCon As New OleDbConnection(<your Excel connection string>)
        Dim ExcelAdapter As New OleDbDataAdapter(<your Excel select string>, ExcelCon)
        Dim ExcelTable As New DataTable
        ExcelAdapter.Fill(ExcelTable)
        Dim SQLCon As New SqlConnection(your SQL connection string>)
        Dim SQLAdapter As New SqlDataAdapter(<your SQL select string>, SQLCon)
        Dim SQLTable As New DataTable
        SQLAdapter.Fill(sqltable)
        Dim cb As New SqlCommandBuilder(SQLAdapter)
        For Each dr As DataRow In ExcelTable.Rows
            Dim ndr As DataRow = SQLTable.NewRow
            ndr.ItemArray = dr.ItemArray
            SQLTable.Rows.Add(ndr)
        Next
        SQLAdapter.Update(SQLTable)

Both "select strings" should be on the lines of "SELECT * FROM <your table/sheet name>".

If the Excel input is not already in string format, you will need to replace the line

            ndr.ItemArray = dr.ItemArray

with

            For i As Integer = 0 To ExcelTable.Columns.Count - 1
                ndr(i) = CStr(dr(i))
            Next

in order to convert all values to strings.

If not all the Excel columns are to be exported to the SQL table, or in a different order, you will need to map the columns specifically - for example -

           ndr(0) = dr(3)

and so on.

Anyway, try that, and we can look at specific problems if/when they arise.

Roger
0
 

Author Comment

by:radhakrishan
ID: 17080072
Excellent, works.

One more advise and then I will accept this "EXCELLENT" user.

I want the user to browse/select the file on their drive. Can u provide/advice code for this where user can select the file and that xls file will be used for "IMPORT" function.
0
 
LVL 34

Accepted Solution

by:
Sancler earned 500 total points
ID: 17080211
It sounds like you want a FileDialog - see this for example

http://msdn2.microsoft.com/en-us/library/system.windows.forms.openfiledialog.aspx

The example in that is not quite appropriate, but you could substitute .xls for .txt in the .Filter property and get the selected file's name from the .FileName property after an OK result.  You should be able to drag a FileDialog onto your form from the Toolbox in Design Mode on the IDE.

Roger
0
 

Author Comment

by:radhakrishan
ID: 17080225
Excellent. Thanks for ur help.

Last question: Can't we add two table to DATAGRIDVIEW ?
0
 
LVL 34

Expert Comment

by:Sancler
ID: 17080821
The basic answer is "No".  If you have two tables that are related - i.e. with the primary key of one being a foreign key in another - you can join the tables and display data from both.  See this, for example

http://www.windowsforms.net/Samples/Go%20To%20Market/DataGridView/DataGridView%20FAQ.doc#_Toc119903511

But I don't think that's what you have in mind.

Roger
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Suggested Courses

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question