strategy you use for searching records

[adshocker]

hi all,

i'm interested in knowing different strategies for searching records in a table.

for example, in an application you have a customers screen where you can add, edit and delete a record. this screen can only display one record at a time and uses textbox, combobox controls.

when you want to search for a record, what strategy do you use?

i've seen some people create another form and use it for filtering records. then selects the record then pass it to the customers form.

also if you're familiar with oracle forms, it has a pre-built in enter_query and execute_query functions where it uses the same customer form for searching records.

can you guys share other methods or strategies?

thanks.

 

[cjard]

hi all,

i'm interested in knowing different strategies for searching records in a table.

for example, in an application you have a customers screen where you can add, edit and delete a record. this screen can only display one record at a time and uses textbox, combobox controls.

when you want to search for a record, what strategy do you use?

Depends on the criteria. I was given a spec that "any field of any form must be searchable" which is quite a tall order..

When in search mode, the dataset is cleared and a single row added to all tables the form shows. The user types stuff. I then look at the data that has been entered and for all non-null entries, I build a dynamic sql:

Dim oCmd = New OracleCommand("SELECT * FROM vw_cust_orders WHERE 1=1 ")
ForEach columnName As DataColumn In theDataTable.Columns
  If theRow(column).Value <> DBNull.Value Then
    oCmd.CommandText &= string.Format("AND {0} = :p_{1}", column.Name, theRow(column).Value)
    oCmd.Parameters.AddWithValue("p_" & column.Name, theRow(column).Value)
  End If
Next

'maybe repeat for next related table on show if form has relations

'i then use this in a dataadpater to fill an untyped dataset
'i then show the results from the view (designed to perform the same join
'that the form shows related data of) in a popup and have the user doubleclick
'an event fire/delegate is used to make the original screen fill with data for that customer
'the user can choose whether each "pick" action adds to the shown records or repalces them

 

[adshocker]

i've always like the idea where all fields are searchable. but i had a hard time building the sql query that will depend on which fields are not null.

Dim oCmd = New OracleCommand("SELECT * FROM vw_cust_orders WHERE 1=1 ")
ForEach columnName As DataColumn In theDataTable.Columns
  If theRow(column).Value <> DBNull.Value Then
    oCmd.CommandText &= string.Format("AND {0} = :p_{1}", column.Name, theRow(column).Value)
    oCmd.Parameters.AddWithValue("p_" & column.Name, theRow(column).Value)
  End If
Next

this is a very good tip... i may need to play around with it for a bit for me to fully understand it.

 

[cjard]

Here's my full search form code. It's .NET 2 so some bits may differ. Its also not complete and probably not how i'd do it now (I'd use events rather than delegates for simplicity) but its mostly there.

 

[adshocker]

many thanks. much appreciated.