How to get a parameterized query working with an access database.




8/1/99 Please Note: I don't use Visual Interdev any more and I don't know if they ever fixed this issue. I leave this article up for reference sake. I can not answer any questions about this since I wouldn't remember anything about it anyway.

First of all. The visual tools don't work correctly when using access with a parameterized query. They work fine with a SQL database. This confuses many people, but there is still a way get around this and get everything working.

Here is an example that will show you how to get one working.

You first need to be in an open project and have an existing Data Connection to an Access database set up and working.

For this example I called my Project and my Web parameterizedquery. I am using the Advworks.mdb database and I have put it in a directory in the Web called _database. For best results you should create an identical project on your computer and follow along with this tutorial.

You need a Data Connection set up so right click on the Global.asa file in the Project Explorer and choose Add Data Connection. You now need to set up either a system or file DSN to the access database called Adworks. For this example I created a File DSN and I named it AdvworksConnection. Either one will work. This is a basic principle of Visual Interdev and I assume you can set it up without directions. If you cant you should look in the help files.

After you get that set up your global.asa will expand and look like this.

Now create a new asp page and call it test1.asp.

Open test1.asp and drag cmdSample from the Project Explorer directly into test1.asp between the body tags.

When prompted click yes to enable the scripting object model .

Your screen should look like this.

Now in the Toolbox menu under Design Time Controls.

Drag a TextBox DTC into test1.asp and put it right under your cmdSample recordset.

Name it txtProductName. Choose Recordset1 under Recordset and choose ProductName under field. I could have named our recordset something else earlier, but I forgot and it doesn't really matter for a test project like this. As you develope you will learn that descriptive naming will eventually become a necessity.

After you save that Drag another TextBox DTC into test1.asp and put it right under your txtProductName TextBox DTC.

Name it txtProductDescription and choose Recordset1 under Recordset and choose ProductDescription under field.

These two textbox DTC's will show us the results of our query when we run the page.

Now.. This is the tricky part. Under the Script Outline Menu.    Under Server Objects & Events       Under Recordset1 Double click on the onbeforeopen event. Some code will appear in your test1.asp file. It will look like this. <SCRIPT ID=serverEventHandlersVBS LANGUAGE=vbscript RUNAT=Server> Sub Recordset1_onbeforeopen() End Sub </SCRIPT>

This is where will assign the value of the variable for the parameter in the query.

Add the following code between the Sub and the End Sub.

SampleVariable="1"
Recordset1.setParameter 0,SampleVariable

It will look like this.

Now save test1.asp and run it in your browser. You should something like this.



Try changing the number in the onbeforeopen event and running the page again. As long as you enter an existing ID number you will get results.

Ok... so we successfully passed a variable into a parameterized access query.

But nobody is going to hard code the variable like in the example above. We want this sucker to be dynamic. Probably the variable will come from the HTTP header.

If that is the case you would do something like this.



Then if you run that page add this to the URL
?RandomVariable=2
and experiment by changing the number and refreshing the page.

It might look something like this.


http://localhost/parameterizedquery/test1.asp?RandomVariable=2

Yeeessshh.. I'm glad this Tutorial is over and I hope it helps you understand this better. This was a big problem for me when Visual Interdev 6 first came out and I know how frustrating all of this can be. This is simply meant to get you going on the right track. There are a few things I didn't talk about like making sure the parameter settings for the variable data types are correct, but this example didn't need for those to be adjusted so you can figure those out later on your own.

Good Luck... and remember to take things in steps... Don't move on until everything you have done so far works. If you try to do things all at once you will have a terrible time and will have to do some serious troubleshooting.

Lastly... I would like to thank Michael C. Amundsen for helping me to understand all of this. Without his help via newsgroup posts...  I would never have been able to write this tutorial.

Michael also has a web page with some helpful ASP information. See Below.

http://www.amundsen.com