I am trying to transfer data from an excel file to a database table. I am able to collect the data into a dataset table. However, I need to store the dataset table into an sql server database table.
Any assistance will be appreicated
Thanks.
Any assistance will be appreicated
VB.NET:
Below is the code that does this.
[SIZE=2][COLOR=#008000][COLOR=#000000]===============================================[/COLOR][/COLOR][/SIZE][COLOR=#008000]
[/COLOR][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] myFile [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = Server.MapPath("testxls.xls")[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] MyConnection [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbConnection( _[/SIZE]
[SIZE=2]"provider=Microsoft.Jet.OLEDB.4.0;data source=" & myFile & _[/SIZE]
[SIZE=2]";Extended Properties=Excel 8.0;")[/SIZE]
[SIZE=2]MyConnection.Open()[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] MyCmd [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] OleDbCommand[/SIZE]
[SIZE=2]MyCmd = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbCommand("select * from [Sheet1$]", MyConnection)[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] MyAdapter [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] OleDbDataAdapter = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbDataAdapter[/SIZE]
[SIZE=2]MyAdapter.SelectCommand = MyCmd[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] myDS [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataSet = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataSet[/SIZE]
[SIZE=2]MyAdapter.Fill([COLOR=red]myDS, "myTable")[/COLOR][/SIZE]
=================================================
Last edited by a moderator: