Question String Splitting (Get Keywords for Search)

[Rod]

Hi Guys,

Hope you are all well?

I am writing a search ability in my code that will take a string, capture the keywords and then return all entries from a database that contain those words in a specified column. It works!!! Except for one thing.

(Where xSQL = my SQL statement passed to the database (handled elsewhere) and strComments is the sentance to be split)

[COLOR="Blue"]Private Sub [/COLOR]SearchComments([COLOR="blue"]ByRef[/COLOR] xSQL [COLOR="blue"]As String[/COLOR], [COLOR="blue"]ByVal [/COLOR]strComments [COLOR="blue"]As String[/COLOR])

        Const Space As Char = [COLOR="Red"]" "c[/COLOR]
        Const Comma As Char = [COLOR="Red"]","c[/COLOR]

        [COLOR="Blue"]Dim [/COLOR]Delimeters() [COLOR="blue"]As Char [/COLOR]= {Space, Comma}
        [COLOR="blue"]Dim [/COLOR]ResultArray [COLOR="blue"]As String[/COLOR]() = strComments.Split(Delimeters)

        xSQL &= [COLOR="red"]"AND (vw_ContactSessionsOSS.Comments LIKE '%"[/COLOR]        
        [COLOR="blue"]Dim [/COLOR]SubString [COLOR="blue"]As String[/COLOR]
        
        [COLOR="blue"]For Each [/COLOR]SubString [COLOR="blue"]In [/COLOR]ResultArray
            xSQL &= SubString & [COLOR="red"]"%' "[/COLOR]
            [COLOR="blue"]If Not [/COLOR]SubString = ResultArray(UBound(ResultArray)) [COLOR="blue"]Then[/COLOR]                
                  xSQL &= [COLOR="red"]"OR vw_ContactSessionsOSS.Comments LIKE '%"[/COLOR]
            [COLOR="blue"]Else[/COLOR]                
                  xSQL &= [COLOR="red"]") "[/COLOR]      
            [COLOR="blue"]End If[/COLOR]
        [COLOR="blue"]Next[/COLOR]
[COLOR="blue"]End Sub[/COLOR]

So this works absolutley fine.

Next, how would I capture a carriage return? As the control the user types the keywords in is a multiline textbox, it is possible.


Thanking you in advance

Rod

 

[cjard]

Please dont write SQLs like this (read the PQ link in my signature for reasons why)

You'd be better off writing like this:

Dim terms() as String
Dim delimiters as String = ", " & Environment.NewLine

terms = txtSearchTerms.Text.Split(delimiters.ToCharArray(), StringSplitOptions.RemoveEmptyEntries)

Dim sqlCmd as SqlCommand = New SqlCommand()

sqlCmd.CommandText = "SELECT * FROM table WHERE whatever = @whatever AND (1=0" 'we put a test in that is always false so it wont contribute to the OR
sqlCmd.Parameters.AddWithValue("@pWhatever", someTextBox.Text)

For i as Integer = 0 To terms.Length - 1
  sqlCmd.CommandText &= (" OR [searchColumn] LIKE @pTerm" & i)
  sqlCmd.Parameters.AddWithValue("@pTerm" & i, "%" & term & "%")
Next i
sqlCmd.CommandText &= ")"

You end up with an SQL:

SELECT * FROM table WHERE [whatever] = @pWhatever AND (1=0 OR [searchColumn] = @term1 OR [searchColumn] = @term2 OR [searchColumn] = @term3)

And your SqlCommand has a parameters collection with all the values the user typed.. The % is embedded into the parameter value, and databases understand this
Always always always use parameterized queries when coding. Always. Even if you have to build an SQL like here, never ever ever just concatenate the values the user typed, into the SQL - youre setting up a security nightmare

Note1: please read and follow the .NET naming conventions for variables. It makes it easier for other programmers to read your code. Things That Start With Initial Caps Are xpected To Be Classes, Methods, Or Properties. variables typically have namesInCamelCase
While the final point may sound like a nitpick, I make assumptions about things I see in code, based on the case of the name and your code really throws me.. Avoid naming variables the same name as methods (SubString) etc. Have a read of .NET Naming Conventions and Programming Standards (C#.Net, VB.Net, J#.Net) and Best Practices - irritatedVowel.com Programming

 

[Rod]

Hi cjard,

Thank you for your help, I have been away for a few days hence my delayed reply!

I will take some time to read over what you suggested, I understand your reasoning.
I will post up my modified version when it is better.

Thanks mate