Non exact search of a database
Hi,
I'm trying to do a non exact search of a database. ie. searching an address book searching John instead of John Smith.
I've been told its possible to do but you have to make your own commands for it. I was wondering if someone could give me a a bit of a start on it as I really dont know where to begin with this.
If not, point me in the right direction?
Re: Non exact search of a database
The simplest thing to do use a LIKE query. It's limited though. It'll find you John Smith but it won't find you, for example, Jon smith.
If you need that level of fuzziness then you end up employing a whole bunch of techniques. You can use a SOUNDEX but it's not very good on it's own. You can use paired lists of words that you want to consider the same (eg Smith = Smythe) but they tend to get huge. Overall, if you want real fuzziness I'd buy in a third party product.
Re: Non exact search of a database
I've tried getting a LIKE query together for this, however I know I can't just give it a query, there are other bits involved.
This is what I have so far:
Code:
Dim SQLQuery = "Select FirstName from dbo.Contacts WHERE FirstName LIKE '%" & Me.txtFirstName.Text & "%'"
Try
Dim command As New SqlCommand()
command.Connection = conn
command.CommandText = SQLQuery
command.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.Message)
End Try
Sorry I sound a bit silly asking this but I wanted to try and get maximum functionality.
Re: Non exact search of a database
What's the problem with what you've got? It looks OK to me.
The only comment I'd make is that it's not a great idea to use a wildcard (%) at the begninning of a search string. It makes the query non-sagable which is a fancy way of saying that the database won't be able to use indexes on it. It won't be a problem when your database is small but as more contacts get added this query will start to run like a dog.
Re: Non exact search of a database
If the first name always starts in the first position of the database column take off the first '%'. By having it there SQL will not use an index if there is one on that column. It will be non SARGABLE. If you don't have an index consider adding one to speed things up. Otherwise it looks like you have what you want, FunkyDexter's comments about a fuzzy search aside. He is right but if that is not a consideration I think you are good to go.
Edit: Missed FunkyDexter's last comments.
Re: Non exact search of a database
edit: yeah, but you corrected my ropey spelling so it was worth it :)
Re: Non exact search of a database
Its not actually doing anything at all, I've removed the first % sign and nothing.
Its within a button so that when people press search, it should then trigger the query.
Re: Non exact search of a database
Start with the basics...go to a query window for your database, for me on MS SQL it is enterprise manager, run the query:
Select FirstName from dbo.Contacts WHERE FirstName LIKE 'John%' (make sure that is what is in Me.txtFirstName.Text).
Does anything come back?
Re: Non exact search of a database
Yes, I get results appearing. I then tried moving this to my GUI and ran it but again nothing appeared, I think it might be a problem with the GUI code as opposed to the database query :/
Re: Non exact search of a database
You're probably better taking your query to the .Net sub forum then but the first thing I'd do is set a break point in the event behind your button. Make sure the events firing and then step the code through until you actually query the database. At that point use the immediate window to make sure the sql your sending across is what you think it is.
Re: Non exact search of a database
Quote:
Originally Posted by
FunkyDexter
You're probably better taking your query to the .Net sub forum then but the first thing I'd do is set a break point in the event behind your button. Make sure the events firing and then step the code through until you actually query the database. At that point use the immediate window to make sure the sql your sending across is what you think it is.
I agree...I'm not even sure ExecuteNonQuery() is what you should be using. I think that more for stored procedures and database updates. A data reader or other mechanism may be what you want.
Re: Non exact search of a database
Quote:
...I'm not even sure ExecuteNonQuery()
Ah, I didn't spot that but I'm damn sure it's not what you should be using. The clues in the title, it's for executing non-query operations, e.g. updates, inserts and deletes. It doesn't return a dataset. You want ExecuteReader.
Re: Non exact search of a database
Here is something to look at from http://vb.net-informations.com/ado.n...ataadapter.htm
I just tested it and it works (using my table and connection).
Code:
Imports System.Data.SqlClient
Public Class Form1
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
'Just reading
Dim sqlCnn As SqlConnection
Dim sqlCmd As SqlCommand
Dim adapter As New SqlDataAdapter
Dim ds As New DataSet
Dim i As Integer
Dim sql As String
Dim mconnectionString As String = "Server=DEVSQL\TPA1;Database=Manhattan;Trusted_Connection=Yes"
sqlCnn = New SqlConnection(mConnectionString)
sql = "Select firstname from refUserInfo where firstname like 'John%'"
sqlCnn.Open()
sqlCmd = New SqlCommand(sql, sqlCnn)
adapter.SelectCommand = sqlCmd
adapter.Fill(ds)
For i = 0 To ds.Tables(0).Rows.Count - 1
MsgBox(ds.Tables(0).Rows(i).Item(0))
Next
adapter.Dispose()
sqlCmd.Dispose()
sqlCnn.Close()
End Sub
End Class
Re: Non exact search of a database
This is for read only:
Code:
Imports System.Data.SqlClient
Imports System.Data.OleDb
Public Class SQL_IO
Shared WithEvents con As SqlConnection
Private Sub btnSQLDataReaderII_Click(sender As System.Object, e As System.EventArgs) Handles btnSQLDataReaderII.Click
'Just reading
con = New SqlConnection("Server=devsql\tpa1;Initial Catalog=manhattan;Integrated Security=SSPI")
Dim cmd As New SqlCommand()
cmd.CommandText = "SELECT top 10 FirstName FROM refuserinfo where firstname like 'John%'"
cmd.Connection = con
Try
con.Open()
Dim reader As SqlDataReader = cmd.ExecuteReader
While reader.Read()
MsgBox(reader.Item("FirstName"))
End While
reader.Close()
Finally
con.Close()
End Try
End Sub
End Class
Re: Non exact search of a database
Thank you so much for the help! I tried playing around with the position of the % sign and managed to get it to search how I wanted it to. Thank you guys again!