By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,905 Members | 894 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,905 IT Pros & Developers. It's quick & easy.

ASP LIKE query using parameterized query

P: n/a
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


Jan 12 '07 #1
Share this Question
Share on Google+
8 Replies


P: n/a
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"
Jan 12 '07 #2

P: n/a
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcomwrote in message
news:el**************@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-urlencoded');
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 "<table>"
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 "<tr><td>" & right("00" & projects(0,i),3) & "-" & right("00" &
projects(1,i),3) & " " & projects(2,i) & "</td></tr>"
next
else
SQLrsDisconnect()
prt "<tr><td>no records returned</td></tr>"
end if
prt "</table>"
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


Jan 12 '07 #3

P: n/a

"Roland Hall" <nobody@nowherewrote in message
news:u%****************@TK2MSFTNGP02.phx.gbl...
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcomwrote in message
news:el**************@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
Jan 12 '07 #4

P: n/a
Roland Hall wrote:
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcomwrote in message
news:el**************@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.

<snip TMI>
--
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"
Jan 12 '07 #5

P: n/a
"Roland Hall" <nobody@nowherewrote in message
<eL*************@TK2MSFTNGP06.phx.gbl>:
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
Jan 12 '07 #6

P: n/a
"Mike Brind" <pa*******@hotmail.comwrote in message
news:uG**************@TK2MSFTNGP03.phx.gbl...
>
"Roland Hall" <nobody@nowherewrote in message
news:u%****************@TK2MSFTNGP02.phx.gbl...
>"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcomwrote in message
news:el**************@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
Jan 13 '07 #7

P: n/a

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcomwrote in message
news:um**************@TK2MSFTNGP06.phx.gbl...
Roland Hall wrote:
>"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcomwrote in message
news:el**************@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
Jan 13 '07 #8

P: n/a
"RoyVidar" <ro*************@yahoo.nowrote in message
news:mn***********************@yahoo.no...
"Roland Hall" <nobody@nowherewrote in message
<eL*************@TK2MSFTNGP06.phx.gbl>:
>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
Jan 13 '07 #9

This discussion thread is closed

Replies have been disabled for this discussion.