On Sun, 25 Jan 2004 18:21:46 -0800,
<(E-Mail Removed)> wrote:
>>-----Original Message-----
>>setup: IIS5, Access 2000 DB, Frontpage 2003
>>
>>Looking for ASP INSERT statement that can:
>>
>>insert a new record into a table with various values
>>from a form filled out by a user, and a value that
>>increments the value of one of the fields +1.
>
>Please refer to the article:
>
>Saving Form Data in a Database
>http://www.interlacken.com/winnt/tip...ow.aspx?tip=44
>
>>in other words, at the time the user decides to submit
>>the newly created record to the db, is there a way to
>>have an sql query run which looks for the higest value
>>of the field "filenumber", adds 1 to it and saves it as
>>a variable, perhaps into a hidden field, which is
>>added to the INSERT statement mentioned earlier?
>
>Yes, but there's a major flaw in doing so.
>
>Certainly, you can run a SQL statement like:
>
>SELECT Max(mytable.id) AS MaxOfid FROM mytable;
>
>and there by get the largest id value in the mytable
>table. The flaw comes about in this scenario:
>
>1. Visitor 1 gets the highest existing id value,
> adds 1 and saves it in a hidden form field.
> If the highest existing value is 100, the hidden
> form field contains 101.
>
>2. Visitor 2 gets the highest existing id value,
> adds 1 and saves it in a hidden form field.
> Again, the hidden form field contains 101.
>
>3. Visitor 1 gets around to adding his or her record.
>
>4. Visitor 2 gets around to adding his or her record.
> A duplicate key results.
>
>>Do not wish to use an autonumber field for various
>>reasons.
>
>Why not? If it's mostly because it's hard to find out
>what the new autonumber value is, the article I cited
>earlier should resolve that.
+++++++++++++++++++++++++++++++++++++++++++++
Whoa! What an excellent article....thanks; it's going to the top of my
favorites in my ASP folder.
My reasons for not using the autonumber field are as follows:
the users, for whatever reason, need to re-enter these file numbers
into another d/b on another system (don't ask) and require that the
file numbers remain constant and in a logical series. deleting
records would create gaps in their file numbers (not such a bad thing)
or, worst case, repairing or compacting their d/b would re-number
their files (so I hear), and that would be a very bad thing.
After a weekend of immersing myself in a begginer's ASP book, I came
up with the following code/approach which I think will avoid the major
flaw you mentioned about two users trying to save the same file
number.
They enter the new file without *any*, and at the instant of their
INSERT submission, an SQL query is run to retrieve the higest number
of the file number field+1 and then use it's resulting value in a
variable (newnum) to add to their INSERT statement. Then the updating
page shows the user what their new number is.
<%
straccount = Request.Form("account")
strmbl = Request.Form("mbl")
strcontainer = Request.Form("container")
strvessel = Request.Form("vessel")
strpo = Request.Form("po")
strpcs = Request.Form("pcs")
strcommodity = Request.Form("commodity")
strremote_computer_name = Request.ServerVariables("REMOTE_HOST")
struser_name = Request.ServerVariables("REMOTE_USER")
strbrowser_type = Request.ServerVariables("HTTP_USER_AGENT")
Set objHamDB=Server.CreateObject("ADODB.Connection")
objHamDB.ConnectionTimeout=60
objHamDB.Open "DSN=import_entry"
Set recordSet=Server.CreateObject("ADODB.Recordset")
recordSet.Open "SELECT (max(ref)+1) as MyMax FROM Results", objHamDB
newnum=recordSet("MyMax")
sqlStatement="INSERT INTO
Results(account,mbl,container,vessel,po,pcs,commodity,ref,remote_computer_name,user_name,browser_type,[timestamp])
VALUES ('"&straccount&"','"&strmbl&"','"& strcontainer
&"','"&strvessel&"','"&strpo&"','"&strpcs&"','"&strcommodity&"',"&newnum&",'"&strremote_computer_name&"','"&struser_name&"','"&strbrowser_type&"',
now())"
Set recordSet=objHamDB.Execute(sqlStatement)
objHamDB.Close
Set objHamDB=Nothing
%>
As you can see, the user's submission form sends nothing to this
insert processing page in the way of either the file number (ref) or
the timestamp, letting this page come up with its own values at the
instant of submission and then later showing the fields submitted to
the user later in the page. I even included a link in case the user
wants to go back to an update page to re-edit this newly created
record in case they made any errors. (and needless to say, the update
page will not process any attempts to change the ref number)
This is my first attempt at trying to use ASP instead of the FP
wizards and bots, and I love the flexibility and freedom of it,but I
also realize I have a lot to learn about writing good code, especially
in the error-handling department, which will be my next project.
I have no illusions that the above code is any good or not, tho it
*seems*, after some testing, to work ok. My thinking is that even if
two users were to hit the submit button at the same time, the query
that looks for the next higest number should be quick enuff to prevent
two users from getting the exact same number. Even so, I think I'll
try to work on an error handling page if one user's submission fails
due to a duplication error which would ask the failing submitter to
retry.
Many thanks for your comments and great website.
Larry
- - - - - - - - - - - - - - - - - -
"Forget it, Jake. It's Chinatown."
Similar Threads
