Solved

Pass Parameter to MS Access Query Using ASP in Dreamweaver 8

Posted on 2011-09-27
8
288 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

910 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