Hi, I'm building a web application in VB.NET 1.1 where users will upload Excel files at this webpage and the web application will transfer the data from the uploaded Excel file into MS SQL 2000. There are around 600-700 records and about 56 columns in the Excel file which is to be uploaded daily. There will be a few different files which I need to implement for as well.
I've sourced for and tried alot of methods. But either they can't satisfy my requirements or they just can't simply work.
- Most simple and direct way I've tried is creating a oledb connection to the Excel file in the following codes...
Code: ( text )
Function TestUploadData1(ByVal vFile As String) As String
Dim Oleda As System.Data.OleDb.OleDbDataAdapter
Dim Olecn As System.Data.OleDb.OleDbConnection
Dim dt1 As DataTable
Olecn = New System.Data.OleDb.OleDbConnection( _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & vFile & ";" & _
"Extended Properties=Excel 8.0;HDR=Yes")
Olecn.Open()
Dim ExcelCommand As New System.Data.OleDb.OleDbCommand("SELECT INTO [ODBC;Driver={SQL Server};Server=(local);Database=dbSSC;Trusted_Conn ection=yes].[tblOutstanding] FROM [Report$];", Olecn)
ExcelCommand.ExecuteNonQuery()
Olecn.Close()
End Function
But from the above codes I kept getting the error "Could not find installable ISAM." at the line Olecn.Open(). I've tried to set the registry to C:\Windows\System32\msexcl40.dll but to no avail...
- I've tried another method using the OpenRowSet in the following codes
Code: ( text )
Function TestUploadData2(ByVal vFile As String) As String
Dim cn As SqlConnection
Dim cm As SqlCommand
Dim strSQL As String
cn = New SqlConnection("server=localhost; user ID=accessSSC; password=accessSSC541; database=dbSSC")
cn.Open()
strSQL = "INSERT INTO tblOutstanding SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=" & vFile & ";HDR=Yes','SELECT * FROM [Report$]')"
cm = New SqlCommand(strSQL, cn)
cm.ExecuteNonQuery()
cn.Close()
End Function
For the above codes, I kept getting the error "Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server." at the line cm.ExecuteNonQuery() and I've tried to disble the Ad Hoc settings in the registry and tried to create a link server but to no avail as well...
- I've tried DTS but I'd need to DTS package to run whenever the users upload a file. Is there an easy way that I can run the designated DTS package to run through my .Net web application?
Any help would be deeply appreciated.
Thanks & Regards,
Sambasivarao Ambati