Tutorials > Adobe Dreamweaver > Tutorial #177
Displaying Latest Record Upon Creation
Level: Beginner
Requirements: DreamweaverMX, Access
Language: ASP, VBScript, SQL
Introduction
This tutorial is for use specifically with an Access database. Where getting the ID of a record that has just been inserted in some of the bigger databases like SQL Server is relatively easy, Access does not provide the @@IDENTITY feature that its big brother does.
If your website has a high usage rate with multiple users actively entering data, the usual method of using the ORDER BY {field} DESC is not always a reliable method. With the method demonstrated here, you will learn how to uniquely identify the last record created by a logged in user and display for verfication purposes. This particular example is specifically set for non-logged in users who are entering data.
This tutorial will show you a quick and easy way of getting the id of the last record inserted into an Access database using Dreamweaver.
Step 1: Creating the table
We only need one small table for this tutorial. Create a new table as follows…
tblRecords
- RecordID (Primary Key, Autonumber)
- RecordCode (Text size=100)
- Forename (Text)
- Surname (Text)
Step 2: Inserting the new record
We will now create a small form asking for the user to enter their forename and surname. When we insert this record we will also insert a hidden value that will consist of 100 randomly selected characters. Once the record is created, we will use this value to identify the record that has just been created and display it on a separate page.
Create a new page and call it insert.asp and on it we will create our form.
As you can see, I have placed two textfields on the form along with a submit button and a hidden field. Name them as I have done in the image above.
Next we need to populate the hidden field with the random set of characters that we will use to insert into the RecordCode field on the database. So, go into the code editor and enter the following code near the top of the page.
<%
Function GenerateRandomCode()
Const charactersToUse = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"
Dim lengthOfCharacters, codeWord, randomNumber, i
lengthOfCharacters = Len(charactersToUse)
Randomize
For i = 1 To 100
randomNumber = Int((lengthOfCharacters * Rnd) + 1)
codeWord = codeWord & Mid(charactersTouse, randomNumber, 1)
Next
GenerateRandomCode = codeWord
End Function
%>
This handy little function simply creates a value that contains 100 randomly selected characters. Now that we have the code, we need to run it and place the returned value in the form.
As you will see, this is a function. A function returns a value in the name of the function itself. So if you call this function, it will return a random value, simple as that. To call the function, we simply add the following tag into our hidden field.
When the page is first loaded, the hidden fields value will see that it has to call our function in order for it to get its value. So once called, the value of the hidden field will be equal to the randomly selected characters that our function prepared for it.
Save the page and run the page in your browser. View the source and scroll down till you find the html for our form. Look at the value of the hidden field.
Step 3: Inserting the record
All we need to do now is apply a standard Dreamweaver Insert behaviour to our page, pairing the values on the form to those of the table we created earlier.
Create your Insert behaviour setting it up as follows.
Save your page.
Step 4: Viewing the new record
The question still remains, how do we load the record that has just been selected? To do this we need to do another little bit of code fiddling in the code editor, so open it up and scroll to the top.
It is a tad messy in there now that we have applied our insert behaviour. We now need to grab the RecordID of the newly created record from our table. To do this we need to put a recordset to a bit of none usual Dreamweaver use. Below the function that we created earlier, add the following code.
Here we are simply creating a function that contains a recordset within it. The recordset searches through the tblRecords table for the last record that has the same RecordCode that was used in the form to insert the new record. That is quite a mouthful I know, to cut a long story short it finds our new record.
Now that we can find our record, we need to use this GetRecordID function to pass through the ID of our new record to the viewRecord.asp page which will display it.
To do this, we have to do a little tiny bit of editing to the insert behaviour code that Dreamweaver placed at the top of our page.
Locate the following line of Dreamweaver code...
...and replace it with this.
What we are now telling the page to do is upon inserting of the new record, to redirect to the viewRecord.asp but to also pass through a parameter called recordCode with the value we get from our GetRecordID function.
Save the page and close it.
Create a new page called viewRecord.asp and on it create a recordset using the following setup.
Now in the design editor, simply drag the recordset fields across onto your page anywhere with whatever example text you feel like entering. For example.
Save your page, close it and run the insert.asp page in your browser and fill in the form. You should now have a working method of creating a new record and viewing it immediately when using Microsoft Access as the backend database.
| Tutorial By | Submitted On | Views | Rating |
|---|---|---|---|
| Rob Boyle | 17/03/2008 | 5986 | Not Yet Rated Rate Tutorial |
