By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,224 Members | 1,143 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,224 IT Pros & Developers. It's quick & easy.

Using the InputParameters on a form to pass user data to a SQLServer Stored Proc

P: 1
I am trying to pass parameters to a stored proc via the
InputParameters on the form.

I set up the Proc with a couple of parameters with the LIKE operator (e.g. LIKE
@p_File_Type)

On the form, in the InputParameters property I can string the values
out with commas inbetween and it works like a charm.

Problem is, if the user leaves a value blank, I would like the query
to select all values for that field. I have tried using the wildcard '%', leaving the
value blank, NULL, etc and nothing seems to work.

Anyone have any ideas?
Aug 8 '07 #1
Share this Question
Share on Google+
7 Replies


Rabbit
Expert Mod 10K+
P: 12,392
The wildcard for any number of characters is "*"
Aug 20 '07 #2

ADezii
Expert 5K+
P: 8,669
I am trying to pass parameters to a stored proc via the
InputParameters on the form.

I set up the Proc with a couple of parameters with the LIKE operator (e.g. LIKE
@p_File_Type)

On the form, in the InputParameters property I can string the values
out with commas inbetween and it works like a charm.

Problem is, if the user leaves a value blank, I would like the query
to select all values for that field. I have tried using the wildcard '%', leaving the
value blank, NULL, etc and nothing seems to work.

Anyone have any ideas?
This should work but has not been thoroughly tested. You must modify the Stored Procedure directly so that it returns the specified value or ALL (%). Again, it has not been thoroughly tested in the InputParameters context, but it will return specified values and ALL Records if % is entered as a Criteria. Let me know how you make out.
Expand|Select|Wrap|Line Numbers
  1. ALTER PROCEDURE dbo.StoredProcedure1
  2. @strCity varchar(30)
  3. AS SELECT     EmployeeID, LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address, City, Region,  
  4.                       PostalCode, Country, HomePhone, Extension, Photo, Notes, ReportsTo
  5. FROM         dbo.Employees
  6. WHERE    City Like '%' + @strCity + '%'
Aug 20 '07 #3

ADezii
Expert 5K+
P: 8,669
The wildcard for any number of characters is "*"
Sorry Rabbit:
We are in the world of SQL ServerLand where "*" and "?" don't exist anymore, and "%" and "_" are the Status Quo (Access Projects). The InputParameters Property is only applicable to Access Projects, and does not even exist in conventional Access Databases. LOL.
Aug 20 '07 #4

Rabbit
Expert Mod 10K+
P: 12,392
SQL server? I should pay more attention, shouldn't this be in the SQL server forum then?
Aug 20 '07 #5

ADezii
Expert 5K+
P: 8,669
SQL server? I should pay more attention, shouldn't this be in the SQL server forum then?
Not really, because it's a Project, it's an Access Front End connecting to an SQL Server Back End, and besides InputParameters is a Form Property relating to the Access Component.
Aug 20 '07 #6

Rabbit
Expert Mod 10K+
P: 12,392
But in the end, what they need is correct SQL server syntax and not Access syntax.
Aug 21 '07 #7

ADezii
Expert 5K+
P: 8,669
But in the end, what they need is correct SQL server syntax and not Access syntax.
You are correctamundo.
Aug 26 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.