none
Updating DBF Data RRS feed

  • Question

  • I need to update a dbf file (for subsequent interfacing with another program).  I am able to
    1. Load the DBF into a DataGridView
    2. Delete one/more entire row
    3. Add a New row and populate fields.

    However, I cannot seem to change the value of an existing field and save the changes.  I get the error "Error in UPDATE command Syntax", though I can't see it.  I tried adding a primary key to the dbf, but with no success.  Any help will be appreciated.

    Private Sub LoadGridData()
            Dim dsTR As New DataSet
            Dim SQLStr As String
            Dim dbfFile As String
            Dim rst As New ADODB.Recordset
            Dim ds As New DataSet
            Dim gDataSet As DataSet = New DataSet


            Try
                SQLStr = "SELECT * FROM Landuse.dbf"

                ' SET THE PATH TO THE MDB FILE
                dbfFile = Application.StartupPath

                ' Build the Connection String
                strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                "Data Source=" & dbfFile & ";" & _
                                "Extended Properties=DBASE III;Mode=Share Deny None;"

                ' SET THE CONNECTION
                dbConnection = New OleDb.OleDbConnection(strConnection)

                ' SET UP THE COMMANDS
                sAdapter = CreateAdapter(dbConnection)

                Try
                    sAdapter.Fill(gDataSet, "Landuse")
                Catch exOleDB As OleDbException
                    MsgBox(exOleDB.Message)
                End Try

                sDataTable = gDataSet.Tables(0)
                DGV1.DataSource = sDataTable 'DGV1 is a DataGridView

            Catch ex As Exception
                MsgBox(ex.Message)
            End Try

        End Sub


        Public Function CreateAdapter(ByVal thisConnection As OleDbConnection) As OleDbDataAdapter
                Dim thisAdapter As New OleDbDataAdapter
                'The Select Command
                Dim sCommand As OleDbCommand = New OleDbCommand("SELECT * FROM LANDUSE", thisConnection)
                'Add the Parameters for the SelectCommand
                thisAdapter.SelectCommand = sCommand
       
                'The Insert Command
                Dim sText As String = ""
                sText = "INSERT INTO LANDUSE VALUES (@zid,@zone, @pop)"
                sCommand = New OleDbCommand(sText, thisConnection)
                sCommand.Parameters.Add("@zid", OleDbType.Integer, 4, "ZID")
                sCommand.Parameters.Add("@zone", OleDbType.Integer, 4, "ZONE")
                sCommand.Parameters.Add("@pop", OleDbType.Integer, 4, "POP")
                thisAdapter.InsertCommand = sCommand
       
                'The Update Command
                sText = "UPDATE Landuse SET ZONE=@zone, POP=@pop WHERE ZID=@OldZID"
                sCommand = New OleDbCommand(sText, thisConnection)
                sCommand.Parameters.Add("@zone", OleDbType.Integer, 4, "ZONE")
                sCommand.Parameters.Add("@pop", OleDbType.Integer, 4, "POP")
                Dim parameter As OleDbParameter = sCommand.Parameters.Add("@OldZID", OleDbType.Integer, 4, "ZID")
                parameter.SourceVersion = DataRowVersion.Original
                thisAdapter.UpdateCommand = sCommand
       
                'The Delete Command
                sText = "DELETE FROM LANDUSE WHERE ZID=@oldZID"
                sCommand = New OleDbCommand(sText, thisConnection)
                sCommand.Parameters.Add("@OldZID", OleDbType.Integer, 4, "ZID")
                parameter.SourceVersion = DataRowVersion.Original
                thisAdapter.DeleteCommand = sCommand
       
                Return thisAdapter

        End Function

         Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
                Dim changesDataSet As New DataSet
                Dim testTable As DataTable
                dbConnection.Open()
                Try
                    changesDataSet = sDataTable.DataSet.GetChanges(DataRowState.Modified Or DataRowState.Added Or DataRowState.Deleted)
                    testTable = changesDataSet.Tables(0)
       
                    If Not (changesDataSet Is Nothing) Then
                            sAdapter.Update(sDataTable)
                            sDataTable.AcceptChanges()
                    Else
                            sDataTable.RejectChanges()
                   End If
                Catch ex As Exception
                    MsgBox(ex.Message)
                Finally
                    DGV1.Refresh()
                    dbConnection.Close()
                End Try
       
        End Sub

    Tuesday, February 2, 2010 2:47 PM

Answers

All replies