I have always used Excel.interop before but I thought oledb would be simpler because, for this application, I only have to read the data which it can't seem to do. It accesses the file alright because I can't open the file while I am at a breakpoint. It attempts to read the file because oExcelReader.fieldcount = 14 which is correct. oExcelReader.hasrows = true which is not correct. The line where I use getstring(1) errors out and says there is no data for this row/column.

I have googled and googled but can't find an answer to this problem. People usually give up and use interop which I am very close to doing.

Structure VTSPointData
    Dim Name As String
    Dim Description As String
    Dim Area As String
    Dim IODevice As String
    Dim Address As String
    Dim Minimum As Int32
    Dim maximum As Int32
    Dim EGU As String
End Structure


Public Class Form1

    Private sExcelConn As String
    Private oExcelReader As OleDbDataReader
    Private oExcelConn As OleDbConnection
 
    Private Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
        sExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = c:\myfile.xls; Extended Properties=""Excel 8.0;HDR = YES"""
        oExcelConn = New OleDbConnection(sExcelConn)
        oExcelConn.Open()
        GetTagInfo()

    End Sub


 
    Private Sub GetTagInfo()
        Dim oPoint As VTSPointData
        Dim sType As String 'Gets the type of block
        Dim oCommand As New OleDbCommand("SELECT * FROM [BB$]", oExcelConn)
        Dim oTagData As New DataSet

        oExcelReader = oCommand.ExecuteReader
        oExcelReader.GetValues(TempArray)
        oPoint = New VTSPointData
        oPoint.Name = oExcelReader.GetString(1) 'it errors out on this line.  no data exist for the row/column
        sType = oExcelReader.GetString(2)
        oPoint.Description = oExcelReader.GetString(3) & " " & oPoint.Name
        oPoint.Area = GetArea(oExcelReader.GetString(14))
        oPoint.IODevice = GetDevice(oExcelReader.GetString(5))
        oPoint.Address = GetAddress(oExcelReader.GetString(5))
        If sType = "AI" Or sType = "AO" Or sType = "AR" Then
            oPoint.maximum = oExcelReader.GetInt32(11)
            oPoint.Minimum = oExcelReader.GetInt32(10)
            oPoint.EGU = oExcelReader.GetString(12)
        End If

    End Sub

    Private Function GetArea(ByRef sArea As String) As String
        return "area"

    End Function

    Private Function GetDevice(ByRef sDevice As String) As String
        return "device"
    End Function

    Private Function GetAddress(ByRef sAddress As String) As String
        return "Address"
    End Function

  

End Class

Use GetValue() or indexer(item) method and verify database null using IsDBNull() method.

HasRow will return values if the excel is closed. I think in your case you have it open? Hope this helps.
AutoTester

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.