473,598 Members | 3,145 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Someone else editing record ? Only me on the server ?

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 ,
[EmailRegionMana ger] [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).
Jul 20 '05 #1
5 4050
Ryan (ry********@hot mail.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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
Ryan (ry********@hot mail.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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3
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
Jul 20 '05 #4
Ryan (ry********@hot mail.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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #5
Yep, something on my list to sort out. Thanks for the advice.

Erland Sommarskog <es****@sommars kog.se> wrote in message news:<Xn******* *************** @127.0.0.1>...
Ryan (ry********@hot mail.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.

Jul 20 '05 #6

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

Similar topics

4
3658
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 then update it by clicking a submit button on the same line. I¹m doing it this way so the user can quickly update a number of records one at a time. I know I could do this by passing the userid info to a detail page but really want to do it the way...
2
1732
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 when I'm editing a new record, but disabled when editable an existing record. Any ideas Mark
3
1741
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, photographs, experience etc. Basically it is an asp.net front end with SQL Server 2000 as the data store. The problem behaviour occurs when two users are editing the same type of data (and therefore requesting the same aspx page). If they both...
2
1373
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 checkbox to true to enforce referential integrity. i don't have problem changing the checkbox to true if the record/row in my datagrid is already save. the problem is that if it is the 1st time i add record to this datagrid and then update the...
8
1234
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 very little effect on what I can do, but there is one thing that I would like to know how to do (if it can be done). When I do something that involves getting the current date or time, my application gets it from the server it is running on, which...
4
1324
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 display a message to the user stating that it is currently being updated. What is the best way to achieve this?
0
1631
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 Access instead of SQL Server. Everything works fine until I get to the last step, where I try to insert a record. When I hit the New link it lets me add the new data, but then when I hit Insert, I get the yellow page which I have paseted below. I...
12
1798
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 control. is the only secure way with sessions? i was also thinking about perhaps creating a meta table that says who is editing which record right now. any advice? thanks, dino
22
10965
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 edits the telephone number. 2. UserB opens the same form and tries to navigate to customerA's record. At this point I want to inform UserB that the record is locked. How do
0
7985
marktang
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
7896
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
8393
jinu1996
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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
6713
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
5848
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5438
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
3939
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1503
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1246
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.