473,325 Members | 2,442 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

SP problem with editing records

I have an SP which I use to insert into a table data from another. For
example...

INSERT INTO myWorkTable
(Field1, Field2)

SELECT
myField1, myField2

FROM
myNewTable

It's a pretty straight forward sp. Now, the data that gets inserted
seems to have remained in a state that stops me updating it later. The
SP is called by one app which is then closed and not used again until
the following month. The sp shouldn't be used by anything else.

I have another application which goes to the table (in my example
myWorkTable) and is supposed to open and edit specific records which
it does correctly navigate to (a few days after the sp). It fails and
I get an error message stating that the record is being edited by
another user. This record should not be opened by any other process.

If I export the data and re-import it into the table, it works
perfectly fine.

The code used to edit the table is fine and has worked for a couple of
years and I'm happy with it. The only change I have made is to use an
SP to import the data instead of an old method using Access 2000. The
functionality is the same, even though the connection is different.
The Delphi app uses BDE.

Inserting directly through SQL works as I would have expected and I
can edit the record as I need.

I'm guessing that Delphi (which I use for the app) is somehow not
freeing up the data locked by the SP when it finished calling the SP,
even when it is closed. However, it seems a little bizarre as I would
expect it to free up the data affected by the SP once it has finished
(maybe this is the wrong assumption).

So, my question is, can an SP lock records in this way ? If so,
how/why and can I stop this once finished, or is there a way of
checking / resetting records which were locked up and should no longer
be this way. I can't see anything obvious which shows the records are
locked, but am willing to delve further.

Thanks

Ryan
Jul 20 '05 #1
2 1214
Ryan (ry********@hotmail.com) writes:
I have an SP which I use to insert into a table data from another. For
example...

INSERT INTO myWorkTable
(Field1, Field2)

SELECT
myField1, myField2

FROM
myNewTable

It's a pretty straight forward sp. Now, the data that gets inserted
seems to have remained in a state that stops me updating it later. The
SP is called by one app which is then closed and not used again until
the following month. The sp shouldn't be used by anything else.

I have another application which goes to the table (in my example
myWorkTable) and is supposed to open and edit specific records which
it does correctly navigate to (a few days after the sp). It fails and
I get an error message stating that the record is being edited by
another user. This record should not be opened by any other process.


I would use the Profiler to see what is actually being submitted by that
application. It sounds to me that this is an issue with optimistic
locking. It wasn't clear to me, though, whether you got the message
when you were trying to save the row, or already when you were trying
to read it.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
I was just trying to edit the row. I hadn't got to saving the data as
yet. I don't think there will be much problem with reading it.

I would use the Profiler to see what is actually being submitted by that
application. It sounds to me that this is an issue with optimistic
locking. It wasn't clear to me, though, whether you got the message
when you were trying to save the row, or already when you were trying
to read it.

Jul 20 '05 #3

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

Similar topics

4
by: Dan Weeb | last post by:
Hi All, I have struggled through this far with help from many of you so thanks. I am stuck again. I am really new to this so don't be harsh :-) There are a few problems. You can run the script...
7
by: Drew | last post by:
I have a db table like the following, UID, int auto-increment RegNo Person Relation YearsKnown Now here is some sample data from this table,
7
by: Dave Elliott | last post by:
I have a db that is split and shared on the network. I gave the user a custom menu to use. We both use the same tables of course. On my PC I can create new records using their menu on my PC, on...
3
by: Oren | last post by:
Hi, I've posted this message over at the ms-sql newsgroup – no luck. Maybe some one here could help. I have an Access application with linked tables via ODBC to MSSQL Server 2000. Having a...
2
by: Mike MacSween | last post by:
I've been able, intermittently, to duplicate a problem a client had. Two users editing the same field of the same record. One saves. The other gets the 'another user has edited this record... do...
5
by: Ross A. Finlayson | last post by:
Hi, I'm scratching together an Access database. The development box is Office 95, the deployment box Office 2003. So anyways I am griping about forms and global variables. Say for example...
24
by: Bob Alston | last post by:
Most of my Access database implementations have been fairly small in terms of data volume and number of concurrent users. So far I haven't had performance issues to worry about. <knock on wood> ...
1
by: panwala_bhavesh | last post by:
Thanks for any help - I have a form to enter records. It works fine. It is crowded with over 30 fields. It has as its datasource a table (maybe it should be a query of that table?). What I want to...
0
by: LiamLiamLiam | last post by:
G'day all. I having a problem with my formview. I'll ty to explain my situation as best as i can. I have a page with a search field at the top which is just a simple asp:textbox. Below that i...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

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.