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...
Expand|Select|Wrap|Line Numbers
- 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_Connection=yes].[tblOutstanding] FROM [Report$];", Olecn)
- ExcelCommand.ExecuteNonQuery()
- Olecn.Close()
- End Function
- I've tried another method using the OpenRowSet in the following codes
Expand|Select|Wrap|Line Numbers
- 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
- 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,
Keith Chang