Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2933
  • Last Modified:

DataTable problem in VB.Net 2005

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
radhakrishan
Asked:
radhakrishan
  • 5
  • 5
1 Solution
 
SanclerCommented:
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
 
radhakrishanAuthor Commented:
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
 
SanclerCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
radhakrishanAuthor Commented:
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
 
radhakrishanAuthor Commented:
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
 
SanclerCommented:
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
 
radhakrishanAuthor Commented:
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
 
SanclerCommented:
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
 
radhakrishanAuthor Commented:
Excellent. Thanks for ur help.

Last question: Can't we add two table to DATAGRIDVIEW ?
0
 
SanclerCommented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now