Alphabetic search in Datawindow 1

[docky5]

Hi,

I have a DW which is retriving an SQL table's column. I can scroll down and up in the DW but I want to add the option if I press i.e. letter "p" then it scrolls to the first row which starts with that letter.
I used keydown() command to identify the pressed button. And as the scrolltorow() needs the number of the row to go there I created a temporary table in my SQL script which retrieves the data, and add en extra IDENTITY field and insert all the data from the original table to this temporary ordering by name. SO then I could determine with a select the number of the first row which starts with the specified letter. It works but when I close the window and open again I have an error msg:
"Failed to commit transaction. Attempt to initiate a new SQL server operation with results pending."
I close this error msg and works again but I think this is not normal.

What can be the problem? Is there any other better solution for this alphabetic search?

Thanks the help in advance.

 

[thekl0wn]

Here's how I would go about it...

First off, either sort the datawindow using the SetSort() and Sort() functions, or in the SQL using the ORDER BY clause, to sort based on the column you'll be searching on. This is a key element.

Create a user function to accept a KeyCode, and pass back its string/char value. It will look something like this:

/*uf_keycode_value( akc_key )*/
CHOOSE CASE akc_key
CASE KeyA!
RETURN 'A'
CASE KeyB!
RETURN 'B'
...
CASE ELSE
RETURN ''
END CHOOSE

Then, in the pbm_keydown or pbm_keyup user-mapped event, you'll have some code that looks similar to this:

/*user-event of DataWindow mapped to either the pbm_keydown or pbm_keyup event*/
String ls_find, ls_key
Long ll_row

ls_key = Upper( uf_keycode_value( Key ) )
ls_find = "Upper( Left( search_col_name, " + Len( ls_key ) + " ) = '" + ls_key + "'"
ll_row = Find( ls_find, 1, dw_search.RowCount( ) )

SelectRow( 0, FALSE )
IF ll_row > 0 THEN
SelectRow( ll_row, TRUE )
ScrollToRow( ll_row )
END IF


Let me know if you want explanation on anything, if I've missed the point, etc...