FOXPRO pack table

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();

-----------------
LVL 1
winsoftechAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CaptainCyrilFounder, Software Engineer, Data ScientistCommented:
SET EXCLUSIVE ON
USE dataproducts
PACK
USE

or

USE dataprodcuts EXCLUSIVE;PACK;USE
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
winsoftechAuthor Commented:
"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
CaptainCyrilFounder, Software Engineer, Data ScientistCommented:
PACK removes all records marked for delettion permanently. They should go!!!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

winsoftechAuthor Commented:
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
CaptainCyrilFounder, Software Engineer, Data ScientistCommented:
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
winsoftechAuthor Commented:
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
winsoftechAuthor Commented:
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
CaptainCyrilFounder, Software Engineer, Data ScientistCommented:
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
winsoftechAuthor Commented:
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
winsoftechAuthor Commented:
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
CaptainCyrilFounder, Software Engineer, Data ScientistCommented:
so this test shows you were accessing the right file
0
CaptainCyrilFounder, Software Engineer, Data ScientistCommented:
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
winsoftechAuthor Commented:
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
winsoftechAuthor Commented:
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
CaptainCyrilFounder, Software Engineer, Data ScientistCommented:
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
CaptainCyrilFounder, Software Engineer, Data ScientistCommented:
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
winsoftechAuthor Commented:
Yes, its test data.....I do have numeric data..but it didnt got effected.......
0
CaptainCyrilFounder, Software Engineer, Data ScientistCommented:
sending the file by email will not help.

Is it a test data?

give me a name of a numeric or string field
0
winsoftechAuthor Commented:
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
CaptainCyrilFounder, Software Engineer, Data ScientistCommented:
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
CaptainCyrilFounder, Software Engineer, Data ScientistCommented:
I will be back online in around 1 hour
0
CarlWarnerCommented:
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
CaptainCyrilFounder, Software Engineer, Data ScientistCommented:
The asker did "SET EXCLUSIVE ON;PACK dataproducts" and did not work.

I wonder why.
0
winsoftechAuthor Commented:
'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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.