ListView & SQL doesn't work. Please Help 1

[gerry03]

Hi
I want to fill LVe1 (listview) with all the records having customer name as in text box and i have used the following lines, but there is no output instead as soon as i start typing name, existing record disappear which i load at form loading.
I am new to Vb please explain the cause also

Please help to sort out.
Thanks in advance


Private Sub Text1_Change()
Set rsSrch = New ADODB.Recordset

rsSrch.Open "select * from bill_table where customer_name like '*" & frmEdit.Text1.Text & "*'", ConnEdit, adOpenStatic, adLockOptimistic

Lve1.ListItems.Clear

Do Until rsSrch.EOF

Set x = Lve1.ListItems.Add(, , rsSrch.Fields!bill_no)
x.SubItems(1) = Format(rsSrch.Fields!bill_date, "dd-mm-yyyy&quot
x.SubItems(2) = rsSrch.Fields!customer_name
x.SubItems(3) = rsSrch.Fields!address

rsrsSrch.MoveNext
Loop
End Sub

 

[johnwm]

Here is a couple of thoughts to start with.

1. If you want the 'lookup' to occur after you've typed a whole name in, then use the Text1_Validate event, (which fires just before the control loses focus) rather than the Text1_Change event (which fires every time that the text changes, which is every keystroke)

2. For SQL type search you also need to change your wildcard search character from * to %, so it looks like:
[tt]
rsSrch.Open "select * from bill_table where customer_name like '%" & frmEdit.Text1.Text & "%'", ConnEdit, adOpenStatic, adLockOptimistic[/tt]

3. The search string you're using will get all records that include your Text1.text anywhere within the customer_name field, so Text1.Text = "bert" will find "bert jones", and will also find "jim robertson"

4. You have used <Set rsSrch = New ADODB.Recordset> without closing the recordset. It's usual to close recordsets explicitly when they are done. Between <Loop> and <End Sub> insert <Set rsSrch = Nothing>

5. The bang notation is only retained for backward compatibility. In ADO the preferred method of addressing fields is rsSrch.Fields(Index), where Index is zero-based on the returned recordset. You can also use rsSrch.Fields(&quot;customer_name&quot, but it's not as fast

6. Your Select statement is pulling all fields from the database. If there is only 3 or 4, then that's fine but if the table has 20 fields it may be better to specify the fields you need returned. As in
[tt]&quot;select bill_date, customer_name, address from bill_table where customer_name.....[/tt]

Hope that gets you started. Come back if you need more!


________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[gerry03]

thanks johnwm

% done the wonder !!!