Last post Oct 12, 2011 12:00 PM by Jackxxx
Oct 12, 2011 10:18 AM|Jackxxx|LINK
I use the following connection code to connect to an excel file after it is uploaded, but if I use the connection to view the objcommand data in a gridview It will not release the connection until the session times out. So I'm having to wait 20 minutes to
upload the file again.
So I'm wondering if there is a better way to connect to the Excel file that will not lock it up?
Protected Function ExcelConnection() As OleDbCommand
' Connect to the Excel Spreadsheet .Jet.OLEDB.4.0
Dim xConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("~/Import/ExcelImport.xls") & ";" & _
"Extended Properties=Excel 8.0;" 'HDR=NO;IMEX=1"
' create your excel connection object using the connection string
Dim objXConn As New OleDbConnection(xConnStr)
' use a SQL Select command to retrieve the data from the Excel Spreadsheet
' the "table name" is the name of the worksheet within the spreadsheet
Dim objCommand As New OleDbCommand("SELECT * FROM [Clients$]", objXConn)
Oct 12, 2011 11:57 AM|bobj181|LINK
First of all, you need to close the connection. Secondly, why are you connecting to the excel file? Are you trying to upload it into a database, if so, why not skip this, and use SSIS (SQL Server Intregrated Services). This is exactly what the tool is
Oct 12, 2011 12:00 PM|Jackxxx|LINK
I'm using the express versions of SQL Server, so I don't have access to the SSIS tool. Also the process is done from an asp.net page.