altering .cdx indices for vfp

[Bernie Yaeger]

My client has a visual foxpro app that he wants me to hook into. I'm
connecting to it via odbc with this connectionstring:
Dim oconn_d As New OdbcConnection("Driver={Microsoft Visual FoxPro
Driver};SourceType=DBF;SourceDB=c:\test;exclusive=NO")

These are free standing tables, all with .cdx files. I do not own nor do I
intend to use vfp to work with this; rather, I am working with it
exclusively inside vb .net. To display data, I have no problems; however,
if he wants me to modify data, this requires altering the .cdx, which
probably happens automatically using vfp, but not with odbc. What can I do
to update .cdx files when accessing them via odbc?

Tx for any help.

Bernie Yaeger

 

[Cor Ligthert]

Bernie,

Dit you try it already in the newsgroup

microsoft.public.fox.helpwanted

Maybe sees Cindy your message in this VBNet newsgroup however probably
faster there. And I thought that I had seen that there everytime new
providers for Foxpro.

Cor

 

[Bernie Yaeger]

Hi Cor,

I was expecting to hear from Cindy. No, I did not try fox.helpwanted, but
tx for the idea - I will post there now.

Thanks again,

Bernie

 

[Cindy Winegarden]

Hi Bernie,

The following works just fine in VB 2003:
Option Explicit On
Option Strict On

Imports System
Imports System.Data
Imports System.Data.OleDb


Module Module1
Sub Main()
Try

'-- Download and install the latest VFP OLE DB data provider
'-- from
http://msdn.microsoft.com/vfoxpro/downloads/updates/default.aspx

'-- FoxPro code:
'-- Create Table C:\Temp\TestIndex Free (FirstName C(10),
LastName C(10))
'-- Index On LastName + FirstName Tag FullName

Dim conn As OleDbConnection
conn = New OleDbConnection("Provider=VFPOLEDB.1;Data
Source=C:\Temp;")
conn.Open()

'-- Lets create some data to work with
Dim cmd1 As New OleDbCommand("Insert Into TestIndex Values ('A',
'B')", conn)
Dim cmd2 As New OleDbCommand("Insert Into TestIndex Values ('X',
'Y')", conn)
Dim cmd3 As New OleDbCommand("Insert Into TestIndex Values ('F',
'G')", conn)
cmd1.ExecuteNonQuery()
cmd2.ExecuteNonQuery()
cmd3.ExecuteNonQuery()

'-- FoxPro code:
'-- Use C:\Temp\TestIndex Order FullName

'-- Command results:
'-- Firstname Lastname
'-- A B
'-- F G
'-- X Y


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

End Sub
End Module
<<

Why not use the FoxPro and Visual FoxPro OLE DB data provider?
Are you sure you're working with a CDX index and not an IDX index?
Does the index have the same name as the corresponding table?
What exactly are the index expressions on the table? Are there any
user-defined functions, etc.?
Does "modify data" mean anything other than insert/delete/update data?

 

[Cindy Winegarden]

Hi Bernie,

I was out of town and have spent most of today catching up.

 

[Bernie Yaeger]

Hi Cindy,

Thanks for your help.

I've downloaded and referenced the vfp oledb driver, but I am still having a
problem. I'm using this code:
Dim oconn_d As New OleDbConnection("Provider=VFPOLEDB.1;Data
Source=c:\testmom;")

Try

oconn_d.Open()

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

Dim da_d As New OleDbDataAdapter("select * from box", oconn_d)

Dim ds_d As New DataSet("DS")

da_d.Fill(ds_d, "DS")

Dim mcommandbuilder As OleDbCommandBuilder = New OleDbCommandBuilder(da_d)

Dim irow As DataRow

For Each irow In ds_d.Tables(0).Rows

irow(3) = "BY"

Exit For

Next

Try

da_d.Update(ds_d, "DS")

Catch ex As Exception

MessageBox.Show(ex.Message)

Exit Sub

End Try

Here's the problem - I can display data without difficulty; however, the
commandbuilder doesn't work because it says 'can't modify a table without
key column information'. Evidently the table does not have a primary key.
But if that is true, how could it have a .cdx file? Will I have to create
my own update commands?

Thanks again for your assistance.

Bernie

 

[Cindy Winegarden]

Hi Bernie,

VFP indexes can be created on any field or combination of fields. Any and
all indexes are stored in a CDX (compound index) file, so there can be more
than one index. It's possible for Fox tables to not have any primary key at
all.

What about using an SQL Update statement with a Where clause that specifies
criteria for the rows you want to update? You'd use Execute NonQuery and a
statement like:

Update MyTable Set MyField = SomeValue Where SomeCondition and SomeCondition

If the table has a PK then you'd use ...Where KeyField = SomeValue

 

[Bernie Yaeger]

Hi Cindy,

'Where somecondition and somecondition' doesn't get it done:

I cannot for the life of me figure out how to modify the backend when a
table has no primary key. I have used all of the examples in Sceppa's book
and they all fail - or, rather, update every row, even unmodified rows, when
a table has no primary key. I cannot alter the tables for business reasons.
Is there any solution to this dilemma? I cannot offer a where clause that
makes sense - one table has 75 columns and all may be exactly the same.

Tx for any help.

Bernie

 

[Bernie Yaeger]

Hi Cindy,

By the way, the .cdx file is not updated when I update the table using the
new vfp driver. Have you actually used this provider to update .cdx files?
If so, can you give me a code snippet where it worked. I must be leaving
something of importance out.

Tx,

Bernie

 

[Cindy Winegarden]

Hi Bernie,

By the way, the .cdx file is not updated when I update the table using the
new vfp driver.

How are you determining this - by the time on the file? If there is an index
on LastName, for example, and you change the FirstName field you should not
expect to see any changes to the index files.

Do you know what the indexes on the table are?

Have you actually used this provider to update .cdx files? If so, can you
give me a code snippet where it worked. I must be leaving something of
importance out.

It's really not the data provider, but rather the Fox data engine that keeps
the indexes up-to-date. It's similar to SQL Server in that the ODBC and OLE
DB drivers provide a way to send commands to the database, but the SQL
Server data engine takes care of updating indexes appropriately when values
change in the affected fields.

I posted code a couple of messages up in this thread that updates the table
indexes appropriately. I can test this by inserting data via VB and OLE DB
and then opening the table in FoxPro, setting the order to that index, and
viewing the data.

 

[Bernie Yaeger]

Hi Cindy,

You are absolutely correct. I was determining whether the .cdx was updated
by the date. I also believed - and I used to program in clipper using the
..cdx driver and then for more than 10 years in ca visual objects using the
cdx driver - that you had to have an index open (odbf:setorder("lnamex")) to
have the .cdx change, but I just tested it (ca visual objects) without
opening the index file and it does indeed change anyway!

But I do not know what the index tags are, as I can't open the table using
vb .net. I might be able to attach the index file using an older version of
crystal, but I was wondering - do you know a tool (other than vfp itself)
with which I could open the files and indexes to see the data and index
tags? I used to use dbu.exe, but it won't open these files.

Thanks for your help.

Bernie

 

[Cindy Winegarden]

Hi Bernie,

I can't find a way to read the index tag expressions via OLE DB or ODBC.
There are 3rd-party DBF viewers; one is DBFView (http://www.dbfview.com/)
and you can try it to make sure it will give you what you need before you
spend any money.

Back to your original question - how to update data. The code example you
gave was:

For Each irow In ds_d.Tables(0).Rows
irow(3) = "BY"
Exit For
Next

It looks like you're planning to update every row in your dataset, although
it looks like you exit the loop after the first iteration. In any case, if
you're planning to update every row, why not use SQL Pass-Through and
ExecuteNonQuery:

"Update MyTable Set SomeField = SomeValue"

You can use a Where clause to narrow down the replacements, otherwise all
rows will be updated. If your user is paging through a grid, surely he has a
way of deciding which row he wants. In that case you can use a Where clause
that uses values from the row the user is on as criteria. If there are some
rows that are _exactly_ the same then each of them will be updated, but
there again, how does the user decide to use the first one he comes to
rather than the last one?

 

[Bernie Yaeger]

Hi Cindy,

Tx for the info - I will look into dbfview; that is the kind of tool I had
in mind.

I was just testing the update; that's why I exited the loop. But that is
what began the problem - after changing just the first row in the loop, all
rows in the table changed to 'BY'! Sounds, crazy, I know, but inside update
command I enter once and all the rows change, probably because there is no
where clause and the update event has the row passed into it but doesn't
recognize a distinction. That's what got me going on primary key etc. But
you are correct - if I use several other row columns in a where clause, if
they happen to update all rows which have all the same criteria, it doesn't
matter, as they are all the same!

Tx for your help.

Bernie