search requiers more than 1 parameter?

vbpetrov

New member
Joined
Jan 26, 2010
Messages
2
Programming Experience
Beginner
Hi, my name is Viktor and i have this problem:

My IT teacher at the HTMU(Chemical and Metalurgy University of sofia) gave me a project to write a database software....using the free Visual basic from Visual Studio 2008....I've started the project and got to a dead end... :-(
The problem I've got is that I've made a search engine using a datagrid and query...fine....but I can search only 1 parameter or 1 word...but i need to find info that requiers more than 1 parameter.... the filter I'm usig is this....
SELECT npored, [datanapoluchavane nvarchar], datanavrashtane, lice_poluchatel, adres_zakontakti, opisanie_na_materiala, zabelejka

FROM materiali_polzvani_ot_arhiva
WHERE (datanavrashtane LIKE N'%' + @datanavrashtane + N'%') AND
(lice_poluchatel LIKE N'%' + @lice_poluchatel + N'%') AND
(adres_zakontakti LIKE N'%' + @dadres_zakontakti + N'%') AND
(opisanie_na_materiala LIKE N'%' + @opisanie_na_materiala + N'%') AND
(zabelejka LIKE N'%' + @zabelejka + N'%')


I've tried almost everything but no result.... can someone tell me what will be the proper way to make a search engine that will find data on 2 or more parameters using a single textbox......

The other problem is connected to the search engine....i've entered some tables in the program...but when i tried to delete some data the program told me that i can't do it....for some reason....and the search engine stops when it hit a blank row....but when i tried to give the query a update option using the save button on the nav menu it tels me that i've got to write a proper update rule.... here's a example of the code that concerns this part....

Private Sub PuPRegistarBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PuPRegistarBindingNavigatorSaveItem.Click
Me.Validate()
Me.PuPRegistarBindingSource.EndEdit()
Me.TableAdapterManager.UpdateAll(Me.InfodataDataSet1) <=== this bit here!
Me.PuPRegistarTableAdapter.Fill(Me.InfodataDataSet1.PuPRegistar)

End Sub


That really my problem...can somebody please HELP!
Thanks alot....

Best Regards Viktor:D
 

cjard

Well-known member
Joined
Apr 25, 2006
Messages
7,056
Programming Experience
10+
Good that youre using the proper tutorials :)

First off:
when i tried to delete some data the program told me that i can't do it....for some reason....
Probably your table doesnt have a primary key, so visual studio cannot design a query automatically that can exactly identify one row in order to delete or update it. If you get an error like:
"DeleteCommand required when passing a collection with deleted rows"

This is most often the case

Add a primary key to your table and then delete and re-make a new tableadapter (or configure it again to re-generate the SQLs)


SELECT npored, [datanapoluchavane nvarchar], datanavrashtane, lice_poluchatel, adres_zakontakti, opisanie_na_materiala, zabelejka

FROM materiali_polzvani_ot_arhiva
WHERE (datanavrashtane LIKE N'%' + @datanavrashtane + N'%') AND
(lice_poluchatel LIKE N'%' + @lice_poluchatel + N'%') AND ....
The SQL can work but youre making it too complicated

Do this:
WHERE (datanavrashtane LIKE @datanavrashtane) AND
(lice_poluchatel LIKE @lice_poluchatel) AND ...
And then put the % signs into the parameter value in VB instead:

myTableAdapter.FillByXXX("%" & datanavrashtaneTextbox.Text & "%", "%" & lice_poluchatelTextbox.Text & "%")

Note that putting % at the start can make the query very slow. Actually you should just do this:
myTableAdapter.FillByXXX(datanavrashtaneTextbox.Text, lice_poluchatelTextbox.Text)

let the user put his own % signs in, because if they know the exact name then no point wasting database resources searching with %. This way user can choose to have:
HELLO <= fast query of exactly HELLO
HELLO% <= slower query starting with HELLO
%HELLO% <=very slow query containing HELLO
% <= all records

Note that for any text box that are left blank, put the value as "%" otherwise you will get no results
 
Top Bottom