dcsimg
Results 1 to 15 of 15

Thread: Non exact search of a database

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Aug 2012
    Posts
    17

    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?

  2. #2
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,408

    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.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Aug 2012
    Posts
    17

    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.

  4. #4
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,408

    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.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  5. #5
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,388

    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.

  6. #6
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,408

    Re: Non exact search of a database

    edit: yeah, but you corrected my ropey spelling so it was worth it
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Aug 2012
    Posts
    17

    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.

  8. #8
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,388

    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?

  9. #9

    Thread Starter
    Junior Member
    Join Date
    Aug 2012
    Posts
    17

    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 :/

  10. #10
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,408

    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.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  11. #11
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,388

    Re: Non exact search of a database

    Quote Originally Posted by FunkyDexter View Post
    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.

  12. #12
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,408

    Re: Non exact search of a database

    ...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.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  13. #13
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,388

    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

  14. #14
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,388

    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

  15. #15

    Thread Starter
    Junior Member
    Join Date
    Aug 2012
    Posts
    17

    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!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width