using foxpro table from VB.net 2005 with oledb

[cj]

We have a legacy accounting system (not developed in house) here that
happens to be written in Visual FoxPro. One of the tables has an index
that is actually a coded function COMPANY1 &&"G_RETSDX(COM PANY)".
When I try to use this table in VFP I get an error message "File
'g_retsdx.prg' does not exist." I click ok and continue on. We've
developed many VFP programs outside the accounting system that use this
table and we have to include the function g_retsdx in each of them so
this index will be set and this error will not occur.

Now I need to write a web service that will use this table using VB.net
2005. I set up the connection and commands but when I go to execute a
command, even fill a datatable, I get an exception that says, you
guessed it, "File 'g_retsdx.prg' does not exist." How do I get around this?

I'm sure I could ignore the thrown exception but... I take it Oledb is
capable of reading the indexes of foxpro tables and would update those
indexes if a record was added or deleted. Since I'd be ignoring an
error on one of the many indexes in that CDX I would think it would
corrupt the CDX if I added or deleted a record. And I can't have that
happen.

I'm cross posting this in foxpro and vb.net and I really could use some
advice.

Thanks.

P.S. This is what the VFP foxpro function g_retsdx.prg is:
FUNCTION g_retsdx
PARAMETERS lc_field

*-- Trace call and assertion test removed to permit out of system USE

*-- Declare local variables private
PRIVATE lc_fld1, ;
lc_fld2, ;
lc_fld3, ;
lc_sndxpr

*-- Initialize values for three pieces of lc_field
lc_fld1 = ""
lc_fld2 = ""
lc_fld3 = ""

*-- Remove leading blanks
lc_field = LTRIM(lc_field)

*-- Define the three pieces prior to SOUNDEX conversion
IF LEN(lc_field) 0

*-- If more than one word in string
IF " " $ lc_field
*-- Setup first word
lc_fld1 = LEFT(lc_field, AT(" ", lc_field) - 1)

*-- If remainder is longer than location of " " + 1
lc_field = LTRIM(IIF(LEN(l c_field) AT(" ", lc_field) + 1, ;
SUBSTR(lc_field , AT(" ", lc_field) + 1 ), ""))

ELSE
*-- Setup first and only word
lc_fld1 = lc_field
lc_field = ""
ENDIF

*-- If not at end of string
IF LEN(lc_field) 0

*-- If more than one word left in string
IF " " $ lc_field
*-- Setup second word
lc_fld2 = LEFT(lc_field, AT(" ", lc_field) - 1)

*-- If remainder is longer than location of " " + 1
lc_field = LTRIM(IIF(LEN(l c_field) AT(" ", lc_field) + 1, ;
SUBSTR(lc_field , AT(" ", lc_field) + 1 ), ""))

ELSE
*-- Setup second and last word
lc_fld2 = lc_field
lc_field = ""
ENDIF

*-- If not at end of string
IF LEN(lc_field) 0

*-- If more than one word left in string
IF " " $ lc_field
*-- Setup third word
lc_fld3 = LEFT(lc_field, AT(" ", lc_field) - 1)

*-- If remainder is longer than location of " " + 1
lc_field = LTRIM(IIF(LEN(l c_field) AT(" ", lc_field) + 1, ;
SUBSTR(lc_field , AT(" ", lc_field) + 1 ), ""))

ELSE
*-- Setup third and last word
lc_fld3 = lc_field
lc_field = ""
ENDIF

ENDIF && LEN(lc_field) 0

ENDIF && LEN(lc_field) 0

ENDIF && LEN(lc_field) 0

*-- Develop three piece soundex key expression
lc_sndxpr = SOUNDEX(lc_fld1 ) + SOUNDEX(lc_fld2 ) + SOUNDEX(lc_fld3 )

*-- Return key expression
RETURN lc_sndxpr

[Steven Cheng[MSFT]]

Hi Cj,

From your description, you're encountering some problem when try dealing
with a VPF database in ADO.NET/VB.NET code, correct?

As for the VFP database, have you ever used through .net ADO.NET before and
in what kind of project are you currently using it(winform or web )?

Since I'm not quite familiar with VFP, I think we'd better first isolate
the issue to a typical function. If we can repro it through a simplified
one, that'll help much for continuous troubleshooting .

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------

>Date: Tue, 27 Nov 2007 10:47:31 -0500
From: cj <[e-mail] am>
User-Agent: Thunderbird 2.0.0.6 (Windows/20070728)
MIME-Version: 1.0
Subject: using foxpro table from VB.net 2005 with oledb

>We have a legacy accounting system (not developed in house) here that
happens to be written in Visual FoxPro. One of the tables has an index
that is actually a coded function COMPANY1 &&"G_RETSDX(COM PANY)".
When I try to use this table in VFP I get an error message "File
'g_retsdx.pr g' does not exist." I click ok and continue on. We've
developed many VFP programs outside the accounting system that use this
table and we have to include the function g_retsdx in each of them so
this index will be set and this error will not occur.

Now I need to write a web service that will use this table using VB.net
2005. I set up the connection and commands but when I go to execute a
command, even fill a datatable, I get an exception that says, you
guessed it, "File 'g_retsdx.prg' does not exist." How do I get around

this?

>
I'm sure I could ignore the thrown exception but... I take it Oledb is
capable of reading the indexes of foxpro tables and would update those
indexes if a record was added or deleted. Since I'd be ignoring an
error on one of the many indexes in that CDX I would think it would
corrupt the CDX if I added or deleted a record. And I can't have that
happen.

I'm cross posting this in foxpro and vb.net and I really could use some
advice.

Thanks.

P.S. This is what the VFP foxpro function g_retsdx.prg is:
FUNCTION g_retsdx
PARAMETERS lc_field

*-- Trace call and assertion test removed to permit out of system USE

*-- Declare local variables private
PRIVATE lc_fld1, ;
lc_fld2, ;
lc_fld3, ;
lc_sndxpr

*-- Initialize values for three pieces of lc_field
lc_fld1 = ""
lc_fld2 = ""
lc_fld3 = ""

*-- Remove leading blanks
lc_field = LTRIM(lc_field)

*-- Define the three pieces prior to SOUNDEX conversion
IF LEN(lc_field) 0

*-- If more than one word in string
IF " " $ lc_field
*-- Setup first word
lc_fld1 = LEFT(lc_field, AT(" ", lc_field) - 1)

*-- If remainder is longer than location of " " + 1
lc_field = LTRIM(IIF(LEN(l c_field) AT(" ", lc_field) + 1, ;
SUBSTR(lc_field , AT(" ", lc_field) + 1 ), ""))

ELSE
*-- Setup first and only word
lc_fld1 = lc_field
lc_field = ""
ENDIF

*-- If not at end of string
IF LEN(lc_field) 0

*-- If more than one word left in string
IF " " $ lc_field
*-- Setup second word
lc_fld2 = LEFT(lc_field, AT(" ", lc_field) - 1)

*-- If remainder is longer than location of " " + 1
lc_field = LTRIM(IIF(LEN(l c_field) AT(" ", lc_field) + 1, ;
SUBSTR(lc_field , AT(" ", lc_field) + 1 ), ""))

ELSE
*-- Setup second and last word
lc_fld2 = lc_field
lc_field = ""
ENDIF

*-- If not at end of string
IF LEN(lc_field) 0

*-- If more than one word left in string
IF " " $ lc_field
*-- Setup third word
lc_fld3 = LEFT(lc_field, AT(" ", lc_field) - 1)

*-- If remainder is longer than location of " " + 1
lc_field = LTRIM(IIF(LEN(l c_field) AT(" ", lc_field) + 1, ;
SUBSTR(lc_field , AT(" ", lc_field) + 1 ), ""))

ELSE
*-- Setup third and last word
lc_fld3 = lc_field
lc_field = ""
ENDIF

ENDIF && LEN(lc_field) 0

ENDIF && LEN(lc_field) 0

ENDIF && LEN(lc_field) 0

*-- Develop three piece soundex key expression
lc_sndxpr = SOUNDEX(lc_fld1 ) + SOUNDEX(lc_fld2 ) + SOUNDEX(lc_fld3 )

*-- Return key expression
RETURN lc_sndxpr

[cj]

Steven, unfortunately this problem could probably only be answered by
someone who knows VFP. I did succeed in finding such a person on
another forum. I appreciate your attempt to help. Thanks.
Steven Cheng[MSFT] wrote:

Hi Cj,

From your description, you're encountering some problem when try dealing
with a VPF database in ADO.NET/VB.NET code, correct?

As for the VFP database, have you ever used through .net ADO.NET before and
in what kind of project are you currently using it(winform or web )?

Since I'm not quite familiar with VFP, I think we'd better first isolate
the issue to a typical function. If we can repro it through a simplified
one, that'll help much for continuous troubleshooting .

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------

>Date: Tue, 27 Nov 2007 10:47:31 -0500
From: cj <[e-mail] am>
User-Agent: Thunderbird 2.0.0.6 (Windows/20070728)
MIME-Version: 1.0
Subject: using foxpro table from VB.net 2005 with oledb

>We have a legacy accounting system (not developed in house) here that
happens to be written in Visual FoxPro. One of the tables has an index
that is actually a coded function COMPANY1 &&"G_RETSDX(COM PANY)".
When I try to use this table in VFP I get an error message "File
'g_retsdx.pr g' does not exist." I click ok and continue on. We've
developed many VFP programs outside the accounting system that use this
table and we have to include the function g_retsdx in each of them so
this index will be set and this error will not occur.

Now I need to write a web service that will use this table using VB.net
2005. I set up the connection and commands but when I go to execute a
command, even fill a datatable, I get an exception that says, you
guessed it, "File 'g_retsdx.prg' does not exist." How do I get around

this?

>I'm sure I could ignore the thrown exception but... I take it Oledb is
capable of reading the indexes of foxpro tables and would update those
indexes if a record was added or deleted. Since I'd be ignoring an
error on one of the many indexes in that CDX I would think it would
corrupt the CDX if I added or deleted a record. And I can't have that
happen.

I'm cross posting this in foxpro and vb.net and I really could use some
advice.

Thanks.

P.S. This is what the VFP foxpro function g_retsdx.prg is:
FUNCTION g_retsdx
PARAMETERS lc_field

*-- Trace call and assertion test removed to permit out of system USE

*-- Declare local variables private
PRIVATE lc_fld1, ;
lc_fld2, ;
lc_fld3, ;
lc_sndxpr

*-- Initialize values for three pieces of lc_field
lc_fld1 = ""
lc_fld2 = ""
lc_fld3 = ""

*-- Remove leading blanks
lc_field = LTRIM(lc_field)

*-- Define the three pieces prior to SOUNDEX conversion
IF LEN(lc_field) 0

*-- If more than one word in string
IF " " $ lc_field
*-- Setup first word
lc_fld1 = LEFT(lc_field, AT(" ", lc_field) - 1)

*-- If remainder is longer than location of " " + 1
lc_field = LTRIM(IIF(LEN(l c_field) AT(" ", lc_field) + 1, ;
SUBSTR(lc_field , AT(" ", lc_field) + 1 ), ""))

ELSE
*-- Setup first and only word
lc_fld1 = lc_field
lc_field = ""
ENDIF

*-- If not at end of string
IF LEN(lc_field) 0

*-- If more than one word left in string
IF " " $ lc_field
*-- Setup second word
lc_fld2 = LEFT(lc_field, AT(" ", lc_field) - 1)

*-- If remainder is longer than location of " " + 1
lc_field = LTRIM(IIF(LEN(l c_field) AT(" ", lc_field) + 1, ;
SUBSTR(lc_field , AT(" ", lc_field) + 1 ), ""))

ELSE
*-- Setup second and last word
lc_fld2 = lc_field
lc_field = ""
ENDIF

*-- If not at end of string
IF LEN(lc_field) 0

*-- If more than one word left in string
IF " " $ lc_field
*-- Setup third word
lc_fld3 = LEFT(lc_field, AT(" ", lc_field) - 1)

*-- If remainder is longer than location of " " + 1
lc_field = LTRIM(IIF(LEN(l c_field) AT(" ", lc_field) + 1, ;
SUBSTR(lc_field , AT(" ", lc_field) + 1 ), ""))

ELSE
*-- Setup third and last word
lc_fld3 = lc_field
lc_field = ""
ENDIF

ENDIF && LEN(lc_field) 0

ENDIF && LEN(lc_field) 0

ENDIF && LEN(lc_field) 0

*-- Develop three piece soundex key expression
lc_sndxpr = SOUNDEX(lc_fld1 ) + SOUNDEX(lc_fld2 ) + SOUNDEX(lc_fld3 )

*-- Return key expression
RETURN lc_sndxpr