|
Home > Archive > Microsoft Content Management Server > February 2004 > asp.net error
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| Kulwinder Sayal 2004-02-09, 2:37 am |
| Hi,
I'm trying to create a user control and I'm getting the following error.
Line 53: cmdSearch.Parameters.Add( "@searchphrase", txtSearchPhrase.Text )
Line 54: conPUbs.Open
Line 55: dtrSearch = cmdSearch.ExecuteReader()
Line 56: While dtrSearch.Read
Line 57: lblResults.Text &= "<li>" & dtrSearch( "Title" )
Source File: C:\Inetpub\wwwroot\aspnetstore\freetext.aspx Line: 55
Stack Trace:
[SqlException: Incorrect syntax near the keyword 'order'.]
Followig is the sql commands I'm using and I can't find any error. Please
can anyone help???
Thanks,
Kulwinder
Sub Button_Click( s As Object, e As EventArgs )
Dim conPubs As SqlConnection
Dim strSearch As String
Dim cmdSearch As SqlCommand
Dim dtrSearch As SqlDataReader
Dim sSearch As string
Dim Search As string
Dim aryKeys As string()
Dim Key As string
Dim nSimpleAs string
conPubs = New SqlConnection(
" Server=Localhost;UID=sa;PWD=Goyankees99;
Database=aspnetstore" ) strSearch =
"select distinct id, source, source2, title, issuedate,sortdate from
indexsearch where "
If Request("search") <> "" Then
sSearch = sSearch & "&search=" & Server.URLEncode(Request("search")) search
= lcase(Replace(Request("search"), "'", "''"))
strSearch = strSearch & "(1=1"
aryKeys = split(search, " and ")
If Ubound(aryKeys) > 0 Then
For each key in aryKeys
strSearch = strSearch & " and (source like '%" & key & "%' or source2 like
'%" & key & "%' or title like '%" & key & "%' or issuedate like '%" & key &
"%')"
Next
nSimple = 1
End If aryKeys = split(search, " or ")
If Ubound(aryKeys) > 0 Then
For each key in aryKeys
strSearch = strSearch & " or (source like '%" & key & "%' or source2c like
'%" & key & "%' or title like '%" & key & "%' or issuedate like '%" & key &
"%')"
Next
nSimple = 1
End If
if nSimple = "" then
strSearch = strSearch & " and source like '%" & search & "%' or source2 like
'%" & search & "%' or title like '%" & search & "%' or issuedate like '%" &
search & "%'"
end if
strSearch = strSearch & ")"
End If
strSearch = strSearch & " order by sortdate desc" cmdSearch = New
SqlCommand( strSearch, conPubs ) cmdSearch.Parameters.Add( "@searchphrase",
txtSearchPhrase.Text )
conPUbs.Open
dtrSearch = cmdSearch.ExecuteReader()
While dtrSearch.Read lblResults.Text &= "
" & dtrSearch( "Title" )
End While
conPubs.Close
| |
| Stefan [MSFT] 2004-02-09, 2:37 am |
| Hi Kulwinder,
please let your program write out the ACTUAL query being used rather than
list your code to create it.
This will show us the query which causes the problem.
Cheers,
Stefan.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kulwinder Sayal" <ksayal@trucking.org> wrote in message
news:uRT78Ry7DHA.2676@TK2MSFTNGP10.phx.gbl...
> Hi,
>
> I'm trying to create a user control and I'm getting the following error.
>
> Line 53: cmdSearch.Parameters.Add( "@searchphrase",
txtSearchPhrase.Text )
> Line 54: conPUbs.Open
> Line 55: dtrSearch = cmdSearch.ExecuteReader()
> Line 56: While dtrSearch.Read
> Line 57: lblResults.Text &= "<li>" & dtrSearch( "Title" )
> Source File: C:\Inetpub\wwwroot\aspnetstore\freetext.aspx Line: 55
>
> Stack Trace:
>
> [SqlException: Incorrect syntax near the keyword 'order'.]
>
>
>
>
> Followig is the sql commands I'm using and I can't find any error. Please
> can anyone help???
>
> Thanks,
> Kulwinder
>
>
> Sub Button_Click( s As Object, e As EventArgs )
> Dim conPubs As SqlConnection
> Dim strSearch As String
> Dim cmdSearch As SqlCommand
> Dim dtrSearch As SqlDataReader
> Dim sSearch As string
> Dim Search As string
> Dim aryKeys As string()
> Dim Key As string
> Dim nSimpleAs string
> conPubs = New SqlConnection(
> " Server=Localhost;UID=sa;PWD=Goyankees99;
Database=aspnetstore" ) strSearch
=
> "select distinct id, source, source2, title, issuedate,sortdate from
> indexsearch where "
> If Request("search") <> "" Then
> sSearch = sSearch & "&search=" & Server.URLEncode(Request("search"))
search
> = lcase(Replace(Request("search"), "'", "''"))
> strSearch = strSearch & "(1=1"
> aryKeys = split(search, " and ")
> If Ubound(aryKeys) > 0 Then
> For each key in aryKeys
> strSearch = strSearch & " and (source like '%" & key & "%' or source2
like
> '%" & key & "%' or title like '%" & key & "%' or issuedate like '%" & key
&
> "%')"
> Next
> nSimple = 1
> End If aryKeys = split(search, " or ")
> If Ubound(aryKeys) > 0 Then
> For each key in aryKeys
> strSearch = strSearch & " or (source like '%" & key & "%' or source2c like
> '%" & key & "%' or title like '%" & key & "%' or issuedate like '%" & key
&
> "%')"
> Next
> nSimple = 1
> End If
> if nSimple = "" then
> strSearch = strSearch & " and source like '%" & search & "%' or source2
like
> '%" & search & "%' or title like '%" & search & "%' or issuedate like '%"
&
> search & "%'"
> end if
> strSearch = strSearch & ")"
> End If
> strSearch = strSearch & " order by sortdate desc" cmdSearch = New
> SqlCommand( strSearch, conPubs ) cmdSearch.Parameters.Add(
"@searchphrase",
> txtSearchPhrase.Text )
> conPUbs.Open
> dtrSearch = cmdSearch.ExecuteReader()
> While dtrSearch.Read lblResults.Text &= "
> " & dtrSearch( "Title" )
> End While
> conPubs.Close
>
>
| |
| Kulwinder Sayal 2004-02-09, 3:38 am |
| Hi Stefan,
I'm trying to do search. The search can be one word or more. If it's one
word then the query is simple.If the search string is long then i'm
splitting into an array and looking for each key word from 3 different
fields in the database. Also there's if the search has "and" or "OR", we are
taking it out from the string.
Thanks,
Kulwinder
"Stefan [MSFT]" <stefang@online.microsoft.com> wrote in message
news:uH7uKUy7DHA.2432@TK2MSFTNGP10.phx.gbl...
> Hi Kulwinder,
>
> please let your program write out the ACTUAL query being used rather than
> list your code to create it.
> This will show us the query which causes the problem.
>
> Cheers,
> Stefan.
>
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
>
> "Kulwinder Sayal" <ksayal@trucking.org> wrote in message
> news:uRT78Ry7DHA.2676@TK2MSFTNGP10.phx.gbl...
> txtSearchPhrase.Text )
Please[color=blue]
strSearch[color=blue]
> =
> search
> like
key[color=blue]
> &
like[color=blue]
key[color=blue]
> &
> like
'%"[color=blue]
> &
> "@searchphrase",
>
>
| |
| Stefan [MSFT] 2004-02-09, 3:38 am |
| Hi Kulwinder,
the error message indicates that the query contains an error near the ORDER
keyword.
So it would be helpful to see the query being executed.
Cheers,
Stefan.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kulwinder Sayal" <ksayal@trucking.org> wrote in message
news:eEJaj5y7DHA.2088@TK2MSFTNGP10.phx.gbl...
> Hi Stefan,
>
> I'm trying to do search. The search can be one word or more. If it's one
> word then the query is simple.If the search string is long then i'm
> splitting into an array and looking for each key word from 3 different
> fields in the database. Also there's if the search has "and" or "OR", we
are
> taking it out from the string.
>
> Thanks,
> Kulwinder
> "Stefan [MSFT]" <stefang@online.microsoft.com> wrote in message
> news:uH7uKUy7DHA.2432@TK2MSFTNGP10.phx.gbl...
than[color=blue]
> rights.
error.[color=blue]
> Please
> strSearch
> key
> like
> key
source2[color=blue]
> '%"
>
>
|
|
|
|
|