SQL Statement for searching in ADO.NET
-
I am trying to create a search for a sql database i have been writing. i have managed to get the search to search "firstName". if i replace this with any of my other column names it also works fine, but i am trying to get it to search multiple columns at once. i have tried searching on google but dont really know what to search for. i thought it might have been an AND or an OR statement but couldnt get these working either. this is my code; Sub BindDataGrid1() Dim CS As String CS = _ "data source=(local)\SQLEXPRESS;" & _ "initial catalog=mus_search_test;" Dim objCN As SqlConnection objCN = New SqlConnection(CS) Dim objDA As SqlDataAdapter objDA = New SqlDataAdapter("Select * from Musician_Search", objCN) Dim DS_SearchMusician_Search As DataSet DS_SearchMusician_Search = New DataSet objDA.Fill(DS_MS11, "Musician_Search") Dim objDV As DataView objDV = DS_MS11.Tables("Musician_Search").DefaultView ' sql statement for row filter MySearch = "firstName LIKE '*" & txtSearch.Text & "*'" objDV.RowFilter = MySearch RecNum = objDV.Count.ToString
John Michael Kinsella kinsellajohn@hotmail.com
-
I am trying to create a search for a sql database i have been writing. i have managed to get the search to search "firstName". if i replace this with any of my other column names it also works fine, but i am trying to get it to search multiple columns at once. i have tried searching on google but dont really know what to search for. i thought it might have been an AND or an OR statement but couldnt get these working either. this is my code; Sub BindDataGrid1() Dim CS As String CS = _ "data source=(local)\SQLEXPRESS;" & _ "initial catalog=mus_search_test;" Dim objCN As SqlConnection objCN = New SqlConnection(CS) Dim objDA As SqlDataAdapter objDA = New SqlDataAdapter("Select * from Musician_Search", objCN) Dim DS_SearchMusician_Search As DataSet DS_SearchMusician_Search = New DataSet objDA.Fill(DS_MS11, "Musician_Search") Dim objDV As DataView objDV = DS_MS11.Tables("Musician_Search").DefaultView ' sql statement for row filter MySearch = "firstName LIKE '*" & txtSearch.Text & "*'" objDV.RowFilter = MySearch RecNum = objDV.Count.ToString
John Michael Kinsella kinsellajohn@hotmail.com
Hey Can the SQL not be something like this SELECT * FROM T_TABLE WHERE FIRST_NAME LIKE @PARAM1 AND DESC LIKE @PARAM2 You could then have the user select the filters and call the correct prodedure/pass the correct dynamic SQL. In the case of the latter row filter would be : "firstName LIKE '*" & txtSearch.Text & "*' AND lastname LIKE '*" & txtSearch.Text & "*'" (The syntax of quotation marks may be incorrect as i just copied and pasted yours) Hope this helps. PS you may want to use stored procedures instead of dynamic sql as they help with security Dan Edit* There are many ways to search on more than one field such as making temporary tables etc this is just a simple example.
-
Hey Can the SQL not be something like this SELECT * FROM T_TABLE WHERE FIRST_NAME LIKE @PARAM1 AND DESC LIKE @PARAM2 You could then have the user select the filters and call the correct prodedure/pass the correct dynamic SQL. In the case of the latter row filter would be : "firstName LIKE '*" & txtSearch.Text & "*' AND lastname LIKE '*" & txtSearch.Text & "*'" (The syntax of quotation marks may be incorrect as i just copied and pasted yours) Hope this helps. PS you may want to use stored procedures instead of dynamic sql as they help with security Dan Edit* There are many ways to search on more than one field such as making temporary tables etc this is just a simple example.
many thanks for your reply, i will give it a shot.. it sounds perfectly plausible but i am not too familiar with SQL yet.. John
John Michael Kinsella kinsellajohn@hotmail.com
-
many thanks for your reply, i will give it a shot.. it sounds perfectly plausible but i am not too familiar with SQL yet.. John
John Michael Kinsella kinsellajohn@hotmail.com
-
Hi, Dan.. i am having troubles with the same thing. am i supposed to be using both the statements you suggested? the syntax for the second part was functional the way i sent it to you and it doesnt seem to work with your additions.. i brainstormed all the obvious problems, like i have secondName insted of lastName.. and extra/missing quotation marks... but i cant work it out.. i just get error messages when i try to search. see the issue im finding really challenging is that i havent handled much SQL. my course deals with one part at a time.. ie. asp.. then ado.. then sql... but i am having trouble understanding one and not the other.. its a home study course and so help from tutors is limited.. so, any other suggestions would be much appreciated
John Michael Kinsella kinsellajohn@hotmail.com
-
Hi, Dan.. i am having troubles with the same thing. am i supposed to be using both the statements you suggested? the syntax for the second part was functional the way i sent it to you and it doesnt seem to work with your additions.. i brainstormed all the obvious problems, like i have secondName insted of lastName.. and extra/missing quotation marks... but i cant work it out.. i just get error messages when i try to search. see the issue im finding really challenging is that i havent handled much SQL. my course deals with one part at a time.. ie. asp.. then ado.. then sql... but i am having trouble understanding one and not the other.. its a home study course and so help from tutors is limited.. so, any other suggestions would be much appreciated
John Michael Kinsella kinsellajohn@hotmail.com
Hi What you need to do is allow the user to have a search filter - for example we will use firstname and age. If we wanted to know all the people whose firstname began with an Dan and were older than 18 we would have a quesry like: SELECT * FROM T_EMPLOYEES WHERE FIRSTNAME LIKE 'Dan%' AND AGE > 18 If you can post your errors i will be able to help more Dan Edit** Try using a stored procedure instead of dynamic SQL too. Try this URL http://www.w3schools.com/sql/default.asp -- modified at 10:16 Tuesday 3rd July, 2007