Dont really know where to put this(Q:Free Tables used by C#) 1

[Wolfen12]

Hi

We have a existing project in Visual Fox pro using Free Tables.

We are writing a other frontend using C#, but has to connect to the Free Tables.

I can Select and Insert, but update and delete seems to be a problem, reason being that there is no primary key.This makes sense to me, but what if my table has a primary key(Stored in the cdx file)?? Does C# not read cdx files??

Can anyone maybe help me with this question

 

[Mike Lewis]

Hi Woflen12,

First of all, how are you accessing the free tables? Are you using an ODBC driver, or OLE DB, or a third-party library?

You ask about C# reading CDXs, but of course it is not C# that is interacting with the data.

Free tables don't have primary indexes, but they can have candidate indexes (which are the VFP equivalent of unique indexes), and these are properly exposed to ODBC and OLE DB, so updates and deletes should work fine.

Can you show an example of the code you are using?

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Wolfen12]

Hi

I'm using OLE DB.I created the dataset, adapter and connection in design view with the Data - toolbox.When you configure the DataAdapter it gives you a wizard where you can set if it should update and delete and select and Insert.I selected all of them, but when you click next it gives the following error messages on Insert and Delete:

Could not determine which coumns uniquely identify the rows for Customers

So according to me the cdx file can't be read, as this is the file where the indexes and settings of the dbf file is stored.

Here is my connectionstring
Connection

this.oleDbConnection1.ConnectionString = "User ID=;DSN=;Cache Authentication=False;Data Source=\"C:\\Tables\";Provider=\"VFPOLE" +"DB.1\ ";Collating Sequence=MACHINE;Mask Password=False;Mode=Share Deny None;Encryp" +
"t Password=False";

 

[Olaf Doschke]

Hi Wolfen,

cdx file contains indexes, true. But free tables can't have a primary key index type. That's a limitation of free tables.

VFP knows several index types, one being primary, then candidate, which is quite the same, but a table may have several candidate indexes, then there are unique and regular indexes. And free tables can't have a primary index, but only candidate indexes.

Nevertheless OLEDB can handle free tables too. In the installation dir of the vfp oledb provider you'll find a chm help file, which has some samples of how to use vfpoledb from visual studio.net. Start from there and see how far you get.

Bye, Olaf.

 

[Mike Lewis]

Wolfen12,

So according to me the cdx file can't be read, as this is the file where the indexes and settings of the dbf file is stored.

Not necessarily. It's much more likely that the free tables don't have a candidate index. As I mentioned earlier, a candidate index is what other databases call a unique index. This is what you need in order to do the update or delete.

I suggest you check the tables within to see if such indexes exist. If you have VFP installed but are unsure how to do that, we can give you step-by-step instructions. If you don't have VFP, I can suggest alternative methods.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Wolfen12]

Hi all

Thanks again for all the help

I actually managed to update and delete, I just had to add the code myself, but yes my problem is still the indexes/uniqueID's/Primary keys.I want to prevent any user from inserting a duplicate entry.I do Have Fox pro 9 on my PC.

Thanks a million guys

 

[Mike Lewis]

my problem is still the indexes/uniqueID's/Primary keys

Given that you have successfully updated and deleted, what is the nature of the problem now?

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Olaf Doschke]

To prevent duplicates, create a candidate index on the free table, if there isn't already one defined (open the table exclusive, start the datasession window with SET, then select the table and look into it's [Properties] and click [Modify] from there to get to the table designer. Then look in the index tab, if there is a candidate index defined.

You either need a field or a combination of fields, that would be a candidate for a primary key and can create a candidate index on an expression concatenating the fields to a char, max 240 bytes mength.

If you have such an index, a duplicate will result in an error.

If only the full list of field applies you should introduce a new field for holding an ID/key of the record, as that is much easier to handle as a foreign key for references in other tables and handle unwanted duplicate in other fields by additional candidate indexes.

Bye, Olaf.

 

[Wolfen12]

Thanks for the advise, Im realy new to this VFP and Free Tables.

When I go to modify, I see the table structure, the field seems to have an index set to it(there's an up arrow).

Can you maybe explain step for step how to see and use that index in C#

Thanks for the help

 

[Mike Lewis]

Wolfen12,

I see the table structure, the field seems to have an index set to it(there's an up arrow).

The up-arrow is a good start. The next step is to go the Indexes tab (in the same dialogue). This lists all the indexes in the table. The important thing is that the Type column should show "Candidate". This is equivalent to a unique index, which is the pre-requisite for the update and delete.

If that's OK, you don't need to do anything special to use the index. The VFP OLE or ODBC driver will find it and use it automatically.

If the Type column doesn't show Candidate, you can change it. But it is possible that the dialogue is read-only (this is indicated in the window title). If so, you need to make sure that the table is opened for exclusive use.

To do that, go back to the command window. Type:

USE MyTable EXCLUSIVE

where MyTable is the path and filename of the table. Then type:

MODIFY STRUCTURE

This will get you back to the table-modification dialogue, where you can change the index type to Candidate if necessary. Note that this will fail if the index already contains duplicate entries.

Hope this all makes sense. Come back if you need more help.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Wolfen12]

Hi again

Thanks very much for althis exellent help.
I'm starting to get really into this and learning something.

1.The type is set to regular
If I understand this correctly, then if the type is set to candidate, then nothing in C# has to be done, I'll automatically will get an error if I try to put in duplicates??

2.That is a problem then:
I'm at a company writing a third party application using the company's tables for the Data, so I am not allowed to do structural changes on the Tables.What now??

 

[Mike Lewis]

I'm starting to get really into this and learning something.

That's always good to hear.

<< if the type is set to candidate, then nothing in C# has to be done, I'll automatically will get an error if I try to put in duplicates?? >>

That's correct.

<< I am not allowed to do structural changes on the Tables.What now?? >>

Then you can't set they index to candidate.

It might be possible to make a setting that says that you don't need the unique key after all, but unfortunately I can't tell you how to do that within your environment. Also, it might have undesirable side-effects.

Is there any chance that you can persuade whoever is responsible to let you add the candidate key? It would make life much easier, and it shouldn't affect any other application that is using the table.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Wolfen12]

Hi Mike/OlafDoschke

I guess well be closing this thread now.Seems the last few Posts was the solution

Unfortunatly the project manager of the main application is on summer vacation, but he is back somewhere next week.

According to me he is treating the duplicate validation in code, which means to me that it wont be risky setting the type, because it is handled just before the time in code, but I'll just make double sure from him that it wont affect their application in some sort of other way, but I oubt it

Thanks so much

 

[markros]

You can try a different approach.

Instead of insert (even if you suspect it should be an insert) first try to do an update and then check the number of records affected. I'm not sure how to get this number, though.

Then, if the number was zero, it means it's a true new record, and now you may try insert.

It is still not 100% bullet-proof solution since another user may try to insert while you're attempting this check.