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
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 we need to make a Data Command.
Right click on your DataEnvironment in the project explorer window and choose Add Data Command.
Name the command cmdSample.
Under SQL statement type in SELECT ProductID, ProductName, ProductDescription FROM
Products WHERE (ProductID = ?)
You can build this query in the SQL builder but
you cant run it and it will give you this message when you save the query.
Unable to determine the parameter information for the parameters. Just ignore that because it is still going to work.
After you create your Data Command your project explorer will 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
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
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.
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
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
and experiment by changing the number and refreshing the page.
It might look something like this.
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
Michael also has a web page with some helpful ASP information. See