SQL Server query using LIKE has problems with spaces under VBScript

Question

[Withdrawing question: the issue arose from not escaping values submitted in web form. The ASP script removed unescaped spaces thereby causing the problem.]

In a web page I am attempting to conduct a search against a table in MS SQL Server using VBScript. The search uses a LIKE clause (with wildcards). The search works fine if the input has no spaces in it. However, if there is a space in the input, the search returns nothing.

For example, if I search for IT Department I get nothing, but if I search for Department I get IT Department. Somehow, the space between IT and Department is causing trouble. If I run the script directly against the server using SQL Server Management Studio, the search using IT Department returns the proper results.

Also, if I don't use SQL placeholder parameters but instead put in search string directly, the search works.

It is only in the case where the search string contains a space and uses placeholders where the search fails.

Here is the code I am using:

Dim oCmd, OffDescParam, description, query
description = "IT Department"
Set oCmd = Server.CreateObject("ADODB.Command")

OffDescParam = "%" & description & "%"
Set objOffDescParam = _
  oCmd.CreateParameter("@offdesc", adChar, adParamInput, Len(OffDescParam), OffDescParam)

query = "SELECT OfficialDescription " & _
        "FROM [MyDatabase].[dbo].[Organizations] " & _
        "WHERE (OfficialDescription LIKE ?)" & _
        "ORDER BY OfficialDescription"
' If I use "WHERE (OfficialDescription LIKE '%IT Department%')" it works.

oCmd.ActiveConnection = "some/connection/string"
oCmd.ActiveConnection.CursorLocation = adUseClient
oCmd.CommandType = adCmdText
oCmd.CommandText = query

oCmd.Parameters.Append objOffDescParam
oCmd.Prepared = True
Set oRst = CopyRecordSet(oCmd.Execute)
oCmd.ActiveConnection.close

Answer 1

Doesn't it need to be...

"WHERE (OfficialDescription LIKE '?')" & _

Answer 2

Your code works for me (well actually I got an error on the CopyRecordSet line but I suspect that is just something to do with my vbscript/ado version)

In Profiler I saw the following

declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,N'@P1 char(15)',N'SELECT blah FROM [test].[dbo].[mytable] WHERE (mycol LIKE @P1)ORDER BY mycol','%IT Department%'
select @p1

When I ran this directly in Management Studio

declare @p1 int
exec sp_prepexec @p1 output,N'@P1 char(15)',N'SELECT blah FROM [test].[dbo].[mytable] WHERE (mycol LIKE @P1)ORDER BY mycol','%IT Department%'
select @p1

I got back results. Maybe try using profiler your end to see if all is as expected.

Answer 3

Two things you could try, tho it looks like you're doing it correctly:

• ADO or SQL Server can occasionally behave in a surprising way when you use adChar. Try using adVarChar instead.
• VB's Len function sometimes returns the number of bytes (though for a string, it shouldn't.) Try manually calculating the length of the string and passing that to CreateParameter.

Answer 4

what is the field's data type?