Solved

DataTable problem in VB.Net 2005

Posted on 2006-07-10
10
2,923 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
  • 5
  • 5
10 Comments
 
LVL 34

Expert Comment

by:Sancler
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:radhakrishan
Comment Utility
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
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 34

Expert Comment

by:Sancler
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Excellent. Thanks for ur help.

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

Expert Comment

by:Sancler
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

772 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now