Solved

DataTable problem in VB.Net 2005

Posted on 2006-07-10
10
2,924 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Tabcontrol Caption? 1 31
Make a border less form movable 2 23
Populating an array of locale_time_info 8 37
VS.net 2010 11 24
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…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

895 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

18 Experts available now in Live!

Get 1:1 Help Now