Importing excel to db

[newbee0000]

Dears,

I need to to do three things in my form..

1) Import an Excel file into Datagrid
2) Make changes in the grid
3) Save the grid back it into Sql Server Database.

i am not able to think about way of achieving the third step.

any help please.

 

[jmcilhinney]

1. Use ADO.NET to populate a DataTable.
2. Bind the DataTable to your grid.
3. Use ADO.NET to save the data to the database.

You'll need to use OleDb for step 1 but you can use OleDb or SqlClient for step 3. If you use OleDb for both then you only need one OleDbDataAdapter but two OleDbConnections. Either way you only need one DataTable.

The trick is to set the AcceptChangesDuringFill property of your OleDbDataAdapter to False, so that the RowState of all DataRows remains Added. That way, when you call Update to save the data, all those rows will be inserted into SQL Server.

See www.connectionststrings.com for all the connections string formats.

 

[cjard]

ConnectionStrings.com - Forgot that connection string? Get it here! will give you a connection string to read your excel file into a datatable.

If the column names are different in each file, you may find it easier to:

Create a typed datatable using the wizard, linked to sqlserver
Read the excel file into a generic dataset
Transfer the data into the typed dataset
Show it on screen
Save it

 

[newbee0000]

Thanks a lot,

i am new. can you refere to any article, i can follow and do the steps you recommended....

will be great help.

Thanks a lot a again for taking for your time and effort.

 

[cjard]

See the DW2 link in my signature; Creating a Simple Data App

As for reading excel files, see ConnectionStrings.com - Forgot that connection string? Get it here!, then look up how to use a DataAdapter. It's not hard. Something like this:

Dim da as New DataAdapter("SELECT * FROM [Sheet1]", YOUR_CONNECTION_STRING)
Dim ds as New DataSet
da.Fill(ds)

'now ds contains teh excel file data

 

[newbee0000]

I populate the Grid using this

Dim DtSet As System.Data.DataSet

Dim MyCommand As System.Data.OleDb.OleDbDataAdapter

Dim str As String

str = "provider=Microsoft.Jet.OLEDB.4.0;data source='" & FileName_txt.Text & "';Extended Properties=Excel 8.0;"
MyConnection = New System.Data.OleDb.OleDbConnection(str)

' Select the data from Sheet1 of the workbook.

MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [sheet1$] where code is not null", MyConnection)

MyCommand.TableMappings.Add("Table", "ORder")

DtSet = New System.Data.DataSet

MyCommand.Fill(DtSet)

Each Column represents each country , which i have to update in a row. i added a table with lot of columns. i was thinking if i can dump the grid in that table from there i can update the tables i require to.

myConnection = New SqlConnection("Server=Dev;uid=sa;pwd=good;database=kbsys")

from here ...i am lost

 

[cjard]

youre reading the excel file. good. now go and read the dw2 link like i told you and learn how to work with sql server.

then copy the data from the DtSet.Table1 table row by row into your sql table and upload it.


If it's easier, you can save the excel file on the server where sqlserver is, and then make sqlserver link to it and read the data..