?
Solved

Pass Parameter to MS Access Query Using ASP in Dreamweaver 8

Posted on 2011-09-27
8
Medium Priority
?
319 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

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
 
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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

770 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