Trust ID PBCEX logo 300x102

Problem with multiple connections to same SQL DB

7 years 4 months ago 7 years 4 months ago by Chris. Reason: Added log files
Chris
Fresh Boarder
Fresh Boarder
Posts: 3
More
Topic Author
Problem with multiple connections to same SQL DB #2520
I’ve been having an issue when there are multiple connections to the same database, the scenario is as follows

• We have a SQL Server 2008 R2 Database set up with CardExchange Ultimate 6.3.2.3 reading writing to the same table
• The DB is set up to auto generate the ID of each new record
• We have 2 PCs, PC1 and PC2, and CardExchange freshly opened on each.
• PC1, creates a record with no issue (record is created and correct details are highlighted and displayed in the names section, edit database record and on the card, I repeated the process another 2 times and no issue. I’ve had a look in the DB and there are 3 new records created with sequential IDs (2426, 2427, 2428) – see extract from DB
• PC2 creates a record but now all of a sudden the name jumps to a different record, if I search for the record and select it, I get completely a different records details in the edit database record and on the card. If I look at the DB, SQL has created the record correctly with the next sequential ID (2429).

Looking at the logs for each PC, I can see all the correct Inserts happening and the Select @@Identity getting the correct ID for newly inserted record.

For some strange reason CardExchange is associating the record just created with a different ID

If I refresh the data by pressing F5, it resolves the issue (not an ideal scenario to refresh the data after every action)

I can reproduced this error easily and it occurs when there is more than one connection to a DB, the insert by PC2 isn’t at the same time as PC1 so there shouldn’t be any conflict with reading the @@Identity

I’ve included the logs which has the SQL statements

Any help would be greatly appreciated
Attachments:
7 years 4 months ago
Rutger Koperdraad
Platinum Boarder
Platinum Boarder
Posts: 1566
More
Problem with multiple connections to same SQL DB #2527
Hi Chris,

I spent quite some time testign without being able to reproduce the issue. Would it be possible to send me the data folder? If it is too big for teh forum, you can probably use This email address is being protected from spambots. You need JavaScript enabled to view it. or make it downloadable somehow.

regards,
Rutger.

Rutger Koperdraad
Senior Software Engineer

"A reasonable man adapts himself to the world. The unreasonable man persists in trying to adapt the world to himself. All progress, therefore, depends on the unreasonable man." (Bernard Shaw)
7 years 4 months ago
Chris
Fresh Boarder
Fresh Boarder
Posts: 3
More
Topic Author
Problem with multiple connections to same SQL DB #2529
Hi Rutger,

I have emailed the data folder to the support email address.

Hope you can help.

Cheers,

Chris
7 years 4 months ago
Sergio Agostinho
Administrator
Administrator
Posts: 508
More
Problem with multiple connections to same SQL DB #2531
Hi Chris,

Please remove the location filter and run the tests again on the pc that is giving you problems, i think the filter is doing it's job and forcing the records to be shown to some records only.

If the problem is not solved with the filters disabling please send us a backup of the sql server database and i can reproduce here exactly the same environment as you have there and try to figure out another reason for your issue.

With best regards
Sergio Agostinho
7 years 4 months ago
Chris
Fresh Boarder
Fresh Boarder
Posts: 3
More
Topic Author
Problem with multiple connections to same SQL DB #2532
Thanks Sergio, I will give this a try when I get into the office tomorrow.

In preparation, here's the table create script we are using.

CREATE TABLE [dbo].[_CardExchange](
[ID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[EmploymentCategory] [varchar](25) NULL,
[CompanyName] [varchar](50) NULL,
[NewCardNo] [varchar](6) NULL,
[NewCardExpiry] [date] NULL,
[Location] [varchar](50) NULL,
[CardNoGeorgeSt] [varchar](6) NULL,
[DateCardPrinted] [date] NULL,
[NumberTimesCardPrinted] [int] NULL,
[PrintedByUserName] [varchar](50) NULL,
[Photo] [varbinary](max) NULL
) ON [PRIMARY]

GO
7 years 4 months ago
Rutger Koperdraad
Platinum Boarder
Platinum Boarder
Posts: 1566
More
Problem with multiple connections to same SQL DB #2534
Dear Chris,

I found the cause of the issue. it turns out that CardExchange, when selecting the current record after in insert, is not using the ID returned by the SELECT @@IDENTITY, but an "intelligent guess" that fails when your loaded name list is not up to date. I think that is you do a refresh (F5) before inserting the new record, the error is not occurring.

To solve this, a bug-fix release is needed. We are planning such a release within about a month. if this is not an acceptable period for you, please contact your software vendor, so that can come to an agreement with our sales department.

regards,
Rutger.

Rutger Koperdraad
Senior Software Engineer

"A reasonable man adapts himself to the world. The unreasonable man persists in trying to adapt the world to himself. All progress, therefore, depends on the unreasonable man." (Bernard Shaw)
7 years 3 months ago
Rutger Koperdraad
Platinum Boarder
Platinum Boarder
Posts: 1566
More
Problem with multiple connections to same SQL DB #2666
Hi Chris,

This issue has been fixed in a bug-fix release for version 6, which can be downloaded from

dl.dropbox.com/u/41923064/Installers/CardExchange6550.exe .

regards,
Rutger.

Rutger Koperdraad
Senior Software Engineer

"A reasonable man adapts himself to the world. The unreasonable man persists in trying to adapt the world to himself. All progress, therefore, depends on the unreasonable man." (Bernard Shaw)
×

Notice

The forum is in read only mode.