|
Introduction
Where I work, Visual Interdev 6 has been assigned as a development IDE. I was suppose to develop an inventory application that required several updateable forms to be populated based on a parameter passed from a previous form. I found that existing documentation really didn't help. I had access to Microsoft's first book on VID 6 entitled Microsoft® Visual InterDev(TM) 6.0 Programmer's Guide and I had access to a half dozen paid tech support phone calls. Sad to say, both avenues were dead ends: the instructions were either so imprecise as to lead to confusion or were simply wrong. To finally figure out how to get a recordset control (DTC) in Visual Interdev 6 to accept a parameter and then return an updateable recordset was so time consuming I decided to write an article in case others had the same problems.
The Nuts and Bolts
- Here's how to do it. Create an Access database called test.mdb and populate it with a table called tblTest. Give the table a field called Last_Name. In the field put two records: Jones and Smith.
Figure 2 :
- Create a Web Project in VID 6 called testParm.
- Create a system DSN connecting to the test.mdb database.
- Add a data connection called Connection1 to the testParm project.
- In the "testParm" project, create a page called PassParm.htm.
- On that page put a form called form1.
- Within the form put a text box and submit button.
- Let the "name" property of the text box be called "Parameter."
- Make form1 "Post" the "Parameter" to an asp page called ParmTest.asp (you are about to create that page.)
Figure 2 :
Let ParmTest.asp have a DTC recordset called recordset1. In the properties of Recordset1, set the SQL command of that recordset to this: Select * From tblTest and then close the properties box.
- Add a bound DTC text box control and bind it to recordset1 and the field to Last_Name.
- Add a DTC button called button1 and let its caption read Save.
Figure 3 :
The Code
You're still on the ParmTest.asp page. On the "Server Objects and Events" section of the "Scripting Object" double click the "Recordset1" icon, then double click the "onbeforeopen" icon. A code outline will be inserted into the "head" section of the page. Repeat the process for the "Button1" icon and its "onclick" icon. Then write some code in the header outlines(see it below).
When you're done, the code section should look exactly the following:
<HEAD>
<SCRIPT ID=serverEventHandlersVBS LANGUAGE=vbscript
RUNAT=Server>
Sub Recordset1_onbeforeopen()
Recordset1.setSQLText ("SELECT * FROM tblTest Where " &_
Last_Name = '" &_
Request.Form("Parameter") & "'")
End Sub
Sub Button1_onclick()
Recordset1.updateRecord
End Sub
</SCRIPT>
</HEAD>
The Catch
If you follow these instructions exactly, what I've just shown you will return a record in the .asp page when the name Jones or Smith is typed into the text box on the .htm page and the submit button is clicked. But the record returned in the .asp page is not updateable. Oh, it looks updateable, and it acts updateable when the "Save" button is clicked. But change Jones to Jonesy and you'll find that the record in the underlying table is not updated. Jones is still Jones. To get Jones to Jonesy, you must change one of the properties in the recordset1 control.
The Trick That Make It Updatable
Open the Properties box of the recordset1 control. Click the "Implementation" tab, then click the "Preserve Recordset in Session State" option box. Close the properties box, test the parameter passing process and you will find you have an updateable record. Change Jones to Jonesy, click the "Save" button, look in the underlying table and voila,
Figure 3 :
there you see Jonesy.
Catch 2
But there are still problems. Say you decide to update the record and change Jones to Jonesy. It saves the change when you click the "submit" button. But when you use your "back" button and go back to the .htm page to enter another parameter, things go wrong. When you click submit on the .htm page and go to your .asp page, you find that your .asp page still has the previous record. In other words, setting the "Preserve Recordset in Session State" property means you actually do preserve the recordset. Forever. Well, not really. But it does take a trick to get it to work properly.
You need to add one more button at the bottom of the form and one more sub procedure in the header section for the .asp page. The button is button2 and has as its caption "Return To PassParm.htm." The following code is placed in the header with the code you added earlier:
Sub button2_onclick()
Recordset1.close
Response.Redirect("PassParm.htm")
End Sub
When you add the button and place that code in the header section with the rest of your code, save the change and run your app. Clicking the new button will close the recordset manually. In this way whenever you move from one page to another you will release the recordset allowing you to input a new parameter.
The Final Catch
There's one remaining catch that I haven't figured out how to remove. The user simply cannot use the "Back" button to navigate back to the parameter input page (PassParm.htm). Here's why. Say the user goes to the .asp page but decides not to alter the record. Instead they hit their back button to return to the PassParm.htm. When they enter a new parameter in PassParm.htm and go to the .asp page they will become confused because the new parameter will not have returned a new record. Instead they will find the old record still in the .asp page. In short, the recordset will not have been closed. (Remember our "Preserve Recordset in Session State" property change?)
To workaround this problem I put a note on the .asp page telling the user to use the "Return To PassParm.htm" button, not the back button.
Enjoyable and profitable coding!
About the Author
Neal Horsley is currently an Active Server Pages application developer for the Center for Disease Control in Atlanta. Coding is a second career begun five years ago after twenty years in business management and ownership roles. Hobbies include English writing.
|
