Create SQL FullText statement via VB.Net

Question

Create SQL FullText statement via VB.Net

AhTu_SQL2k+

Ten Centuries

Points: 1012
More actions
January 24, 2017 at 2:23 am

#320934

I am trying to create a dynamic SQL statement that uses CONTAINS but I am stuck with double quotes ('"') issue.
Here is the sample TSQL statement that I want to achieve,
With cte_maxRows as (Select count(*) As totalRows from s_armaster), cte_idsOnPage as ( Select dbcode From s_armaster Where contains(dbcode ,'"*3*"') Order By dbcode Offset 25 Rows fetch first 25 rows only ) Select p.dbcode, t.name, t.area, t.tel1, t.email, t.prefix, t.status, t.add1, t.add2, t.add3, t.term_code, t.rowid, m.totalrows From cte_idsOnPage p inner Join s_armaster t On p.dbcode = t.dbcode, cte_maxRows m order by t.dbcode
My Code Behind in VB.net
...
Dim fltrStr As String = "Contains(dbcode, '" & ControlChars.Quote & "*3*" & ControlChars.Quote & "')"
Dim sqlStr As String = GetSQLStr(e.Start, e.Limit, sortStr, fltrStr)
sqlstr returns extra ( " ) quotes in the CONTAINS search string.
With cte_maxRows as (Select count(*) As totalRows from s_armaster), cte_idsOnPage as ( Select dbcode From s_armaster
Where contains(dbcode ,'""*3*""')
Private Function GetSQLStr(ByVal start As Int32, limit As Int32, sortStr As String, ByVal fltrStr As String) As String
   Dim sqlStr As String =
     "With cte_maxRows as (Select count(*) As totalRows from s_armaster), " &
      "cte_idsOnPage as ( " &
       "Select dbcode " &
       " From s_armaster " &
       " Where " & fltrStr &
       " Order By " & sortStr &
       " Offset " & start & "Rows fetch first " & limit & " rows only " &
       " ) " &
     " Select p.dbcode, t.name, t.area, t.tel1, t.email, t.prefix, t.status, t.add1, t.add2, t.add3, t.term_code, t.rowid, m.totalrows " &
     " From cte_idsOnPage p " &
     " inner Join s_armaster t On p.dbcode = t.dbcode, cte_maxRows m " &
     " order by t." & sortStr
   Return sqlStr
End Function
What can I do to resolve it?
Thanks in advance
Together, we can make wonders!

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Is it as simple as just removing the ControlChars from your VB code?
Dim fltrStr As String = "Contains(dbcode, '" & "*3*" & "')"

John

AhTu_SQL2k+ Ten Centuries Points: 1012 More actions · Answer 2

John Mitchell-245523 - Tuesday, January 24, 2017 2:37 AM
Is it as simple as just removing the ControlChars from your VB code?
Dim fltrStr As String = "Contains(dbcode, '" & "*3*" & "')"
John

Thanks, John Mitchell for fast response. I did try as your suggestion but it turns out
Contains(dbcode, '*3*') and no result return from the query.
I want it the filter to be Contains(dbcode, '"*3*"')

Together, we can make wonders!

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

OK, so you either get double quotes or no quotes! I don't know enough about VB to guess why that is. As a kludge, you could do a replace on the final value of sqlStr so that you substitute double quotes for single. I know you might have the same difficulty in working out how to escape your quotes, but it's worth a try.

John

Umer Akram SSC-Insane Points: 22209 More actions · Answer 4

AhTu_SQL2k+ - Tuesday, January 24, 2017 2:44 AM
John Mitchell-245523 - Tuesday, January 24, 2017 2:37 AM
Is it as simple as just removing the ControlChars from your VB code?
Dim fltrStr As String = "Contains(dbcode, '" & "*3*" & "')"
John
Thanks, John Mitchell for fast response. I did try as your suggestion but it turns out
Contains(dbcode, '*3*') and no result return from the query.
I want it the filter to be Contains(dbcode, '"*3*"')

check the following Enclose values in double quotes in VB.net

David Burrows SSC Guru Points: 65144 More actions · Answer 5

I tested your code and it returned your expected results.

Contains(dbcode, '"*3*"') is the valid FTS format, however you cannot use * as a prefix as it is used as a suffix,
so Contains(dbcode, '"3*"') is the only correct format but probably will not return what you want

Please supply sample variations for dbcode and which ones you want to select

Far away is close at hand in the images of elsewhere.
Anon.

AhTu_SQL2k+ Ten Centuries Points: 1012 More actions · Answer 6

David Burrows - Tuesday, January 24, 2017 6:49 AM
I tested your code and it returned your expected results.
Contains(dbcode, '"*3*"') is the valid FTS format, however you cannot use * as a prefix as it is used as a suffix,
so Contains(dbcode, '"3*"') is the only correct format but probably will not return what you want
Please supply sample variations for dbcode and which ones you want to select

Thanks, David.
List of sample dbcode are,
3000C0201
3000C021
3000C022
3000D001
3000D002
3000D003
3000D004
3000D006
3000D007
3000D008
3000E002
Contains(dbcode, '"*3*"') and Contains(dbcode, '"3*"') will return same result in this scenario. To remove extra quotes from the string ( example, '""*3*""' ) which is dynamic built from code behind is my headache.

Together, we can make wonders!

David Burrows SSC Guru Points: 65144 More actions · Answer 7

With your data
Contains(dbcode, '"3*"') will return all the rows (providing each code you posted is in a separate row)
Contains(dbcode, '"*3*"') will not return any rows as the * prefix will take as a literal character and used as such in the works search (ie * would have to be at the beginning of the word)
Contains(dbcode, '"*3*"') and Contains(dbcode, '"3*"') both conditions together will not return any rows due to previous reason

Does dbcode contain only one code?
If so, why are you using FTS to search for *3* when LIKE '3%' would give you the results you are looking for.

Far away is close at hand in the images of elsewhere.
Anon.

AhTu_SQL2k+ Ten Centuries Points: 1012 More actions · Answer 8

David Burrows - Tuesday, January 24, 2017 9:17 AM
With your data
Contains(dbcode, '"3*"') will return all the rows (providing each code you posted is in a separate row)
Contains(dbcode, '"*3*"') will not return any rows as the * prefix will take as a literal character and used as such in the works search (ie * would have to be at the beginning of the word)
Contains(dbcode, '"*3*"') and Contains(dbcode, '"3*"') both conditions together will not return any rows due to previous reason
Does dbcode contain only one code?
If so, why are you using FTS to search for *3* when LIKE '3%' would give you the results you are looking for.

Thanks again, David.
I notice the performance of LIKE is slower than FTS Contains especial there are more filters in the same command. I am testing FTS on a dummy table which contains 500,000 rows. Anyway, I guess I have to go back to use LIKE instead of 'contains' since dynamic creating of sql command text using 'contains' from code-behind is not possible.

Together, we can make wonders!

Phil Parkin SSC Guru Points: 247037 More actions · Answer 9

AhTu_SQL2k+ - Tuesday, January 24, 2017 9:40 AM
David Burrows - Tuesday, January 24, 2017 9:17 AM
With your data
Contains(dbcode, '"3*"') will return all the rows (providing each code you posted is in a separate row)
Contains(dbcode, '"*3*"') will not return any rows as the * prefix will take as a literal character and used as such in the works search (ie * would have to be at the beginning of the word)
Contains(dbcode, '"*3*"') and Contains(dbcode, '"3*"') both conditions together will not return any rows due to previous reason
Does dbcode contain only one code?
If so, why are you using FTS to search for *3* when LIKE '3%' would give you the results you are looking for.
Thanks again, David.
I notice the performance of LIKE is slower than FTS Contains especial there are more filters in the same command. I am testing FTS on a dummy table which contains 500,000 rows. Anyway, I guess I have to go back to use LIKE instead of 'contains' since dynamic creating of sql command text using 'contains' from code-behind is not possible.

I don't do VB.NET any more, but in C#, there does not seem to be an issue:
string quote = "\""; MessageBox.Show(string.Concat("Contains(dbcode, '", quote, "*3*", quote, "')"));

AhTu_SQL2k+ Ten Centuries Points: 1012 More actions · Answer 10

Phil Parkin - Tuesday, January 24, 2017 1:29 PM
AhTu_SQL2k+ - Tuesday, January 24, 2017 9:40 AM
David Burrows - Tuesday, January 24, 2017 9:17 AM
With your data
Contains(dbcode, '"3*"') will return all the rows (providing each code you posted is in a separate row)
Contains(dbcode, '"*3*"') will not return any rows as the * prefix will take as a literal character and used as such in the works search (ie * would have to be at the beginning of the word)
Contains(dbcode, '"*3*"') and Contains(dbcode, '"3*"') both conditions together will not return any rows due to previous reason
Does dbcode contain only one code?
If so, why are you using FTS to search for *3* when LIKE '3%' would give you the results you are looking for.
Thanks again, David.
I notice the performance of LIKE is slower than FTS Contains especial there are more filters in the same command. I am testing FTS on a dummy table which contains 500,000 rows. Anyway, I guess I have to go back to use LIKE instead of 'contains' since dynamic creating of sql command text using 'contains' from code-behind is not possible.
I don't do VB.NET any more, but in C#, there does not seem to be an issue:
string quote = "\""; MessageBox.Show(string.Concat("Contains(dbcode, '", quote, "*3*", quote, "')"));

Thank you for your advice, SSCrazy Eights.

I converted your code into VB.Net,
Dim quote As String = """"
MessageBox.Show(String.Concat("Contains(dbcode, '", quote, "*3*", quote, "')"))
It produces '""*3*""'. Extra quotes in the string also.

Together, we can make wonders!

Phil Parkin SSC Guru Points: 247037 More actions · Answer 11

AhTu_SQL2k+ - Wednesday, January 25, 2017 1:54 AM
Phil Parkin - Tuesday, January 24, 2017 1:29 PM
AhTu_SQL2k+ - Tuesday, January 24, 2017 9:40 AM
David Burrows - Tuesday, January 24, 2017 9:17 AM
With your data
Contains(dbcode, '"3*"') will return all the rows (providing each code you posted is in a separate row)
Contains(dbcode, '"*3*"') will not return any rows as the * prefix will take as a literal character and used as such in the works search (ie * would have to be at the beginning of the word)
Contains(dbcode, '"*3*"') and Contains(dbcode, '"3*"') both conditions together will not return any rows due to previous reason
Does dbcode contain only one code?
If so, why are you using FTS to search for *3* when LIKE '3%' would give you the results you are looking for.
Thanks again, David.
I notice the performance of LIKE is slower than FTS Contains especial there are more filters in the same command. I am testing FTS on a dummy table which contains 500,000 rows. Anyway, I guess I have to go back to use LIKE instead of 'contains' since dynamic creating of sql command text using 'contains' from code-behind is not possible.
I don't do VB.NET any more, but in C#, there does not seem to be an issue:
string quote = "\""; MessageBox.Show(string.Concat("Contains(dbcode, '", quote, "*3*", quote, "')"));
Thank you for your advice, SSCrazy Eights.
I converted your code into VB.Net,
Dim quote As String = """"
MessageBox.Show(String.Concat("Contains(dbcode, '", quote, "*3*", quote, "')"))
It produces '""*3*""'. Extra quotes in the string also.

I created a VB.NET Windows app using this code and do not see extra quotes:

AhTu_SQL2k+ Ten Centuries Points: 1012 More actions · Answer 12

Phil Parkin - Wednesday, January 25, 2017 5:54 AM
AhTu_SQL2k+ - Wednesday, January 25, 2017 1:54 AM
Phil Parkin - Tuesday, January 24, 2017 1:29 PM
AhTu_SQL2k+ - Tuesday, January 24, 2017 9:40 AM
David Burrows - Tuesday, January 24, 2017 9:17 AM
With your data
Contains(dbcode, '"3*"') will return all the rows (providing each code you posted is in a separate row)
Contains(dbcode, '"*3*"') will not return any rows as the * prefix will take as a literal character and used as such in the works search (ie * would have to be at the beginning of the word)
Contains(dbcode, '"*3*"') and Contains(dbcode, '"3*"') both conditions together will not return any rows due to previous reason
Does dbcode contain only one code?
If so, why are you using FTS to search for *3* when LIKE '3%' would give you the results you are looking for.
Thanks again, David.
I notice the performance of LIKE is slower than FTS Contains especial there are more filters in the same command. I am testing FTS on a dummy table which contains 500,000 rows. Anyway, I guess I have to go back to use LIKE instead of 'contains' since dynamic creating of sql command text using 'contains' from code-behind is not possible.
I don't do VB.NET any more, but in C#, there does not seem to be an issue:
string quote = "\""; MessageBox.Show(string.Concat("Contains(dbcode, '", quote, "*3*", quote, "')"));
Thank you for your advice, SSCrazy Eights.
I converted your code into VB.Net,
Dim quote As String = """"
MessageBox.Show(String.Concat("Contains(dbcode, '", quote, "*3*", quote, "')"))
It produces '""*3*""'. Extra quotes in the string also.
I created a VB.NET Windows app using this code and do not see extra quotes:

My bad, Phil. My previous reply was misleading you. I converted, modified and tested your code in Webform as the following,
Dim quote As String = """" Dim tmpStr = String.Concat("Contains(dbcode, '", quote, "*3*", quote, "')")

Notice that the tmpstr returns "Contains(dbcode, '""*3*""')" from debug watch window.

You are right, in windows form the messagebox shows "Contains(dbcode, '"*3*"')"

What I am working on is an ASP.Net program, it is strange both Winform and Webform return two different results with same coding.
Thank you.

Together, we can make wonders!