[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

DataTable (xsd) select and wildcard parameters

Posted on 2007-10-19
15
Medium Priority
?
913 Views
Last Modified: 2013-12-09
Is it possible to pass a wildcart into a select statements required parameter to return any values found?

SELECT     FLD_LOCID, FLD_NAME
FROM         TBL_LOCATIONS
WHERE     (FLD_LOCID = @Param1)

And pass "*" as Param1? (* does not work by the way :))

Nugs
0
Comment
Question by:Nugs
  • 7
  • 3
  • 2
  • +2
15 Comments
 
LVL 21

Assisted Solution

by:surajguptha
surajguptha earned 500 total points
ID: 20110481
Instead when you want all the results just dont include the where clause.
Pass the commandtext as SELECT     FLD_LOCID, FLD_NAME
FROM         TBL_LOCATIONS
0
 
LVL 20

Assisted Solution

by:REA_ANDREW
REA_ANDREW earned 500 total points
ID: 20110505
if you want to return all values from the table, do as surajguptha suggests.  BUT you are doing it correctly if you want to see any values which have FLD_LOCID equal to @Param1.

Andrew
0
 
LVL 2

Author Comment

by:Nugs
ID: 20110606
ok, sorry but maybe i should explain some more...

The actual select statement looks something like this:

SELECT     FLD_LOCID, FLD_NAME
FROM         TBL_LOCATIONS
WHERE     (FLD_LOCID = @Param1) AND (FLD_NAME = @Param2)

now i my code there may be a senario where i want to get the results by using param2 and NOT param one... So essentially i want to be able to do this when calling the xsd tableadapter:

TBL_LocationAdapter.GetData("*", "value")
or
TBL_LocationAdapter.GetData("5", "*")
or
TBL_LocationAdapter.GetData("5", "value")

Essentially i want to be able to pass a wildcard value inot the param1 or param2 that will return all record or essentially skip that perticular filter...

Possible?

Nugs
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 96

Assisted Solution

by:Bob Learned
Bob Learned earned 500 total points
ID: 20110615
Nugs,

Are you talking about the LIKE operator?

SELECT     FLD_LOCID, FLD_NAME
FROM         TBL_LOCATIONS
WHERE     FLD_LOCID LIKE @Param1

Bob
0
 
LVL 20

Expert Comment

by:REA_ANDREW
ID: 20110676
OR are you saying you want to generate the where clause on the fly determined by previous logic.

Andrew
0
 
LVL 2

Author Comment

by:Nugs
ID: 20110692
well not really, my code allow the data to be return by either the FLD_LOCID or FLD_NAME or both

SELECT     FLD_LOCID, FLD_NAME
FROM         TBL_LOCATIONS
WHERE     (FLD_LOCID = @Param1) AND (FLD_NAME = @Param2)

I need a way to pass a wildcard value into either Param1 or Param2 essentially only return records based on the other param (the one that is not wild)

I do not think there is a way of doing what i want here, i have never heard of wildcard values in a select statement (other the SELECT * FROM...) but thought it was worth a try to ask..

Nugs
0
 
LVL 19

Accepted Solution

by:
NickUpson earned 500 total points
ID: 20110713
SELECT     FLD_LOCID, FLD_NAME
FROM         TBL_LOCATIONS
WHERE     (FLD_LOCID like @Param1) AND (FLD_NAME like @Param2)

and set the param fields to '%' to get everything
0
 
LVL 2

Author Comment

by:Nugs
ID: 20110725
REA_ANDREW: that would be an option but i am using XSD datasets and tableadapters generated by VS2005... I don't have much control over the SQL statement from my codebehind (or at least i don't think i do)
0
 
LVL 2

Author Comment

by:Nugs
ID: 20110766
NickUpson: Trying that now, but what if the Params are int types??
0
 
LVL 2

Author Comment

by:Nugs
ID: 20110810
yeah it does not like % as it's parameter, FLD_LOCID is looking for a int value
0
 
LVL 21

Expert Comment

by:surajguptha
ID: 20111062
I would suggest you to build the query at run time to inlcude the where clauses only if the values are not *
0
 
LVL 2

Author Comment

by:Nugs
ID: 20111096
I was hoping someone would not say that :(

Thanks,

Nugs
0
 
LVL 19

Expert Comment

by:NickUpson
ID: 20111221
SELECT     FLD_LOCID, FLD_NAME
FROM         TBL_LOCATIONS
WHERE     (FLD_LOCID = @Param1 or FLD_LOCID  is null)
         AND (FLD_NAME like @Param2or FLD_LOCID  is null)

set the param to null when you want to have it effectively ignored
0
 
LVL 2

Author Comment

by:Nugs
ID: 20111322
there is no way to set a int type to null. int are value types and require a number value. Also you can not pass 'null' as the barameter as it is looking for a int, i get a "cannot convert from '<null>' to 'int'" error.
0
 
LVL 19

Expert Comment

by:NickUpson
ID: 20111375
you can certainly define a column in a table as a nullable integer, so I assume that there is some way to use that inside delphi
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question