472,952 Members | 2,067 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,952 software developers and data experts.

Data Shifting Between Records

Is there a problem with stability when one uses too many text (memo) fields?
I'm having a problem with data from one record occasionally ending up in
another record, though apparently not through any user interaction.

I'm using SQL 7 and accessing the tables through the ODBC driver. There are
two tables with a one-to-one relationship -- TableA and TableB. TableB is
the one that's having this occasional problem. TableB has about 30 text
fields out of a total of about 50 fields in the table. The problem is
usually with one or two text fields containing data from a different record,
one that was created close to when the problem record was created.

Example: TableB has fields 1-9, say. In one record, field 1 has A, field 2
as B, field 3 has C, and so on.

In another record (created shortly after the other one), field 1 has AA,
field 2 has BB, field 3 has CC, and so on.

The user works with the records, everything's fine. Then one day the user
notices that in the second record, field 1 has AA, field 2 has BB, but field
3 has C instead of CC. In other words, all data's fine, except for one,
maybe two fields, that have data from a previously-created record.

At first glance this seems to be a user-interaction thing, that somehow the
user inadvertantly placed data from the older record into the newer one,
either through a shortcut, or by having that data on the clipboard, or
whatever. But a recent incident opposes that theory.

I have two forms in the front end for editing records (the forms are bound
to the ODBC table links). Form1 is bound only to TableA (the one that
doesn't have the problem); Form2 is bound to a query that is TableA joined
with TableB.

In the recent incident where data shifted, both the record that was affected
and the record from which the data came were both only edited with Form1. In
other words, TableB never came into play; yet its data was somehow affected.

When a record is created, the user completes a few fields in a form, and
then a stored procedure creates a record in TableA and then a sister record
in TableB (using the TableA record's autonumber primary key as its primary
key). A couple of user entered values are entered into the TableB record.
But if the user is using Form1, they never see the TableB record.

In this case, the TableB record's two fields got changed to fields from an
earlier record (one which was created a little earlier the same day), even
though both records were only edited in Form1 (according to the history
log), which doesn't touch TableB.

Thus, I'm wondering if there's a possibility that either the SQL database or
the ODBC driver somehow shifted the data from one record into another. That
seems far-fetched. But, at this point, since a table that the user didn't
touch somehow had its data changed to data from a different record, I'm
trying to explore all possibilities.

Thanks for any insight!

Neil

Mar 23 '08 #1
4 2235
"Neil" <no****@nospam.netwrote in message
news:7u*****************@nlpi068.nbdc.sbc.com...
Is there a problem with stability when one uses too many text (memo)
fields?
I'm having a problem with data from one record occasionally ending up in
another record, though apparently not through any user interaction.
<snipped>

Thus, I'm wondering if there's a possibility that either the SQL database
or
the ODBC driver somehow shifted the data from one record into another.
That
seems far-fetched. But, at this point, since a table that the user didn't
touch somehow had its data changed to data from a different record, I'm
trying to explore all possibilities.
Can you post the code and DDL for the tables and calls being made?

That might help.

If this were a real bug, I suspect it would be reported by now. (and maybe
it has, have you tried the MS knowledge base?)

Thanks for any insight!

Neil


--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
Mar 23 '08 #2
Neil (no****@nospam.net) writes:
Thus, I'm wondering if there's a possibility that either the SQL
database or the ODBC driver somehow shifted the data from one record
into another. That seems far-fetched. But, at this point, since a table
that the user didn't touch somehow had its data changed to data from a
different record, I'm trying to explore all possibilities.
Since your description is very abstract, it's difficult to tell what
is going on, but the likelyhood that the ODBC driver would confuse tables
on its own is nil. The likelyhood that SQL Server would do it also very
small, but it could be a corruption issue. However, a corruption usually
manifests itself with violent error messages. But run DBCC CHECKDB on the
database to rule that out.

I would direct my attention to the application code. That is, the stored
procedures and the client code. Something may not be what you think it is.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Mar 23 '08 #3
Since your description is very abstract, it's difficult to tell what
is going on, but the likelyhood that the ODBC driver would confuse tables
on its own is nil.
A point of clarity: it's not confusing tables, but confusing records. The
correct tables are being updated. But, somehow, a related table had its data
modified in the corresponding record.

I mention the ODBC driver, especially in the context of multiple text
fields, because it has been known to go to the wrong record when going to a
record in a large recordset. In one application, I frequently would have the
ODBC driver go to the record that was exactly 100 in sequence behind the one
it was supposed to go to. I found that moving to the last record before
going to the one I needed resolved the issue. I think it was fixed with a
later patch, though.

Come to think of it, I don't think that was a problem with the ODBC driver,
but with the Jet engine, which was using the ODBC driver to access cs
databases. So it could be a Jet issue, actually.

Point is, a front end engine getting confused when accessing a record in a
cs database via ODBC is not without precedent.
The likelyhood that SQL Server would do it also very
small, but it could be a corruption issue. However, a corruption usually
manifests itself with violent error messages. But run DBCC CHECKDB on the
database to rule that out.
CHECKDB said everything was OK. Didn't think it was SQL Server, but figured
it wouldn't hurt to ask here, especially since the people here might have
come across something like this while using ODBC in some applications.
>
I would direct my attention to the application code. That is, the stored
procedures and the client code. Something may not be what you think it is.
Yes, that would be the likely culprit. Only thing is, there is no code that
updates those fields. And it's usually just one or two fields, not the
entire record, that's awry. Could be something the user's doing; but I can't
imagine what. They say they enter data in the record, check it, everything's
fine. Then, sometime later, they go back and look at the record, and it has
data from another record in one or two fields. It's happening about once or
twice a month now.

Thanks,

Neil
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Mar 23 '08 #4
Neil (no****@nospam.net) writes:
I mention the ODBC driver, especially in the context of multiple text
fields, because it has been known to go to the wrong record when going
to a record in a large recordset. In one application, I frequently would
have the ODBC driver go to the record that was exactly 100 in sequence
behind the one it was supposed to go to. I found that moving to the last
record before going to the one I needed resolved the issue. I think it
was fixed with a later patch, though.

Come to think of it, I don't think that was a problem with the ODBC
driver, but with the Jet engine, which was using the ODBC driver to
access cs databases. So it could be a Jet issue, actually.
Yes, Access or Jet or something else that uses the ODBC driver could
maybe do something like that. But the driver on its own? That seems less
likely to me.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Mar 23 '08 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

16
by: D Witherspoon | last post by:
I am developing a Windows Forms application in VB.NET that will use .NET remoting to access the data tier classes. A very simple way I have come up with is by creating typed (.xsd) datasets. For...
4
by: Tim Smith | last post by:
I have a project that requires logging of data to a file. The file is supposed to hold records until it reaches a certain size, then start shifting the oldest records out as the new data is being...
2
by: salsipius | last post by:
Can someone please help me clarify the below code. I think the shifting has to do with converting datatypes and/or loss of data but am not really clear on the details, could you help shed some...
8
by: Eric.Medlin | last post by:
I am reading in 1bit data to a buffer that contains over 30000 bits and I would like to beable to bitshift the entire buffer and AND and OR it with other buffers of the same size. I though I could...
10
by: krunalb | last post by:
Hi, I am trying to shift unsigned long long value by 64 bits and this is what i get #include <stdio.h> int main() { unsigned short shiftby= 64;
20
by: Charles Sullivan | last post by:
I understand different processor hardware may store the bits in a byte in different order. Does it make a difference in C insofar as bit-shifting unsigned char variables is concerned? E.g, if I...
4
by: Neil | last post by:
I previously posted about data shifting between records in my Access 2000 MDB with a SQL Server 7 back end, using ODBC linked tables. Every once in a while, data from one record mysteriously...
0
by: hancjiao | last post by:
"Neil" <nospam@nospam.net写入消息新闻:7Tquj.11650$Ej5.10391@newssvr29.news.prodigy.net...
5
by: _dee | last post by:
I'm working on a port of a legacy app originally written in C. Data was compacted into bit fields. Are there any sites or books that cover optimized handling of this type of data? I'd need to...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
2
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
3
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
0
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.