writing a new record using oledb

[terrance78900]

I am having trouble writing a record into a visual foxpro dbf. i am using oledb to write it. I can get the record into the database, but when i try to access the table from a visual foxpro application, i can't see the record i wrote until i re-index the file. HELP

 

[bborissov]

Could you post the code where you insert that record?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[terrance78900]

******CONNECTION STRING*******************
Public connectstring As String = "Provider= VFPOLEDB.1;Data Source = f:\crmswin;MODE=SHARE DENY NONE;EXCLUSIVE=FALSE;"


Public Function AddShopTicket(ByVal e As ShopTicket)
Dim ItemAdded As Boolean
e.nontax = False
e.Desc_text = False
e.Paid = False
Dim amt As Double = 0.0
e.Order_no = "00" + e.Order_no
mycommand = New OleDbCommand("", mycon)
mycommand.ExecuteNonQuery()
Dim dr As OleDbDataReader
Dim str As String = "INSERT INTO ordfile.dbf(Store_code,Order_no, cust_no, Cname, Cphone, Cphone2, Inv_no, Date_order,paid, salesman, ord_type, frt_amt, frt_code, tax_code, dep_amt, model, desc1, disc_type, non_tax, desc_text, po_num, project, type_cash, cash_amt, chk_amt, cc_amt, chk_no,cc_recno, dr_lic, inv_amt, state_tax, cnty_tax, city_tax, inv_tot, no_tax_typ, non_taxed, taxed_amt, amt_paid, user_id, inv_date, brand, serial_no, contact) VALUES (' ', '" & e.Order_no & "', '9999', '" & e.CustName & "', '" & e.phone & "', ' ', ' ', {" & e.DropDate & "}, .F., ' ', ' ', 0.00,' ', '37214', 0.00, '" & e.model & "', '" & e.status & "', ' ', .F., .T., ' ', ' ', ' ', 0.00, 0.00, 0.00, ' ', ' ', ' ', 0.00, 0.00, 0.00, 0.00, 0.00, ' ', 0.00, 0.00, 0.00, ' ', { }, '" & e.Brand & "', '" & e.Serial & "', '" & e.Contact & "')"
mycommand = New OleDbCommand(str, mycon)
Try
dr = mycommand.ExecuteReader
'mycommand = New OleDbCommand("USE ORDFILE SHARED INDEX ORDKEY", mycon)
'mycommand.ExecuteNonQuery()
'Dim str1 As String = "PACK ORDFILE.DBF"
dr.Close()
'mycommand = New OleDbCommand(str1, mycon)
'mycommand.ExecuteNonQuery()
ItemAdded = True
Catch ex As Exception
ItemAdded = False
MsgBox(ex.Message)
'MsgBox(ex.ToString)
'MsgBox("Error Adding Shop Ticket Into Ordfile.dbf", MsgBoxStyle.Critical)
End Try
Return ItemAdded
End Function

 

[bborissov]

OK,
That commented line gives me some idea:

'mycommand = New OleDbCommand("USE ORDFILE SHARED INDEX ORDKEY", mycon)

From it I realize that you use IDX as indexes, right?
If so yo are out of luck. VFP didn't open them automatically. You should reindex file all the time you do some changes in that table and forgot to open that index.
Why not start to use compound indexes. They are stored in one CDX file and VFP automatically opens when the table is open and refresh them after you do some changes.
Let me give you an example:

CLEAR
SET SAFETY OFF 

CREATE TABLE Test FREE (Fld1 int)
INDEX ON Fld1 TO Test && That creates a IDX file
CLOSE DATA ALL

SELECT 0
USE Test INDEX Test
INSERT INTO Test VALUES (3)
INSERT INTO Test VALUES (2)
INSERT INTO Test VALUES (1)
BROWSE

SEEK 2
? [seek 2], FOUND()

CLOSE DATA ALL

*** BUT! (open table w/o open the index)
SELECT 0
USE Test
INSERT INTO Test VALUES (6)
INSERT INTO Test VALUES (5)
INSERT INTO Test VALUES (4)
SET INDEX TO Test
BROWSE    && You will see only first 3 records

? [seek 5], FOUND()

SET INDEX TO 
BROWSE    && You will see all records


*** Now lets use CDX
CLOSE DATA ALL
SELECT 0
USE Test EXCLUSIVE 
INDEX ON Fld1 TAG Test
CLOSE DATA ALL
INSERT INTO Test VALUES (9)
INSERT INTO Test VALUES (8)
INSERT INTO Test VALUES (7)
BROWSE 
SEEK 8 ORDER Test
? [seek 8 with CDX], FOUND()

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[terrance78900]

is there any way to reindex the files from vb.net without having exclusive use of the file. The reason is that the vfp application is already written, but it is an older application(text-based), but we want to start using forms to access our data.

 

[bborissov]

Unfortunately, no. To reindex tables using compound indexes tables have to opened exclusive.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[JRB-Bldr]

"it is an older application(text-based), but we want to start using forms to access our data"
and
"from vb.net"

So it would seem that you are trying to re-create your original Foxpro application (typically text-based Foxpro applications are Foxpro-DOS, not Visual Foxpro) to a VB.Net application.

Obviously most of us here would suggest that you upgrade your old Foxpro application to Visual Foxpro 9 where you could not only end up with a state-of-the-art application utilizing full GUI interfaces based on a current development language, but also do the conversion task so MUCH more easily. Admittedly, not everyone is convinced of that.

Regardless, there are a number of resources available for information about converting from FP/VFP to VB.NET.
A Google search of the web for VFP "VB.NET" returns quite a few 'hits'

Some of those include:

http://msdn.microsoft.com/en-us/vfoxpro/bb190277.aspx

http://www.amazon.com/Visual-FoxPro-Basic-NET/dp/0672326493

http://www.aims-co.com/VFP2VB_form/Home.htm

Perhaps it might be worth your time to consult some of those references or look on the web for contractors intimately familiar with both languages who could assist you.

Good Luck,
JRB-Bldr

 

[terrance78900]

Thanks. Appreciate all the help. So it is possible to create a windows forms application with VFP9? That is all that i need to do right now. We are switching the databases to mySQL early next year.

 

[bborissov]

Sure,
You could create a full Windows application w/o any problems and I am sure several times faster than any other language

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[JRB-Bldr]

Fortunately, once you have re-created your application in VFP9, you can continue to use the application, but merely change the back-end to MySQL when needed. Thereby eliminating the need to yet again re-develop the application in yet another language.

Admittedly you might want keep that future back-end change in mind when you do your VFP9 development. Certain design approaches and 'hooks' put in up-front will GREATLY facilitate data source changes later.

Good Luck,
JRB-Bldr