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

How to send condition used in Where clause through Parameter

P: 4
Hi,

I am using Access 2003 and .Net 2005.

I have one problem in access query. I am trying to send the condtion of where clause through parameter to Access query as follows...


Expand|Select|Wrap|Line Numbers
  1. PARAMETERS [@SearchCriteria] Text ( 255 );
  2.  
  3. SELECT *
  4. FROM EMPLOYEE
  5. WHERE [@SearchCriteria];

When I am running query and putting condition as a value for parameter[@SearchCriteria] like EmployeeId = 1, its giving me all the records without any filteration.

I tried to call this query from my application in .Net and also directly from access. But still it's not working.

Any ideas on how to acheive this ?

Thanks in Advance.
May 24 '07 #1
Share this Question
Share on Google+
8 Replies

puppydogbuddy
Expert 100+
P: 1,923
Hi,

I am using Access 2003 and .Net 2005.

I have one problem in access query. I am trying to send the condtion of where clause through parameter to Access query as follows...


PARAMETERS [@SearchCriteria] Text ( 255 );

SELECT *
FROM EMPLOYEE
WHERE [@SearchCriteria];


When I am running query and putting condition as a value for parameter[@SearchCriteria] like EmployeeId = 1, its giving me all the records without any filteration.

I tried to call this query from my application in .Net and also directly from access. But still it's not working.

Any ideas on how to acheive this ?

Thanks in Advance.
It looks like your search criteria is numeric, not text:
[@SearchCriteria] like EmployeeId = 1

Try changing your parameter definition to integer as follows:
PARAMETERS [@SearchCriteria] Integer;
May 24 '07 #2

P: 4
It looks like your search criteria is numeric, not text:
[@SearchCriteria] like EmployeeId = 1

Try changing your parameter definition to integer as follows:
PARAMETERS [@SearchCriteria] Integer;

Hi, Thanks for your reply.

Actually what I am trying to do in .Net is like this.......

Expand|Select|Wrap|Line Numbers
  1. string  strSearchCriteria = "EmployeeId LIKE '" + txtEmployeeId.Text.Trim() + "%'";
  2.  
  3. if (cboStatus.Text.Trim() != "")
  4.     strSearchCriteria += " AND Status = '" + cboStatus.Text + "'";
  5.  
  6. Database db = DatabaseFactory.CreateDatabase("DBConnection");
  7. DbCommand cmd = db.GetStoredProcCommand("SearchEmployee");
  8. cmd.CommandType = CommandType.StoredProcedure;
  9.  
  10. db.AddInParameter(cmd, "@SearchCriteria", DbType.String, strSearchCriteria);
  11.  
  12. Dataset dsEmployee = db.ExecuteDataSet(cmd);
  13.  
  14.  
So as u must have observed, SerachCriteria can be any thing. It can have many conditions combined using AND. Columns EmployeeId and Status are Text column.


Assume that,
txtEmployeeId.Text is "S" and cboStatus.Text is "Completed"

When I send this SearchCriteria as a parameter to Stored procedure, it should go at place of use of the parameter in query and my query should become..

SELECT *
FROM Employee
WHERE EmployeeId LIKE 'S%' AND Status = 'Completed'


But it's not working and SP is returning all records.

I know that we can implement this in SQL Server 2000 by using stored procedue sp_executesql which "executes a Transact-SQL statement or batch that can be reused many times, or that has been built dynamically".

How to acheive this in MS Access ?
May 24 '07 #3

puppydogbuddy
Expert 100+
P: 1,923
Hi, Thanks for your reply.

Actually what I am trying to do in .Net is like this.......

Expand|Select|Wrap|Line Numbers
  1. string strSearchCriteria = "EmployeeId LIKE '" + txtEmployeeId.Text.Trim() + "%'";
  2.  
  3. if (cboStatus.Text.Trim() != "")
  4. strSearchCriteria += " AND Status = '" + cboStatus.Text + "'";
  5.  
  6. Database db = DatabaseFactory.CreateDatabase("DBConnection");
  7. DbCommand cmd = db.GetStoredProcCommand("SearchEmployee");
  8. cmd.CommandType = CommandType.StoredProcedure;
  9.  
  10. db.AddInParameter(cmd, "@SearchCriteria", DbType.String, strSearchCriteria);
  11.  
  12. Dataset dsEmployee = db.ExecuteDataSet(cmd);
  13.  
  14.  
So as u must have observed, SerachCriteria can be any thing. It can have many conditions combined using AND. Columns EmployeeId and Status are Text column.


Assume that,
txtEmployeeId.Text is "S" and cboStatus.Text is "Completed"

When I send this SearchCriteria as a parameter to Stored procedure, it should go at place of use of the parameter in query and my query should become..

SELECT *
FROM Employee
WHERE EmployeeId LIKE 'S%' AND Status = 'Completed'


But it's not working and SP is returning all records.

I know that we can implement this in SQL Server 2000 by using stored procedue sp_executesql which "executes a Transact-SQL statement or batch that can be reused many times, or that has been built dynamically".

How to acheive this in MS Access ?
Try it this way:(separate the value from the parameter name)
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM EMPLOYEE
  3. WHERE [@SearchCriteria] = 1;
May 24 '07 #4

NeoPa
Expert Mod 15k+
P: 31,770
As I understand parameters in parameter queries, they cannot be used as you are trying to use them (Replacing a part of the SQL itself). They can simply hold a single value.
May 25 '07 #5

P: 4
As I understand parameters in parameter queries, they cannot be used as you are trying to use them (Replacing a part of the SQL itself). They can simply hold a single value.

Thanks for reply

I got one solution on following link. http://steve.emxsoftware.com/Optiona...Search+Queries It works great. Author has given following query as a solution.

Expand|Select|Wrap|Line Numbers
  1.        SELECT * FROM Employee e
  2. WHERE (@EmployeeID IS NULL OR e.EmployeeID = @EmployeeID)
  3.   AND (@DepartmentID IS NULL OR e.DepartmentID = @DepartmentID)
  4.   AND (@ManagerID IS NULL OR e.ManagerID = @ManagerID)
  5.   AND (@LocationID IS NULL OR e.LocationID = @LocationID)
But I have one doubt, how can we use it in situations where we need to search the records based on date.

Example : Parameters are CreatedDateFrom and CreatedDateTo.

User can search by CreatedDateFrom or by CreatedDateTo or by both of them.

In this case, there are three possible queries.......

1. If only CreatedDateFrom is specified
Query =>
Expand|Select|Wrap|Line Numbers
  1. Select *
  2. from Employee
  3. where Format(CreatedDate,"yyyy/mm/dd") >= Format('@CreatedDateFrom',"yyyy/mm/dd");
2. If only CreatedDateTo is specified

Query =>
Expand|Select|Wrap|Line Numbers
  1. Select *
  2. from Employee
  3. where Format(CreatedDate,"yyyy/mm/dd") <= Format('@CreatedDateTo',"yyyy/mm/dd");
3. If both CreatedDateFrom and CreatedDateTo are specified

Query =>
Expand|Select|Wrap|Line Numbers
  1. Select *
  2. from Employee
  3. where Format(CreatedDate,"yyyy/mm/dd") BETWEEN Format('@CreatedDateFrom',"yyyy/mm/dd") AND Format('@CreatedDateTo',"yyyy/mm/dd");
I tried to create the query which will include all these condtions. but I couldn't do that. Do you have any ideas on how to do this?
May 29 '07 #6

NeoPa
Expert Mod 15k+
P: 31,770
First of all, I think you need to decide which system you are going to use for this.
The SQL syntax is NOT the same when using Access, SQL Server and .NET.
What you have here seems to be SQL Server code.
If I answered your question it would be as an Access expert and I would say use the Nz() function. This is NOT portable though, so you need to make it clear what you are intending. There are SQL Server as well as .NET forums here on this site too. You need to let us know exactly what your intention is before we spend time sending you somewhere that may be entirely inappropriate.
May 29 '07 #7

P: 4
First of all, I think you need to decide which system you are going to use for this.
The SQL syntax is NOT the same when using Access, SQL Server and .NET.
What you have here seems to be SQL Server code.
If I answered your question it would be as an Access expert and I would say use the Nz() function. This is NOT portable though, so you need to make it clear what you are intending. There are SQL Server as well as .NET forums here on this site too. You need to let us know exactly what your intention is before we spend time sending you somewhere that may be entirely inappropriate.
I want this for MS Access 2003 and .Net 2005.
Although the SQL syntax is NOT the same when using Access, SQL Server and .NET, whatever SQL code I found on that link is successfully running on MS Access 2003 with correct results. Do you think that there is some alternative solution for it using Access specific SQL syntax ?
May 30 '07 #8

NeoPa
Expert Mod 15k+
P: 31,770
I would certainly have used different SQL within Access.
As I said in an earlier post, I would use Nz(). This is not available outside the Jet SQL Engine though.
I know nothing of .NET :(
Most of the SQL I see posted in here looks more like SQL Server (TSQL) code to me though.
Is it really good for me to lead you down an Access SQL path if that's not what you're doing. I'm happy to, but you will need to remember where I'm coming from.
May 30 '07 #9

Post your reply

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