Pass Parameter to MS Access Query Using ASP in Dreamweaver 8

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?
ccbaileyAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
datAdrenalineConnect With a Mentor Commented:
>> 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
 
AshrafedesCommented:
Yes , you can Save Value on any table and take this Value to run as Filter in Access query

Do you get me  ?
0
 
ccbaileyAuthor Commented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
datAdrenalineCommented:
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
 
ccbaileyAuthor Commented:
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
 
datAdrenalineCommented:
>> 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
 
ccbaileyAuthor Commented:
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
 
ccbaileyAuthor Commented:
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
All Courses

From novice to tech pro — start learning today.