I got an error: System.InvalidOperationException: The ConnectionString property has not been initialized.

Question

User2013552827 posted

 I don't know fix it. My code:

...
    Private Sub btnCarrega_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnCarrega.Click
        Try
            Dim strCaminho As String = "D:\\Dados\\" & "Acidentarias.xlsx"
            Dim strConn As String = "Provider=Microsoft.Jet.OleDb.4.0;" _
                       & "data source=" & strCaminho _
                       & "Extended Properties=Excel 10.0;"

            'HDR Pula Primeira lina do Excel

        Catch ex As Exception
            Me.lblMessage.Text = "Erro na conexão com a planilha. " & ex.Message
        End Try

        'Primeiro DataGrid - vou exibir todos os dados da planilha
        Dim objConn As New OleDbConnection(strConn)
        Dim strSql As String = "Select * From Acidentarias" ' Previamente definido no arquivo Excel
        lblSql1 = strSql

        Dim objCmd As New OleDbCommand(strSql, objConn)

        Try
            'abre a conexão com a fonte de dados e executa a consulta SQL para retornar os dados e vinculando-os ao datagrid1
            objConn.Open()
            dtgAcidentarias1.DataSource = objCmd.ExecuteReader()
            dtgAcidentarias1.DataBind()
        Catch exc As Exception
            Response.Write(exc.ToString())
        Finally
            objConn.Dispose()
        End Try

    End Sub

 

The erros is about objConn.Open() - I think...

Thanks!!

Answer 1

User1631025 posted

I think this is a variable scope issue. Try declaring strConn outside of the try block.

Dim strConn As String

Try
    strConn = "Provider=Microsoft.Jet.OleDb.4.0;" _  
        & "data source=" & strCaminho _  
        & "Extended Properties=Excel 10.0;"

Answer 2

User1867929564 posted

yes,declare your variable outside try.
also, objConn.Dispose()
instead use objConn.Close() everywhere.

also check that excel file is close. 

Answer 3

User2013552827 posted

Thanks, but now I got the error:  "Could not find installable ISAM"

I changed the Excel 2007 Provider as string:

         Dim strConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;" _
                                & "Data Source=D:\\Dados\\Archive.xlsx;" _
                                & "Extended Properties=Excel 12.0; HDR=YES;"

I tried to register the msexcl40.dll again and the error persists.

Answer 4

User1867929564 posted

This is because connection is wrong.
Which part is wrong can't say exactly.
 Either Microsoft.ACE.OLEDB.12.0 or Extended Properties=Excel 12.0 or both(my guess)

refer this,
http://www.google.co.in/search?hl=en&source=hp&q=could+not+find+installable+isam+excel&aq=1&aqi=g10&aql=&oq=Could+not+find+installable+ISAM&gs_rfai=

Answer 5

User2013552827 posted

I tried to change the excel compatibity for 8.00 and saved as 2003 and works!! ... but doesn't find the selection in the excel plan1 named by "interval selection" as Dados inside the excel... The code reference: Dim strSql As String = "Select * From Dados"

Any ideas? Thanks

Answer 6

User1867929564 posted

try following link again but this time solve the problem.If problem solve then state the problem here,

http://www.velocityreviews.com/forums/t112927-how-to-connect-to-excel.html

http://www.codeproject.com/KB/vb/Excel_Connectivity.aspx

http://www.dotnetjohn.com/articles.aspx?articleid=54 

Answer 7

User2013552827 posted

Thanks Kumar! Work the connection, but only with Excel 2003. Excel 2007 isn't yet.

When I changed the provider for Extended Properties = Excel 12.0 I got the ISAM error. I've been searched and I readed that I need to add a reference in the Visual Studio (http://vb.net-informations.com/excel-2007/vb.net_excel_2007_open_file.htm) and after I got the error: "Error 2 Task could not find AxImp.exe using the SdkToolsPath C:\Program Files\Microsoft SDKs\Windows\v7.0A\bin\NETFX 4.0 Tools\ or the registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SDKs\Windows\v7.0A. Make sure the SdkToolsPath is set and the tool exists in the correct processor specific location under the SdkToolsPath and that the Microsoft Windows SDK is installed Temis" And I didn't find the AxImp.exe in all of my system.

Answer 8

User1867929564 posted

 you mean to say that,you want to connect to Excel 2003 and Excel 2007 both.
Explain the scenerio ?

May be Excel 2007 not properly install.

read this carefully,

http://forums.asp.net/t/1169005.aspx

Answer 9

User-1148431695 posted

Hi,

Please check the following link for connection string to Excel 2007.

http://www.connectionstrings.com/excel-2007

IMO, your connection string is missing some keywords.

Answer 10

User2013552827 posted

I change the code for:

                                Dim strConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;" _
                                & "Data Source=D:\\Dados\\Archieve.xlsx;" _
                                & "Extended Properties=""Excel 12.0 Xml; IMEX=1; HDR=YES;"""

And install the AccessDatabaseEngine. And works well in Excel 2007.

Thanks, your help was invaluable.