Link to home
Start Free TrialLog in
Avatar of macterra
macterra

asked on

How to create a view with parameters in SQL

Within MS Access I can create a query similar to this:

SELECT Q.companyName, Count(*) AS RFQs, FROM Quotations AS Q
WHERE companyResponseDate BETWEEN [dateBegin] AND [dateEnd] GROUP BY Q.companyName;

but when I try to do the same thing in a C# .NET program:

CREATE VIEW ResponseSummary AS SELECT Q.companyName, Count(*) AS RFQs, FROM Quotations AS Q
WHERE companyResponseDate BETWEEN [dateBegin] AND [dateEnd] GROUP BY Q.companyName;

I get an OleDbException "VIEW cannot contain a parameter". Is there another way to construct the SQL so that Access will create a query with parameters?
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Not that I'm aware of. It's fairly straightforward to pop a few Inputboxes (not sure what they're called in C#, however) and gather input params from them:

Dim dteBegin, dteEnd

dteBegin = InputBox("Enter Begin Date:")
dteEnd = InputBox("Enter End Date::")

If IsDate(dteBegin) and IsDate(dteEnd) Then
  CREATE VIEW ResponseSummary AS SELECT Q.companyName, Count(*) AS RFQs, FROM Quotations AS Q
WHERE companyResponseDate BETWEEN " & dteBegin & " AND " & dteEnd & " GROUP BY Q.companyName;
End If

You may need to wrap your dates in hashmarks for Jet dbs:

  CREATE VIEW ResponseSummary AS SELECT Q.companyName, Count(*) AS RFQs, FROM Quotations AS Q
WHERE companyResponseDate BETWEEN #" & dteBegin & "# AND #" & dteEnd & "# GROUP BY Q.companyName;
Access does not have Views.  All you can do in Access is to create a Query.  Or you can simply code the SQL in your program, and pass the SQL to Access to be executed.

AW
Hmm, not completely right Arthur, Access can have views in an access project, but only when MS SQL is installed.
See: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/off2000/html/IDH_acdefAccessProject.asp

I guess for C# .NET this might help:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndive/html/data08092001.asp

Nic;o)
You are mixing two things View and Query. They are different. View is static, you can not have parameters in view because you can use view as a table. Like Select * from youVewName. You can not do it with query so. You should decide what you want to use and based on what I see you need Stored Procedure or just select statement with parameters. Please explain what you try to implement.
ASKER CERTIFIED SOLUTION
Avatar of alexgud
alexgud
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of macterra
macterra

ASKER

I am able to create a Query (without the parameters) in Access from C#/.NET with the following SQL statement:

CREATE VIEW ResponseSummary AS SELECT Q.companyName, Count(*) AS RFQs, FROM Quotations AS Q
GROUP BY Q.companyName;

I wanted to use a Query so that my crystal report can run off it, but if there is no way to do that in Access I can find a work around.
When using Crystal Reports, why not create a parameter less query in Access, connect to the report and let CR handle the parameter ?

Nic;o)
make your first query local in access with paremeters like:

SELECT Q.companyName, Count(*) AS RFQs, FROM Quotations AS Q
WHERE companyResponseDate BETWEEN [dateBegin] AND [dateEnd] GROUP BY Q.companyName;

 and, save the above SQL as qMyQuery in Access and use:

CREATE VIEW ResponseSummary AS SELECT companyName, RFQs FROM qMyQuery

mike

Thanks eghtebas, but if I create a query in the db first manually there is no need to do it programmatically. The plan is to start from an empty database and create all the tables and queries I need when the program first runs (i.e. the db design and all dependencies are localized to the code). It would be even better if I could create the mdb file from the program too, but I haven't found a way to do that.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Excellent, thanks Nico.

OK, I went with the workaround of doing the parameterized query in C# and passing the resulting DataSet to the crystal report.