Weird problem


My back-end database is
Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)
"Aug 22 2017 17:04:49 Copyright © 2017 Microsoft Corporation Express Edition (64-bit) on Windows Server 2019 Datacenter 10.0 (Build 17763: ) (Hypervisor) "

And my web browser is
Firefox 74.0.1 (64-bit)

And my OS is
Windows 10

When processing a form input, I first create a random 10 digits number as a simple variable for a primary key for the data set. <cfset myID = left(rand(),10)>

The server responded with the following err msg:
Violation of PRIMARY KEY constraint ‘PK1’. Cannot insert duplicate key in object ‘dbo.mytbl’. The duplicate key value is (2183812345).
The “2183812345” is the previous value of this var.

So, it seems that the server is overzealous in caching, that is, it mistakenly saves the variable “myID” value for the previous instance, which it shouldn’t.

Odd thing is, if hit [Back] button and hit [Submit] again, it is processed properly.

Is there a way to ensure that this variable value is always anew?

I can not quite follow. Question: are you creating this primary id with rand() and populating the input form with a hidden field? If so:

  1. When using rand() to generate unique ID, you are not ensuring uniqueness of the primary key. It can, and at some point you will create and hit a duplicate value that will conflict. Why not use autoincrement functionality of the database? If you don’t want an auto incrementin integer as ID, use CreateUUID, but all the string, and not just some part of the string. You’ll also will need to turn primary key field to type string.

  2. The browser will always cache the forms when using back button/history navigation, unless you do some type of workaround. That is a normal functionality which I personally never tried to “break” programaticlly.

But of course, that all depends on what exaclty you want to do. I’m not sure if I am understanding your issue right. Maybe you could tell us a little bit more of what you are trying to do.

Have a nice day!

No, no. This <cfset myID = left(rand(),10)> is at the very top of the form processing cf template, so, the db code looks like this: <cfquery insert into mytbl (myID, myField2) values (#myID#,'myValue'); </cfquery> ( forget about cfqueryparam for now )

Why don’t you just create an auto increment in the database?
What type is myID in the database?

I usually create auto increment for primary key field but not for a particular db table for this particular application.

What about select max, then add 1 and insert in a cftransaction block?
Or maybe it is just a very simple thing. Try left(rand()*1000000000,10), so you get at least a 10 digit postive integer?

Using rand() for this will lead into conflict, because you are not ensuring a unique identifier. Another point is , that rand() creates a float value. So you will have always 0.########. So it depends very much on the data type of the data base field. May be your db engine is trying to cast the value and creating duplicates.

Yes, yesterday I switched to the db solution similar to what you wrote.