How do I pass a wildcard to a data source query
-
What I want to do is something like this, which fails because NumericID is an int type.
ds_Search.SelectParameters.Add("NumericID", "%");
The only relevant hit I was able to find was from the dubious quality website expert exchange (first result, very bottom of the page)[^]which said I either needed to change the DB to have a string field (not possible) or write seperate queries for the wildcards (impractical because there're dozens that would need duplicated). Are those really my only options?
Today's lesson is brought to you by the word "niggardly". Remember kids, don't attribute to racism what can be explained by Scandinavian language roots. -- Robert Royall
-
What I want to do is something like this, which fails because NumericID is an int type.
ds_Search.SelectParameters.Add("NumericID", "%");
The only relevant hit I was able to find was from the dubious quality website expert exchange (first result, very bottom of the page)[^]which said I either needed to change the DB to have a string field (not possible) or write seperate queries for the wildcards (impractical because there're dozens that would need duplicated). Are those really my only options?
Today's lesson is brought to you by the word "niggardly". Remember kids, don't attribute to racism what can be explained by Scandinavian language roots. -- Robert Royall
A numeric is not a string, but you can convert the NumericId to a string in a query. Only then can it be compared with a string.
Jean-Christophe Grégoire
-
A numeric is not a string, but you can convert the NumericId to a string in a query. Only then can it be compared with a string.
Jean-Christophe Grégoire
Do you mean a query that would take a string parameter and convert it into a number before looking at the table? If so, could you give an example?
Today's lesson is brought to you by the word "niggardly". Remember kids, don't attribute to racism what can be explained by Scandinavian language roots. -- Robert Royall
-
What I want to do is something like this, which fails because NumericID is an int type.
ds_Search.SelectParameters.Add("NumericID", "%");
The only relevant hit I was able to find was from the dubious quality website expert exchange (first result, very bottom of the page)[^]which said I either needed to change the DB to have a string field (not possible) or write seperate queries for the wildcards (impractical because there're dozens that would need duplicated). Are those really my only options?
Today's lesson is brought to you by the word "niggardly". Remember kids, don't attribute to racism what can be explained by Scandinavian language roots. -- Robert Royall
-
What records are you selecting with that? Every record that has an int-value of "%"? I don't understand what you are trying to do here. What is the SQL-variant of the query you wanna run?
I are troll :)
Hopefully this'll still make sense after being (heavily) cut down and generalized, the real query has more stuff in all 3 sections of sql statement.
SELECT table1.table1ID AS ID, table2.OrgTypeID
FROM (table1 INNER JOIN table2 ON table1.table2ID = table2.table2ID)
WHERE (table2.table4ID = ?)table2.table4IDis selected from a listbox that contains all the values in table4 (each giving a table4ID value) or "all" in which case I want to pass a wildcard in.Today's lesson is brought to you by the word "niggardly". Remember kids, don't attribute to racism what can be explained by Scandinavian language roots. -- Robert Royall
-
Hopefully this'll still make sense after being (heavily) cut down and generalized, the real query has more stuff in all 3 sections of sql statement.
SELECT table1.table1ID AS ID, table2.OrgTypeID
FROM (table1 INNER JOIN table2 ON table1.table2ID = table2.table2ID)
WHERE (table2.table4ID = ?)table2.table4IDis selected from a listbox that contains all the values in table4 (each giving a table4ID value) or "all" in which case I want to pass a wildcard in.Today's lesson is brought to you by the word "niggardly". Remember kids, don't attribute to racism what can be explained by Scandinavian language roots. -- Robert Royall
dan neely wrote:
or "all" in which case I want to pass a wildcard in.
How about removing the
WHERE (table2.table4ID = ?)
If you omit the WHERE-clause altogether, wouldn't that effectively give you all the records that a wildcard would select?
I are troll :)
-
dan neely wrote:
or "all" in which case I want to pass a wildcard in.
How about removing the
WHERE (table2.table4ID = ?)
If you omit the WHERE-clause altogether, wouldn't that effectively give you all the records that a wildcard would select?
I are troll :)
See my original post. Sometimes I need a wildcard, sometimes I have a specific value. I'd prefer not having to duplicate a dozenish queries if I don't have to in order to implement the wildcard because of the increased maintainability difficulty.
Today's lesson is brought to you by the word "niggardly". Remember kids, don't attribute to racism what can be explained by Scandinavian language roots. -- Robert Royall
-
See my original post. Sometimes I need a wildcard, sometimes I have a specific value. I'd prefer not having to duplicate a dozenish queries if I don't have to in order to implement the wildcard because of the increased maintainability difficulty.
Today's lesson is brought to you by the word "niggardly". Remember kids, don't attribute to racism what can be explained by Scandinavian language roots. -- Robert Royall
As far as I know, ANSI-SQL supports wildcards only for filtering on text-based columns, through the LIKE-operator. This option gives you two separate queries;
SELECT table1.table1ID AS ID, table2.OrgTypeID
FROM (table1 INNER JOIN table2 ON table1.table2ID = table2.table2ID)
WHERE (table2.table4ID = 10)(10 being an example of a specific value)
SELECT table1.table1ID AS ID, table2.OrgTypeID
FROM (table1 INNER JOIN table2 ON table1.table2ID = table2.table2ID)
WHERE (CAST(table2.table4ID AS NVARCHAR) LIKE ('%'))The second version will convert all values in the table4ID-column to the NVARCHAR datatype.
I are troll :)