Cannot insert record to dbf file? Help

[nick]

I always get "Operation must use an updateable query" when I try to insert a
new record in a dbf file. My connection string is:

"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\dbfs;Extended
Properties=dBase IV;User ID=Admin;Password="

And Code is

Dim dbfConnectionString as String = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=C:\dbfs;Extended Properties=dBase IV;User ID=Admin;Password="
Dim InsertCmdStr As String = "Insert Into wolog (WOTYPE, REF_NO, PRIORITY,
Unit, DEPT, DEPT_ID, PAC2, RQNAME, RQPHONE, SHOP, ASTO, WODATE, OPTR,
LOCATION, COMM1, ESTHR, STATUS, UP, ROOM, FLR) values
('D','D700804','5','90','740','1','2BUS','a','a','SEC','','11/10/2004
2:02:27 PM','login','2 BUS ST a ','a ',0,'N','N','a','')"
Dim cmd As New OleDbCommand(InsertCmdStr, New
OleDbConnection(dbfConnectionString))
cmd.Connection.Open()
Try
REF_NO = cmd.ExecuteScalar()
Catch err As Exception
Throw err
Finally
If Not cmd.Connection Is Nothing Then
cmd.Connection.Close()
End If
End Try

The dbf file has a few indics and one of them are unique index (5 columns)

However, I can successfully run the SQL statement in MS Access to append a
record in the linked dbf table.

Any clue?

 

[Jeff Dillon]

Are you sure it shouldn't be c:\dbfs.dbf for the Data Source?

Can you do a SELECT ok?

Jeff

 

[nick]

Thanks for reply. I got the following error if I change to the file name:

'C:\dbfs\WOLOG.dbf' is not a valid path. Make sure that the path name is
spelled correctly and that you are connected to the server on which the file
resides.

The following code runs without any problem (with my original connection
string):

Dim da As New OleDbDataAdapter("select ref_no from wolog",
dbfConnectionString)
Dim dt As New DataTable
da.Fill(dt)

So select shouldn't have any problem.

 

[Jeff Dillon]

Can you perform this update with your DBF query tool?

Possibly drop the "into" clause? just "insert tablename..."

Jeff

 

[nick]

Using Access can insert a record in the dbf file.

Drop "into" will cause "Syntax error in INSERT INTO statement."

....

 

[yonggangwang]

hi:
change the Data Source=C:\dbfs\WOLOG.dbf
Or just copy connectiongstring from dataadapter.

 

[John M Deal]

I've been working with dbf files lately and am using the same connection
string that you are and inserts are working fine. The one difference
that I see between our code is that when you do an insert statement you
need to specify the dbf file name including extension or it doesn't
work. Remember that when you work with dbf files the folder that holds
them is the database and the dbf file is the table. I don't know if
this is the only issue but you could try the following insert statement:

Dim InsertCmdStr As String = "Insert Into wolog.dbf (WOTYPE, REF_NO,
PRIORITY, Unit, DEPT, DEPT_ID, PAC2, RQNAME, RQPHONE, SHOP, ASTO,
WODATE, OPTR, LOCATION, COMM1, ESTHR, STATUS, UP, ROOM, FLR) values
('D','D700804','5','90','740','1','2BUS','a','a','SEC','','11/10/2004
2:02:27 PM','login','2 BUS ST a ','a ',0,'N','N','a','')"

The reason the original would work in Access is that access creates an
mdb that contains a linked table to the dbf. The linked table is named
the same as the dbf file without the extension so this "alias" allows
for the difference in the insert statement. Theoretically you could do
your insert against the proxy mdb file and let access pass it through to
the dbf, but from my last experience trying this the linked table
references the dbf by absolute path so you'd have to make sure it was
always in the same place (dev, testing, production...)

Have A Better One!

John M Deal, MCP
Necessity Software

 

[Paul Clement]

¤ I always get "Operation must use an updateable query" when I try to insert a
¤ new record in a dbf file. My connection string is:
¤
¤ "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\dbfs;Extended
¤ Properties=dBase IV;User ID=Admin;Password="
¤
¤ And Code is
¤
¤ Dim dbfConnectionString as String = "Provider=Microsoft.Jet.OLEDB.4.0; Data
¤ Source=C:\dbfs;Extended Properties=dBase IV;User ID=Admin;Password="
¤ Dim InsertCmdStr As String = "Insert Into wolog (WOTYPE, REF_NO, PRIORITY,
¤ Unit, DEPT, DEPT_ID, PAC2, RQNAME, RQPHONE, SHOP, ASTO, WODATE, OPTR,
¤ LOCATION, COMM1, ESTHR, STATUS, UP, ROOM, FLR) values
¤ ('D','D700804','5','90','740','1','2BUS','a','a','SEC','','11/10/2004
¤ 2:02:27 PM','login','2 BUS ST a ','a ',0,'N','N','a','')"
¤ Dim cmd As New OleDbCommand(InsertCmdStr, New
¤ OleDbConnection(dbfConnectionString))
¤ cmd.Connection.Open()
¤ Try
¤ REF_NO = cmd.ExecuteScalar()
¤ Catch err As Exception
¤ Throw err
¤ Finally
¤ If Not cmd.Connection Is Nothing Then
¤ cmd.Connection.Close()
¤ End If
¤ End Try
¤
¤ The dbf file has a few indics and one of them are unique index (5 columns)
¤
¤ However, I can successfully run the SQL statement in MS Access to append a
¤ record in the linked dbf table.
¤
¤ Any clue?
¤

This isn't an ASP.NET application is it?


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)