Solved

DataTable problem in VB.Net 2005

Posted on 2006-07-10
10
2,927 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
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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

820 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