Delete rows from a foxpro dbf file, that seems to be locked

jkirkerx

The same project again. Yes I know it's old, and I'm not the author of it, but it's quick cash. I have this dbf file that seems to be locked or something. In some other dbf files, I can delete a record. But in this one, even using another program called DBF Manager, I can't delete rows. So I tried 2 methods, using 2 different drivers. The dbf file has about say 20 rows for invoice number 28794, and in the 2nd example returns 1 in ExecuteNonQuery The first method I tried was using Jet, which is what I used through out the program. But I get a 0 back from ExecuteNonQuery

Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & m_path
Const queryString1 As String = _
" DELETE FROM ARTRS01H.dbf h " & _
" WHERE h.FINVNO = @FINVNO "

    Const queryString2 As String = \_
    " DELETE FROM ARTRS01.dbf v " & \_
    " WHERE v.FINVNO = @FINVNO "

    Using connection As New System.Data.OleDb.OleDbConnection(connString)
        Using command As New System.Data.OleDb.OleDbCommand(queryString1, connection)

            Dim paramFINVNO As System.Data.OleDb.OleDbParameter
            paramFINVNO = New System.Data.OleDb.OleDbParameter("@FINVNO", OleDbType.VarChar)
            paramFINVNO.Value = p.Trim
            command.Parameters.Add(paramFINVNO)

            Try
                connection.Open()
                Dim x As Integer = command.ExecuteNonQuery()
                command.CommandText = queryString2
                x += command.ExecuteNonQuery()
                pValue = If(x > 0, True, False)

            Catch sqlEx As SqlClient.SqlException
                pValue = False

            Catch exdb As OleDb.OleDbException
                pValue = False

            Catch ex As Exception
                pValue = False

            Finally
                connection.Close()

            End Try

The 2nd method I tried was using the Visual Fox Pro, in which I get a 1 back from ExecuteNonQuery

Dim connString As String = "Provider=VFPOLEDB; Data Source=" & m_path & ";"
Dim queryString1 As String = _
" SET EXCLUSIVE ON" & _
" DELETE FROM ARTRS01H.dbf " & _
" WHERE FINVNO = '" & p & "' " & _
" PACK "

    Using connection As New System.Data.OleDb.OleDbConnection(connString)
        Using command As New System.Data.OleDb.OleDbCommand(queryString1, connection)

CHill60

This really is digging out at the very back of my memory, but I vaguely remember a similar problem. I think there was another file created - either same name as database with a different suffix, or logon id, or random numbers+letters - I don't recall which and I've seen all three of these over the years. Any how - this file contained details of locks on the dbf and didn't always get cleared down properly - leaving things locked. If you're using WIN7 upwards then you might be hitting issues with virtual storage depending on where the DBF file is stored - came across something similar a couple of years ago. Good luck!

jkirkerx

Thats a start for me at least, Thanks! Strange, I deleted some funky looking row data at the bottom of the file and now I can delete records using DBF Manager. Guess I'll give my vb code another try again now.

21st Century Globalism has become Socialism on a planetary scale, in which the unequal treaties of the past have come back into play.

jkirkerx

I got this working, in which I can delete by Customer Number which is Column 2 and the index, but I can't delete by using Column 0, Invoice Number. When I use DBF Manager and do a search by invoice number, it comes back search string not found. Think it's because it a partial string or something? This technology is before I got into programming, and I never really used back in 1997.

Dim connString As String = "Provider=VFPOLEDB; Data Source=" & m_path & "; DELETED = FALSE;"

    Using connection As New System.Data.OleDb.OleDbConnection(connString)
        Dim c1 As New System.Data.OleDb.OleDbCommand("SET EXCLUSIVE ON", connection)
        Dim c2 As New System.Data.OleDb.OleDbCommand("SET DELETED ON", connection)
        Dim c3 As New System.Data.OleDb.OleDbCommand("DELETE FROM ARTRS01H WHERE FCUSTNO = ? AND FINVNO = ?", connection)
        Dim c4 As New System.Data.OleDb.OleDbCommand("PACK", connection)

        Dim paramFCUSTNO As System.Data.OleDb.OleDbParameter
        paramFCUSTNO = New System.Data.OleDb.OleDbParameter("@FCUSTNO", OleDbType.VarChar)
        paramFCUSTNO.Value = pCustNo
        c3.Parameters.Add(paramFCUSTNO)

        Dim paramFINVNO As System.Data.OleDb.OleDbParameter
        paramFINVNO = New System.Data.OleDb.OleDbParameter("@FINVNO", OleDbType.VarChar)
        paramFINVNO.Value = pInvNo
        c3.Parameters.Add(paramFINVNO)

        Try
            connection.Open()
            c1.ExecuteNonQuery()
            c2.ExecuteNonQuery()
            Dim x As Integer = c3.ExecuteNonQuery()
            c4.ExecuteNonQuery()
            pValue = If(x > 0, True, False)

        Catch sqlEx As SqlClient.SqlException
            pValue = False

        Catch exdb As OleDb.OleDbException
            pValue = False

        Catch ex As Exception
            pValue = False

        Finally
            connection.Close()

        End Try

    End Using

21st Century Globalism has become Socialism on a planetary scale, in which the unequal treaties of the past have come back into play.

jkirkerx

I think I finally got the code correct to delete the records by customer number and invoice, guess I should run 1 more test on it. Maybe I just fixed the index file and now it works. Tired of working on this, not fun!

Dim dwXCode As Integer = registry_shared.read_HKCU_dataPath(m_path)
Dim connString As String = "Provider=VFPOLEDB; Data Source=" & m_path & "; DELETED = FALSE;"

    Using connection As New System.Data.OleDb.OleDbConnection(connString)
        Dim c1 As New System.Data.OleDb.OleDbCommand("SET EXCLUSIVE ON", connection)
        Dim c2 As New System.Data.OleDb.OleDbCommand("SET DELETED ON", connection)
        Dim c3 As New System.Data.OleDb.OleDbCommand("DELETE FROM ARTRS01H WHERE FCUSTNO = ? AND FINVNO = ?", connection)
        Dim c4 As New System.Data.OleDb.OleDbCommand("DELETE FROM ARTRS01 WHERE FCUSTNO = ? AND FINVNO = ?", connection)
        Dim c5 As New System.Data.OleDb.OleDbCommand("PACK", connection)

        c3.Parameters.Add("@FCUSTNO", OleDbType.VarChar).Value = pCustNo
        c3.Parameters.Add("@FINVNO", OleDbType.VarChar).Value = pInvNo

        c4.Parameters.Add("@FCUSTNO", OleDbType.VarChar).Value = pCustNo
        c4.Parameters.Add("@FINVNO", OleDbType.VarChar).Value = pInvNo

        Try
            connection.Open()
            c1.ExecuteNonQuery()
            c2.ExecuteNonQuery()
            Dim x As Integer = c3.ExecuteNonQuery()
            x += c4.ExecuteNonQuery()
            c5.ExecuteNonQuery()
            pValue.Result = If(x > 0, True, False)
            pValue.Message = x & " record(s) were deleted successfully"

        Catch sqlEx As SqlClient.SqlException
            pValue.Result = False
            pValue.Message = sqlEx.Message.ToString

        Catch exdb As OleDb.OleDbException
            pValue.Result = False
            pValue.Message = exdb.Message.ToString

        Catch ex As Exception
            pValue.Result = False
            pValue.Message = ex.Message.ToString

        Finally
            connection.Close()

        End Try

21st Century Globalism has become Socialism on a planetary scale, in which the unequal treaties of the past have come back into play.