String manipulation in sql query 1

[llmclaughlin]

I am creating a query string off search box text. The problem is for some reason I cannot get the single quote around the strings, about to drive me nuts. See below.

' search based on txtSearch Text Box
If txtName.Text <> "" Then
Dim strA As String = txtName.Text
Dim strB As String
Dim strC As String
Dim strD As String

'Replace all commas with a space
Textbox has airplane,wing
strB = strA.Replace(",", " ")
actual result -> strB = airplane wing

'Now Replace all spaces with the wildcard character % -
'otherwise you can't find phrases since it doesn't like spaces
'replace every space with: %" and "%
strC = strB.Replace(" ", "%"" And Key_Word like ""%")

acutal result -> strC = "airplane%" And Key_Word like "%wing"

strD = strB.Replace(" ", "%"" AND DESCRIBE LIKE ""%")

actual result -> strD = "airplane%" AND DESCRIBE LIKE "%wing"

strSQL = strSQL & " AND (Key_word like " & "'" & "%" & strC & "%'" & " OR Describe like " & "'" & "%" & strD & "%'" & ")"
actual result -> strSQL = (CAT_ID = 10) AND (Key_word like '%airplane%" And Key_Word like "%wing%' OR Describe like '%airplane%" AND DESCRIBE LIKE "%wing%')

I cannot find a way to get rid of the double quotes

Any suggestions

Louie

 

[Borvik]

Your double quotes are coming from your replace lines:

strC = strB.Replace(" ", "%"" And Key_Word like ""%")
strD = strB.Replace(" ", "%"" AND DESCRIBE LIKE ""%")

Try this instead

strC = strB.Replace(" ", "%' And Key_Word like '%")
strD = strB.Replace(" ", "%' AND DESCRIBE LIKE '%")

 

[llmclaughlin]

Thank you, knew I was overlooking something.

Louie