Looking for Easy way to search in a DataGrid or Grid?

I am looking to implement a search and goto in a DataGrid. If any of you have used Lotus Notes 5, when you select a row heading and start typing it throws up a search box that automatically searches that column and goes to the row that matches.
I am considering implementing something similar in my VB app.

flow:
user clicks on a column header in the grid.
user starts typing
search dialog comes up and the char the user typed is entered along with all other char they type.
upon hiting return the column that was highilghted in the grid is searched and the 1st match is gone to.
If no match found then nothing happens, statusbar displays message.

I am looking for an easy way to search the specific column in the grid for a value??
I am currently using the DataGrid (MSDATGRD.OCX)
LVL 2
diarmaidAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TimCotteeHead of Software ServicesCommented:
Hi diarmaid,

Something like this simplified example:

In main form with datagrid:

Public intCurCol

Private Sub DataGrid1_HeadClick(ByVal ColIndex As Integer)
    intCurCol = ColIndex
End Sub

Private Sub DataGrid1_KeyPress(KeyAscii As Integer)
    Dialog.Show vbModal,Me
    Dialog.Text1.SetFocus
    Dialog.Text1.Text = Chr(KeyAscii)
    Dialog.Text1.SelStart = 2
    KeyCode = 0
End Sub

This just sets the current column and opens a dialog form with the first keypress showing.

For the dialog form:


Option Explicit

Private Sub OKButton_Click()
    With Form1.Adodc1.Recordset
        .Find .Fields(Form1.intCurCol).Name & " Like '" & Text1.Text & "%'"
        If .EOF Then
            MsgBox "Not Found"
        End If
    End With
End Sub

Which just finds the first partial match of the entered text.

Tim Cottee MCSD, MCDBA, CPIM
Brainbench MVP for Visual Basic
http://www.brainbench.com
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
diarmaidAuthor Commented:
Thanks Tim. Not really what I was looking for though. This would search adodc whereas I want to search the contents of the grid itself.
I already have textboxes for filtering the grid via the adodc so the user ends up with x amount of records.

What I then want to do is allow a search of the contents/data in the grid without having to use the adodc at all, kinda just a static search of the data. I havent come across a way to do this with the datagrid or the grid and am unsure if it can easily be done.

Filtering via the adodc isnt really an option as this will probably be removed and replaced by code at a later stage. The grid is only being used to display data, and select single record at a time for deletion. there will be absolutely no edit capabilities in the grid, so I dont need it to be bound.

0
diarmaidAuthor Commented:
Pity there isnt a row = datagrid.find("text", column) function or method
or row = datagrid.match("text", column)
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

TimCotteeHead of Software ServicesCommented:
If you want to run this unbound then perhaps a flexgrid is a better solution than the datagrid. The problem with the bound grid is that you have to search the underlying recordset because the grid itself doesn't contain all the values all the time, they are loaded into the grid when they need to be displayed. With the flexgrid, you have a .TextMatrix(Row,Col) property which you can easily iterate through to find a match:

For intRow = .FixedRows To .Rows - 1
  If .TextMatrix(intRow,CurrentColumn) Like txtToMatch & "*" Then
     'Display it and finish
  End If
Next

Or something along these lines.
0
diarmaidAuthor Commented:
Im really at investigation - prototype stage.
Sounds like a possible option. Data in the grid will be comming from a SQL 6.5 DB.

Flexgrid has to use the Ordinary Data control. I was pushed toward the adodc and DataGrid because of OLEDB supposed to be faster.
 My connection to the db is through ODBC DSN.
I will investigate + get back.

Insight. Basically my app is for business end users to maintain their own lookup/info tables, that all their other tools rely on. The idea is that when new records need to be added or existing records need to be changed in the tables they will be able to do it themselves. This will take away the need for the DBA or Systems staff to do DB updates.
Since they will be working on production tables that all their other tools/utilities rely on, they are only getting very limited edit capabilities ie. grid only being used for display. If they wish to edit a record they must select it and edit it in textboxes.

If you want to give some insight etc. please do, I can throw more points at this as we are now getting a bit off the original Q. tks.
0
TimCotteeHead of Software ServicesCommented:
If you use the MSHFlexgrid it will accept an ADO Data control or ADO recordset through code. It has a few extra features but is otherwise compatible with the flexgrid. If you are not interested in editing then this is a perfectly good solution. I can demonstrate with a bit of code shortly:
0
diarmaidAuthor Commented:
Oh, the reasoning behind applying the type of search specified is that they extensively use notes DBs as well to lookup documents, rates, etc. etc. so they are well used to clicking a column and just typing away to search, thus there would be no learning curve.
0
diarmaidAuthor Commented:
Excellent, I was trying the MSFlexgrid.ocx.
I thought I tried the FlexGrid before, one of the columns displays rates but sometimes they are not correctly displayed. eg. 0.0865 is being displayed as 9999994E-2 whereas 0.1215 displays correctly.
0
TimCotteeHead of Software ServicesCommented:
For the search dialog form:

Private Sub OKButton_Click()
    Dim i As Integer
    With Form1.MSHFlexGrid1
        For i = .FixedRows To .Rows - 1
            If .TextMatrix(i, .Col) Like Text1.Text & "*" Then
                .Row = i
                .RowSel = i
                .Col = 0
                .ColSel = .Cols - 1
                Exit For
            End If
        Next
    End With
    Unload Me
End Sub

For the main form:

Private Sub Form_Load()
    Adodc1.Refresh
    Set MSHFlexGrid1.DataSource = Adodc1.Recordset
End Sub

Private Sub MSHFlexGrid1_KeyPress(KeyAscii As Integer)
    Load Dialog
    Dialog.Text1.Text = Chr(KeyAscii)
    Dialog.Text1.SelStart = 2
    Dialog.Show vbModal, Me
End Sub

This mirrors the earlier example but using the MSHFlexgrid and highlights the matching row
0
diarmaidAuthor Commented:
I dont think im going to use the MSHFlexGrid, I still apears to have trouble with small decimals as specified above. From what Ive tried so far it seems a bit ackward/combersome to use.

I will have to experiment a little more but I will probably take this functionality out as i havent made specific design commitments yet.
0
Valliappan ANSenior Tech ConsultantCommented:
You might have a look at:
http://www.componentone.com/products.aspx?ProductCode=1&ProductID=18&cmd=1

http://www.infragistics.com/products/activex/ultragrid.asp

These 3rd party tools come with a lot of features including search/sort, if you are serious about it.

Hope it helps.
0
FicusCommented:
I would use the HFG.  Unless your grid is holding a huge ammount of data the you can perform many formatting functions on bound or unbound grid with little noticible pause.  You can also use the .TextMatrix loop shown to you by TimCottee (which is the best wat to search the grid for a srtring).

If for example you don't like the format of your numbers (this is effected by the field type of you data source) you can perform a loop at the time loading the grids contents to format certain cols or rows.

   With Form1.MSHFlexGrid1
        For i = .FixedRows To .Rows - 1
             .TextMatrix(i, .Col) = format$(.TextMatrix(i, .Col),"###,###.##")
             'you can even add evaluations to format cells a certain color etc...
            End If
        Next
    End With

Turn off the .Redraw Proprerty of the grid to speed things up!

This is a feasible method for reasonable amounts of data.

You should give the points to TimCottee.

Regards,
Tom
0
diarmaidAuthor Commented:
I am using the datagrid along with textboxes and comboboxes to apply a filter using the adodc. I am not currently going to implement the notes type search on the grid. I may at a later stage by writing a class/function that I can hand the datagrid into that will do it for me.

I have implemented a sort function using the adodc when the user clicks on a column header in the grid. Currently this only sorts assending and there is no icon in the grid column header, im not sure if this will make it further than the prototype either.

I have a licensed TDBGrid 7 control and was going to use it, however I made a consious decision not to use it as the tool im developing is lightweight and the grid is only for display purposes.

However if the client wants enhanced functionality after the prototype I will be using it, but time constraints would need to be considered as well.

Again, Thanks for all your time.
0
FicusCommented:
I usually sort on the grid, not the RS like you are doing.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.