Solved

Pass Parameter to MS Access Query Using ASP in Dreamweaver 8

Posted on 2011-09-27
8
286 Views
Last Modified: 2012-05-12
I am creating a database driven web site using asp vbscript in dreamweaver.  I have a MS Access query that is based on another query that has a parameter.  In other words, my first query is this:

SELECT tbl_Books_Read.Books_Read_ID, tbl_Books_Read.Book_Link, tbl_Employees.Employee_ID, [Employee_First_Name] & " " & [Employee_Last_Name] AS Employee_Name, tbl_Employees.Employee_First_Name, tbl_Employees.Employee_Last_Name
FROM tbl_Employees RIGHT JOIN tbl_Books_Read ON tbl_Employees.Employee_ID = tbl_Books_Read.Employee_Link
WHERE (((tbl_Employees.Employee_ID)=[Emp_ID]));

Then I have another query based on that one that I need to access from my ASP page in Dreamweaver:

SELECT qry_Books_OTDS.Book_ID, qry_Books_OTDS.Title, qry_Books_OTDS.Author, qry_Books_OTDS.Description, qry_Books_OTDS.Type, qry_Books_OTDS.Pay_Amount, qry_Books_OTDS.Checked_Out_To, qry_Books_OTDS.In_Christys_Library, qry_Books_Read_Employee.Employee_ID, qry_Books_Read_Employee.Employee_Name
FROM qry_Books_OTDS LEFT JOIN qry_Books_Read_Employee ON qry_Books_OTDS.Book_ID = qry_Books_Read_Employee.Book_Link;

Right now I'm just trying to figure out how to pass the [Emp_ID] parameter to my asp page from Dreamweaver and access my first query.  I have this value stored as a Session Variable called varEmpID, but I can't figure out how to pass the parameter to the Access query.  Is this possible?
0
Comment
Question by:ccbailey
  • 4
  • 3
8 Comments
 

Expert Comment

by:Ashrafedes
ID: 36716712
Yes , you can Save Value on any table and take this Value to run as Filter in Access query

Do you get me  ?
0
 

Author Comment

by:ccbailey
ID: 36717293
I am not sure how to do this.  Is this a feature in Dreamweaver or do I need to hand code it.  I am not sure what "Save Value" is - I have the value I need saved as a session variable.  I just need to be able to give this value to access as a parameter (the Access query is expecting a parameter and I get an error right now since i"m not passing it).  I am not sure if running it as a filter in the query would accomplish this, but I'm happy to give it a try if you can tell me how to do it.

Thanks!
0
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 36719579
I am unsure as to how Dreamweaver works, or really what it is in its entirety, but I guessing that you know the Emp_ID you want to pass to the Query object right? ... well, in Dreamweaver, how do you reference the access Query object? The reason I ask is because my first instinct (with what I DON'T know about Dreamweaver) is to REMOVE the parameter from the Access Query object and set the SQL statement to this:

SELECT tbl_Books_Read.Books_Read_ID, tbl_Books_Read.Book_Link, tbl_Employees.Employee_ID, [Employee_First_Name] & " " & [Employee_Last_Name] AS Employee_Name, tbl_Employees.Employee_First_Name, tbl_Employees.Employee_Last_Name
FROM tbl_Employees INNER JOIN tbl_Books_Read ON tbl_Employees.Employee_ID = tbl_Books_Read.Employee_Link

Then in Dreamweaver use the "outer" Query object as the source of a SQL statement ...

So in Dreamweaver instead of setting a property to "someQueryObjectName", set it to "SELECT * FROM someQueryObjectName WHERE Employee_ID = [someDreamWeaverControl]"

If you have to set your Dreamweaver settings to a literal SQL statement, then use the tools DW has to concatenate the employee_Id into the SQL statement then set the property.

----

If this does not seem feasable, then can use explain how Dreamweaver executes the Query object?
0
 

Author Comment

by:ccbailey
ID: 36815277
OK, I see what you are saying.  Generally, yes, I think I could do that, but I don't think it will work in this case.  Let me briefly explain exactly what I'm trying to make happen.  The database is for a book reading program I have with my employees - they can read books for a $$ bonus.  I want this asp page to list all books in the database, with a "yes" next to the ones that person has read.  They only need to see the books they've read, though, so I have created a query using the table where they enter the books they've read (called "tbl_Books_Read"), which asks for the Emp_ID (yes, I know the Emp_ID), which I've called qry_Books_Read_Employee.  This is where the parameter exists that needs to be passed from my ASP page.  But then I am using that query (qry_Books_Read_Employee) in another query with the list  of all the books in the database so I have a list of all books, with a "yes" value for those that particular employee has read (called qry_Books_w_Employee_YesNo).  So the query I'm calling from access is not directly asking for a parameter, rather, it is utilizing another query that is calling the parameter.  So I think your solution would only work in this case if there is a way to create one recordset in asp that is referenced by another recordset in asp.  (Whew - I hope that made sense).

What Dreamweaver basically does is make the creating of asp pages more user friendly by doing a lot of the coding for you.  I am familiar enough with coding to modify things pretty successfully but have never hand coded an entire webpage.  I have posted the code that Dreamweaver creates when I connect to this query below, but but when I test it I get the following error:

 [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

There may be a way to design my database differently to circumvent this issue, but I am really wanting to know if it is possible to pass a parameter from Dreamweaver in this way (I am planning to do other ASP pages in the future and am trying to learn what I can and can't do).

I hope this makes my question clearer - please let me know if anything doesn't make sense.
<%
Dim rsBooksReadEmployee
Dim rsBooksReadEmployee_numRows

Set rsBooksReadEmployee = Server.CreateObject("ADODB.Recordset")
rsBooksReadEmployee.ActiveConnection = MM_connBookList_STRING
rsBooksReadEmployee.Source = "SELECT * FROM qry_Books_w_Employee_YesNo" 
rsBooksReadEmployee.CursorType = 0
rsBooksReadEmployee.CursorLocation = 2
rsBooksReadEmployee.LockType = 1
rsBooksReadEmployee.Open()

rsBooksReadEmployee_numRows = 0
%>

Open in new window

0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 11

Expert Comment

by:datAdrenaline
ID: 36817289
>> so I have created a query using the table where they enter the books they've read (called "tbl_Books_Read"), which asks for the Emp_ID (yes, I know the Emp_ID), which I've called qry_Books_Read_Employee.  This is where the parameter exists that needs to be passed from my ASP page.  But then I am using that query (qry_Books_Read_Employee) in another query with the list  of all the books in the database so I have a list of all books, with a "yes" value for those that particular employee has read (called qry_Books_w_Employee_YesNo).  So the query I'm calling from access is not directly asking for a parameter, rather, it is utilizing another query that is calling the parameter. <<

I understand the dependancies you are describing. What I am proposing is that you don't store ANY parameters in your Access Query objects, just filter when you pass the call to the db engine from DW...

<%
Dim rsBooksReadEmployee
Dim rsBooksReadEmployee_numRows

Set rsBooksReadEmployee = Server.CreateObject("ADODB.Recordset")
rsBooksReadEmployee.ActiveConnection = MM_connBookList_STRING
rsBooksReadEmployee.Source = "SELECT * FROM qry_Books_w_Employee_YesNo WHERE Employee_ID = " & someDreamWeaverVariableStoringEmployeeId 
rsBooksReadEmployee.CursorType = 0
rsBooksReadEmployee.CursorLocation = 2
rsBooksReadEmployee.LockType = 1
rsBooksReadEmployee.Open()

rsBooksReadEmployee_numRows = 0
%> 

Open in new window


So, every time you call a Query object from the Access data file, provide the WHERE clause, instead of trying to store the WHERE clause with a parameter in the Query object.  As long as the SELECT clause from the Query object outputs the field you want to filter, you should be good to go.
0
 

Author Comment

by:ccbailey
ID: 36817901
The problem is that data is left out if I filter the query from Dreamweaver.  Let me try to explain.  Take a look at the tables I've exported to Excel (attached).  (I've included the output from the queries I'm referencing in the attached spreadsheet; each query is a tab in the spreadsheet).  

Book-DB-Queries.xls

The data I am trying to get from Dreamweaver is shown with qry_Books_w_Employee_YesNo - this is filtered to show all books read by Christy Bailey (Emp_ID=4), as well as all the other books in the database that have not been read by Christy Bailey.  

If I combine qry_Books_Read with qry_Books, I can get a query that includes all the books including who has read what books as well as all books that have not been read by anyone. (see qry_Books_Read_Employee).  If I filter that table, say for Christy Bailey, Emp_ID=4, I get the data in qry_Books_Read_Employee_New - (I filtered for Emp_ID=4 and Null, so that I get all the books read by me as well as books I have not read).  However, some books are left out that should be included on this list (if you look at qry_Books_Read_Employee_New, you will see 11 records, whereas qry_Books_w_Employee_YesNo has 13 records).  Herein lies my issue - if I filter at this level (qry_Books_Read_Employee), it does not include books that have been read by somebody else (since I'm filtering for 4 or Null on the Employee_ID field).  For example, in qry_Books_Read_Employee, you will see that "The Dip" and "Guerrilla Marketing" have been read by Wendy WIlson, Emp_ID=2, but not by me.  These books should show up in my final query as "not read" (with a blank Emp_ID), but do not because the value of "Employee_ID" is 2 rather than null or 4 in qry_Books_Read_Employee.  

In my experience, this can be handled in a database by filtering the supporting query (in this case, qry_Books_Read filtered for Emp_ID=4), and then utilizing that query to generate the final query (using the filtered qry_Books_Read and qry_Books to create a new query that shows ALL books and including a field showing which books have been read by EmpID=4).  Doing this, I am able to create qry_Books_w_Employee_YesNo.

I apologize for being long-winded.  I hope this is clear - please let me know if something doesn't make sense.  If there is a way to accomplish this using dreamweaver, I am open, but so far I haven't been able to figure that out, either.

Thanks again for your help.  
0
 
LVL 11

Accepted Solution

by:
datAdrenaline earned 500 total points
ID: 36892674
>> In my experience, this can be handled in a database by filtering the supporting query (in this case, qry_Books_Read filtered for Emp_ID=4) <<

Yes, that is definately true. That is typically done via an ADODB.Command object or with a DAO.QueryDef object... but ... as I was thinking about this last night (I know I'm wierd that way :) ), I recalled that Jet/ACE SQL understands the EXEC keyword in order to 'Execute' stored procedures.  When looking at a Jet/ACE database through ADODB glasses, as parameterized Access Query object is a Stored Procedure, so you really don't need to un-parameterize your Access Query objects as I was suggesting, you can simply change your recordset's .Source to EXECute your stored procedure, thus allowing you to pass the parameter, instead of SELECTing it:

<%
Dim rsBooksReadEmployee
Dim rsBooksReadEmployee_numRows

Set rsBooksReadEmployee = Server.CreateObject("ADODB.Recordset")
rsBooksReadEmployee.ActiveConnection = MM_connBookList_STRING
rsBooksReadEmployee.Source = "exec qry_Books_w_Employee_YesNo " & variableForEmployeeId
rsBooksReadEmployee.CursorType = 0 'adOpenForwardOnly
rsBooksReadEmployee.CursorLocation = 2 'adUseServer
rsBooksReadEmployee.LockType = 1 'adLockReadOnly
rsBooksReadEmployee.Open()

In literal form the .Source would look like this when you want to look at Employee_ID 4 (Christy):

exec qry_Books_w_Employee_YesNo 4

To learn more about the exec Jet/ACE keyword, click here
0
 

Author Closing Comment

by:ccbailey
ID: 36905852
BINGO!  Perfect - this is exactly what I was looking for.  Dreamweaver's code looks a little different, but I was able to go into the "Advanced" window for creating a recordset and use "EXEC" rather than "SELECT", and it worked beautifully.  Thanks so much - and thanks for bearing with me as I tried to explain what I was after. . .
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now