Excluding Deleted Records from returned Set using

Question

VS 2010, VB.NET, WINFORMS. In my app i have a need to import foxpro database tables and exclude the deleted records. The problem is that FOXPRO tables keep deleted items inside the same table. I have tried using DELETED=NO in the connection string but vb throws

"Format of the initialization string does not conform to the OLE DB specification."

My function is as follows:

    Dim _DBConn1 As String = "provider=vfpoledb.1; Data Source = " & file1 & ";DELETED=NO"
    Dim _DBConn2 As String = "provider=vfpoledb.1; Data Source = " & file2 & ";DELETED=NO"
    Dim _DBConn3 As String = "provider=vfpoledb.1; Data Source = " & file3 & ";DELETED=NO"
    Dim _DBConn4 As String = "provider=vfpoledb.1; Data Source = " & file4 & ";DELETED=NO"

    Using _connection As New OleDbConnection(_DBConn1)
        Dim _savedId As String = String.Empty
        _connection.Open()
        Using _command As New OleDbCommand("SELECT * FROM " & _fileName1 & "", _connection)
            Using _reader2 As OleDbDataReader = _command.ExecuteReader
                While _reader2.Read
                    counter += 1
                End While
                y = 100 / counter
                Dim x As String = String.Empty
            End Using
            Using _reader As OleDbDataReader = _command.ExecuteReader
                While _reader.Read
                    Dim _letter As Integer = Nothing
                    Dim _name As String = String.Empty
                    Dim _content As String = String.Empty
                    Dim _copies As Integer = Nothing
                    Dim _type As Integer = Nothing
                    Dim _fee As Decimal = Nothing

                    _letter = _reader.Item(0)
                    _name = _reader.Item(1)
                    _content = _reader.Item(2)
                    _copies = _reader.Item(3)
                    _type = _reader.Item(4)
                    _fee = _reader.Item(5)
                    _UpdateLetters(_letter, _name, _content, _copies, _type, _fee)
                    _progress += y
                    Dim d As Integer = Convert.ToInt16((Convert.ToString(y).Split(".")(0)))
                    ProgressBar1.Increment(d)
                End While
            End Using
        End Using
        _connection.Close()
    End Using



    Using _connection As New OleDbConnection(_DBConn2)
        Dim _savedId As String = String.Empty
        _connection.Open()
        Using _command As New OleDbCommand("SELECT * FROM " & _fileName2 & "", _connection)
            Using _reader2 As OleDbDataReader = _command.ExecuteReader
                While _reader2.Read
                    counter += 1
                End While
                y = 100 / counter
                Dim x As String = String.Empty
            End Using
            Using _reader As OleDbDataReader = _command.ExecuteReader
                While _reader.Read
                    Dim _unit As String = String.Empty
                    Dim _Size As String = String.Empty
                    Dim _contractDate As String = String.Empty
                    Dim _deposit As Decimal = Nothing
                    Dim _Tfirst As String = String.Empty
                    Dim _Tlast As String = String.Empty
                    Dim _optional As String = String.Empty
                    Dim _address1 As String = String.Empty
                    Dim _address2 As String = String.Empty
                    Dim _city As String = String.Empty
                    Dim _st As String = String.Empty
                    Dim _zip As String = String.Empty
                    Dim _hphone As String = String.Empty
                    Dim _drLicense As String = String.Empty
                    Dim _employer As String = String.Empty
                    Dim _wphone As String = String.Empty
                    Dim _bname As String = String.Empty
                    Dim _baddress1 As String = String.Empty
                    Dim _baddress2 As String = String.Empty
                    Dim _bCity As String = String.Empty
                    Dim _bState As String = String.Empty
                    Dim _bZip As String = String.Empty
                    Dim _bPhone As String = String.Empty
                    Dim _contact_Name As String = String.Empty
                    Dim _contact_Address1 As String = String.Empty
                    Dim _contact_Address2 As String = String.Empty
                    Dim _contact_City As String = String.Empty
                    Dim _contact_State As String = String.Empty
                    Dim _contact_zip As String = String.Empty
                    Dim _contact_phone As String = String.Empty
                    Dim _balance As Decimal = Nothing
                    Dim _lastPaymentDate As String = String.Empty
                    Dim _lastPayAmount As Decimal = Nothing
                    Dim _memo As String = String.Empty
                    Dim _lateFee As Decimal = Nothing
                    Dim _email As String = String.Empty

                    _unit = _reader.Item(0)
                    _Size = _reader.Item(1)
                    _contractDate = _reader.Item(2)
                    _deposit = _reader.Item(3)
                    _Tfirst = _reader.Item(4)
                    _Tlast = _reader.Item(5)
                    _optional = _reader.Item(6)
                    _address1 = _reader.Item(7)
                    _address2 = _reader.Item(8)
                    _city = _reader.Item(9)
                    _st = _reader.Item(10)
                    _zip = _reader.Item(11)
                    _hphone = _reader.Item(12)
                    _drLicense = _reader.Item(13)
                    _employer = _reader.Item(15)
                    _wphone = _reader.Item(16)
                    _bname = _reader.Item(17)
                    _baddress1 = _reader.Item(18)
                    _baddress2 = _reader.Item(19)
                    _bCity = _reader.Item(20)
                    _bState = _reader.Item(21)
                    _bZip = _reader.Item(22)
                    _bPhone = _reader.Item(23)
                    _contact_Name = _reader.Item(24)
                    _contact_Address1 = _reader.Item(25)
                    _contact_Address2 = _reader.Item(26)
                    _contact_City = _reader.Item(27)
                    _contact_State = _reader.Item(28)
                    _contact_zip = _reader.Item(29)
                    _contact_phone = _reader.Item(30)
                    _balance = _reader.Item(32)
                    _lastPaymentDate = _reader.Item(33)
                    _lastPayAmount = _reader.Item(34)
                    _memo = _reader.Item(46)
                    _lateFee = _reader.Item(49)
                    _email = _reader.Item(50)
                    _UpdateTenent(_unit, _Size, _contractDate, _deposit, _Tfirst, _Tlast, _optional, _address1, _address2, _city, _st, _zip, _hphone, _drLicense, _employer, _wphone, _bname, _baddress1, _baddress2, _bCity, _bState, _bZip, _bPhone, _contact_Name, _contact_Address1, _contact_Address2, _contact_City, _contact_State, _contact_zip, _contact_phone, _balance, _lastPaymentDate, _lastPayAmount, _memo, _lateFee, _email)
                    _progress += y
                    Dim d As Integer = Convert.ToInt16((Convert.ToString(y).Split(".")(0)))
                    ProgressBar2.Increment(d)
                End While
            End Using
        End Using
        _connection.Close()
    End Using

Any ideas how my Connection string is wrong? Google results pointed me in the direction of DELETED=NO but its throwing that exception error now that I have added it..

Answer 1

The VfpOleDb provider excludes deleted records by default. So your connection string doesn't need to include the deleted setting. If you wanted to include delete records... you would include "deleted=false" in the connection string.