Post reply

Create SQL FullText statement via VB.Net

  • 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!

  • January 24, 2017 at 2:37 am

    #1924543

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

    John

  • January 24, 2017 at 2:44 am

    #1924545

    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!

  • January 24, 2017 at 2:55 am

    #1924549

    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

  • January 24, 2017 at 6:17 am

    #1924606

    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

  • January 24, 2017 at 6:49 am

    #1924615

    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.

  • January 24, 2017 at 8:28 am

    #1924641

    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!

  • January 24, 2017 at 9:17 am

    #1924658

    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.

  • January 24, 2017 at 9:40 am

    #1924663

    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!

  • January 24, 2017 at 1:29 pm

    #1924727

    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, "')"));

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • January 25, 2017 at 1:54 am

    #1924800

    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!

  • January 25, 2017 at 5:54 am

    #1924824

    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:

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • January 25, 2017 at 11:57 pm

    #1925005

    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!

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

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