Microsoft Access Select Query With Parameters From VB.NET And Visual Studio 2012

Greetings;

I use Visual Studio (VS) 2012 for development. I haven't had to query Microsoft Access 2010 in years, however, I find myself in this situation.

For select queries I like to use an .xsd file which represents a DataSet file in VS. I usually proceed by using the VS Server Explorer, opening a connection to the desired database and using drag-and-drop to pull over the stored procedures or views I would like in my dataset definition. In Microsoft Access 2010 I see views, stored procedures and functions which are draggable.

When I drag the desired select query (created within Microsoft Access 2010) onto the dataset file within VS 2012 I receive a warning stating "Parameter information for stored procedure/function could not be retrieved. Parameters collection will be empty." My select query is listed under Functions within VS Server Explorer.

How might I correct this problem? I like using stored procedures (or in the case functions in Microsoft Access 2010). My Microsoft Access 2010 select query does require a parameter to be passed. I am using other select queries (listed under Views) which do not have a parameter and I have no warnings or errors with those.

Much thanks in advance for any assistance you may offer.


David Bach
David BachAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
If the query is using VBA function calls within the SQL then you will not be able to use them.
0
David BachAuthor Commented:
Hi TheHiTechCoach;

There is no VBA code in the Microsoft Access 2010 database, so, there are no VBA function calls.

Much thanks ... David Bach
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
There is no VBA code in the Microsoft Access 2010 database, so, there are no VBA function calls.
Just because there are not any code module does not mean a query is not using VBA functions.

The code modules are for User Defined Functions (UDF).  There is also all the built-in Access VBA functions like Left(), IIF(), etc.

If the query is using any VBA function, User Defined Functions (UDF) or built-in Access VBA function, then the query will have issues.

I would recommend verifying that there are no VBA function calls in the SQL. Can you view the SQL for the query?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

David BachAuthor Commented:
Hi TheHiTechCoach;

Yes, the SQL for the query follows:
SELECT Product.bigProductId, Product.strGuid
FROM Product
WHERE (((Product.strGuid)=[?strGuid]));

Open in new window

Hope this helps ... David Bach
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
Access is having is probably having an issue with [?strGuid].

What is [?strGuid]?
0
David BachAuthor Commented:
Hi TheHiTechCoach;

[?strGuid] is an input parameter representing a string.

As a side note, I am using delete, insert and update queries successfully against this database and all of those types of queries are passing parameters.

Hope this helps ... David Bach
0
RobOwner (Aidellio)Commented:
The [?strGuid] That's what is causing your issue.  You will need to  pass this from your app.  Remove it and do the filtering in your VS code:
SELECT Product.bigProductId, Product.strGuid
FROM Product

Open in new window

0
RobOwner (Aidellio)Commented:
You can edit the TableAdapter associated with your DataTable to specify new queries:

http://msdn.microsoft.com/en-us/library/ms171902.aspx

This is a walkthrough to edit the TableAdapter and create a parametized query, your sql will then become:
http://msdn.microsoft.com/en-us/library/ms171905.aspx

SELECT Product.bigProductId, Product.strGuid
FROM Product
WHERE Product.strGuid=?;

Open in new window


sample vs tableadapter code
Dim strGuid As String = "123456789"
myTableAdapter.FillByCity(MyDataSet.Products, strGuid)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David BachAuthor Commented:
Greetings Tagit;

Much obliged for the solution. I much prefer Microsoft SQL Server to Microsoft Access - and that's putting it mildly.

Thank you both for your time and patience with me.

David Bach
0
RobOwner (Aidellio)Commented:
Don't we all? :-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.