Answered Search by keyword

[c3rb3ru5]

i have problem about search

'module call by keyword
cmdhikari = New SqlCommand("Select AIRes from T_GKnowledge where Keyword1 LIKE '%" & TChat.Text & "%'", conayumi)

when i search "test" is appear but when i search "test beta" not appear

i just wanna ask how to make search system by keyword like "Google have"

Example Test Beta

then output is "Test" and "Beta" but appear all like Test Beta Program...etc.

Thank for advance(Newbie)

 

[jmcilhinney]

Are you saying that you want to be able to find "test" or "beta" anywhere in the text without it necessarily being together?

 

[c3rb3ru5]

yes sir that it. if PHP is easy i think there availble in VB,net. cuz i wanna make some protoype like "Auto Search Control Engine"

 

[IanRyder]

Hi,

What you will need to do then is to Split your TChat.Text property by the space character to get an Array of words and then use a For loop to construct your SQL Query with the OR conditional operator and Wildcards for each word in the Array.

Hope that helps.

Cheers,

Ian
BTW, you should be using Parameters when building SQL queries. Have a look here to see how and why:-
John McIlhinney's .NET Developer Blog: Using Parameters in ADO.NET

 

[c3rb3ru5]

any example sir?

i have this table record

N ResultSearch Keyword0 Keyword 1

1. Test game beta system Test Beta

when i put this "Test Beta lol" not appear i mean every single the keyword like "Test" and "Beta" must be appear like google have. hmmmm even i already Add "Or"

stilll not appear. im using Try..Catch..End Try sir. so this need "For" to make it search like that...?

sorry im noob in vb.net but i try all just not appear same using Like with "%","_", and "*" still not appear

 

[IanRyder]

Hi,

Here is a quick example of how to build an SQL Query based on an array of words from a TextBox. The query is built using Wildcards and the OR conditional operator where necessary and then each Parameter is added to the SQLCommand Object ready for the execution of the Query.

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
  Dim myKeyWords() As String = TextBox1.Text.Split(" "c)
  Dim mySQLQuery As String = "SELECT AIRes FROM T_GKnowledge WHERE "
  Dim sqlConn As New SqlConnection("Your Connection")
  Dim sqlCmnd As New SqlCommand
 
  For KeyWordCount As Integer = 0 To myKeyWords.Length - 1
    Dim strKeyParameter As String = String.Format("@Param{0}", KeyWordCount)
    Dim strWhereClause As String
 
    If KeyWordCount = 0 Then
      strWhereClause = String.Format("Keyword1 LIKE {0}", strKeyParameter)
    Else
      strWhereClause = String.Format(" OR Keyword1 LIKE {0}", strKeyParameter)
    End If
    mySQLQuery &= strWhereClause
    sqlCmnd.Parameters.AddWithValue(strKeyParameter, String.Format("{0}{1}{0}", "%", myKeyWords(KeyWordCount)))
  Next
 
  With sqlCmnd
    .CommandText = mySQLQuery
    .Connection = sqlConn
  End With
 
  'To test what has been created:-
  MsgBox(mySQLQuery)
  For Each myParam As SqlParameter In sqlCmnd.Parameters
    MsgBox(String.Format("Parameter Name: {0}, Parameter Value: {1}", myParam.ParameterName, myParam.Value))
  Next
End Sub

Hope that helps.

Cheers,

Ian

 

[c3rb3ru5]

yes work about read parameter

and i put this in end

[COLOR=#333333][FONT=Consolas] 
 For Each myParam As SqlParameter In sqlCmnd.Parameters[/FONT][/COLOR]
[COLOR=#333333][FONT=Consolas]    MsgBox(String.Format("Parameter Name: {0}, Parameter Value: {1}", myParam.ParameterName, myParam.Value))
    dthikari = New DataTable
    dahikari.Fill(dthikari)
    answers.DataBindings.Add("text", dthikari, "AIRes", True)

[/FONT][/COLOR]
[COLOR=#333333][FONT=Consolas]  Next[/FONT][/COLOR]
[COLOR=#333333][FONT=Consolas]End Sub
[/FONT][/COLOR]

when i add binding is Run Great thank you. but have error after appear record of binding. i call this On module name ConnHikari

Imports System.Data.SqlClient
Module ConnHikari
    'This Module Data Table
    Public dahikari As SqlDataAdapter
    Public dthikari As DataTable
End Module