Syntax Problem in Code using SCOPE() function

[Guest]

Trying to use Microsoft Indexing Service.

Here is my code using SQL Query Analyzer, which works perfectly.

SELECT * FROM
OPENQUERY(FileSystem,
'SELECT RANK, FileName, Characterization
FROM SCOPE(''"/ASPNetDemo/Resumes"'')
WHERE FREETEXT(''Baione'') > 0
ORDER BY RANK DESC' )

but... when I put this code in the script in my ASPX page, apparently the
syntax does not work. The following is all of my code.

Sub btnSearch_Click(ByVal s As Object, ByVal e As EventArgs)
Dim conMyData As SqlClient.SqlConnection
Dim strSearch As String
Dim cmdSearch As SqlClient.SqlCommand
Dim dtrSearch As SqlClient.SqlDataReader
Dim x As String
x = "'/ASPNetDemo/Resumes'"


conMyData = New
SqlClient.SqlConnection("Server=Localhost;UID=sa;PWD=test;Database=Pubs")
strSearch = "SELECT * FROM " & _
"OPENQUERY(FileSystem, " & _
"'SELECT RANK, FileName, VPath, Characterization " & _
"FROM SCOPE(''" & "\ASPNetDemo\Resumes" & "'') " & _
"WHERE FREETEXT(''" & txtSearchPhrase.Text & _
"'') > 0 " & _
"ORDER BY RANK DESC' )"

cmdSearch = New SqlClient.SqlCommand(strSearch, conMyData)
conMyData.Open()
Try
dtrSearch = cmdSearch.ExecuteReader()
lblResults.Text = "<ul>"
While dtrSearch.Read
lblResults.Text &= "<li> (" & dtrSearch("RANK") / 10 & "%) "
lblResults.Text &= "<a href=""" &
dtrSearch("VPath").ToString()
lblResults.Text &= """>"
lblResults.Text &= dtrSearch("FileName") & "</a><br>"
lblResults.Text &= dtrSearch("Characterization")
lblResults.Text &= "<p>"
End While
Catch ex As Exception
lblResults.Text = "Please rephrase your query"
End Try
conMyData.Close()
End Sub

Any help in pointing out the correct syntax using the SCOPE function would
be greatly appreciated.

Thanks

 

[Chris R. Timmons]

Trying to use Microsoft Indexing Service.

Here is my code using SQL Query Analyzer, which works perfectly.

SELECT * FROM
OPENQUERY(FileSystem,
'SELECT RANK, FileName, Characterization
FROM SCOPE(''"/ASPNetDemo/Resumes"'')
WHERE FREETEXT(''Baione'') > 0
ORDER BY RANK DESC' )

<code snipped>

conMyData = New
SqlClient.SqlConnection("Server=Localhost;UID=sa;PWD=test;Databas
e=Pubs")
strSearch = "SELECT * FROM " & _
"OPENQUERY(FileSystem, " & _
"'SELECT RANK, FileName, VPath, Characterization " & _
"FROM SCOPE(''" & "\ASPNetDemo\Resumes" & "'') " & _
"WHERE FREETEXT(''" & txtSearchPhrase.Text & _
"'') > 0 " & _
"ORDER BY RANK DESC' )"

Umpty,

You have different slash characters in the VB code. I believe the /
character is required, not the \ character.

 

[Guest]

Chris, this didn't work...

Thanks,
Umpty

Umpty,

You have different slash characters in the VB code. I believe the /
character is required, not the \ character.