The SelectCommand property has not been initialized before calling 'Fill

Question

Im trying to upload some records from an excel file and got that error when uploaded the source to the server.

`sqlQuery = "Select * from [Sheet1$]"
    oleConn = New OleDb.OleDbConnection
    oleConn.ConnectionString = conString
    If oleConn.State = ConnectionState.Closed Then oleConn.Open()

    oleCmd = New OleDb.OleDbCommand(sqlQuery, oleConn)
    oleCmd.Connection = oleConn
    oleCmd.CommandText = "Select * from [Sheet1$]"'--tried to add this but gettin the same error
    oleCmd.CommandType = CommandType.Text'--and this

    oleDa = New OleDb.OleDbDataAdapter(oleCmd)
    oleDa.SelectCommand = oleCmd
    oleDa.Fill(oleDs)
    oleCmd.Dispose()

    If oleConn.State = ConnectionState.Open Then oleConn.Close()
    Return oleDs` 

Answer 1

Try This code

    Private Sub buttonExecute_Click(sender As Object, e As EventArgs) Handles buttonExecute.Click
    Try
        ' Fill the DataGridView and connection string TextBox
        Dim FilePath As String = "C:\ExcelFile.xls" 'This is where file is located
        Dim FileType As String = "*.xlsx" 'This what extinction that file have
        Using connection As New OleDbConnection(GetConnectionString(FilePath, FileType))
            connection.Open()
            textBoxConnectionString.Text = GetConnectionString(FilePath, FileType)
            Using adapter As New OleDbDataAdapter("Select * from [Sheet1$]", connection)
                adapter.Fill(data)
            End Using
        End Using
    Catch ex As Exception
        ' Display any errors
        MessageBox.Show("[" & ex.[GetType]().Name & "] " & ex.Message & ex.StackTrace)
    End Try
End Sub

Here method to getconiction string:

    Function Excel_GetConnectionString(FilePath As String, FileType As String) As String
    ' Name of the Excel worksheet to open
    Dim filename As String = FilePath

    ' Note: the Types array exactly matches the entries in openFileDialog1.Filter
    ' For Excel 2007 XML (*.xlsx)
    ' For Excel 2007 Binary (*.xlsb)
    ' For Excel 2007 Macro-enabled (*.xlsm)
    ' For Excel 97/2000/2003 (*.xls)
    Dim Types As String() = {"Excel 12.0 Xml", "Excel 12.0", "Excel 12.0 Macro", "Excel 8.0", "Excel 5.0"}
    ' For Excel 5.0/95 (*.xls)
    ' Note: openFileDialog1.FilterIndex was saved into textBoxFilename.Tag
    Dim Type As String = Types(CInt(FileType) - 1)

    ' True if the first row in the Excel data is a header (used for column names, not data)
    Dim Header As Boolean = True

    ' True if columns containing different data types are treated as text
    '  (note that columns containing only integer types are still treated as integer, etc)
    Dim TreatIntermixedAsText As Boolean = True

    ' Build the actual connection string
    Dim builder As New OleDb.OleDbConnectionStringBuilder()
    builder.DataSource = filename
    If Type = "Excel 5.0" OrElse Type = "Excel 8.0" Then
        builder.Provider = "Microsoft.Jet.OLEDB.4.0"
    Else
        builder.Provider = "Microsoft.ACE.OLEDB.12.0"
    End If
    builder("Extended Properties") = Type & ";HDR=" & (If(Header, "Yes", "No")) & ";IMEX=" & (If(TreatIntermixedAsText, "1", "0"))

    ' The "ACE" provider requires either Office 2007 or the following redistributable:
    '  Office 2007 Data Connectivity Components:
    '    http://www.microsoft.com/downloads/details.aspx?familyid=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

    ' The "ACE" provider can be used for older types (e.g., Excel 8.0) as well.

    ' The connection strings used for Excel files are not clearly documented; see the following links for more information:
    '  Excel 2007 on ConnectionStrings.com:
    '    http://www.connectionstrings.com/excel-2007
    '  Excel on ConnectionStrings.com:
    '    http://www.connectionstrings.com/excel
    '  Microsoft OLE DB Provider for Microsoft Jet on MSDN:
    '    http://msdn.microsoft.com/en-us/library/ms810660.aspx
    '  KB247412 Methods for transferring data to Excel from Visual Basic:
    '    http://support.microsoft.com/kb/247412
    '  KB278973 ExcelADO demonstrates how to use ADO to read and write data in Excel workbooks:
    '    http://support.microsoft.com/kb/278973
    '  KB306023 How to transfer data to an Excel workbook by using Visual C# 2005 or Visual C# .NET:
    '    http://support.microsoft.com/kb/306023
    '  KB306572 How to query and display excel data by using ASP.NET, ADO.NET, and Visual C# .NET:
    '    http://support.microsoft.com/kb/306572
    '  KB316934 How to use ADO.NET to retrieve and modify records in an Excel workbook with Visual Basic .NET:
    '    http://support.microsoft.com/kb/316934

    Return builder.ConnectionString
End Function