Hi,
Does anyone know how i go about writing a search query using paramaters, ive been using this as my template:
Select Distinct Recipedata.Name, Ingredients.Ingredient, Directions.Directions
from Recipedata,Ingredients,Directions
Where Ingredients.Ingredient Like '%Chicken%' and
RecipeData.RecipeID = Ingredients.RecipeID and Ingredients.RecipeID = Directions.RecipeID;
Problem is now that i want to use paramters instead of hard coded values as this is for a user. How do i go about this This database has a VB2k5 front end for the desktop and PDA(and no the target PDA is not web enabled so web servcies arent available to me).
Thanks Much
how to search a database by paramaters
Phizz
Thanks again for your reply.I have tried the code above,i got a waring about the code being obsolete but i fixed it.
I cant fully say it has worked as it has no output. I have tried as well to adapt the theory out of code to mine suffice to say the query analyser throws out that use of parentheses in queries.Frankly i see no reason why the above code should not work.On the matter of output i have been trying to get my results on a datagrid with no sucess.Eventually the goal of this form is for the user to be able to select via combobox what type of data do they want to search for and just type just a part of a name and things that get a match are displayed in a datagrid.Im going to try again with getting output to the datagrid, once i have that i can hopefully get the results of my query to output there.By any chance is there a way to do this via tableadapters
Thank you again for your help.
Windows Mobile 5.0 smartphone
It appears you believe parameter value is hard coded in the query That is absolutely not the case. < xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
Parameter value is set in your code as follows:
Dim sSQL As String = "SELECT RecipeData.Name, Ingredients.Ingredient, Directions.Directions" +
"FROM RecipeData, Ingredients, Directions" +
"WHERE (RecipeData.Name LIKE )" ' There's no values here, just ' ' placeholder.
Dim command As New SqlCeCommand(sSQL, connection)
command.Parameters.Add("@Name, PARAMETER_VALUE_HERE) ' That adds parameter and sets it's value. You can change value as many times as needed without changing query.
PARAMETER_VALUE_HERE is your parameter value. It can be say, textbox.Text or whatever else you want it to be. Query text remains the same.
There's nothing strange about error, '%' should be a part of parameter value or it would cause syntax error.
You can add % to value using, say, String.Format if needed:
command.Parameters.Add("@Name, String.Format("%{0}%", textbox1.Text))
That would take text from textbox1, wrap it in %% and that as parameter value.
yibeltalisme
ChoclatB0y
I have tried this to search by paramater but i get a error message right after I enter in the paramater that tells me that the input string was not in the correct format. I have no clue as to why im getting this error , here is the query im working with:
SELECT RecipeData.Name, Ingredients.Ingredient, Directions.Directions
FROM RecipeData, Ingredients, Directions
WHERE (RecipeData.Name LIKE '% ' + @tosearch + ' %')
Daniel Stolt
That should work:
WHERE (RecipeData.Name LIKE )
Don't forget to actually add parameter to your command and set it's value to whatever you’re searching for, e.g. "%Chicken%"
If you're using SQL Mobile you can used named parameters:
WHERE (RecipeData.Name LIKE @name)