How to do a search 1

[RSX02]

I have a table that have fields First_name, Last_name, city and country.
In a form I want that the user be able to do a search with these field. The user must be able to search by the fields that he want to search by and he can search by the number of the fields that he want. For example he can search by the field First_name and City. Or by the Last_name and Country, or simply by city.

I saw that I can use 3 ways..(but i'm not sure that it's the right way to use these so if not, tell me please)
I saw that I could use these ways:

the Find method on the DataRowCollection object,
the Select method of the DataTable object
And the FindRows methods on the DataView object.

I would like to know which is the best to use...and also how to use it as I'm pretty new with .net...

I also want to display all records retreived in a datagrid...

If somebody could give me an example of how to use the best way I will realllllllyyyy appreciate!!
Thanks in advance.

 

[SqueakinSweep]

dt.select will return a DataRow collection which cant be set as the Grids Datasource

Personally I would use create a Dataview which you can then set the grids recordsource to. It is also very simple to use.

Dim dv As DataView = dt.DefaultView
dv.RowFilter = "country='UK'"
DataGrid1.DataSource = dv

Sweep
...if it works dont mess with it

 

[RSX02]

SqueakinSweep
Thank you for your reply.

Does it have to way to pass many parameters even if it is empty. I presently use a dataview as you told me but I don't want to use this kind of code that look all possibilities..example:

If Me.SearchFirstName.Text = String.Empty And Me.SearchLastName.Text = String.Empty And Me.SearchCity.Text = String.Empty And Me.SearchCountry.Text = String.Empty Then
Dv.RowFilter() = ""
Else
If Me.SearchFirstName.Text <> String.Empty And Me.SearchLastName.Text = String.Empty And Me.SearchCity.Text = String.Empty And Me.SearchCountry.Text = String.Empty Then
Dv.RowFilter() = "First_Name = '" + Replace(Me.SearchFirstName.Text, "'", "''&quot + "'"
'etc...
end if


Does it have something that I can only use only one line and if the textbox is empty it doesn't look for this field?

Thanks in advance

 

[SqueakinSweep]

Build your 'Select' statement dynamically
Code below untested...but gives you the idea

dim sFilter as String=""
if me.searchfirstname.text <> string.empty
   sfilter+="first_name='"+me.searchfirstname.text+"'"
endif
if me.searchlastname.text <> string.empty
   if sFilter.length>0 then sfilter+=" and "
   sfilter+="last_name='"+me.searchlastname.text+"'"
endif
dv.rowfilter(sFilter)

Sweep
...if it works dont mess with it

 

[RSX02]

that's better that I have for sure!
I'll test it and give you some news about it.
Thank you Sweep

 

[RSX02]

I thing this is the simpliest I can have so thank you very much Sweep!
A simple solution that I can understand!

 

[RSX02]

Another question

What if I would like that the user enter only a part of the first_name for example: the First_name is John...and the user enter "oh" for the first name. I would like that all records with the First_name that have the string "oh" in it (anywhere in the string) to be retrieved?

If I take this part of the code...How could I adapt it to this:
sfilter+="first_name='"+me.searchfirstname.text+"'"

 

[RSX02]

I found it

sFilter += "first_name like '" + "%" + Replace(Me.SearchFirstName.Text, "'", "''&quot + "%" + "'"

 

[Paszt]

I'm waiting for chiph to reprimand you for leaving yourself open to SQL injection (do a Google search, or view the FAQ in the VB Databases forum).

 

[RSX02]

I'm trying to find a way to do the select the way that Chip wrote the query that Paszt told me....but I don't know if I'm wrong but it seems that in the code, it miss some parts? For example the declaration of some variables (NumUsers, User(i),PWD(i), etc...)...

The select doesn't work as the Insert also, So I don't know how can I tell the query "where First_name = " & me.TxtFirstName.text with the query that Chip wrote.

Sorry to be that beginner but I'm trying to understand by myself but I guess I missed some things.

Thanks in advance for people that gonna try to help me!

 

[RSX02]

I tried to find something that can fit with my problem but as I am new with .net I'm not able to solve my problem.
Does somebody can help me with this please?

Thanks in advance