Ado.net Excel locking problem

[DotNetBlocks]

Hello,
I am having a problem with ado.net and excel. When an upate is run, it executes fine, but i the actual updates do not take place untill i recompile the project. Plus, if i try an open the file again to do a select statment, it will not open.

Does any one have hany sugestions?

'VS 2003 running on XP Pro

    Function NonExecuteSQL(ByVal FilePath As String, ByVal SQL As String) As Boolean
        Me.cData = New DataSet
        Me.cError = ""
        Try
            Dim cn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & FilePath & "; Extended Properties=""Excel 8.0;""")
            Dim objCmd As New OleDbCommand(SQL, cn)
            Dim objReader As OleDbDataReader

            objCmd.Connection.Open()
            objReader = objCmd.ExecuteReader()
            objCmd.Connection.Close()

            Return True
        Catch objError As Exception

            Me.cError = Err.Description
            Return False

        Finally

        End Try

    End Function

Babloome

 

[dvannoy]

this is from connectionstrings.com

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
"HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.
"IMEX=1;" tells the driver to always read "intermixed" data columns as text. Note that this option might affect excel sheet write access negative.
TIP! SQL syntax: "SELECT * FROM [sheet1$]" - i.e. worksheet name followed by a "$" and wrapped in "[" "]" brackets.
TIP! Check out the [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel] located registry REG_DWORD "TypeGuessRows". That's the key to not letting Excel use only the first 8 rows to guess the columns data type. Set this value to 0 to scan all rows. This might hurt performance.
Important note! The two double quota ("") in the string are escaped quotas (VB syntax), you may have to change this to your language specific escape syntax (ex. \") or maybe single quota (').

 

[DotNetBlocks]

Thanks Dvannoy,
Thanks for your replay, but we have already tried this, and the same issue happens.

 

[dvannoy]

maybe it's your network? how many rows are we talking about?