473,703 Members | 2,503 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

ASP LIKE query using parameterized query

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
8 8047
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
"Bob Barrows [MVP]" <re******@NOyah oo.SPAMcomwrote in message
news:el******** ******@TK2MSFTN GP06.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.qMyLook up 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
parameterize d 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]=[iclientJobNumbe r]))))<>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(ity pe,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.as p?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.setRequestHea der('Content-Type','applicat ion/x-www-form-urlencoded');
x.onreadystatec hange = function() {
if (x.readyState == 4 && x.status == 200) {
// alert(x.respons eText);
document.getEle mentById('proje cts').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.getEle mentById('cjn') .value='';"
prt " document.getEle mentById('pn'). value='';"
prt " event.keyCode == 13 ?
getProjects(0,d ocument.getElem entById('cid'). value): null;"
prt " break;"
prt " case 'cjn':"
prt " document.getEle mentById('cid') .value='';"
prt " document.getEle mentById('pn'). value='';"
prt " event.keyCode == 13 ?
getProjects(1,d ocument.getElem entById('cjn'). value): null;"
prt " break;"
prt " case 'pn':"
prt " document.getEle mentById('cid') .value='';"
prt " document.getEle mentById('cjn') .value='';"
prt " event.keyCode == 13 ?
getProjects(2,d ocument.getElem entById('pn').v alue): 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(ity pe, iclientid, iclientJobNumbe r, iprojectName)
dim projects, i
SQLrsConnect()
select case itype
case "0"
conn.qProjectCl ientID iclientid, rs
case "1"
conn.qProjectCl ientJobNumber iclientJobNumbe r, rs
case "2"
conn.qProjectNa me 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

"Roland Hall" <nobody@nowhere wrote in message
news:u%******** ********@TK2MSF TNGP02.phx.gbl. ..
"Bob Barrows [MVP]" <re******@NOyah oo.SPAMcomwrote in message
news:el******** ******@TK2MSFTN GP06.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.qMyLooku p 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
parameteriz ed 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
Roland Hall wrote:
"Bob Barrows [MVP]" <re******@NOyah oo.SPAMcomwrote in message
news:el******** ******@TK2MSFTN GP06.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.qMyLooku p 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
"Roland Hall" <nobody@nowhere wrote 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.queri es), but I suppose it could
look something like this

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

--
Roy-Vidar
Jan 12 '07 #6
"Mike Brind" <pa*******@hotm ail.comwrote in message
news:uG******** ******@TK2MSFTN GP03.phx.gbl...
>
"Roland Hall" <nobody@nowhere wrote in message
news:u%******** ********@TK2MSF TNGP02.phx.gbl. ..
>"Bob Barrows [MVP]" <re******@NOyah oo.SPAMcomwrote in message
news:el******* *******@TK2MSFT NGP06.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.qMyLook up 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
parameterize d 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

"Bob Barrows [MVP]" <re******@NOyah oo.SPAMcomwrote in message
news:um******** ******@TK2MSFTN GP06.phx.gbl...
Roland Hall wrote:
>"Bob Barrows [MVP]" <re******@NOyah oo.SPAMcomwrote in message
news:el******* *******@TK2MSFT NGP06.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.qMyLook up 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
"RoyVidar" <ro************ *@yahoo.nowrote in message
news:mn******** *************** @yahoo.no...
"Roland Hall" <nobody@nowhere wrote 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.qMyLook up 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
parameterize d 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.queri es), 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
1988
by: PiGei | last post by:
Hi all, I'm trying to build a function that - providing the dbname and the query name - show the results. I don't know how to solve this problem... when I try to insert the variable into this call cnnSimple.x_qry rstSimple
8
12922
by: deko | last post by:
I'm trying to open a Recordset based on a parameterized query. I'm kind of new to parameterized queries, so I'm sure I'm missing something simple. Set qdfs = db.QueryDefs Set qdf = qdfs("qryInvoices") qdf.Parameters("prmInv") = strInvoice qdf.Parameters("prmCid") = lngCustomerID Set rst = db.OpenRecordset("qryInvoices")
1
1995
by: Jorell | last post by:
Hey everyone, I am currently using Microsofts DataAccess Application block ( SQLHelper ) and what I want to do is use a parameterized query instead of just SQL. I can not use stored procedures, I just want to create an sql statement such as this: Dim SelectStatement As String = _ "SELECT folder_id, " & _ "document, " & _
11
3793
by: anony | last post by:
Hello, I can't figure out why my parameterized query from an ASP.NET page is dropping "special" characters such as accented quotes & apostrophes, the registered trademark symbol, etc. These symbols insert without problem from query analyzer, so that suggests it's something within ASP.NET. I've tried using .NET textbox web controls as well as html textareas. I have a test database set up with 4 fields: varchar, nvarchar, text, and...
2
1473
by: Howard | last post by:
How do I do this with parameterized query? without parameterized query: string search = "hello world search"; search = search.Replace(" ", " AND "); SELECT * FROM TABLE1 WHERE TEXT LIKE '%' + @search + '%' parameterized query:
2
1481
by: JSheble | last post by:
After building a parameterized ADO query, is there a method or a statement where you could see the actual query, with the parameterized values included?? -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
29
5505
by: wizofaus | last post by:
I previously posted about a problem where it seemed that changing the case of the word "BY" in a SELECT query was causing it to run much much faster. Now I've hit the same thing again, where basically almost any change I make to how the query is executed (so that it still performs the same function) causes the performance to jump from a dismal 7 or 8 seconds to instantaneous. It's a very simple query of the form: SELECT Min(MyValue)...
11
16327
by: funky | last post by:
hello, I've got a big problem ad i'm not able to resolve it. We have a server running oracle 10g version 10.1.0. We usually use access as front end and connect database tables for data extraction. We have been using oracle client 10.1.0.2 with it's odbc for a while without problem. The problem arose when we decided to reconnect all the tables and save password. Some query became suddenly very slow. Then I've discovered that the tables...
3
2223
by: xlar54 | last post by:
Is there a way to see the exact SQL being generated from a parameterized query? I am using this technique but am getting some strange SQL errors during execution and I would like to see the final SQL that is being generated.
0
8755
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8667
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9116
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9013
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8961
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7860
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
4428
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
2445
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2063
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.