Search function to fill datagridview

[gate7cy]

Hello everyone. I have a windows form with a datagridview, a textbox and a button. What I want to accomplish is to search and fill the datagridview with user data inputted in the textbox. I have managed to do this using ExtServicesBindingSource.Filter and giving one variable to search. This way does not accept more that 1 search criteria. So I want a way to make vb search the table in more than 1 variable. Thanks for the replies. I am using VB 2008 express and Access 2007.

 

[MattP]

This should lead you in the right direction: How Do I: Create a Search Form?

 

[cjard]

Indeed, you should get the database to search and return only a few matching rows, rather than download hundreds or thousands of rows and then use a client side filter to reduce them

 

[gate7cy]

Thanks for the replies. cjard you mean I should use the get method rather than the fill method when I create my queries? Referring back to my original question I want to use more than one criteria to search through my database. The video is helpfull up to a point but the end results I get on my VB express are not the same. In my toolstrip generated I do not have a search textbox added or the label. Only the button. If I will add to the toolstrip a textbox will it be automatically associated to my query?
Also the video does not advice how to go when you want to use more than one field for filtering

 

[cjard]

Thanks for the replies. cjard you mean I should use the get method rather than the fill method when I create my queries?

Get and Fill both approach the database to return (a subset of) rows from the table. The difference is that Fill fills an existing table, and Get gets you a new table. Typically, when working with bound data you would Fill whatever table the controls are bound to

What I meant by not downloading a million rows then client side filtering them was:

Write a Fill query that takes a parameter:
FillByUser(myDataTableToFill, "john smith")

Rather than download a million users, then filter locally:
Fill(myDataTableToFill)
myDataTableToFill.DefaultView.Filter = "[name] = 'john smith'"

Hopefully the reasons for this are fairly obvious but here's a run down:
The latter doesnt use database indexes, generates massive amounts of network traffic and uses a dumb search strategy. In short, it's the slowest, worst way to do a search. DBs are for searching.

Also the video does not advice how to go when you want to use more than one field for filtering

Write your query like this:

SELECT * FROM table WHERE
stringColumn LIKE @stringParam AND
(numberColumn = @numberParam OR @numberParam IS NULL) AND
(dateColumn = @dateParam OR @dateParam = #01-JAN-1900#) AND
(dateColumn BETWEEN @lowerDate AND @upperDate)


To wildcard the string, pass "%" as a value
To wildcard the number, pass Nothing
To wildcard the date, pass New DateTime(1900, 1, 1)
Or if youre using date ranges pass very low and very high values


Naturally, if youre using a DB that cannot have numbers be NULL or whjatever, you have to be sensitive to the needs of the DB. Pass a number that will never occur, like -999999999, and check whether the parameter is equal to that too

If you can't see how it works, let me know, but basically each clause to be wildcarded must evaluate its OR to true

 

[gate7cy]

thanks for all the replies. Found what I was looking for. I used the solution on this link
Search Access Database with textbox.text & return dataset & fill

 

[gate7cy]

cjard thanks for the beautiful breakdown solution you have provided. Just some questions if you have the time? The @numberparam, @dateparam, string param, @lowerdate, and @upperdate where and how do I create them? Is this line in your code a parameter :
'myDataTableToFill.DefaultView.Filter = "[name] = 'john smith'"'?
If not can you show how to write one? I only a newbie in VB, please do understand me.
Thanks

 

[MattP]

...Also the video does not advice how to go when you want to use more than one field for filtering

Here's the query used in the video:

SELECT        CustomerID, LastName, FirstName, Address, City, State, ZIP, Modified
FROM            Customer
WHERE        (LastName LIKE @LastName + '%')

Lets say you wanted to filter by first and last name. You could add a FirstName parameter like this:

SELECT        CustomerID, LastName, FirstName, Address, City, State, ZIP, Modified
FROM            Customer
WHERE        (LastName LIKE @LastName + '%') AND (FirstName LIKE @FirstName + '%')

When you call the query you just need to provide the additional data for the FirstName parameter.

	Private Sub FillByFirstAndLastToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
	Handles FillByFirstAndLastToolStripButton.Click
		Try
			Me.CustomerTableAdapter.FillByFirstAndLast _
			(Me.CustomerSearchDataSet.Customer, Me.ToolStripLastNameTextbox.Text, Me.ToolStripFirstNameTextbox.Text)
		Catch ex As Exception
			System.Windows.Forms.MessageBox.Show(ex.Message)
		End Try
	End Sub

 

[cjard]

thanks for all the replies. Found what I was looking for. I used the solution on this link
Search Access Database with textbox.text & return dataset & fill

And boy shouldn't you just read my recent post to the Microsoft moderator who gave the "answer". His advice was horrendous, and I couldnt believe the date was 2008; it should have been more like 1988

 

[cjard]

cjard thanks for the beautiful breakdown solution you have provided. Just some questions if you have the time? The @numberparam, @dateparam, string param, @lowerdate, and @upperdate where and how do I create them? Is this line in your code a parameter :
'myDataTableToFill.DefaultView.Filter = "[name] = 'john smith'"'?
If not can you show how to write one? I only a newbie in VB, please do understand me.
Thanks

Have a read of the PQ link in my sig, some info is there about what these things are. Basically:

x as New SqlCommand("SELECT * FROM table WHER col LIKE @param")
x.Parameters.AddWithValue("@param", "John Smith%")
x.ExecuteBlahBLah..
'reuse
x.Parameters("@param").Value = "Sarah Jones%"
x.ExecuteBlahBlah


>'myDataTableToFill.DefaultView.Filter = "[name] = 'john smith'"'?
Nothing there is parameterized and I'd strongly recommend against using client side filter in MOST instances



If you read the DW2 link in my signature, section "Creating a Form to Search Data" you'll see how easy this stuff should be; Visual Studio will do all the boring bits for you

 

[cjard]

Here's the query used in the video:

SELECT CustomerID, LastName, FirstName, Address, City, State, ZIP, Modified
FROM Customer
WHERE (LastName LIKE @LastName + '%')[/code]

I would never advocate putting + '%' into an SQL because youre then forcing it to be a wildcarded query all the time

it is much better to leave the user or developer to decide whether to put the % into the parameter value ot not, in which case it still functions like a wildcard but you can choose:

parameterValue = "Smith" 'only people with lastname Smith are returned

parameterValue = "Smith%" 'Simth, Smithe, Smithy etc are returned

Lets say you wanted to filter by first and last name. You could add a FirstName parameter like this:

Yes, but this isnt dynamic.



To make a dynamic parameterized query you either:

Make the query with ALL the search terms in in advance, and then just set those to "match-all" wildcards if you do not want to filter on that item

Example:

SELECT * FROM people WHERE first = @first and Last = @last

'all people named smith
@first = "%"
@last = "Smith"

'all People named George
@first = "George"
@last = "%"

'George Smith
@first = "George"
@last = "Smith"

'george smith or george seth or .. etc
@first = "George"
@last = "S%th"

Dynamically concatenate the SQL with parameters in, then dynamically build the parameters collection, then run the query

'pseaudocode

x as new SqlComamnd("SELECT * FROM people WHERE 1=1 ") 'base query

For each textbox on the form
  if the textbox isnt blank
    x.CommandText += " AND " & textbox.Tag.ToString() & " LIKE @" & textbox.tag.ToString()
    x.Parameters.AddWithValue("@" & textbox.Tag.ToString(), textbox.text)
Next

See how this works? suppose we put George in firsttextbox and Smith in lasttextbox. Also we have put the name of the db column into the tag, e.g.
firstTextbox.tag = "firstCol"
lastTextbox.tag = "lastCol"

Our sql ends up as:
SELECT * FROM people WHERE 1=1 AND firstcol = @firstcol AND lastcol = @lastcol

Our parameters ends up as:
AddWithValue("@firstcol", "George")
AddWithValue("@lastcol", "Smith")


Note that the 1=1 is just a DUMMY useless operation that contributes NOTHING in the SQL. but it means we can just concatenate ANDs onto the end of it without causing a syntax error. This avoids complicated logic in VB of "if this is the first text box then don't put AND else..."