I have some software (written in Delphi 5) which has been working for
several months without a problem.
I have been given a copy of the database on our development server
(SQL 7) and have pointed the software to this. So, nothing has changed
with the software. In theory the servers should be the same.
However, if I try to make a small change to any record (as I would on
the 'live' system), I get an error stating that another user is
editing the record. Now, I'm the only person with access to this
server. So am I stopping myself I wonder ?
If I create an ODBC connection to the database through Access 2000, I
get the same error. I feel I can reasonably assume that the software
is not at fault.
I'm sure it's something SQL based, whether it's an error or
configuration difference, but I'm stuck.
I've checked who is locking/blocking, and yes it will show me as
having the record open, but this is me attempting to edit the record
so I would expect this.
I can edit the table directly in SQL, but it takes 30 seconds or more,
freezes EM and then allows the change. Table info below :
if exists (select * from sysobjects where id =
object_id(N'[dbo].[PostReceived]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[PostReceived]
GO
CREATE TABLE [dbo].[PostReceived] (
[PostID] [int] IDENTITY (1, 1) NOT NULL ,
[Type] [varchar] (100) NULL ,
[ClientsName] [varchar] (100) NULL ,
[DateReceived] [datetime] NULL ,
[EnteredBy] [varchar] (100) NULL ,
[AssignedTo] [varchar] (100) NULL ,
[DateAssignedTo] [datetime] NULL ,
[Adviser] [varchar] (100) NULL ,
[TargetDate] [datetime] NULL ,
[CompletionDate] [datetime] NULL ,
[Completed] [bit] NULL ,
[KeyAccount] [varchar] (100) NULL ,
[Notes] [text] NULL ,
[Specific1] [varchar] (20) NULL ,
[Specific2] [varchar] (20) NULL ,
[Specific3] [varchar] (20) NULL ,
[Specific4] [varchar] (20) NULL ,
[Specific5] [varchar] (20) NULL ,
[ToDelete] [bit] NULL ,
[EnterUser] [varchar] (20) NULL ,
[Returned] [bit] NULL ,
[ReturnDate] [datetime] NULL ,
[ReturnReason] [varchar] (87) NULL ,
[PrintAdviser] [bit] NULL ,
[EmailAdviser] [bit] NULL ,
[EmailSM] [bit] NULL ,
[EmailRegionManager] [bit] NULL ,
[ReturnText] [varchar] (150) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
(Yes I know about using Identity in the key, but it's on my list to
change before anyone says anything). 5 4028
Ryan (ry********@hotmail.com) writes: I have some software (written in Delphi 5) which has been working for several months without a problem.
Has it? Haven't you been posting about it all over again and again? :-)
However, if I try to make a small change to any record (as I would on the 'live' system), I get an error stating that another user is editing the record. Now, I'm the only person with access to this server. So am I stopping myself I wonder ?
If I create an ODBC connection to the database through Access 2000, I get the same error. I feel I can reasonably assume that the software is not at fault.
I'm sure it's something SQL based, whether it's an error or configuration difference, but I'm stuck.
So what is the exact error message you get with ODBC and Access? Including
bracketed stuff like [Microsoft SQL Server Driver] and that?
This is usually a client thing, unless you have a trigger on the table.
SQL Server is certainly not producing a message like that out of the blue.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Ryan (ry********@hotmail.com) writes: Access Error :
"This record has been changed by another user since you started editing it. It you save the record, you will overwrite the changes the other used made.
Copying the changes to the clipboard will let you look at the values the other user entered, and then paste your changes back in if you decide to make changes."
Options to copy to clipboard or drop changes are shown. The error was thrown trying to edit the record in a linked table.
So that is an error message from Access, and since I don't know Access
I have no idea how Access arrives at this conclusion, but I can tell
you that it is not SQL Server that whispers in it is ear.
I can think of two ways to find out:
1) Use the SQL Server Profiler, to see what Access sends to SQL Server.
2) Ask in an Access newsgroup.
Delphi Error :
"Project WorkMan.exe raised exception class EDBEngineError with message 'Couldn't perform the edit because another user changed the record.'. Process stopped. Use Step or Run to continue."
Same thing applies here. Save for the recommendation of an Access newsgroup.
If I am to guess something, Access and Delphi both sets up some server-
side cursors, and then both manages to get lost somewhere when they are
trying to implement optimistic locking.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Erland,
Thanks for your help on this. I managed to find the answer in the
Access forum, albeit pointing to a change I needed to make in SQL.
It's the bit fields that cause the problem. Specifically how ODBC
handles these with NULL values. Part of what I was doing (stupidly)
was adding a bit field into the structure of the table in question and
not setting the values to 0 and not setting the default.
I'd got it in my notes, so assumed I'd done it so didn't bother
checking. It may have helped if I'd mentioned this, but somehow forgot
all about it. An easy oversight. Mental note to self - look for the
easy answer, not the complex one :-) 'Occams Razor' springs to mind
for any brain boxes reading this. (do a search for it if you don't
know what I mean).
So to fix it I did...
1) Ran an UPDATE query, setting all NULL bit fields to 0.
2) On the SQL Server side, set a default value of 0 for the BIT
fields. (or, on
the Access/Delphi side, make sure you provide the value even if
editing an existing record with a NULL value)
Funnily enough it works. Strange how that happens isn't it :-). I'll
let myself out quietly.......
Ryan
Ryan (ry********@hotmail.com) writes: It's the bit fields that cause the problem. Specifically how ODBC handles these with NULL values. Part of what I was doing (stupidly) was adding a bit field into the structure of the table in question and not setting the values to 0 and not setting the default.
That's probably one of the things you could have seen if you had used
the Profiler.
1) Ran an UPDATE query, setting all NULL bit fields to 0.
2) On the SQL Server side, set a default value of 0 for the BIT fields. (or, on the Access/Delphi side, make sure you provide the value even if editing an existing record with a NULL value)
Going back and looking your table definition, I notice that all columns
but the primary key is nullable. I don't think this is a good design.
Bit columns should normally be NOT NULL - unless there really is a need
for three-valued logic. And I find it difficult to believe that
business rules permit ClientsName and DateReceived to be NULL.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Yep, something on my list to sort out. Thanks for the advice.
Erland Sommarskog <es****@sommarskog.se> wrote in message news:<Xn**********************@127.0.0.1>... Ryan (ry********@hotmail.com) writes: It's the bit fields that cause the problem. Specifically how ODBC handles these with NULL values. Part of what I was doing (stupidly) was adding a bit field into the structure of the table in question and not setting the values to 0 and not setting the default.
That's probably one of the things you could have seen if you had used the Profiler.
1) Ran an UPDATE query, setting all NULL bit fields to 0.
2) On the SQL Server side, set a default value of 0 for the BIT fields. (or, on the Access/Delphi side, make sure you provide the value even if editing an existing record with a NULL value)
Going back and looking your table definition, I notice that all columns but the primary key is nullable. I don't think this is a good design. Bit columns should normally be NOT NULL - unless there really is a need for three-valued logic. And I find it difficult to believe that business rules permit ClientsName and DateReceived to be NULL. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: webhigh |
last post by:
Iım not sure if this a PHP question or an MySQL question but here it goes.
I have a repeat region of a table called userid
What Iım trying to accomplish is being able to edit the record and...
|
by: Mark Perona |
last post by:
I created an ASP.net form with an editable datagrid on it. I can
create new records, and update and delete existing records. The
problem I have is that I want a field in the grid to be editible...
|
by: Guy Penfold |
last post by:
Hi all,
I have an asp.net application exhibiting rather alarming behaviour. Among other things, the application provides actors with an online profile that allows them to showcase their skills,...
|
by: jaYPee |
last post by:
i'm wondering how can i update the current record in datagrid while
still editing the record. cause i have a checkbox in my datagrid and i
want to save the record before changing the value of this...
|
by: Nathan Sokalski |
last post by:
I use a webhosting service that is something like halfway around the world
from me and most of the people that will be viewing my site (and I am
assuming their server is too). This obviously has...
|
by: C |
last post by:
Hi,
I have a popup window (aspx) which allows users to edit a database table
record.
If a second user tries to edit the record I want to check if it is being
currently edited / updated and...
|
by: tom c |
last post by:
I am going through "Walkthrough: Editing and Inserting Data in Web
Pages with the DetailsView Web Server Control" found at
http://msdn2.microsoft.com/en-us/library/sdba1d59.aspx
I am using...
|
by: dino d. |
last post by:
hi everyone- my subject pretty much says it all- is there a secure way
to do this? the non-secure ways are, as i understand it, to populate
a listbox with indices as names, or maybe use a hidden...
|
by: paul |
last post by:
A crude, unlikely scenario just so I can get my head around this:
Split DB; front end back end. Each user has the FE locally the BE is
stored on a server.
1. UserA opens customerA's record and...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |