ASP & Access2k question

[Micromanaged]

Running asp pages against an Access 2k database.

2 questions for those vastly more knowledgeable than myself.

- Are wildcards allowed in Access 2k SQL statements? If so, what are
they, and what, if anything, do I need to be aware of programmatically
in ASP?

- Can I use the 'LIKE' keyword in Access 2k SQL statements? If so, what
is the correct syntax both within the database directly and is ASP?

Thanks.

*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Ken Schaefer]

a) Yes you can use wildcards. For OLEDB Provider, you use standard ANSI SQL
wildcards.

b) Yes, you can use LIKE

[url]www.adopenstatic.com/faq/likequeries.asp[/url]

Cheers
Ken


"Micromanaged" <[email protected]> wrote in message
news:ucb4$[email protected]..

>
>
> Running asp pages against an Access 2k database.
>
> 2 questions for those vastly more knowledgeable than myself.
>
> - Are wildcards allowed in Access 2k SQL statements? If so, what are
> they, and what, if anything, do I need to be aware of programmatically
> in ASP?
>
> - Can I use the 'LIKE' keyword in Access 2k SQL statements? If so, what
> is the correct syntax both within the database directly and is ASP?
>
> Thanks.

[PW]

"Micromanaged" <[email protected]> wrote in message
news:ucb4$[email protected]..

> - Are wildcards allowed in Access 2k SQL statements? If so, what are
> they, and what, if anything, do I need to be aware of programmatically
> in ASP?
>
> - Can I use the 'LIKE' keyword in Access 2k SQL statements? If so, what
> is the correct syntax both within the database directly and is ASP?
>

Select * from myTable where FIELD like "%text%"


HTH,
PW

[Bullschmidt]

<<
- Are wildcards allowed in Access 2k SQL statements? If so, what are
they, and what, if anything, do I need to be aware of programmatically
in ASP?

- Can I use the 'LIKE' keyword in Access 2k SQL statements? If so, what
is the correct syntax both within the database directly and is ASP?

>>

Like Options:

All except blanks:
Like '%'

Starting with A:
Like 'A%'
Or could do this: Like 'a%'

A somewhere in the field:
Like '%A%'
Or could do this: Like '%a%'

One character an A or B or D:
Like '[A,B,D]'
Or could do this: Like '[a,b,d]'

One character A through C as the first character:
Like '[A-C]%'
Or could do this: Like '[a-c]%'

A through C as the 1st character and A through H as the 2nd character:
Like '[A-C][A-H]%'
Or could do this: Like '[a-c][a-h]%'

Starting with Sm, ending with th, and anything for the 3rd character:
Like 'SM?TH'
Or could do this: Like 'sm?th'

Digit for the 1st character:
Like '#%'
Or could do this: Like '[0-9]%'

Not in a range of letters:
Like '[!a-c]'

Not start with a range of letters:
Like '[!a-c]%'

Not start with a number:
Or could do this: Like '[!0-9]%'

Best regards,
J. Paul Schmidt, Freelance ASP Web Developer
[url]http://www.Bullschmidt.com[/url]
Classic ASP Design Tips, ASP Web Database Sample (Freely Downloadable)


*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Harag]

On Wed, 28 Jul 2004 09:31:33 +0800, "PW" <[email protected]>
wrote:

>
>"Micromanaged" <[email protected]> wrote in message
>news:ucb4$[email protected]..

>> - Are wildcards allowed in Access 2k SQL statements? If so, what are
>> they, and what, if anything, do I need to be aware of programmatically
>> in ASP?
>>
>> - Can I use the 'LIKE' keyword in Access 2k SQL statements? If so, what
>> is the correct syntax both within the database directly and is ASP?
>>

>
>
>
>Select * from myTable where FIELD like "%text%"
>

I wouldn't recommend using the "Select * from " - Name the columns
you want including in the result. By putting a * your making the DB
engine work harder, (just to save you some typing) First the engine
has to find out what columns are in a table then get the info. If you
tell it what columns you want this has 2 advantages.

1) The engine knows the columns so can just return the info
2) it will help you later on (or other coders) when you relook at your
code.

HTH

Al.

[Aaron [SQL Server MVP]]

> Select * from myTable where FIELD like "%text%"

Use ' not " for delimiting the string inside the SQL statement.

And don't use SELECT *

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)

[Micromanaged]

This is the error I get when I enter a% on the asp page:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
operator) in query expression '(CompanyName LIKE 'a%' ORDER BY
CompanyName'.
/lioa/srchrslts.asp, line 127

This is the code involved:

Dim objConn
Set objConn=Server.CreateObject("ADODB.Connection")
objConn.ConnectionString="DRIVER={Microsoft Access Driver (*.mdb)};" & _
"DBQ=" & DBPath

objConn.Open
Dim strSQL

If rsrch="CompanyName" Then
strSQL="SELECT Distinct CompanyName, AddressLine1, AddressLine2,
AddressLine3, " & _
"City, State, Zip, Country FROM Customers " & _
"WHERE (CompanyName LIKE '" & rcrtra & "' " & _
"ORDER BY CompanyName"
End If

Response.Write("<tr><td>" & objRS.Fields.Item(0).Value & "</td><td>" &
objRS.Fields.Item(1) & "</td><td>" & objRS.Fields.Item(2) & "</td><td>"
& objRS.Fields.Item(3) & "</td><td>" & objRS.Fields.Item(4) &
"</td></tr>")
objRS.MoveNext

Set objRS=Nothing
objConn.Close
Set objConn=Nothing

*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Aaron [SQL Server MVP]]

Well, you have an opening ( but not a closing ) ...

You probably could have caught this yourself, using:

Response.Write StrSQL

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)




"Micromanaged" <[email protected]> wrote in message
news:ey$[email protected]..

>
>
> This is the error I get when I enter a% on the asp page:
>
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
> operator) in query expression '(CompanyName LIKE 'a%' ORDER BY
> CompanyName'.
> /lioa/srchrslts.asp, line 127
>
> This is the code involved:
>
> Dim objConn
> Set objConn=Server.CreateObject("ADODB.Connection")
> objConn.ConnectionString="DRIVER={Microsoft Access Driver (*.mdb)};" & _
> "DBQ=" & DBPath
>
> objConn.Open
> Dim strSQL
>
> If rsrch="CompanyName" Then
> strSQL="SELECT Distinct CompanyName, AddressLine1, AddressLine2,
> AddressLine3, " & _
> "City, State, Zip, Country FROM Customers " & _
> "WHERE (CompanyName LIKE '" & rcrtra & "' " & _
> "ORDER BY CompanyName"
> End If
>
> Response.Write("<tr><td>" & objRS.Fields.Item(0).Value & "</td><td>" &
> objRS.Fields.Item(1) & "</td><td>" & objRS.Fields.Item(2) & "</td><td>"
> & objRS.Fields.Item(3) & "</td><td>" & objRS.Fields.Item(4) &
> "</td></tr>")
> objRS.MoveNext
>
> Set objRS=Nothing
> objConn.Close
> Set objConn=Nothing
>
> *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
> Don't just participate in USENET...get rewarded for it!

[Micromanaged]

That did the trick. I ran a response.write and noticed that I neglected
to put a closing ) on my SQL statement. Corrected that, and life works
now.

Thanks for the assistance.


*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!