Desktop application with sql like Google search engine

Question

I'm using Vb.net 2012 with sql as backhand. This is my sql server database table its name is "Timetable"

__________________________
Name | Course            |    
_____|___________________|
a    | DTP Flash AutoCad |(All these are courses with space between each courses) 
b    | PHP Asp   C#      |
c    | Flash WebDesigning|
_____|___________________|

I am running this sql query

Private Sub TxtName_TextChanged(sender As Object, e As EventArgs) Handles TxtName.TextChanged
    Try
        Dim txt As Integer = Val(Len(TxtName.Text))
        MsgBox(Val(txt))
        Dim str As String = "select FName, SurName,Courses,TotalFees,Balance,TotalDuration,DOA,DOC,Status,Time,ContactNo,ONo from TimeTable where FName='" & TxtName.Text & "' or Courses= SUBSTRING('" & TxtName.Text & "',0,50)"
        Dim ds As New DataSet
        adp = New SqlDataAdapter(str, cn)
        adp.Fill(ds)
        DataGridView1.DataSource = Nothing
        DataGridView1.Refresh()
        DataGridView1.DataSource = ds
        DataGridView1.DataSource = ds.Tables(0)

    Catch ex As Exception
        MsgBox(ex.Message)
    End Try

End Sub

The data is displayed in gridview when searched with name
But when I write the course name with substring it doesn't displays me... I have to write whole course name.... i.e "DTP Flash AutoCad".
I want that if user types DTP it displays the data, even though the row contains DTP Flash AutoCad.

As a side note, please meet [Bobby Tables](http://stackoverflow.com/q/332365/11683). — GSerg, Jan 05 '14 at 09:29

score 2 · Accepted Answer · answered Jan 05 '14 at 09:37

2

Look up the SQL keyword LIKE. (This in no way endorses your design.)

answered Jan 05 '14 at 09:37

simon at rcl

7,326
1
17
24

Spikeh · Answer 2 · 2014-01-05T09:48:06.243

I'm presuming you're using SQL Server? Different database engines (Oracle, DB2 et al) have different ways to do this.

You need to use LIKE. Alternatively, for a more efficient lookup (providing you have FULL TEXT INDEXING available on your version of SQL), you can use CONTAINS.

As a bit of a side note, I would also look into stored procedures or parameterised queries. Running dynamic SQL in the way you are doing so is a security concern.

For completeness, here's the "Dim str" line from your code with a LIKE clause in it for T-SQL:

Dim str As String = "select FName, SurName,Courses,TotalFees,Balance,TotalDuration,DOA,DOC,Status,Time,ContactNo,ONo from TimeTable where FName LIKE'%" & TxtName.Text & "%' or Courses LIKE '%" & TxtName.Text & "%'"

As another side note, I can almost guarantee that Google won't use a LIKE clause. In fact, I bet they don't even use an SQL database - it's much more likely to be some kind of document storage, though I haven't really looked into it in that much detail before (and I don't think the information is available to the public). LIKE is not very efficient when searching large sets of data, and SQL isn't the best way to store enormous data sets either.

thnk u spikeh .. ur query worked...:) i will surely work on ur suggestions... i need to deploy this app in other pc...Please guide me with the procedure.because it will generate path error...!! please help as m new to this world of programming...! — AmIt PagarIa, Jan 05 '14 at 09:55

Desktop application with sql like Google search engine

2 Answers2