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.Ole Db.OleDbDataAda pter
Dim Olecn As System.Data.Ole Db.OleDbConnect ion
Dim dt1 As DataTable
Olecn = New System.Data.Ole Db.OleDbConnect ion( _
"Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=" & vFile & ";" & _
"Extended Properties=Exce l 8.0;HDR=Yes")
Olecn.Open()
Dim ExcelCommand As New System.Data.Ole Db.OleDbCommand ("SELECT INTO [ODBC;Driver={SQ L Server};Server= (local);Databas e=dbSSC;Trusted _Conn ection=yes].[tblOutstanding] FROM [Report$];", Olecn)
ExcelCommand.Ex ecuteNonQuery()
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\Syst em32\msexcl40.d ll 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=localhos t; user ID=accessSSC; password=access SSC541; database=dbSSC" )
cn.Open()
strSQL = "INSERT INTO tblOutstanding SELECT * FROM OPENROWSET('Mic rosoft.Jet.OLED B.4.0','Excel 8.0;Database=" & vFile & ";HDR=Yes','SEL ECT * FROM [Report$]')"
cm = New SqlCommand(strS QL, cn)
cm.ExecuteNonQu ery()
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.ExecuteNonQu ery() 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
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.Ole Db.OleDbDataAda pter
Dim Olecn As System.Data.Ole Db.OleDbConnect ion
Dim dt1 As DataTable
Olecn = New System.Data.Ole Db.OleDbConnect ion( _
"Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=" & vFile & ";" & _
"Extended Properties=Exce l 8.0;HDR=Yes")
Olecn.Open()
Dim ExcelCommand As New System.Data.Ole Db.OleDbCommand ("SELECT INTO [ODBC;Driver={SQ L Server};Server= (local);Databas e=dbSSC;Trusted _Conn ection=yes].[tblOutstanding] FROM [Report$];", Olecn)
ExcelCommand.Ex ecuteNonQuery()
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\Syst em32\msexcl40.d ll 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=localhos t; user ID=accessSSC; password=access SSC541; database=dbSSC" )
cn.Open()
strSQL = "INSERT INTO tblOutstanding SELECT * FROM OPENROWSET('Mic rosoft.Jet.OLED B.4.0','Excel 8.0;Database=" & vFile & ";HDR=Yes','SEL ECT * FROM [Report$]')"
cm = New SqlCommand(strS QL, cn)
cm.ExecuteNonQu ery()
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.ExecuteNonQu ery() 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