locked
Search Engine Problems RRS feed

  • Question

  • Hello...

    (sorry for my bad english and i hope you understand me)

    I build with instructions of a youtube video an Search engine with Visual Studio 2010 and SQL.

    In SQL i build a VIEW that shows me only the 3 columns.

    My problem is when i tried to search a letter it shows me nothing. But when try a number it searchs correct.

    Imports System.Data.SqlClient
    Public Class Form1

        Dim connection As New SqlConnection("Server = xxxxxx; Database =xxxxx; User ID = xxxx; Password = xxxxxx")


        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load, TextBox1.TextChanged

            FilterData("")

        End Sub

        Public Sub FilterData(valueToSearch As String)
            'SELECT * FROM [dbo].[Telefonliste_Lang] WHERE CONCAT(Vorname,Nachname, Durchwahl) like '%%'
            Dim searchQuery As String = "Select * from [dbo].[Telefonliste_Lang] where (Vorname like '% " & valueToSearch & "%' Or Nachname like '% " & valueToSearch & "%' or Durchwahl like '% " & valueToSearch & "%')"

            Dim command As New SqlCommand(searchQuery, connection)
            Dim adapter As New SqlDataAdapter(command)
            Dim table As New DataTable()

            adapter.Fill(table)

            DataGridView1.DataSource = table



        End Sub

        Private Sub btn_suche_Click(sender As Object, e As EventArgs) Handles btn_suche.Click

            FilterData(TextBox1.Text)

        End Sub
    End Class

    Please Help

    Monday, April 23, 2018 9:25 AM

All replies

  • Hello,

    First off you should be use parameters for your query. 

    Now the first step is to write your query by connecting to your database via Visual Studio's Server Explorer.

    Example, I want to get all Contact titles that start with sales. I create a parameter (this parameter in code is a parameter for your command object)

    DECLARE @ContactLikeCondition AS NVARCHAR(MAX) = 'sales%'
    SELECT 
    	Cust.CompanyName 
    FROM 
    	dbo.Customers AS Cust 
    WHERE 
    	Cust.ContactTitle LIKE  @ContactLikeCondition

    If the query works then use it. Even thou you have several conditions you can still apply the above e.g.

    DECLARE @ContactLikeCondition AS NVARCHAR(MAX) = 'sales%'
    DECLARE @Country AS NVARCHAR(50) = 'Germany'
    
    SELECT 
    	Cust.CompanyName 
    FROM 
    	dbo.Customers AS Cust 
    WHERE 
    	Cust.ContactTitle LIKE  @ContactLikeCondition AND Country = @Country

    Two take-a-ways

    • Always write test queries and validate they work prior to using them in code.
    • Always use command parameters.

    When queries are complex it's best to write unit test too. 

    See also Microsoft TechNet Writing SQL for your application.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, April 23, 2018 10:16 AM
  • Hi Karen,

    the connection to the Server is still there.

    I think i need the right string 2 Search in Database with my Searchbox.

        Dim searchQuery As String = "Select * from [dbo].[Telefonliste_Lang] where (Vorname like '% " & valueToSearch & "%' Or Nachname like '% " & valueToSearch & "%' or Durchwahl like '% " & valueToSearch & "%')"

    cheers Drago

    Monday, April 23, 2018 12:01 PM
  • Hi Karen,

    the connection to the Server is still there.

    I think i need the right string 2 Search in Database with my Searchbox.

        Dim searchQuery As String = "Select * from [dbo].[Telefonliste_Lang] where (Vorname like '% " & valueToSearch & "%' Or Nachname like '% " & valueToSearch & "%' or Durchwahl like '% " & valueToSearch & "%')"

    cheers Drago

    I don't see you using parameters as suggested e.g.

    Public Function FilterData(pContactTitle As String) As DataTable
        Dim selectStatement As String =
                <SQL>
                SELECT 
    	            Cust.CompanyName 
                FROM 
    	            dbo.Customers AS Cust 
                WHERE 
    	            Cust.ContactTitle LIKE  @ContactLikeCondition
                </SQL>.Value
    
        Dim dt As New DataTable
        Using cn As New SqlConnection With {.ConnectionString = _connectionString}
            Using cmd As New SqlCommand With {.Connection = cn, .CommandText = selectStatement}
                cmd.Parameters.AddWithValue("@ContactLikeCondition", pContactTitle)
                cn.Open()
                dt.Load(cmd.ExecuteReader())
            End Using
        End Using
    
        Return dt
    
    End Function
    In your case you can use the parameter as many times as needed.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Monday, April 23, 2018 12:07 PM
  • Hi Karen,

    the connection to the Server is still there.

    I think i need the right string 2 Search in Database with my Searchbox.

        Dim searchQuery As String = "Select * from [dbo].[Telefonliste_Lang] where (Vorname like '% " & valueToSearch & "%' Or Nachname like '% " & valueToSearch & "%' or Durchwahl like '% " & valueToSearch & "%')"

    cheers Drago


    Hi lvandrago1988,

    As Karen's suggestion, you can use parameter in your sql query like this:

    Public Sub FilterData(valueToSearch As String)
            Dim searchQuery As String = "Select * from [dbo].[Telefonliste_Lang] where Vorname like '%@Vorname%' Or Nachname like '%@Nachname%' or Durchwahl like '%@Durchwahl%'"
            Dim command As New SqlCommand(searchQuery, connection)
            command.Parameters.AddWithValue("@Vorname", valueToSearch)
            command.Parameters.AddWithValue("@Nachname", valueToSearch)
            command.Parameters.AddWithValue("@Durchwahl", valueToSearch)
    
            Dim adapter As New SqlDataAdapter(command)
            Dim table As New DataTable()
            adapter.Fill(table)
            DataGridView1.DataSource = table
        End Sub
    

    Then you said that you need the right string 2 search in database with your searchbox, I don't understand about this. I find your sql query is right, can you post some table data in here, I will test it at my side.

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, April 24, 2018 1:57 AM