ASP LIKE query using parameterized query

am 12.01.2007 01:04:07 von Roland Hall

In Access you use "*" + [passed variable] + "*", + can be replaced with &
Calling a parameterized query in Access requires % be used in place of *,
however, all that I have read show dynamic SQL passed to Access:

WHERE [some column] LIKE '" & ASPvar & "' % ORDER BY ...

However, my call is similar to:

conn.qMyLookup strVar, rs

If I modify the query in Access to:

"%" & [passed variable] & "%"

I get all records. If I only put it at the end, as suggested, I only get
matches at the end, not throughout the column.

Ex.

search for 'a'

Rows:

abc
bca
cab

all that is returned is 'bca' If I used the % before and after, it will
return all 3 rows in my recordset. I've done this before but I cannot
remember if I ever used performed a wildcard search with a parameterized
query. Am I restricted to using only dynamic SQL to get this to work?

Any help appreciated.

Roland Hall

Re: ASP LIKE query using parameterized query

am 12.01.2007 02:09:27 von reb01501

Roland Hall wrote:
> In Access you use "*" + [passed variable] + "*", + can be replaced
> with & Calling a parameterized query in Access requires % be used in
> place of *, however, all that I have read show dynamic SQL passed to
> Access:
> WHERE [some column] LIKE '" & ASPvar & "' % ORDER BY ...
>
> However, my call is similar to:
>
> conn.qMyLookup strVar, rs
>
> If I modify the query in Access to:
>
> "%" & [passed variable] & "%"
>
> I get all records. If I only put it at the end, as suggested,

I don't understand what you mean by this. Put what "at the end"?

> I only
> get matches at the end, not throughout the column.
>
> Ex.
>
> search for 'a'
>
> Rows:
>
> abc
> bca
> cab
>
> all that is returned is 'bca'

So you used
... LIKE "%' & parm
in the query, right?

> If I used the % before and after, it
> will return all 3 rows in my recordset. I've done this before but I
> cannot remember if I ever used performed a wildcard search with a
> parameterized query. Am I restricted to using only dynamic SQL to
> get this to work?
It's not clear to me what you want. Which row do you want returned and why?

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: ASP LIKE query using parameterized query

am 12.01.2007 02:09:27 von reb01501

Re: ASP LIKE query using parameterized query

am 12.01.2007 03:20:18 von Roland Hall

"Bob Barrows [MVP]" wrote in message
news:elNcOZeNHHA.3588@TK2MSFTNGP06.phx.gbl...
> Roland Hall wrote:
>> In Access you use "*" + [passed variable] + "*", + can be replaced
>> with & Calling a parameterized query in Access requires % be used in
>> place of *, however, all that I have read show dynamic SQL passed to
>> Access:
>> WHERE [some column] LIKE '" & ASPvar & "' % ORDER BY ...
>>
>> However, my call is similar to:
>>
>> conn.qMyLookup strVar, rs
>>
>> If I modify the query in Access to:
>>
>> "%" & [passed variable] & "%"
>>
>> I get all records. If I only put it at the end, as suggested,
>
> I don't understand what you mean by this. Put what "at the end"?

%

>> I only
>> get matches at the end, not throughout the column.
>>
>> Ex.
>>
>> search for 'a'
>>
>> Rows:
>>
>> abc
>> bca
>> cab
>>
>> all that is returned is 'bca'
>
> So you used
> ... LIKE "%' & parm
> in the query, right?

Actually to get my query value to return any row where my value is found, I
need it before and after (it = %)

>> If I used the % before and after, it
>> will return all 3 rows in my recordset. I've done this before but I
>> cannot remember if I ever used performed a wildcard search with a
>> parameterized query. Am I restricted to using only dynamic SQL to
>> get this to work?
> It's not clear to me what you want. Which row do you want returned and
> why?

I found my errors, I had two. I had 3 nested IIFs in my query and decided
to make 3 different queries for two reasons:

1. Access is very limited compared to SQL
2. I couldn't get it to work

After that, I couldn't get the query which returns projects by name to work
but I was still passing the type of request, the client ID and the client
job number, all of which I no longer needed. I only needed to now pass the
project name.

I also had an error in my main app where I was passing the wrong ID in my
javascript to my remote scripting function.

Good to hear from you. Haven't talked to you in awhile and thanks for
responding.

This is the query I'm using that actually was not the part of the problem.

SELECT Projects.[Client ID], Projects.[Client Job Number], Projects.[Project
Name], Projects.[Project Number]
FROM Projects
WHERE [Project Name] LIKE "%" & [iprojectName] & "%"
ORDER BY [Project Name];

This is the nested IIF query I gave up on:

SELECT Projects.[Client ID], Projects.[Client Job Number], Projects.[Project
Name], Projects.[Project Number]
FROM Projects
WHERE (((IIf([itype]=2,[Project Name] LIKE "%" + [iprojectName] +
"%",IIf([itype]=0,[Client ID]=[iclientid],IIf([itype]=1,[Client Job
Number]=[iclientJobNumber]))))<>False))
ORDER BY IIf([itype]=2,[Project Name],IIf([itype]=0,[Client Job
Number],IIf([itype]=1,[Client ID])));

I have 3 text fields that allow only one entry type to be passed; client ID,
client job number or project name. This is a timesheet app for an
architectual firm.

My remote scripting script:

// getprojects.js
var x;
function getProjects(itype,v) {
var cid = '', cjn = '', pn = '', p = '';
switch(itype) {
case 0:
cid = v;
break;
case 1:
cjn = v;
break;
default:
pn = v;
break;
}
var url = "getprojects.asp?itype=" + itype + "&cid=" + cid + "&cjn=" + cjn
+ "&pn=" + pn;
try { x = new XMLHttpRequest(); }
catch (error) {
try { x = new ActiveXObject("MSXML2.XMLHTTP"); }
catch (error) { return false; }
}
x.open("GET", url, true);
x.setRequestHeader('Content-Type','application/x-www-form-ur lencoded');
x.onreadystatechange = function() {
if (x.readyState == 4 && x.status == 200) {
// alert(x.responseText);
document.getElementById('projects').innerHTML = x.responseText;
return true;
}
}
x.send(null);
return true;
}

My timesheet script that called the remote scripting:
prt "function cjncheck(id) {"
prt " switch(id) {"
prt " case 'cid':"
prt " document.getElementById('cjn').value='';"
prt " document.getElementById('pn').value='';"
prt " event.keyCode == 13 ?
getProjects(0,document.getElementById('cid').value): null;"
prt " break;"
prt " case 'cjn':"
prt " document.getElementById('cid').value='';"
prt " document.getElementById('pn').value='';"
prt " event.keyCode == 13 ?
getProjects(1,document.getElementById('cjn').value): null;"
prt " break;"
prt " case 'pn':"
prt " document.getElementById('cid').value='';"
prt " document.getElementById('cjn').value='';"
prt " event.keyCode == 13 ?
getProjects(2,document.getElementById('pn').value): null;"
prt " }"
prt " }"

The last keyCode test was passing id cjn, instead of pn. I often copy my
code instead of rewriting similar lines and I missed changing the ID.

My little asp script that made the call to the query that was still passing
all variables, which were no longer needed once they were split into 3
different queries.

sub getProjects(itype, iclientid, iclientJobNumber, iprojectName)
dim projects, i
SQLrsConnect()
select case itype
case "0"
conn.qProjectClientID iclientid, rs
case "1"
conn.qProjectClientJobNumber iclientJobNumber, rs
case "2"
conn.qProjectName iprojectName, rs
case else
SQLrsDisconnect()
lprt "Error! itype is not 0, 1 or 2"
Response.End
end select
prt "

"
if not (rs.BOF or rs.EOF) then
projects = rs.GetRows()
lprt "records: " & ubound(projects,2)
SQLrsDisconnect()
for i = 0 to ubound(projects,2)
prt ""
next
else
SQLrsDisconnect()
prt ""
end if
prt "

" & right("00" & projects(0,i),3) & "-" & right("00" &
projects(1,i),3) & " " & projects(2,i) & "

no records returned

"
end sub

It's pretty simple code but I still lost hair on it. If the database wasn't
so big and complicated I'd move it to SQL before writing the app but I
didn't have time. It's one day overdue and I have a target on my back.

--

Roland Hall

Re: ASP LIKE query using parameterized query

am 12.01.2007 03:20:18 von Roland Hall

"
if not (rs.BOF or rs.EOF) then
projects = rs.GetRows()
lprt "records: " & ubound(projects,2)
SQLrsDisconnect()
for i = 0 to ubound(projects,2)
prt ""
next
else
SQLrsDisconnect()
prt ""
end if
prt "

" & right("00" & projects(0,i),3) & "-" & right("00" &
projects(1,i),3) & " " & projects(2,i) & "

no records returned

Re: ASP LIKE query using parameterized query

am 12.01.2007 09:53:48 von Mike Brind

"Roland Hall" wrote in message
news:u%23ja1AfNHHA.2232@TK2MSFTNGP02.phx.gbl...
> "Bob Barrows [MVP]" wrote in message
> news:elNcOZeNHHA.3588@TK2MSFTNGP06.phx.gbl...
>> Roland Hall wrote:
>>> In Access you use "*" + [passed variable] + "*", + can be replaced
>>> with & Calling a parameterized query in Access requires % be used in
>>> place of *, however, all that I have read show dynamic SQL passed to
>>> Access:
>>> WHERE [some column] LIKE '" & ASPvar & "' % ORDER BY ...
>>>
>>> However, my call is similar to:
>>>
>>> conn.qMyLookup strVar, rs
>>>
>>> If I modify the query in Access to:
>>>
>>> "%" & [passed variable] & "%"
>>>
>>> I get all records. If I only put it at the end, as suggested,
>>
>> I don't understand what you mean by this. Put what "at the end"?
>
> %
>
>>> I only
>>> get matches at the end, not throughout the column.
>>>
>>> Ex.
>>>
>>> search for 'a'
>>>
>>> Rows:
>>>
>>> abc
>>> bca
>>> cab
>>>
>>> all that is returned is 'bca'
>>
>> So you used
>> ... LIKE "%' & parm
>> in the query, right?
>
> Actually to get my query value to return any row where my value is found,
> I need it before and after (it = %)
>
>>> If I used the % before and after, it
>>> will return all 3 rows in my recordset. I've done this before but I
>>> cannot remember if I ever used performed a wildcard search with a
>>> parameterized query. Am I restricted to using only dynamic SQL to
>>> get this to work?
>> It's not clear to me what you want. Which row do you want returned and
>> why?
>
> I found my errors, I had two.

[snip]

Does this mean you have resolved the issue?

--
Mike Brind

Re: ASP LIKE query using parameterized query

am 12.01.2007 09:53:48 von Mike Brind

Re: ASP LIKE query using parameterized query

am 12.01.2007 16:21:55 von reb01501

Roland Hall wrote:
> "Bob Barrows [MVP]" wrote in message
> news:elNcOZeNHHA.3588@TK2MSFTNGP06.phx.gbl...
>> Roland Hall wrote:
>>> In Access you use "*" + [passed variable] + "*", + can be replaced
>>> with & Calling a parameterized query in Access requires % be used in
>>> place of *, however, all that I have read show dynamic SQL passed to
>>> Access:
>>> WHERE [some column] LIKE '" & ASPvar & "' % ORDER BY ...
>>>
>>> However, my call is similar to:
>>>
>>> conn.qMyLookup strVar, rs
>>>
>>> If I modify the query in Access to:
>>>
>>> "%" & [passed variable] & "%"
>>>
>>> I get all records.

You get ALL records? Even those that don't contain the passed value?

> If I only put it at the end, as suggested,
>>
>> I don't understand what you mean by this. Put what "at the end"?
>
> %
>
>>> I only
>>> get matches at the end, not throughout the column.
>>>
>>> Ex.
>>>
>>> search for 'a'
>>>
>>> Rows:
>>>
>>> abc
>>> bca
>>> cab
>>>
>>> all that is returned is 'bca'
>>
>> So you used
>> ... LIKE "%' & parm
>> in the query, right?
>
> Actually to get my query value to return any row where my value is
> found, I need it before and after (it = %)

So put it there. I don't understand the problem.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: ASP LIKE query using parameterized query

am 12.01.2007 16:21:55 von reb01501

Re: ASP LIKE query using parameterized query

am 12.01.2007 17:47:34 von RoyVidar

"Roland Hall" wrote in message
:
> In Access you use "*" + [passed variable] + "*", + can be replaced
> with & Calling a parameterized query in Access requires % be used in
> place of *, however, all that I have read show dynamic SQL passed to
> Access:
>
> WHERE [some column] LIKE '" & ASPvar & "' % ORDER BY ...
>
> However, my call is similar to:
>
> conn.qMyLookup strVar, rs
>
> If I modify the query in Access to:
>
> "%" & [passed variable] & "%"
>
> I get all records. If I only put it at the end, as suggested, I only
> get matches at the end, not throughout the column.
>
> Ex.
>
> search for 'a'
>
> Rows:
>
> abc
> bca
> cab
>
> all that is returned is 'bca' If I used the % before and after, it
> will return all 3 rows in my recordset. I've done this before but I
> cannot remember if I ever used performed a wildcard search with a
> parameterized query. Am I restricted to using only dynamic SQL to
> get this to work?
>
> Any help appreciated.
>
> Roland Hall

You might try altering the stored Jet query to

....
WHERE [Project Name] LIKE [iprojectName]
....

Then pass the wildcards with the parameter. Dunno how that's done
with ASP (seeing this from ...access.queries), but I suppose it could
look something like this

conn.qMyLookup "%" + strVar + "%", rs

--
Roy-Vidar

Re: ASP LIKE query using parameterized query

am 12.01.2007 17:47:34 von RoyVidar

Re: ASP LIKE query using parameterized query

am 13.01.2007 05:45:13 von Roland Hall

"Mike Brind" wrote in message
news:uGMj2ciNHHA.5000@TK2MSFTNGP03.phx.gbl...
>
> "Roland Hall" wrote in message
> news:u%23ja1AfNHHA.2232@TK2MSFTNGP02.phx.gbl...
>> "Bob Barrows [MVP]" wrote in message
>> news:elNcOZeNHHA.3588@TK2MSFTNGP06.phx.gbl...
>>> Roland Hall wrote:
>>>> In Access you use "*" + [passed variable] + "*", + can be replaced
>>>> with & Calling a parameterized query in Access requires % be used in
>>>> place of *, however, all that I have read show dynamic SQL passed to
>>>> Access:
>>>> WHERE [some column] LIKE '" & ASPvar & "' % ORDER BY ...
>>>>
>>>> However, my call is similar to:
>>>>
>>>> conn.qMyLookup strVar, rs
>>>>
>>>> If I modify the query in Access to:
>>>>
>>>> "%" & [passed variable] & "%"
>>>>
>>>> I get all records. If I only put it at the end, as suggested,
>>>
>>> I don't understand what you mean by this. Put what "at the end"?
>>
>> %
>>
>>>> I only
>>>> get matches at the end, not throughout the column.
>>>>
>>>> Ex.
>>>>
>>>> search for 'a'
>>>>
>>>> Rows:
>>>>
>>>> abc
>>>> bca
>>>> cab
>>>>
>>>> all that is returned is 'bca'
>>>
>>> So you used
>>> ... LIKE "%' & parm
>>> in the query, right?
>>
>> Actually to get my query value to return any row where my value is found,
>> I need it before and after (it = %)
>>
>>>> If I used the % before and after, it
>>>> will return all 3 rows in my recordset. I've done this before but I
>>>> cannot remember if I ever used performed a wildcard search with a
>>>> parameterized query. Am I restricted to using only dynamic SQL to
>>>> get this to work?
>>> It's not clear to me what you want. Which row do you want returned and
>>> why?
>>
>> I found my errors, I had two.
>
> [snip]
>
> Does this mean you have resolved the issue?

Yes, that's why I listed the code to help others. My LIKE query was not the
issue after all.

--

Roland Hall

Re: ASP LIKE query using parameterized query

am 13.01.2007 05:45:13 von Roland Hall

Re: ASP LIKE query using parameterized query

am 13.01.2007 05:46:39 von Roland Hall

"Bob Barrows [MVP]" wrote in message
news:umntk1lNHHA.4604@TK2MSFTNGP06.phx.gbl...
> Roland Hall wrote:
>> "Bob Barrows [MVP]" wrote in message
>> news:elNcOZeNHHA.3588@TK2MSFTNGP06.phx.gbl...
>>> Roland Hall wrote:
>>>> In Access you use "*" + [passed variable] + "*", + can be replaced
>>>> with & Calling a parameterized query in Access requires % be used in
>>>> place of *, however, all that I have read show dynamic SQL passed to
>>>> Access:
>>>> WHERE [some column] LIKE '" & ASPvar & "' % ORDER BY ...
>>>>
>>>> However, my call is similar to:
>>>>
>>>> conn.qMyLookup strVar, rs
>>>>
>>>> If I modify the query in Access to:
>>>>
>>>> "%" & [passed variable] & "%"
>>>>
>>>> I get all records.
>
> You get ALL records? Even those that don't contain the passed value?

Yes, but my 2nd response explains what the issues were and what it took to
fix it.

--

Roland Hall

Re: ASP LIKE query using parameterized query

am 13.01.2007 05:46:39 von Roland Hall

Re: ASP LIKE query using parameterized query

am 13.01.2007 06:08:48 von Roland Hall

"RoyVidar" wrote in message
news:mn.642b7d710afc876c.59509@yahoo.no...
> "Roland Hall" wrote in message
> :
>> In Access you use "*" + [passed variable] + "*", + can be replaced
>> with & Calling a parameterized query in Access requires % be used in
>> place of *, however, all that I have read show dynamic SQL passed to
>> Access:
>>
>> WHERE [some column] LIKE '" & ASPvar & "' % ORDER BY ...
>>
>> However, my call is similar to:
>>
>> conn.qMyLookup strVar, rs
>>
>> If I modify the query in Access to:
>>
>> "%" & [passed variable] & "%"
>>
>> I get all records. If I only put it at the end, as suggested, I only
>> get matches at the end, not throughout the column.
>>
>> Ex.
>>
>> search for 'a'
>>
>> Rows:
>>
>> abc
>> bca
>> cab
>>
>> all that is returned is 'bca' If I used the % before and after, it
>> will return all 3 rows in my recordset. I've done this before but I
>> cannot remember if I ever used performed a wildcard search with a
>> parameterized query. Am I restricted to using only dynamic SQL to
>> get this to work?
>>
>> Any help appreciated.
>>
>> Roland Hall
>
> You might try altering the stored Jet query to
>
> ...
> WHERE [Project Name] LIKE [iprojectName]
> ...
>
> Then pass the wildcards with the parameter. Dunno how that's done
> with ASP (seeing this from ...access.queries), but I suppose it could
> look something like this
>
> conn.qMyLookup "%" + strVar + "%", rs

I thought of that but still didn't work when I tried it. It may work that
way but my 2nd response shows the query was not the problem, at least after
I split it into 3 queries. I just needed to modify my calls and I had one
error in a javascript routine.

--

Roland Hall

Re: ASP LIKE query using parameterized query

am 13.01.2007 06:08:48 von Roland Hall