Excel to SQL vb.net

[knea]

Hi!

I am using VB.NET. I am trying to send excel data to SQL and I am failing. I need to send data from an excel sheet to a SQL server table that already exists. In other words I need to append data on SQL. I have heard of something called BULK INSERT but I don't know if thats the way to go. I tried running bulk insert and it give me the error saying I don't have permissions to run BULK INSERT command. I tried running Distributed query but got the error saying I don't have permissions to run Ad hoc queries.
BULK INSERT COmmand:
OBJMYCMD.CommandText = "BULK INSERT BBDATA.dbo.eExpenseData FROM 'D:\test.xls'"

Distributed Query:
SQLString = "SELECT * INTO eExpenseData FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',"
SQLString = SQLString & "'Excel 8.0;Data Source=C:\Test.xls',"
SQLString = SQLString & "'SELECT * FROM [Data$]')"

Help Please!!

Thanks
Knea

 

[SHelton]

The easiest way I have found is to load the Excel data straight into a dataset/datatable and then update the SQL Server database from there. The following code works by loading a named range from a worksheet into a datatable (you would then need to iterate through the rows and insert each into the SQL Server database):

Imports System.Data.OleDb

Dim cOLE As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test.xls;Extended Properties=Excel 8.0;&quot

Dim dtSource As New DataTable()

Dim daOLE As New OleDbDataAdapter("SELECT * FROM NamedRange", cOLE)

daOLE.Fill(dtSource)

If you use a dataset you could then use a CommandBuilder object to build the insert statements for you.