Re: ODBC break the table index

From: Jeroen van Kalken (I_at_dont.like.spam)
Date: 08/24/04


Date: Tue, 24 Aug 2004 23:44:10 +0200


It could be because the first index has an expression that evaluates
to either a 1 or a 9 length string. just add 8 spaces behind the Z to
make it always produce a 9 char string.

>index on iif(balance<>0,"A"+DTOS(servdate),"Z") tag balance additive

(also 'additive' is superflous when adding tags to a compound index)

>On Tue, 24 Aug 2004 08:47:03 -0700, Steven <Steven@discussions.microsoft.com> wrote:

>Hi, Cindy:
>
>The webservice only accept string and put each field into table(use insert
>into....SQL command, and then update dataset). There is another program which
>talk to the database and do medical billing management. This program is
>developed by VFP7.0. Not all times but sometimes we have to do reindex to
>see the result, and refresh doesn't work.
>
>Here are the index expressions:
>index on iif(balance<>0,"A"+DTOS(servdate),"Z") tag balance additive
>index on ptrecid+invno+DTOS(servdate) descending tag ptrecid additive
>index on servno tag servno additive
>index on servdate tag servdate additive
>index on ptrecid+DTOS(servdate) descending tag patservdt additive
>index on ptrecid + servno tag patservno additive
>INDEX ON ref1_num TAG ref1_num additive
>INDEX ON insclaimno TAG insclaimno additive
>
>Thanks for your kindly response.
>
>Steven
>
>"Cindy Winegarden" wrote:
>
>> Hi Steven,
>>
>> You can't reindex Fox tables via ODBC or OLE DB. However, CDX indexes are
>> supposed to update themselves automatically when data is added, changed, or
>> deleted.
>>
>> What exactly are the index expressions on your table(s)?
>>
>> "...accept a string which is delimited by "|" and put each field to the
>> table. The problem is after I
>> update table, it break the table index, and I have to index the table before
>> I see the result."
>>
>> Can you insert data directly into the table ("Insert Into myTable Values (1,
>> "Two")") ? What code do you run to get the result where you don't "see" the
>> new data - are you checking it in FoxPro or retrieving a fresh dataset via
>> OLE DB?
>>
>>
>> --
>> Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
>> cindy.winegarden@mvps.org www.cindywinegarden.com
>>
>>
>> "Steven" <Steven@discussions.microsoft.com> wrote in message
>> news:24C91EB5-0FCA-401E-A980-1F6688575CEC@microsoft.com...
>> > Hi, Cindy:
>> >
>> > I use CDX format.
>> >
>> > I changed the way to connect DBC file, but still has the same problem.
>> > Here
>> > is the connection string I used now:
>> > strConnection = "provider=VFPOLEDB.1; " & _
>> > "data source='C:\TEST.DBC';" & _
>> > "Mode=ReadWrite|Share Deny None ;" & _
>> > "Collating Sequence=MACHINE;" & _
>> > "password=''; user id=''"
>> >
>> > Is there any method to reindex the table, or whatever the way to maintain
>> > the table index. My program run under network, and all table open Shared.
>> >
>> > Thanks for your response.
>> >
>> > Steven
>> >
>> > "Cindy Winegarden" wrote:
>> >
>> >> Hi Steven,
>> >>
>> >> Are you using a Compound Index (CDX) or the older IDX format?
>> >>
>> >>
>> >> --
>> >> Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
>> >> cindy.winegarden@mvps.org www.cindywinegarden.com
>> >>
>> >>
>> >> "Steven" <Steven@discussions.microsoft.com> wrote in message
>> >> news:A5A39D63-C1DF-4357-931B-18135762B47D@microsoft.com...
>> >> > Hi,
>> >> >
>> >> > I am using .NET write a webservice that will use ODBC to access Visual
>> >> > Foxpro database table. This webservice will accept a string which is
>> >> > delimited by "|" and put each field to the table. The problem is after
>> >> > I
>> >> > update table, it break the table index, and I have to index the table
>> >> > before
>> >> > I see the result.
>> >> >
>> >> > Can any body give me some comments how to fix this problem? Thanks
>> >> >
>> >> > Steven
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>