Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Table corrupted when connection to database terminated during updating database

Posted on 2001-07-01
8
Medium Priority
?
331 Views
Last Modified: 2008-02-26
Platform: Win98(client), Windows NT 4.0 (server)
Development software: MS Visual Basic 6.0
Other: ODBC Visual FoxPro driver version 6.00.8428.00 or 6.00.8167.00 or 6.01.8629.01,
VB6.0 references: MS ADO 2.0 library

Steps to Reproduce Behavior
---------------------------
Create a database with some dummy tables using MS Visual Foxpro 6.0. In this example, the table used
is 'table1' table with fields: field1 (numeric, size 10, primary keys) and field2(character, size 4).
Table must have primary keys. Situated the database in a PC as server (file server) and the following
codes is executed in another PC as client. Program in client side will running this VB program using
ADO 2.0 to access to the database in another PC via ODBC Visual Foxpro driver in the client PC.
Run following codes.

Dim cnn1 As New ADODB.Connection
Dim rs1 As New Recordset
Dim i As Integer

cnn1.Open "DSN=test"
cnn1.BeginTrans
Set rs1 = cnn1.Execute("Select * from table1")

For i = 1 To 10000
  rs1.AddNew
  rs1!field1 = i
  rs1!field2 = "test"
  rs1.Update
Next

cnn1.CommitTrans
cnn1.Close

During execution of the transaction, update table1, interrupt the transaction by
1) Terminate the network connection such as pull off the network cable between client PC and server
PC.
or
2) Cause power failure to the either PC.
The updating table (table1) will be corrupted. Possible cause, table1.dbf and table.cdx file is not
updated relatively.

Problem
-------
After the table is corrupted, update table on the next time does not return error noting the table had
been corrupted. Transaction is done as if there are not problem or corruption on the database. ADO or
ODBC Visual FoxPro DO NOT trigger error or detect problem when accessing/updating the corrupted table.
ADO or ODBC Visual FoxPro DO NOT be able to repair the corrupted table.

Answer needed
------
How to detect the corrupted table on the following accessing to the table. If possible, through the
same method of VB 6.0 -> ADO -> ODBC VFP -> VFP database. 'Writing a dummy data into the table and retrieve
it again to check whether the table is corrupted' is not a acceptable solution because it cause much
processing to do so and not practical for operations that update the database frequently as detection
of corrupted table must be done on fly or immediately after the occurance.

How to repair the corrupted table. If possible, through the same method of VB 6.0 -> ADO -> ODBC VFP
-> VFP database.

Open to any possible answers!!
0
Comment
Question by:liseko
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 1

Accepted Solution

by:
superchook earned 600 total points
ID: 6243829
If you are using a non client-server database, then you do run the risk od non-sync and/or incomplete data transactions - leading to inevitable corruption.

You have been quite lucky - as at least you have seen the errors in the form of outright inability access the data.  The more insidious corruption is where everything seems ok, but there is a linked table or some other relationship that hasn't been updated correctly.

The LAN disconnect is probably the worst case, as it potentially combines both types of error scenario...  remember that with your database methods, you are processing the database locally, so full pages need to be pulled across the network, modified and written back to the 'server'.

In a client-server model, only the data to be written is sent.  The processing is performed a the remote 'server' end of the connection.

There is only one almost foolproof way of protecting yourself in a shared database environment use transaction style batching of additions/updates...  look at the BeginTrans, Rollback and EndTrans functions to assist in this area.

Good luck

0
 
LVL 5

Expert Comment

by:ianouii
ID: 6244408
make an error catcher in your database function.

Dim cnn1 As New ADODB.Connection
Dim rs1 As New Recordset
Dim i As Integer

on error goto ErrHandleror

cnn1.Open "DSN=test"
cnn1.BeginTrans
Set rs1 = cnn1.Execute("Select * from table1")

For i = 1 To 10000
 rs1.AddNew
 rs1!field1 = i
 rs1!field2 = "test"
 rs1.Update
Next

cnn1.CommitTrans
cnn1.Close

ErrHandleror:
do the necessary things here.

with the error catcher, u should be able to know when was the error happen & what to do next. the error can be easily view by putting this codes.

err.description
err.number

good luck.

0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7140027
Hi liseko,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will suggest to:

    Split points between: superchook and ianouii

liseko, if you think your question was not answered at all or if you need help, you can simply post a new comment here.  Community Support moderators will follow up.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Expert Comment

by:ianouii
ID: 7140042
I suppose I answered the question.

> How to detect the corrupted table on the following
> accessing to the table. If possible, through the
> same method of VB 6.0 -> ADO -> ODBC VFP -> VFP
> database. 'Writing a dummy data into the table and
> retrieve it again to check whether the table is
> corrupted' is not a acceptable solution because it cause > much processing to do so and not practical for
> operations that update the database frequently as
> detection of corrupted table must be done on fly or
> immediately after the occurance.

must we share the points?
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7140116
Are we getting just a tad greedy?  superchook posted first with some perceptive comments (include the pivotal concept of using transactions) and there is no guarantee that your answer actually solves the problem.  Let's see if anything happens this week.  -- Dan
0
 
LVL 5

Expert Comment

by:ianouii
ID: 7142205
hehe... sometimes, those points really drive me crazy... somehow, I'm lazy to answer new posting questions since there're many abandoned & opened questions.
Well, it's up to u guys to decide then. I'm ok either way. :D
0
 
LVL 1

Expert Comment

by:Computer101
ID: 7178905
Points split

Computer101
E-E Moderator
0
 

Author Comment

by:liseko
ID: 11508786
INFO: Possible Causes of Data Corruption in Visual FoxPro

Microsoft Knowledge Base:
Last Reviewed: 5/12/2003 (2.0)  
Keywords: kbDatabase kbinfo KB264867
Q264867
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…
Suggested Courses

688 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