ASP Wildcard

[Newsgroup_User]

Hi,

I thought that the wildcard for text to be used was %, I tried this but it
does not work :

<%
Dim rsUserInfo__MMColParam1
rsUserInfo__MMColParam1 = "%"
If (Request.QueryString("l") <> "") Then
rsUserInfo__MMColParam1 = Request.QueryString("l")
End If
%>
<%
Dim rsUserInfo__MMColParam4
rsUserInfo__MMColParam4 = "1/1/05"
If (date() <> "") Then
rsUserInfo__MMColParam4 = date()
End If
%>
<%
Dim rsUserInfo
Dim rsUserInfo_cmd
Dim rsUserInfo_numRows

Set rsUserInfo_cmd = Server.CreateObject ("ADODB.Command")
rsUserInfo_cmd.ActiveConnection = MM_mydb_STRING
rsUserInfo_cmd.CommandText = "SELECT company, jlocation, startdate, enddate
FROM tbl_main WHERE jlocation= ? AND enddate < ?"
rsUserInfo_cmd.Prepared = true
rsUserInfo_cmd.Parameters.Append rsUserInfo_cmd.CreateParameter("param1",
200, 1, 255, rsUserInfo__MMColParam1) ' adVarChar
rsUserInfo_cmd.Parameters.Append rsUserInfo_cmd.CreateParameter("param2",
135, 1, -1, rsUserInfo__MMColParam4) ' adDBTimeStamp

Set rsUserInfo = rsUserInfo_cmd.Execute
rsUserInfo_numRows = 0
%>

if I omit the where jlocation parameter it works - not sure what I am
missing?

Thanks in advance

[MikeL7]

Change the = ? to Like ? and add the wildcard to before and or after the variable name in the parameter declaration.

%search% - will find a string that occurs anywhere in the text that is 'search'
search% - will find a string that has the first 6 characters 'search'
%search - will find a string that has last six characters 'search'
It depends on how you want to search your db.

Remove either of the % to control how the text is searched
so it would be this:


rsUserInfo_cmd.CommandText = "SELECT company, jlocation, startdate, enddate
FROM tbl_main WHERE jlocation LIKE ? AND enddate < ?"
rsUserInfo_cmd.Prepared = true
rsUserInfo_cmd.Parameters.Append rsUserInfo_cmd.CreateParameter("param1",
200, 1, 255, "%" + rsUserInfo__MMColParam1 + "%") ' adVarChar

[Newsgroup_User]

Thanks Mike, that's working fine now.

Why does DW not do this automatically and we need to manually edit the code?

Regards

"MikeL7" <webforumsuser@macromedia.com> wrote in message
news:g3hihe$d82$1@forums.macromedia.com...
> Change the = ? to Like ? and add the wildcard to before and or after the
> variable name in the parameter declaration.
>
> %search% - will find a string that occurs anywhere in the text that is
> 'search'
> search% - will find a string that has the first 6 characters 'search'
> %search - will find a string that has last six characters 'search'
> It depends on how you want to search your db.
>
> Remove either of the % to control how the text is searched
> so it would be this:
>
>
> rsUserInfo_cmd.CommandText = "SELECT company, jlocation, startdate,
> enddate
> FROM tbl_main WHERE jlocation LIKE ? AND enddate < ?"
> rsUserInfo_cmd.Prepared = true
> rsUserInfo_cmd.Parameters.Append rsUserInfo_cmd.CreateParameter("param1",
> 200, 1, 255, "%" + rsUserInfo__MMColParam1 + "%") ' adVarChar
>
>