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). 5 4060
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
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
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********@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
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. 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 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...
|
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
|
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...
|
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...
|
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...
| |
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?
|
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...
|
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
|
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
|
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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
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...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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...
|
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...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
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...
| |