Update sql server table with dataset from xls file

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a program that imports data from an excel spreadsheet into a dataset. I then create a connection to sql server and check to see if a table exist, drop it and recreate it. I cant figure out how to poulate the newly create sql server table with the dataset from excel. any help would be great. Here is the code:

Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

' Create connection string variable for excel.
Dim a As String = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=c:\supplier requirements.xls;" _
& "Extended Properties=Excel 8.0;"

' Create the connection object by using the preceding connection string.
Dim objConn As New OleDbConnection(a)
' Open connection with the database.
objConn.Open()
' The code to follow uses a SQL SELECT command to display the data from the worksheet.
' Create new OleDbCommand to return data from worksheet.
Dim objCmdSelect As New OleDbCommand("Select * from [supplier requirements$]", objConn)
' Create new OleDbDataAdapter that is used to build a DataSet
' based on the preceding SQL SELECT statement.
Dim objAdapter As New OleDbDataAdapter()
' Pass the Select command to the adapter.
objAdapter.SelectCommand = objCmdSelect
' Create new DataSet to hold information from the worksheet.
Dim objDataset As New DataSet()
' Fill the DataSet with the information from the worksheet.
objAdapter.AcceptChangesDuringFill = False
objAdapter.Fill(objDataset, "XLData")
' Build a table from the original data.
DataGrid1.DataSource = objDataset.Tables(0).DefaultView

'get row count

Dim rows As DataRow()
Dim numberofrows As Integer
rows = objDataset.Tables(0).Select()
numberofrows = rows.Length

Me.DataGrid1.CaptionText = CStr(numberofrows) + " rows succesfully loaded into dataset."

Dim SQLConnection As New System.Data.SqlClient.SqlConnection()
SQLConnection.ConnectionString = "user id=sa; password=sa; initial catalog=akgpilot; server=akg-chris-l\vsdotnet;"
SQLConnection.Open()

Dim sqlString As String = "If exists (SELECT name FROM sysobjects WHERE name = N'JD_EDI' AND type = 'U') DROP TABLE JD_EDI"
Dim sqlString2 As String = "Create Table JD_EDI (Type varchar)"

Dim sqlStringCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(sqlString, SQLConnection)
sqlStringCommand.ExecuteNonQuery()

Dim sqlStringCommand2 As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(sqlString2, SQLConnection)
sqlStringCommand2.ExecuteNonQuery()
 
Create another adapter for the sQL Server table. Set AcceptChangesDuringFill
to false on the adapter you call .Fill with, then just call update on the
same dataset with the second adapter. Here's
http://www.knowdotnet.com/articles/datasetmerge.html and example of doing it
in between tables, but the same principle applies.

--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/
chris said:
I have a program that imports data from an excel spreadsheet into a
Click to expand...
dataset. I then create a connection to sql server and check to see if a
table exist, drop it and recreate it. I cant figure out how to poulate the
newly create sql server table with the dataset from excel. any help would be
great. Here is the code:
Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As
Click to expand...
System.EventArgs) Handles Button1.Click
' Create connection string variable for excel.
Dim a As String = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=c:\supplier requirements.xls;" _
& "Extended Properties=Excel 8.0;"

' Create the connection object by using the preceding connection string.
Dim objConn As New OleDbConnection(a)
' Open connection with the database.
objConn.Open()
' The code to follow uses a SQL SELECT command to display the data from the worksheet.
' Create new OleDbCommand to return data from worksheet.
Dim objCmdSelect As New OleDbCommand("Select * from [supplier requirements$]", objConn)
' Create new OleDbDataAdapter that is used to build a DataSet
' based on the preceding SQL SELECT statement.
Dim objAdapter As New OleDbDataAdapter()
' Pass the Select command to the adapter.
objAdapter.SelectCommand = objCmdSelect
' Create new DataSet to hold information from the worksheet.
Dim objDataset As New DataSet()
' Fill the DataSet with the information from the worksheet.
objAdapter.AcceptChangesDuringFill = False
objAdapter.Fill(objDataset, "XLData")
' Build a table from the original data.
DataGrid1.DataSource = objDataset.Tables(0).DefaultView

'get row count

Dim rows As DataRow()
Dim numberofrows As Integer
rows = objDataset.Tables(0).Select()
numberofrows = rows.Length

Me.DataGrid1.CaptionText = CStr(numberofrows) + " rows succesfully loaded into dataset."

Dim SQLConnection As New System.Data.SqlClient.SqlConnection()
SQLConnection.ConnectionString = "user id=sa; password=sa; initial
Click to expand...
catalog=akgpilot; server=akg-chris-l\vsdotnet;"
SQLConnection.Open()

Dim sqlString As String = "If exists (SELECT name FROM sysobjects WHERE
Click to expand...
name = N'JD_EDI' AND type = 'U') DROP TABLE JD_EDI"
Dim sqlString2 As String = "Create Table JD_EDI (Type varchar)"

Dim sqlStringCommand As System.Data.SqlClient.SqlCommand = New
Click to expand...
System.Data.SqlClient.SqlCommand(sqlString, SQLConnection)
sqlStringCommand.ExecuteNonQuery()

Dim sqlStringCommand2 As System.Data.SqlClient.SqlCommand = New
Click to expand...
System.Data.SqlClient.SqlCommand(sqlString2, SQLConnection)
 
Back
Top