query for names

cesarfranciso · March 25th, 2007, 05:32 PM

VBExp05
Access database by code

How do I query a database "First Name" column for a name when the exact name is not known?
The query is initiated from a Data Entry Form's TextBox.

It goes this way (we suppose there are several First Names starting with 'A'(or any other character of the alphabet) and we are looking for 'Aaron'). Typing 'A' into the corresponding TextBox, all the names starting with A will show up in a DataGridView Form. Typing 'Aa', only the names with 'Aa' as the first two character will show up in a DataGridView Form. Typing Aaron, the Entry Form's TextBoxes will fill up the with Aaron's data.

The logic behind is that if there is more than one query result than open the DataGridView Form in order to choose from the result. If there is only one result than open the name and its related data in the DataEntry Form.

-------------------------------------------------------------------

You can read the rest of the posts, but this is the rather simple Solution to it:

Private Sub Find_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Find.Click

If txtboxLastName.Text <> "" Then
Try
Me.Table1TableAdapter.FillBy(Me.AmDBDataSet.Table1 , txtboxLastName.Text + "%")
Catch ex As System.Exception
System.Windows.Forms.MessageBox.Show(ex.Message)
End Try

In the query builder type: SELECT * From Table WHERE LastName LIKE ?

woodyz · March 26th, 2007, 10:31 AM

You use the LIKE keyword:

Where FirstName LIKE ('Aa%')

In this case, the percent sign acts as a wildcard so the meaning of the string Aa% means:
Anthing that starts with Aa

Woody Z
http://www.learntoprogramnow.com
How to use a forum to help solve problems

gbianchi · March 26th, 2007, 10:36 AM

hi there...

Woody: it this % used for only match 1 character and * for 1 or more chars???

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========

cesarfranciso · March 26th, 2007, 03:17 PM

There is one TextBox (First Name) on the DataEntry Form.

It goes this way (we suppose there are several First Names starting with 'A'(or any other character of the alphabet) and we are looking for 'Aaron').

------OR ANY OTHER CHARACTER OF THE ALPHABET.------------

Suppose I am typing 'B'( or C or D or E etc) looking for Burton. All the names with B will show up in DataGridView. I am typing 'Bu'. All the names starting with 'Bu' will show up in DataGridView. I am typing 'Bur'. All the names starting with Bur (say Burlon, Burgon, etc) will show up in DataGridView except if there is none other than Burton, then Burton with all his related data will show up in a DataEntry Form.

This is a simple question nevertheless nobody seems to be able to answer it. It is doable, because I have a small commercial Access/Jet database based program which does it.

Is this question not a basic, newbie question?

How do you read the TextBox user's input into a query? I want query 'B'+'%' or 'Ba'+'%' or 'C' or 'D' etc, until only one name shows up.

[b]

woodyz · March 26th, 2007, 05:04 PM

Quote:

quote:Originally posted by gbianchi
hi there...

Woody: it this % used for only match 1 character and * for 1 or more chars???

HTH

Gonzalo

I hope I didn't mix anyone up.

It depends on the implementation of SQL, In Oracle and Sql Server we use % for more than one character, and _ (underscore) for a single character... but in Access, I belive we must use the * character for more than one character, and ? for one character.

Woody Z
http://www.learntoprogramnow.com
How to use a forum to help solve problems

woodyz · March 26th, 2007, 05:41 PM

Quote:

quote:Originally posted by cesarfranciso
There is one TextBox (First Name) on the DataEntry Form.

It goes this way (we suppose there are several First Names starting with 'A'(or any other character of the alphabet) and we are looking for 'Aaron').

------OR ANY OTHER CHARACTER OF THE ALPHABET.------------

Suppose I am typing 'B'( or C or D or E etc) looking for Burton. All the names with B will show up in DataGridView. I am typing 'Bu'. All the names starting with 'Bu' will show up in DataGridView. I am typing 'Bur'. All the names starting with Bur (say Burlon, Burgon, etc) will show up in DataGridView except if there is none other than Burton, then Burton with all his related data will show up in a DataEntry Form.

This is a simple question nevertheless nobody seems to be able to answer it. It is doable, because I have a small commercial Access/Jet database based program which does it.

Is this question not a basic, newbie question?

How do you read the TextBox user's input into a query? I want query 'B'+'%' or 'Ba'+'%' or 'C' or 'D' etc, until only one name shows up.

[b]

Okay - first, I think you understand the query part of it.
What you need now is the "text box" part of it. Is that correct?

Each time you add a new character to the textbox, you could do another query. However - this isn't going to be real responsive because hitting the database in a busy network could turn into a preformance issue.

One way to deal with this would be to only bring in the first 5 or 10 matches - at least you would only be moving around a minimal set of data.

This is a simple and common feature. Which part of it would you like help with?

Each keystroke made in the text box can be captured via the change event handler. So with each change event, you will make another query to the database using the entire string currently held in the textbox text property.

Woody Z
http://www.learntoprogramnow.com
How to use a forum to help solve problems

cesarfranciso · March 26th, 2007, 11:12 PM

Thanks,you hit the nail squarely on its head. Yes,the my problem is with the TextBox part. Each time I add a new character to the textbox, I would like to do another query. Individual user, no network-this is just a newbie program)

I was doing the query by the following code for each textbox. This code is not giving me the possibility to change the original query:

Try
Me.Table1TableAdapter.FillBy(Me.NewAppDBDataSet.Ta ble1, FNameTextBox.Text)
Catch ex As System.Exception
System.Windows.Forms.MessageBox.Show(ex.Message)
End Try

P.S: By the way, you were mentioning the performance issue - this is a small program, less than 1MB in size but the start-up is noticeably slow in XPSP2.

woodyz · March 27th, 2007, 11:40 AM

Okay -
First of all, it looks like you are working with VB.NET code, but this forum is a VB6 forum.

None-the-less, you can do exactly as I suggested.

However, if the set of data is very small, which it might be as in your example (or even quite large), you can work with a filtered DataView to get the functionality you want.

For example, the following code (more-or-less) is what you want:

Code:

    Private _data As DataTable

    Private Sub txtName_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtName.TextChanged
        ' NOTE: Add some exception handling
        Dim dv As New DataView

        If _data Is Nothing Then
            InitializeData()
        End If

        dv.Table = _data
        dv.RowFilter = "FirstName LIKE '" & txtName.Text & "%'"
        noteGrid.DataSource = dv

    End Sub

    Private Sub InitializeData()
        ' NOTE: Add some exception handling
        Dim cn As OleDbConnection
        Dim s As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & System.AppDomain.CurrentDomain.BaseDirectory() & "MyData.mdb;"
        cn = New OleDbConnection(s)
        cn.Open()
        Dim da As New OleDbDataAdapter("Select FirstName from People ORDER BY FirstName", cn)
        _data = New DataTable
        da.Fill(_data)
        _data.TableName = "People"
    End Sub

Note that for a DataView filter, you want to use the "%" to mean more than "one or more" characters. The DataView, DataSet and DataTable are disconnected from the original data provider - they use their own conventions, and don't know anything about Access, Oracle, or Sql Server, so to speak.

Woody Z
http://www.learntoprogramnow.com
How to use a forum to help solve problems

cesarfranciso · March 27th, 2007, 06:48 PM

cesarfranciso · March 27th, 2007, 07:22 PM

It is a VB2005Express project. The Database was created by ADOX. (It took me a while to get it half-right). I am looking at your code and I am not sure that it will work for me. But anyway thanks a lot for your effort. You did lot more than anybody else. I am pursuing this problem for more than a month. I have a few books here: Visual Basic 2005 Demystified, Visual Basc 2005 Express, Mastering Visual Basic 2005, The Complete Reference: SQL second edition -- no avail. This kind of answer I get on the net: A* or A%.
I wonder where is the information for this kind of simple problems? How could somebody be able to tackle real difficult problems like connect by modem to a mainframe, read scanner scans into a database, etc.