Writing a query that accepts user input, to query the SQL db

RonMAbbott
RonMAbbott used Ask the Experts™
on
I'm trying to mimick functionality of the database that I originally have in Access on SQL Server 2005.  So far the greatest issues I have is to get my queries to work in a similar manner. Namely, see code below


I thought that putting Access front end onto the SQL Server 2005 backend would allow me to have user input as I do in Access database.  But apparently it isn't that simple.

Is there a good way to solve this problem?  I'm not committed to using ADP, I just thought it would let me use Access GUI for the front end.  I don't have too much experience with either Access db or SQL db, so you may have to spell it out for me :-)


When I run a syntax checker on this query (which works in Access), I get the following error:

"ADO error: Invalid column name 'Enter 1st date-Enter as YYYYMMDD'.
Invalid column name 'Enter 2nd Date-Enter as YYYYMMDD'.
Statement(s) could not be prepared.
Deferred prepare could not be completed."


SELECT Sum(MasterTable.amount) AS SumOfamount, MasterTable.number, Count(MasterTable.item) AS CountOfItem, MasterTable.updatedNumber
FROM MasterTable
WHERE (((MasterTable.date) Between [Enter 1st date-Enter as YYYYMMDD] And [Enter 2nd Date-Enter as YYYYMMDD]))
GROUP BY MasterTable.number, MasterTable.updatedNumber
ORDER BY Count(MasterTable.item) DESC;

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AneeshDatabase Consultant
Top Expert 2009

Commented:
you havr to pass the exact values instead of  [Enter 1st date-Enter as YYYYMMDD]  and [Enter 2nd Date-Enter as YYYYMMDD], there is no way sql waits for the user input

Author

Commented:
So there is no way to add a GUI that would allow that functionality?  The original database was designed to be used by people who are not very technically savvy.  I'm looking for a way that will make it relatively easy for a non-technical user.

One of the suggestions I came accross was to define those values at the top of the query and have the user change them.  I was looking for more user friendly way to do this.

Is there a way to query the user via Access front end, before the query contacts the back end server?  That way we can have those values ready to go before submitting the query to the SQL server.
Consulting - Technology Services
Commented:
You can create access forms that run SQL queries on an SQL 2005 database as follows:
declare @first_date datetime, @second_date datetime
SELECT Sum(MasterTable.amount) AS SumOfamount, MasterTable.number, Count(MasterTable.item) AS CountOfItem, MasterTable.updatedNumber FROM MasterTable WHERE (((MasterTable.date) Between @first_date And @second_date)) GROUP BY MasterTable.number, MasterTable.updatedNumber ORDER BY Count(MasterTable.item) DESC;
Alternatively, create an application in any application programming language e.g C#, Java etc and make it run these queries but simply initialise the variables @first_date And @second_date as you run your application.
All the best bro.
Chris MConsulting - Technology Services

Commented:
You initialise the parameters @first_date And @second_date at run time.
Depending on the programming language, your query to the database can have parameters set to it, which you initialise as you run the query.
In so doing, SQL Server will dynamically build your query with the parsed parameters to have the right query. Thisi s the way SQL server works with applications; parsing parameters at run time.
If there's any language of your choice, let me know so that I help you.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial