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

  • 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!!

    Monday, October 25, 2010 11:38 AM

Answers

All replies

  • 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;"


    Monday, October 25, 2010 3:43 PM
  • User1867929564 posted

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

    also check that excel file is close. 

    Tuesday, October 26, 2010 2:55 AM
  • 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.

    Tuesday, October 26, 2010 7:56 AM
  • 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=

    Tuesday, October 26, 2010 8:13 AM
  • 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

    Tuesday, October 26, 2010 9:40 AM
  • 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 

    Wednesday, October 27, 2010 3:31 AM
  • 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.

    Wednesday, October 27, 2010 10:36 AM
  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 28, 2010 12:55 AM
  • 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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 28, 2010 3:11 AM
  • 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.

    Friday, October 29, 2010 7:16 AM