none
OleDB Connection Issues RRS feed

  • Question

  • Here is the Class that is used - I believe it came from Karen

    Public Class Connections
        Public Sub New()
        End Sub
        ''' <summary>
        ''' Create a connection where first row contains column names
        ''' </summary>
        ''' <param name="FileName"></param>
        ''' <param name="IMEX"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        <DebuggerStepThrough()>
        Public Function HeaderConnectionString(ByVal FileName As String, Optional ByVal IMEX As Integer = 1) As String
            Dim Builder As New OleDbConnectionStringBuilder
            If IO.Path.GetExtension(FileName).ToUpper = ".XLS" Then
                Builder.Provider = "Microsoft.Jet.OLEDB.4.0"
                Builder.Add("Extended Properties", String.Format("Excel 8.0;IMEX={0};HDR=Yes;", IMEX))
            Else
                Builder.Provider = "Microsoft.ACE.OLEDB.12.0"
                Builder.Add("Extended Properties", String.Format("Excel 12.0;IMEX={0};HDR=Yes;", IMEX))
            End If
    
            Builder.DataSource = FileName
    
            Return Builder.ToString
    
        End Function
        ''' <summary>
        ''' Create a connection where first row contains data
        ''' </summary>
        ''' <param name="FileName"></param>
        ''' <param name="IMEX"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        <DebuggerStepThrough()>
        Public Function NoHeaderConnectionString(ByVal FileName As String, Optional ByVal IMEX As Integer = 1) As String
            Dim Builder As New OleDbConnectionStringBuilder
            If IO.Path.GetExtension(FileName).ToUpper = ".XLS" Then
                Builder.Provider = "Microsoft.Jet.OLEDB.4.0"
                Builder.Add("Extended Properties", String.Format("Excel 8.0;IMEX={0};HDR=No;", IMEX))
            Else
                Builder.Provider = "Microsoft.ACE.OLEDB.12.0"
                Builder.Add("Extended Properties", String.Format("Excel 12.0;IMEX={0};HDR=No;", IMEX))
            End If
    
            Builder.DataSource = FileName
    
            Return Builder.ToString
    
        End Function
    End Class
    

    Code I am trying, which came from her as well

    Private FileName As String = IO.Path.Combine("C:\Users\devon\Documents\[Redacted]", "[Redacted].xlsx")
    Private Connection As New Connections
    
    Using cn As New OleDbConnection With {.ConnectionString = Connection.NoHeaderConnectionString(FileName)}
    'OR
    Using cn As New OleDbConnection With {.ConnectionString = Connection.HeaderConnectionString(FileName)}
    
    
    Dim SelectStatement As String = "SELECT * FROM 'Sheet1'" 'Syntax error in query. Incomplete query Clause.
    Dim SelectStatement As String = "SELECT * FROM [Sheet1]" - 'Could not find the object
    Dim SelectStatement As String = "SELECT * FROM Sheet1"  - 'Could not find the object
    Dim SelectStatement As String = "SELECT * FROM Sheet1$" - 'Syntax error in FROM
    Dim SelectStatement As String = "SELECT * FROM 'Sheet1$'" - 'Incomplete query Clause.
    Dim SelectStatement As String = "SELECT * FROM ['Sheet1$']" - '"Sheet1" is not a valid name
    
    I can assure all that the spreadsheet exists and contains "Sheet1" - I am using Excel 2010

    Wednesday, December 4, 2019 8:06 PM

Answers

  • Should be

    Dim SelectStatement As String = "SELECT * FROM [Sheet1$]"


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Devon_Nullman Wednesday, December 4, 2019 9:01 PM
    Wednesday, December 4, 2019 8:45 PM
    Moderator

All replies

  • Should be

    Dim SelectStatement As String = "SELECT * FROM [Sheet1$]"


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by Devon_Nullman Wednesday, December 4, 2019 9:01 PM
    Wednesday, December 4, 2019 8:45 PM
    Moderator
  • Thank you - can you tell me why the $ and brackets are needed ?

    Wednesday, December 4, 2019 9:02 PM
  • Thank you - can you tell me why the $ and brackets are needed ?

    The '$' sign separates the sheet name from the table name. A sheet can contain multiple tables. Leaving the space behind the '$' empty says your using the sheet directly and not only a table on it. OleDb needs the brackets else it considers $ invalid


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, December 4, 2019 9:25 PM
    Moderator
  • Thanks again.
    Friday, December 6, 2019 3:51 AM
  • Thanks again.
    Your welcome :-)

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, December 6, 2019 1:43 PM
    Moderator