Solved

FOXPRO pack table

Posted on 2007-04-03
24
1,313 Views
Last Modified: 2010-05-19
Hi experts,

I want to know how to pack particular table in foxpro database..I m using .NET 1.1 version, with foxpro database..I have following code snippet:---

string strConnection = @"provider=VFPOLEDB.1 ;data source=
                        '"+strPath+"';password='';user id=''";

                        con = new OleDbConnection();
                        
                        con.ConnectionString = strConnection;
                        con.Open();

                        string strPack= "SET EXCLUSIVE ON;PACK dataproducts";

OleDbCommand comPack = new OleDbCommand(strPack,con);
                        
                        int intCount = comPack.ExecuteNonQuery();

-----------------
0
Comment
Question by:winsoftech
  • 12
  • 11
24 Comments
 
LVL 27

Accepted Solution

by:
CaptainCyril earned 500 total points
ID: 18842590
SET EXCLUSIVE ON
USE dataproducts
PACK
USE

or

USE dataprodcuts EXCLUSIVE;PACK;USE
0
 
LVL 1

Author Comment

by:winsoftech
ID: 18842654
"USE dataproducts EXCLUSIVE;PACK;USE" is running fine and giving me the return from comPack.ExecuteNonQuery() as '75'. 75 is the nos of deleted rows in dataproducts.dbf.....BUT after this all execution, when i reopen dataproducts.dbf, I see all the data as it is, instead of a w/o those 75 rows....Please suggest me further steps......
0
 
LVL 27

Expert Comment

by:CaptainCyril
ID: 18842718
PACK removes all records marked for delettion permanently. They should go!!!
0
 
LVL 1

Author Comment

by:winsoftech
ID: 18842787
I tried it again, BUT those 75 records marked for deletion, remains as it is (with black mark when browsed from foxpro, indication that are marked for deletion)...Is there some seperate statement like 'COMMIT' to finalize changes?? My database connection is getting closed properly without any exception, BUT keeping those '75' records as it is...Please suggest me some workaround....
0
 
LVL 27

Expert Comment

by:CaptainCyril
ID: 18842800
PACK will do all the work for you.
Could you be reading the table in another location by mistake?
Could the table or directory be marked as readonly?
0
 
LVL 1

Author Comment

by:winsoftech
ID: 18842811
Its like before inserting any new data, I need to delete all previous records existing in that table..So I delete all the previous data ( which is working, since all the records are marked as black when browsed in foxpro).....After which i use ' USE dataproducts EXCLUSIVE;PACK;USE'....But this is not actually removing all the records.......
0
 
LVL 1

Author Comment

by:winsoftech
ID: 18842819
I m sure that I m reading the required directory, and its not marked as 'read-only' , since i m able to insert new records at runtime...
0
 
LVL 27

Expert Comment

by:CaptainCyril
ID: 18842847
ok. If no one else is using the file do this:

rename dataproducts.dbf to something else
run your code
rename back the file

tell me what happens
0
 
LVL 1

Author Comment

by:winsoftech
ID: 18842878
step1) I renamed dataproducts.dbf to dataproductsold.dbf

step 2) I executed the code : It gave error: 'INVALID FILE OR PATH NAME'

step 3) I again renamed dataproductsold.dbf back to dataproducts.dbf

After opening dataproducts.dbf, I notice no change......
0
 
LVL 1

Author Comment

by:winsoftech
ID: 18842903
Let me send you my code:-



OleDbConnection con = null;
                  try
                  {
                        string strPath = @"D:\CH\dataproducts.dbf";
                        string strConnection = @"provider=VFPOLEDB.1 ;data source=
                        '"+strPath+"';password='';user id=''";

                        con = new OleDbConnection();
                        
                        con.ConnectionString = strConnection;
                        con.Open();

                        string strPack= "USE dataproducts EXCLUSIVE;PACK;USE";
                              
OleDbCommand comPack = new OleDbCommand(strPack,con);
                        
                        int intCount = comPack.ExecuteNonQuery();
                        MessageBox.Show(intCount.ToString());


}
                  catch(Exception ex)
                  {
                        MessageBox.Show(ex.Message);
                                          }

                  finally
                  {
                        if( con != null)
                        {
                              con.Close();
                        }

                  }



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


0
 
LVL 27

Expert Comment

by:CaptainCyril
ID: 18842910
so this test shows you were accessing the right file
0
 
LVL 27

Expert Comment

by:CaptainCyril
ID: 18842929
is it test data? if not then back it up or use another table.

can you do something else?

USE dataproducts; REPLACE ALL id WITH -id

does this work?
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 1

Author Comment

by:winsoftech
ID: 18842961
I executed 'USE dataproducts; REPLACE ALL id WITH -id' .....It returned 75 rows, But there is no change at all in  data...What does 'REPLACE ALL id WITH -id' do anyway???
0
 
LVL 1

Author Comment

by:winsoftech
ID: 18842979
You can tell me yr email id..I can send you the dataproducts.dbf and the .NET code i m using....So that you can suggest me accurately....Its really urgent for me.....
0
 
LVL 27

Expert Comment

by:CaptainCyril
ID: 18843021
I gave an example to replace a numeric data with it's negative. is it test data?

Instead of deleting records permanently, it's a smaller test to see if your .Net code is working.

do you have any numeric data? is it test data first of all? can we play with it?
0
 
LVL 27

Expert Comment

by:CaptainCyril
ID: 18843052
I don't have .Net installed.

Your code looks ok to me. I am not sure if OLE DB does the work. I never used it. I am sure it works if you open FoxPro as a COM object.
0
 
LVL 1

Author Comment

by:winsoftech
ID: 18843054
Yes, its test data.....I do have numeric data..but it didnt got effected.......
0
 
LVL 27

Expert Comment

by:CaptainCyril
ID: 18843080
sending the file by email will not help.

Is it a test data?

give me a name of a numeric or string field
0
 
LVL 1

Author Comment

by:winsoftech
ID: 18843100
ok...

Few fields in dataproducts.dbf

1) product_code : character(20)
2) product_category_code : numeric(5)
3) product_subcategory_code : numeric(5)
4) start_date :date


I tried executing 'USE dataproducts; REPLACE ALL PRODUCT_CATEGORY_CODE WITH -PRODUCT_CATEGORY_CODE'....But there was no effect.....
0
 
LVL 27

Expert Comment

by:CaptainCyril
ID: 18843139
these are long names.
what is the real field name. Should be max 10 chars.

Try
REPLACE ALL code WITH -code *** if it's numeric

Do you check if the command executed properly or returning error messages?

0
 
LVL 27

Expert Comment

by:CaptainCyril
ID: 18843141
I will be back online in around 1 hour
0
 
LVL 33

Expert Comment

by:CarlWarner
ID: 18844120
How To Pack a Table Through the Visual FoxPro ODBC Driver (VFPODBC.dll)
http://support.microsoft.com/default.aspx?scid=kb;en-us;234756
0
 
LVL 27

Expert Comment

by:CaptainCyril
ID: 18845193
The asker did "SET EXCLUSIVE ON;PACK dataproducts" and did not work.

I wonder why.
0
 
LVL 1

Author Comment

by:winsoftech
ID: 18956464
'SET EXCLUSIVE ON;PACK dataproducts' is right command..BUT IT ONLY PACKS THE TABLE IF EXCUTED SEPERATELY IN .NET....For eg
1) 'SET EXCLUSIVE ON'
2) com.ExecuteNonQuery();
3) 'PACK DATAPRODUCTS'
4) com.ExecuteNonQuery();

0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now