Data Change or Conflict Error

[majors479]

Hi,

I apologize if this question should be asked within the Access forumn, but I believe the issue is with SQL. I have a back-end SQL DB connected to an Access front-end DB. When the user clicks a button the following stored procedure is run

@RecordType as varchar(25),
	@PrimaryKey as varchar(50),
	@UserName as varchar(25)

 AS


IF @RecordType = 'Parts'
BEGIN
	DECLARE @CheckedOutBy as varchar(25)
	DECLARE @CheckedOutDate as datetime

	SELECT @CheckedOutBy = ISNULL(CheckedOutByUserName,''), @CheckedOutDate = CheckedOutDate
	FROM tblParts
	WHERE Partscode= @PrimaryKey 

	IF @CheckedOutBy > ''
		IF @CheckedOutBy = @UserName
			SELECT 'Success'
		ELSE
			SELECT 'This record is already checked out by ' + @CheckedOutBy + ' on ' + CONVERT(varchar(25), @CheckedOutDate)
		--SELECT 'This record is already checked out by ' + @CheckedOutBy + ' on ' + CONVERT(varchar(25), @CheckedOutDate)
	
	ELSE
		BEGIN
	
			UPDATE tblParts
			SET 	CheckedOutByUserName = @UserName,
				CheckedOutDate = GETDATE()
			WHERE PartsCode = @PrimaryKey
			
			SELECT 'Success'
	
		END

END

Which enters the user name and date of the record that is being checked out. The user should then be able to continue to edit the record. When the identical front-end is hooked up to one server the user gets the message that "The data has been changed by another user" (which I don't want) after he changes the first field within the record. I realized that the conflict is because the stored procedure is entering data into the record. But when the front-end is hooked up to another server (running the identical stored procedure off of identical table), no such message appears.

I am attempting to ascertain what I possibly need to change within SQL Server that will allow the user not to get the data has changed message.

Thanks so much for your assistance.

Sydney

 

[Qik3Coder]

Check for a trigger or a foreign key on the table in the database that is giving you an issue.

-Sometimes the answer to your question is the hack that works

 

[majors479]

Thanks for the quick reply and your assistance.

Checked both of your suggestions and that isn't it. I believe its the way each SQL Server DB is refreshing the data or possibly the locking and concurrency setting of each DB.

I don't get the message with the DB when I move off of the record and then move back onto it. (Which makes sense). However, with the DB I don't get the message, I can stay on the record and edit it without incident.

Everything with the front-end is the same with both DBs.

PS. The data in the field that I enter is deleted after the message.


Any further help would be greatly appreciated.

Thanks,

Sydney

 

[Qik3Coder]

On the server that you are not getting error on, are you the only one using it?

Where is this message coming from. It doesn't look like a SQL error.

"The data has been changed by another user"

-Sometimes the answer to your question is the hack that works

 

[majors479]

Thanks again for your assistance.

The error is happening in Access and the non-error server has 30 users on it. Even though the error is happening in Access, I'm thinking (I could very well be wrong) its SQL, as the same Access DB linked up to the other SQL Server (with the identical stored procedure) acts differently when the stored procedure updates the record (not another user) and then the user updates the same record.


Thanks,

Sydney

 

[SQLSister]

Do you by chance have a bit field in the table?

If so you need a timestamp column in the table.


"NOTHING is more important in a database than integrity." ESquared