Database Search?

[Rally2000]

Hi Guys!
I would like to add search functionality to my app.
I’m using Labels and Text Boxes on my Form(s), There is actually 3 Forms, and 3 Database’s.
The Databases are beginning to get bigger and the old bound ComboBox is just not good enough to look for entries no more, There is only 1 column that I would like to search in, it is column “Name” witch has the clients Full Name.
What I was thinking about doing is to add a Text Box and a Button, the user would be able to type in a Name and Click Search…… “from here on I need help”
What is the best way to do this?
How should I display choices?
What and how do search?
Almost forgot, it is Access2002 Database and I’m using oledb
I know this is not much to go on and I really appreciate all the help!
Thanks!

[nswan]

i've done something similar to this at work, but had a number of different fields to search with. What you need to do is build your SQL string in code. This enables you to be able to search for wildcards, eg smi*. Here's how to build the sql string.

public function fSqlString() as string
    dim strSQL as string
    strSQL = "select ID from tblData"

    if inStr(txtSearch.text, "*") = 0 then
        strSQL = strSQL & " where names Like '" & txtSearch.text & "'"
    else
        strSQL = strSQL & " where names = '" & txtSearch.text & "'"
    end if
    return strSQL
end if

(i've not checked the syntax of this but hopefully it'll give you the right idea)
You can then get a dataset of just record ID's. Display each record by using the ID stored in the dataset, going to the database, and getting all the information. You could store all the info in the oringinal dataset if the record wasn't too big.

Hope this make sense. Tried to put what could have been a massive explanation into a few lines.

Nick

[scott_nadeau]

I like your signature I ride a Gixxer1000

nswan's code should work for you. I like the way nswan looks for the wildcard and then alters his SQL based on whether or not it is there. When I do this I just automatically add the wildcard, but I might start doing it nswan's way.

[Rally2000]

Thanks Guys!
I'm afraid that I still don't see the light, I fail to understand how to implement that code.
I’m somehow missing it and I’m sorry that I don’t understand.
Maybe someone is able to post a link with some easy steps to do what I’m trying to do.
Thanks!

[Edneeis]

I find the best way to get help is to post what you have so far and to ask specific questions. What keeps you from properly implementing that code? Do you now know where it goes? When you use it do you get an error? What exactly? Also posting either the relevent code or your whole project would give people a starting point and may find smaller overlooked things that are hindering you. I remember in a previous post of yours you said you learned better from example code so in this cause if you post what you have/your project then maybe someone will help you along with it. I would be willing to take a look andhelp where I can.

[Rally2000]

I appreciate that very much!
And I would post some code, but there is really noting to post. Basically I would like to see an example of a similar setup.
That way I would now where to start.
This is (was) my first database exercise, and after I had it working my wife asked me to use it at work, witch I told her was ok, as long as no obligations go with it. LOL
Anyway, I just used the Wizard for my connection to the table and then coded in some of the basic stuff i.e. Add, Delete, Save, forward ………. And this is pretty much it!
I’ve also been looking for info. at the mndn website, and the articles that I did find only added to the confusion.

PS: No, I do not know where to put the Code or how to properly implement it.

[Edneeis]

Well I, personally am not as prone to help if I have to start from scratch. No offense meant but whatever you have even the database itself saves everyone else time to make an example for you. No one is going to judge you or laugh at your code if that is your concern. If they do then just search for their first couple posts and have a laugh on them. Even the bit you mentioned saves time or if you don't have something then make something to get any helpers started. So throw caution to the wind and post what you have however small.

The code posted builds the QueryText part of the command for getting the data. It would be used with the connection or dataset that you built at designtime. What will happen is whenever you want to search you will connect to the database and run a command (either via a command object or dataadapter/dataset) to get the matching data. That code is what you would have that fills the QueryText property of the command object.

[Rally2000]

like I was saying there is notting to Post (yet) I need a start somewhere.

Public Function GetClipboardText() As String
        Dim objClipboard As IDataObject = Clipboard.GetDataObject()
        With objClipboard
            If .GetDataPresent(DataFormats.Text) Then Return _
               .GetData(DataFormats.Text)
        End With
    End Function
    Private Sub LoadData()
        Dim dsTemp As New DataSet1
        Try
            OleDbDataAdapter1.Fill(dsTemp)
            DataSet11.Clear()
            DataSet11.Merge(dsTemp)
        Catch errobj As Exception
            MsgBox(errobj.Message)
        End Try
    End Sub

    Private Sub Count()
        Dim Records, Current As Integer
        Records = Me.BindingContext(DataSet11, "Bankruptcy").Count
        Current = Me.BindingContext(DataSet11, "Bankruptcy").Position + 1
        lblCount.Text = "Record " & Current.ToString & " of " & Records.ToString
    End Sub
    Private Sub frmBankruptcy_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            Me.OleDbConnection1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source= Clients.mdb"
        Catch odcError As OleDb.OleDbException
            MessageBox.Show(odcError.Message)
        End Try
        LoadData()
        pb1.Visible = False
        btnSave.Visible = False
        Count()
    End Sub
    Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click
        Me.BindingContext(DataSet11, "Bankruptcy").Position = 0
        Count()
    End Sub

    Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click
        Me.BindingContext(DataSet11, "Bankruptcy").Position = Me.BindingContext(DataSet11, "Bankruptcy").Count - 1
        Count()
    End Sub

    Private Sub btnPrevius_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevius.Click
        Me.BindingContext(DataSet11, "Bankruptcy").Position -= 1
        Count()
    End Sub

    Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
        Me.BindingContext(DataSet11, "Bankruptcy").Position += 1
        Count()
    End Sub

    Private Sub mnuExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuExit.Click
        Dim dsTemp As DataSet1
        Me.BindingContext(DataSet11, "Bankruptcy").EndCurrentEdit()
        If DataSet11.HasChanges() Then
            If MsgBox("Data has changes. Do you wish to save?", MsgBoxStyle.YesNo + MsgBoxStyle.Information) = MsgBoxResult.Yes Then
                dsTemp = CType(DataSet11.GetChanges, DataSet1)
                OleDbDataAdapter1.Update(dsTemp)
                DataSet11.AcceptChanges()
            End If
        End If
        Me.Close()

    End Sub


    Private Sub mnuAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuAdd.Click
        Me.BindingContext(DataSet11, "Bankruptcy").EndCurrentEdit()
        Me.BindingContext(DataSet11, "Bankruptcy").AddNew()
        btnSave.Visible = True
        pb1.Visible = False
    End Sub

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        Me.BindingContext(DataSet11, "Bankruptcy").EndCurrentEdit()
        Me.BindingContext(DataSet11, "Bankruptcy").AddNew()
        btnSave.Visible = True
        pb1.Visible = False
    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Dim dsTemp As DataSet1
        Me.BindingContext(DataSet11, "Bankruptcy").EndCurrentEdit()
        If DataSet11.HasChanges Then
            dsTemp = CType(DataSet11.GetChanges, DataSet1)
            OleDbDataAdapter1.Update(dsTemp)
            DataSet11.AcceptChanges()
        End If
        Count()
        pb1.Visible = True
        btnSave.Visible = False
    End Sub

    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        Dim dsTemp As DataSet1
        DataSet11.Tables("Bankruptcy").DefaultView.Delete((Me.BindingContext(DataSet11, "Bankruptcy").Position))
        If DataSet11.HasChanges Then
            dsTemp = CType(DataSet11.GetChanges, DataSet1)
            OleDbDataAdapter1.Update(dsTemp)
            DataSet11.AcceptChanges()
        End If
        Count()
    End Sub
Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        ' this is what I need help with??????????????????????????????
    End Sub

this is all I have!

[Edneeis]

Since you already have the data in a dataset there isn't really a need to make another trip to the database itself. How do you want to show the resutl of the search? Here is an example of performing the actual search and getting results but it will just pop up a msgbox saying the result count.

Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        'this assumes textbox1 is where the name is to search for
        Dim filter As String = "Name='{0}'"
        'the following returns an array of datarows that match the search criteria
        Dim results() As DataRow = DataSet11.Tables("Bankruptcy").Select(String.Format(filter, TextBox1.Text))
        MsgBox(String.Format("There are {0} result mathcing the criteria '{1}'", results.Length, TextBox1.Text))
End Sub

[Rally2000]

Ok!
Now I see what that did!
Question: There are 12 Columns in my dataset, and therefore I am using 12 textboxes on my form, is there any way that if the search finds the Name to go ahead and populate all the textboxes with the info that corresponds to that name??
And also! How hard would it be to implement Wildcards into the search?
Thanks for the Information!
It is really appreciated!

[Edneeis]

Ok first to include the wildcard just change the filter text from = to Like and add the * to the end. Then to have it automatically move to the resulting row you can filter the existing dataset instead of returning an array of Datarows as the result:

Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        'this assumes textbox1 is where the name is to search for
        Dim filter As String = String.Format("Name Like '{0}*'", TextBox1.Text)
        DataSet11.Tables("Bankruptcy").DefaultView.RowFilter = filter
    End Sub

If later you need or want to remove the filter just assign it a blank string. That will return you to the full dataset of data.

DataSet11.Tables("Bankruptcy").DefaultView.RowFilter = String.Empty

[Rally2000]

I see! I guess that is pretty simple if you know what you are doing.
That has helped allot already!
All that remains is the corresponding Data! What are my options for that? Am I going to have to add a data grid or something?
Thanks!!!!!!!!!!!

[Edneeis]

Its just a matter of preference really but a DataGrid is a very good method of showing large amounts of data. If you use a Grid it should play well with your current setup just bind it at designtime like the other things.

[Rally2000]

Well, what would you do??
And I don’t want to impose on you but I wonder if you could show me an example of how to get my search result into my display textboxes or datagrid.
Thank you for all your help!

[Edneeis]

If you have bound your textboxes then they should automatically change. If you zip up your solution/project files and database then I'll take it and make an example out of it for you.

[Rally2000]

That sounds Great!
I'm working on some details but I let you know when I'm done.
Thanks!!

[Edneeis]

I'm going through your example and figured I should shed some light on how picky databinding can be. The code I gave 'should' have worked. Now let me explain why it didn't.

DataBinding works with object instances and for some reason doesn't fully resolve the same object by different routes. What does that mean? It means that for objects with a DataSource and DataMember property (like the DataGrid) setting the DataSource to 'DataSet11' and DataMember to 'Bankruptcy' is NOT the same as setting the DataSource to 'DataSet11.Bankruptcy' and not setting the DataMember, even though they should be. This same principle applies to controls (like the textbox) that do not have both a DataSource and DataMember property its just harder to see. Now more to the point - For some reason when you set the DataBinding properties at designtime with a Strong Typed DataSet the IDE writes the Binding code like this:
Me.txtZip.DataBindings.Add(New System.Windows.Forms.Binding("Text", Me.DataSet11, "Bankruptcy.Zip"))
For it to work better or respond properly to changes via code to the table itself it should be written like this:
Me.txtZip.DataBindings.Add(New System.Windows.Forms.Binding("Text", Me.DataSet11.Bankruptcy, "Zip"))
With the full object being the datasource and only the member itself being the DataMember. Now I don't use databinding enough or just don't currently know how to properly sync the changes to the table and the databinding set at designtime. At least not without either changing the bindings at runtime or rewriting them at designtime.

[Edneeis]

Correction I have found a solution. You must create a dataview at designtime and link it to your dataset/table. Then bind all the controls to the dataview instead of directly to the dataset/table. I will send back your example when i am done.

[Rally2000]

Thanks for explaining that!
I will soon be attempting to create something by (I guess what you call hard coding)
I suppose that is the problem with learning out of books, they just don’t teach all them little things. (at least that is my opinion) I have yet to read a book that can teach everything there is to know!
Again Thanks!

[Edneeis]

I have some taint in my mouth of databinding from previous versions of Visual Basic where it out right sucked but in .NET its actually not all bad, not perfect but a huge step forward. Although a lot of it is learning the different wierd quirks like that. The solution ended up being very simple for this one at least. I'll be sending back your example now.

[Rally2000]

Great! It will take me little while to see what you did.
I really appreciate all the time you have taken to show and explain.

With People like you around it won’t take me long to learn.
Once Again, many thanks!

[Rally2000]

I have another Question for you. And I would like an answer based on you’re experience.
Question: What are the Odds of having duplicate Names in a Database that has about a Thousand Records??
If so, how would I handle this?

[Edneeis]

I'd say the odds are likely. Generally speaking you should assume the worst anyway, your apps will turn out better if you do. As to how to handle it, what do you mean? Do you mean when a duplicate is added? Or in the search? In the search I'd either just choose the first result or show more than one result. In the add I'd throw an exception and have the user change the new name. By the way I just sent a new example to you, I was bored.

PS-In some cases its acceptable to have duplicate names, like in most customer databases. That is why you should never have the name be the unique identifier in your database. Always use an autonumber type ID field, which you did in your example (although not autonumber which is not strictly required).

[Rally2000]

That is excellent stuff, but I’m a beginner so this is going to take some time to digest.

Once again thank you so much for all that help.


PS: I’m looking to get this thing wrapped up in the next day or so. When I’m done I will send you the Whole thing to have a look.

[Edneeis]

Sounds like a plan.

[siomai]

hello Edneeis! I find your discussion suitable to my problem but unfortunately i don't understand how you solved it. In this quote you suggested:

Originally posted by Edneeis
Correction I have found a solution. You must create a dataview at designtime and link it to your dataset/table. Then bind all the controls to the dataview instead of directly to the dataset/table. I will send back your example when i am done.

I really don't know much in vb.net yet. Could you show me how you did this? I know it's been a while since you've solved this case but I do hope you could still remember how you did it. Thanks in advance!

[Codehammer]

Concerning the Database Search i too Would like to Go through that Example you Sent that User.

I Have got as Far as being able to do the Stuff you Mentioned, But the Example would be Exactly What I Need to understand 100%

Thanks

[Edneeis]

Sorry guys I no longer have the example. That was 9 months ago.

I'll try to make a new one or if you have something then please post it and I'll work from it otherwise it will probably take a couple days. I'm pretty swamped at work and don't have a lot of free time so it'll be a little slow coming.

[Edneeis]

I don't know if this is the same example as before but I found it with some old code and I think it is what I sent to Rally.

See if that helps, although it looks like a much simpler example then he had.

[Codehammer]

I don't know if this is the same example as before but I found it with some old code and I think it is what I sent to Rally.

See if that helps, although it looks like a much simpler example then he had.

Thanks, Only Found this Today, I Will Check it Tonight. Sorry For No Response.

If you Free Please Try and Figure out My Tabs Query, Find by Searching for Codehammer