Vb 2008 "LIKE" not worling as I want it
I'm trying to learn VB 2008 after many years with VB6. I'm using Vb 2008 and not a more recent version as that's what I have. I'm using an Access db
I'm writing an name & address i/p routine. To be able to quickly select an existing entry, or to avoid duplication I use the LIKE query to select matching names as keystrokes are entered.
In VB6 this is what I've successfully used for years:-
mySQL = "SELECT ref,last_name,first_name,post_code,home_phone,addr1,addr2,addr3 FROM addr" & _
" WHERE last_name LIKE """ & saleaddr(0) & "*"""
In Vb 2008 I call this routine on keyup:-
Private Sub showAddr()
Dim mySQL As String
Dim i As Integer
ListBox1.Items.Clear()
mySQL = "SELECT aLastName FROM address WHERE aLastName LIKE '" & aLastName.Text & "%'"
da = New OleDb.OleDbDataAdapter(mySQL, con1)
da.Fill(ds, "Address")
For i = 0 To ds.Tables("Address").Rows.Count - 1
ListBox1.Items.Add(ds.Tables("Address").Rows(i).Item("aLastName"))
ds.Tables("Address").Rows(i).Item("aLastName") = ""
Next i
ds.Clear()
da.Fill(ds, "Address")
End Sub
This almost works but it finds duplicates of each applicable entry.
Could anyone help please?
Re: Vb 2008 "LIKE" not worling as I want it
To remove duplicates you must return distinct records, and one way for that is to use the GROUP BY clause.
Code:
"SELECT aLastName FROM address WHERE aLastName LIKE '" & aLastName.Text & "%' GROUP BY aLastName"