Uploading Excel Data to Dataset, then DataSet to SQL Server

[jsc0624]

Hi Guys,

What I am thinking is this? Is it possible for me to upload the data from an excel file to dataset of my application first, so that the user can view the data in a gridview to review it first, before the user strike the save button, to save it in the database. So, that in case there is a problem, the gridview will high light all the data with an error. So the user can easily pull out the excel and correct the data before saving it in the database.

Thank you in advance.

[gep13]

Hey,

This is certainly possible.

One thing that I would point out though is that SQL Server has a built in migration tool that will do this work for you. Have you though about using this, rather than implementing your own, or is this part of a business process that you need to meet?

Gary

[jsc0624]

This is part of a business process, because the users need to check the data they are trying to upload before they can save the data in the database. Do you know a tutorial that can help me to start with? Thanks

[gep13]

Hey,

Basically what you need to get to grips with is ADO.Net. There are some links in my signature that will help you there.

Create a connection to the Excel Database, and get the information that you need. Present that to the end user, then, with another connection to the SQL Server, update/insert the rows that you need to.

Gary

[mendhak]

How To Use ADO.NET to Retrieve and Modify Records in an Excel Workbook With Visual Basic .NET

Pay attention to the connection string and to the special form that the queries take. You'll want to either fill up a dataset with the data from the Excel sheet or create a List(Of YourCustomClass) and fill that up. When it's time to save, loop through and run a SqlCommand.ExecuteNonQuery() for each row.