query to check if a certain value does exist in a field

If I need to check if a certain value does exist in a field, and return
either "yes" or "not" which query would be the most effestive?

Jul 19 '05 #1

Bob Barrows

aa wrote:

If I need to check if a certain value does exist in a field, and
return either "yes" or "not" which query would be the most effestive?

What database? Jet? SQL Server? Something else? Never ask for query
assistance without telling us what database you are using.

Do you want to see if a certain record contains the value in the field? Or
do you want to see if ANY record contains that value?

And why do you care how much fun the query has at holiday time? ;-)
(OK, that was a dumb joke, but I could not let "effestive" simply pass
unignored <grin>)

Bob Barrows

--
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"

Jul 19 '05 #2

Sorry, Access 2000 - I thought that should be a core SQL independent of a
particular implementation.
I want to see if there is a certain value in a table column. So I pass this
value to a query and return Boolean yes or not.
If possible, I do not need a recordset.

ps "And why do you care how much fun the query has at holiday time? ;-)"
I am working trhe other way round - working on weekends plus Monadays and
having holiday on the weekdays

"Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message
news:eA*************@TK2MSFTNGP12.phx.gbl...

aa wrote:

If I need to check if a certain value does exist in a field, and
return either "yes" or "not" which query would be the most effestive?

What database? Jet? SQL Server? Something else? Never ask for query
assistance without telling us what database you are using.

Do you want to see if a certain record contains the value in the field? Or
do you want to see if ANY record contains that value?

And why do you care how much fun the query has at holiday time? ;-)
(OK, that was a dumb joke, but I could not let "effestive" simply pass
unignored <grin>)

Bob Barrows

--
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"

Jul 19 '05 #3

Bob Barrows

aa wrote:

Sorry, Access 2000 - I thought that should be a core SQL independent
of a particular implementation.
I want to see if there is a certain value in a table column.

I repeat: do you want to check ALL rows? or a particular row?

Oh, never mind. Here's a solution you can use in either case:

Create a saved query called qCheckCol with this sql:
Select count(*) from table WHERE search_column = pSearchValue

Then, in asp, do this:
dim conn, rs, SearchVal, bValExists
set conn=server.createobject("adodb.connection")
conn.open sConnectionString
set rs=server.createobject("adodb.recordset")
conn.qCheckCol SearchVal, rs
bValExists = cbool(rs(0).value)
rs.close:set rs=nothing
conn.close:set conn=nothing
response.write bValExists

If you only want to check a particular row, add more criteria to the WHERE
clause to identify the particular row you wish to check.

HTH,
Bob Barrows

--
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"

Jul 19 '05 #4

Bob Barrows

Bob Barrows wrote:

Create a saved query called qCheckCol with this sql:
Select count(*) from table WHERE search_column = pSearchValue

This should be:
Select count(*) from table WHERE search_column = [pSearchValue]

Sorry,
Bob
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Jul 19 '05 #5

Bob, I said, "in a table column", not in a cell.
The solution you are offering - this is how the thing is working for the
moment and I consider it cumbersome.
I wonder if there is more elegant way to achieve the same result

"Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message
news:eA*************@TK2MSFTNGP12.phx.gbl...

aa wrote:

If I need to check if a certain value does exist in a field, and
return either "yes" or "not" which query would be the most effestive?

What database? Jet? SQL Server? Something else? Never ask for query
assistance without telling us what database you are using.

Do you want to see if a certain record contains the value in the field? Or
do you want to see if ANY record contains that value?

And why do you care how much fun the query has at holiday time? ;-)
(OK, that was a dumb joke, but I could not let "effestive" simply pass
unignored <grin>)

Bob Barrows

--
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"

Jul 19 '05 #6

Ray at

<aa> wrote in message news:uL**************@TK2MSFTNGP11.phx.gbl...

Bob, I said, "in a table column", not in a cell.
The solution you are offering - this is how the thing is working for the
moment and I consider it cumbersome.
I wonder if there is more elegant way to achieve the same result

In case Bob isn't around, what cell? Are you wondering if a value exists in
a known column name, or if a column name exists in a known table name? I
think Bob's solution about looking for a value in a column would be fine.
Dim oADO, bExists
Set oADO = Server.CreateObject("ADODB.Connection")
bExists = oADO.Execute("SELECT COUNT(YourColumn) FROM YourTable WHERE
YourColumn='" & YourValue & "'").Fields.Item(0).Value > 0
oADO.Close : Set oADO = Nothing

That's the quick and dirty way. It just creates a single record with a
count of the number of times your value exists. If it doesn't exist, the
value is zero. If the returned value is > 0, then it exists somewhere.

To stretch the code out a bit, you can do:

Dim oADO, oRS, bExists
Set oADO = Server.CreateObject("ADODB.Connection")
oRS = oADO.Execute("SELECT COUNT(YourColumn) FROM YourTable WHERE
YourColumn='" & YourValue & "'")
bExists = oRS.Fields.Item(0).Value > 0
oRS.Close : Set oRS = Nothing
oADO.Close : Set oADO = Nothing
Ray at work

Jul 19 '05 #7

Bob Barrows

aa wrote:

Bob, I said, "in a table column", not in a cell.
The solution you are offering - this is how the thing is working for
the moment and I consider it cumbersome.
I wonder if there is more elegant way to achieve the same result

You've lost me. I suggest show us an example of what you are trying to do
instead of trying (unsuccessfully) to describe it. In tabular format, show
us a few rows of sample data and explain what you want a query to return
given that sample data.

Bob Barrows

--
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"

Jul 19 '05 #8

aa

I've got you now, gentlmen.
COUNT - that was the answer I was looking for.
Please do not waste your time on writing the whole code for me - I just need
to understand the principle.

What I want is:
I have a table containing data on people. I need to check if, say, a person
with SSN "000000000" , is there and return yes or no.

I now see that COUNT function in the statement:
SELECT COUNT (SSN) FROM table WHERE SNN="00000000"
counts number of occurences of "00000000" in column SSN.

I still do non understand how I do get the value returned by this function.
The book on SQL I have says (rather vaguely) that such an SQL a statemnt
"returns" a number of rows meeting the specified condition. But I cannot
understand how do I extract this number from the statemnt.

In Bob's example rs(0).value is "00000000", not the number of occurences of
"00000000" in column SSN, is it not ?

Or you mean that COUNT changes the nature of query completely, so that it
ONLY counts occurences and returns no recordset even if there are raws with
the specified value?

In Ray's example syntax used for the same purpose is different:
oRS.Fields.Item(0).Value
I have not met such thing before - is it a standard ASP3 syntax?


<aa> wrote in message news:eK**************@TK2MSFTNGP11.phx.gbl...

If I need to check if a certain value does exist in a field, and return
either "yes" or "not" which query would be the most effestive?

Jul 19 '05 #9

Ray at

Any of these would work:

Set oRS = oADO.Execute("SELECT COUNT(SSN) As TheCount FROM TheTable WHERE
SSN='00000000'")

The whole Recordset.Fields.Items(Index).Value is the absolutionist way of
doing, I'd guess you could say. VB* lets you bypass default properties of
an object, so the .fields.item and .value aren't required. (Some would
argue about the .value part, and I'd agree.) You can also use
Recordset.Fields.Item("ColumnName").Value (in this case, you named the
result "TheCount") I believe using the index value (the first column
selected is 0, the second is 1, and so on) is about 1/10000000th's of a
second faster. This is how I understand it anyway. If you use the index
value, there isn't any need to use the "As TheCount" in the query, because
the column returned in the recordset doesn't need to have a name.

Ray at home

"aa" <aa@virgin.net> wrote in message
news:#$**************@TK2MSFTNGP10.phx.gbl...

I've got you now, gentlmen.

I now see that COUNT function in the statement:
SELECT COUNT (SSN) FROM table WHERE SNN="00000000"
counts number of occurences of "00000000" in column SSN.

I still do non understand how I do get the value returned by this

function. The book on SQL I have says (rather vaguely) that such an SQL a statemnt
"returns" a number of rows meeting the specified condition. But I cannot
understand how do I extract this number from the statemnt.

In Bob's example rs(0).value is "00000000", not the number of occurences of "00000000" in column SSN, is it not ?

Or you mean that COUNT changes the nature of query completely, so that it
ONLY counts occurences and returns no recordset even if there are raws with the specified value?

In Ray's example syntax used for the same purpose is different:
oRS.Fields.Item(0).Value
I have not met such thing before - is it a standard ASP3 syntax?


<aa> wrote in message news:eK**************@TK2MSFTNGP11.phx.gbl...

If I need to check if a certain value does exist in a field, and return
either "yes" or "not" which query would be the most effestive?

Jul 19 '05 #10

Bob Barrows

aa wrote:

I've got you now, gentlmen.
COUNT - that was the answer I was looking for.
Please do not waste your time on writing the whole code for me - I
just need to understand the principle.

What I want is:
I have a table containing data on people. I need to check if, say, a
person with SSN "000000000" , is there and return yes or no.

I now see that COUNT function in the statement:
SELECT COUNT (SSN) FROM table WHERE SNN="00000000"
counts number of occurences of "00000000" in column SSN.

I still do non understand how I do get the value returned by this
function. The book on SQL I have says (rather vaguely) that such an
SQL a statemnt "returns" a number of rows meeting the specified
condition. But I cannot understand how do I extract this number from
the statemnt.

In Bob's example rs(0).value is "00000000", not the number of
occurences of "00000000" in column SSN, is it not ?
No. It contains a number which reflects the count of the records where SSN
contains "00000000".
Count(*) is very efficient, and should be used instead of Count(SSN)

Why haven't you simply opened you database in Access and tried this query?
You would see for yourself.

There is also a section in Access online help called "Microsoft Jet SQL
Reference". You should start reading it :-)

Or you mean that COUNT changes the nature of query completely, so
that it ONLY counts occurences and returns no recordset even if there
are raws with the specified value?
COUNT is an aggregate function which returns a single result based on an
aggregation of rows. Other aggregate functions include SUM, MAX, Min, and
others.

It returns a recordset containing the answer. In this case, it returns a
recordset containing one Field object in one record. The value of that Field
object is the answer returned by the Count aggregate function.


In Ray's example syntax used for the same purpose is different:
oRS.Fields.Item(0).Value
I have not met such thing before - is it a standard ASP3 syntax?

No. It is standard COM syntax.

A COM object is a code construct that can contain objects, collections of
objects, properties, and methods. All collections have certain interfaces to
allow you to move through the items in that collection. One interface is the
Item. It contains a pointer to one of the objects in the collection.

object.collection.Item

You can specify which object in the collection to refer to in several ways,
the most efficient of which is to use the index. The index is zero-based, so
to refer to the first item in the collection, you would use:

object.collection.Item(0)

and so on. Another way is to use the key of the collection, which is usually
a string identifying the object. It is used like this:
object.collection.Item("item_name")

In the current version of vbscript, Item is the default interface, so it
does not need to be specified. In .Net, there are no default interfaces and
properties, so many experts are advising people to explicitly write the
property/interface they wish to use in their code. However, there has been
quite a lot of code written in the last few decades where people have not
bothered to write the name of the interface/property when it's the default.
So this:
object.collection(0)
is equivalent to this:
object.collection.Item(0)

And this:
object.collection("item_name")
is equivalent to this:
object.collection.Item("item_name")

Anyways, it is your choice as to whether or not to explicitly name the
interface/property you wish to use.

An ADO recordset is a COM object. It contains objects, collections of
objects, properties, and methods. One of those collections is the Fields
collection which contains a number of Field COM objects. Each Field object
has several properties: Name, Value, Type, DefinedSize, etc.

There are several ways of specifying which object in a collection you wish
to reference. One way is to use its Name property, which is used as the Item
key:

rs.Fields.Item("field_name").Value

This will return the value contained in the Value property of the Field
object whose Name is "field_name" in the Fields collection of the Recordset
object pointed at by the variable "rs".

Value is the default propery of a Field object, so many people don't bother
specifying it (there can be problems if it is not specified, but most people
do not bother).

Fields is the default collection of the Recordset object, so again, most
people simply leave in out. So this:
rs("field_name")
is equivalent to this:
rs.Fields.Item("field_name").Value

I'm out of time. I think you have enough to chew on here.
Bob Barrows
--
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"

Jul 19 '05 #11

1. "Why haven't you simply opened you database in Access and tried this
query? You would see for yourself"

Because I am here not just to do a certain trick, but to understand how it
is working. That is why I am not asking to write a code for me.

2. "Why haven't you simply opened you database in Access and tried this
query? You would see for yourself."
Becuse I trust you and I see no need to run checks on you. I want you to
explain HOW is it working.

3. "There is also a section in Access online help called "Microsoft Jet SQL
Reference". You should start reading it :-)"

Is it necessary to be a smartarse?
Instead could you please provide a link to this resource? I searched MS site
against "Microsoft Jet SQL Reference" and fount nothing directly relevant.
Ideally iof you could let provide a link to the article about COUNT usage.

4. Are you saying that the word COUNT in
SELECT COUNT (SSN) FROM table WHERE SNN="00000000"
modifies the SELECT statemet so dramatically that it does not return a
recordset with the data, but returns just one integer which is wrapped into
a recordset object?

5. My original question was about avoiding overheads with creating and
destroying a recordset object.
Your variant does not avoid it. If the recordset object is still needed,
then there are other ways to get the same result
For example, evaluate rsObj.BOF or rsObj.EOF
or rsObj.RecordCount (although this one is not reliable)
Or to use the IF

6. There was no need for excurse into accesstin objects' properties - I am
familiar with the basics of OOP.
Al I needed to know was the structire of the ASP recordset object - thank
you for that.

My difficulty is in a diofferent field. In, say, VBscript, to assign a value
returned by a function you use
a=function()
I cannot understand how to assign a value returned by a function which is
part of an SQL statement, to a VBScript variable.

From the COUNT example I see that this value goes to a recordset.
What about the others - do they go to a recordset?


"Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message
news:e6**************@TK2MSFTNGP10.phx.gbl...

aa wrote:

I've got you now, gentlmen.
COUNT - that was the answer I was looking for.
Please do not waste your time on writing the whole code for me - I
just need to understand the principle.

What I want is:
I have a table containing data on people. I need to check if, say, a
person with SSN "000000000" , is there and return yes or no.

I now see that COUNT function in the statement:
SELECT COUNT (SSN) FROM table WHERE SNN="00000000"
counts number of occurences of "00000000" in column SSN.

I still do non understand how I do get the value returned by this
function. The book on SQL I have says (rather vaguely) that such an
SQL a statemnt "returns" a number of rows meeting the specified
condition. But I cannot understand how do I extract this number from
the statemnt.

In Bob's example rs(0).value is "00000000", not the number of
occurences of "00000000" in column SSN, is it not ?
No. It contains a number which reflects the count of the records where SSN
contains "00000000".
Count(*) is very efficient, and should be used instead of Count(SSN)

Why haven't you simply opened you database in Access and tried this query?
You would see for yourself.

There is also a section in Access online help called "Microsoft Jet SQL
Reference". You should start reading it :-)

Or you mean that COUNT changes the nature of query completely, so
that it ONLY counts occurences and returns no recordset even if there
are raws with the specified value?

COUNT is an aggregate function which returns a single result based on an
aggregation of rows. Other aggregate functions include SUM, MAX, Min, and
others.

It returns a recordset containing the answer. In this case, it returns a
recordset containing one Field object in one record. The value of that

Field object is the answer returned by the Count aggregate function.

In Ray's example syntax used for the same purpose is different:
oRS.Fields.Item(0).Value
I have not met such thing before - is it a standard ASP3 syntax?
No. It is standard COM syntax.

A COM object is a code construct that can contain objects, collections of
objects, properties, and methods. All collections have certain interfaces

to allow you to move through the items in that collection. One interface is the Item. It contains a pointer to one of the objects in the collection.

object.collection.Item

You can specify which object in the collection to refer to in several ways, the most efficient of which is to use the index. The index is zero-based, so to refer to the first item in the collection, you would use:

object.collection.Item(0)

and so on. Another way is to use the key of the collection, which is usually a string identifying the object. It is used like this:
object.collection.Item("item_name")

In the current version of vbscript, Item is the default interface, so it
does not need to be specified. In .Net, there are no default interfaces and properties, so many experts are advising people to explicitly write the
property/interface they wish to use in their code. However, there has been
quite a lot of code written in the last few decades where people have not
bothered to write the name of the interface/property when it's the default. So this:
object.collection(0)
is equivalent to this:
object.collection.Item(0)

And this:
object.collection("item_name")
is equivalent to this:
object.collection.Item("item_name")

Anyways, it is your choice as to whether or not to explicitly name the
interface/property you wish to use.

An ADO recordset is a COM object. It contains objects, collections of
objects, properties, and methods. One of those collections is the Fields
collection which contains a number of Field COM objects. Each Field object
has several properties: Name, Value, Type, DefinedSize, etc.

There are several ways of specifying which object in a collection you wish
to reference. One way is to use its Name property, which is used as the Item key:

rs.Fields.Item("field_name").Value

This will return the value contained in the Value property of the Field
object whose Name is "field_name" in the Fields collection of the Recordset object pointed at by the variable "rs".

Value is the default propery of a Field object, so many people don't bother specifying it (there can be problems if it is not specified, but most people do not bother).

Fields is the default collection of the Recordset object, so again, most
people simply leave in out. So this:
rs("field_name")
is equivalent to this:
rs.Fields.Item("field_name").Value

I'm out of time. I think you have enough to chew on here.
Bob Barrows
--
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"

Jul 19 '05 #12

Bob Barrows

aa wrote:

1. "Why haven't you simply opened you database in Access and tried
this query? You would see for yourself"

Because I am here not just to do a certain trick, but to understand
how it is working. That is why I am not asking to write a code for me.
My point was that doing it yourself would HELP you to understand much better
than 20 long-winded explanations from me or anyone else.


2. "Why haven't you simply opened you database in Access and tried
this query? You would see for yourself."
Becuse I trust you and I see no need to run checks on you. I want you
to explain HOW is it working.
I thought I did.
I learn best when I try things for myself ... see them in action.

3. "There is also a section in Access online help called "Microsoft
Jet SQL Reference". You should start reading it :-)"

Is it necessary to be a smartarse?
?????

If I was trying to be a smartarse, I would not have included the smiley. I
spent 20 min. of my time typing out that last reply, only to be called a
"smartarse"?!?!
I am finding it very hard to refrain from telling you to sod off at this
point.
Instead could you please provide a link to this resource?
Access online help. Open Access, press F1, go to the Contents tab, scroll
down till you see the node for Jet SQL reference, etc.
I searchedMS site against "Microsoft Jet SQL Reference" and fount nothing
The Office documentation does not appear to be available on the web. A quick
Google search turned these up:
http://www.devguru.com/Technologies/...sql_intro.html
http://msdn.microsoft.com/library/en.../acfundsql.asp
directly relevant. Ideally iof you could let provide a link to the
article about COUNT usage.

You are not paying me enough to hold your hand through all this. You can
find your own frickin' links. Or buy a book.

4. Are you saying that the word COUNT in
SELECT COUNT (SSN) FROM table WHERE SNN="00000000"
modifies the SELECT statemet so dramatically that it does not return a
recordset with the data, but returns just one integer which is
wrapped into a recordset object?
The SELECT statement is NOT modified. A SELECT statement ALWAYS recturns a
resultset (data in a tabular format). ADO receives the resultset and
transforms it into a recordset object.

A SELECT can return:
1. The contents of column in a table
2. The result of a function, either a scalar function that returns a result
for every row in the table, or an aggregate function. This result is
returned in a new column that does not exist in the source table. In fact,
you do not even need to use a FROM clause:

SELECT Date() as [Today]

will return a recordset with a single record containing a single field
(called Today since I used a column alias) containing the result of the
Date() function.

Again, try it. Experiment. Open a database in Access and use the Query
Builder to create a few queries. You'll see: data returned from a query is
always presented in a datasheet, which is the interface that Access uses to
present resultsets.

This is all covered in online help, and in any number of books about SQL.


5. My original question was about avoiding overheads with creating and
destroying a recordset object.
Your variant does not avoid it.
The only way to get data back from a Jet database is via a recordset.With
SQL Server, it is possible to create stored procedures with output
parameters that can return data without the overhead of a recordset.
If the recordset object is still
needed, then there are other ways to get the same result
For example, evaluate rsObj.BOF or rsObj.EOF
You'd rather scroll through all the records in a table instead of reading
the value in a single record? Very efficient. (now I'm being a "smartarse" -
deal with it)

6. There was no need for excurse into accesstin objects' properties -
I am familiar with the basics of OOP.
To put it bluntly: bullsh!t

If you were, then you would not have asked this: "I have not met such thing
before - is it a standard ASP3 syntax?"

Again, I spent all that time writing that, only to be told it wasn't needed?
I guess I can console myself with the thought that somebody else reading it
may find it useful ...
Al I needed to know was the structire of the ASP recordset object -
OK - one link (but only because it was handy):
http://msdn.microsoft.com/library/en...m/mdaobj01.asp
My difficulty is in a diofferent field. In, say, VBscript, to assign
a value returned by a function you use
a=function()
I cannot understand how to assign a value returned by a function
which is part of an SQL statement, to a VBScript variable.

From the COUNT example I see that this value goes to a recordset.
What about the others - do they go to a recordset?

See above.
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Jul 19 '05 #13

Hi, Bob, you do not have to get offended I've just returned the tone you
assumed to talk to me.
And I do not want you to spend your 20 min on explaining me things which I
did not asked and then expecting me praise you for that.
I just believe that it would be more productive to clear up as early as
possible what I need to be explained and what I don't, thus to use your time
more efficiently. I do appologize for saying something which could be
interpreted as an offence - that was not my intention.

Ragarding the way of learning you advocate - try-and-see - I am familiar
with this method from my scientific background.
This method is useful and the only method when you are studying an unknown
object, like, say, the Universe, which you consider as a black box, send
signals to it, receive the feedback, analyse it, and draw conclusions and
quess how it is working.
Here we are dealing with a well known object created by people and
documented.
The "try-and-see" method is very unefficient. Why I should treat this as a
black box and guess how it is functioning wheraas this should be the
information available off the shelf?
Your argument - "go and buy a book" - does not seem to be valid - it defies
the puposed of this news group.

Anyway thank you for this answer of yours - this is to the point and very
useful indeed. Although there are couple of things there which I would like
to clear up, but not today - it's Xmas eve.

Have a cozy Xmas and happy New Year.

"Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...

aa wrote:

1. "Why haven't you simply opened you database in Access and tried
this query? You would see for yourself"

Because I am here not just to do a certain trick, but to understand
how it is working. That is why I am not asking to write a code for me.
My point was that doing it yourself would HELP you to understand much

better than 20 long-winded explanations from me or anyone else.

2. "Why haven't you simply opened you database in Access and tried
this query? You would see for yourself."
Becuse I trust you and I see no need to run checks on you. I want you
to explain HOW is it working.
I thought I did.
I learn best when I try things for myself ... see them in action.

3. "There is also a section in Access online help called "Microsoft
Jet SQL Reference". You should start reading it :-)"

Is it necessary to be a smartarse?

?????

If I was trying to be a smartarse, I would not have included the smiley. I
spent 20 min. of my time typing out that last reply, only to be called a
"smartarse"?!?!
I am finding it very hard to refrain from telling you to sod off at this
point.

Instead could you please provide a link to this resource?

Access online help. Open Access, press F1, go to the Contents tab, scroll
down till you see the node for Jet SQL reference, etc.

I searchedMS site against "Microsoft Jet SQL Reference" and fount

nothing
The Office documentation does not appear to be available on the web. A quick Google search turned these up:
http://www.devguru.com/Technologies/...sql_intro.html
http://msdn.microsoft.com/library/en.../acfundsql.asp

directly relevant. Ideally iof you could let provide a link to the
article about COUNT usage.

You are not paying me enough to hold your hand through all this. You can
find your own frickin' links. Or buy a book.

4. Are you saying that the word COUNT in
SELECT COUNT (SSN) FROM table WHERE SNN="00000000"
modifies the SELECT statemet so dramatically that it does not return a
recordset with the data, but returns just one integer which is
wrapped into a recordset object?

The SELECT statement is NOT modified. A SELECT statement ALWAYS recturns a
resultset (data in a tabular format). ADO receives the resultset and
transforms it into a recordset object.

A SELECT can return:
1. The contents of column in a table
2. The result of a function, either a scalar function that returns a

result for every row in the table, or an aggregate function. This result is
returned in a new column that does not exist in the source table. In fact,
you do not even need to use a FROM clause:

SELECT Date() as [Today]

will return a recordset with a single record containing a single field
(called Today since I used a column alias) containing the result of the
Date() function.

Again, try it. Experiment. Open a database in Access and use the Query
Builder to create a few queries. You'll see: data returned from a query is
always presented in a datasheet, which is the interface that Access uses to present resultsets.

This is all covered in online help, and in any number of books about SQL.

5. My original question was about avoiding overheads with creating and
destroying a recordset object.
Your variant does not avoid it.
The only way to get data back from a Jet database is via a recordset.With
SQL Server, it is possible to create stored procedures with output
parameters that can return data without the overhead of a recordset.

If the recordset object is still
needed, then there are other ways to get the same result
For example, evaluate rsObj.BOF or rsObj.EOF

You'd rather scroll through all the records in a table instead of reading
the value in a single record? Very efficient. (now I'm being a

"smartarse" - deal with it)

6. There was no need for excurse into accesstin objects' properties -
I am familiar with the basics of OOP.
To put it bluntly: bullsh!t

If you were, then you would not have asked this: "I have not met such

thing before - is it a standard ASP3 syntax?"

Again, I spent all that time writing that, only to be told it wasn't needed? I guess I can console myself with the thought that somebody else reading it may find it useful ...

Al I needed to know was the structire of the ASP recordset object -

OK - one link (but only because it was handy):
http://msdn.microsoft.com/library/en...m/mdaobj01.asp

My difficulty is in a diofferent field. In, say, VBscript, to assign
a value returned by a function you use
a=function()
I cannot understand how to assign a value returned by a function
which is part of an SQL statement, to a VBScript variable.

From the COUNT example I see that this value goes to a recordset.
What about the others - do they go to a recordset?

See above.
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Jul 19 '05 #14

Ray at

set /p holiday="Enter your holiday name here: "

Sigh... Where's the %holiday% spirit? Bob's trying to help you and he put
some decent effort into a number of replies. Coming back all argumentative
and unappreciative is not a way to encourage future help.

Ray at work

<aa> wrote in message news:uH**************@TK2MSFTNGP09.phx.gbl...

Hi, Bob, you do not have to get offended I've just returned the tone you
assumed to talk to me.
And I do not want you to spend your 20 min on explaining me things which I
did not asked and then expecting me praise you for that.
I just believe that it would be more productive to clear up as early as
possible what I need to be explained and what I don't, thus to use your time more efficiently. I do appologize for saying something which could be
interpreted as an offence - that was not my intention.

Ragarding the way of learning you advocate - try-and-see - I am familiar
with this method from my scientific background.
This method is useful and the only method when you are studying an unknown
object, like, say, the Universe, which you consider as a black box, send
signals to it, receive the feedback, analyse it, and draw conclusions and
quess how it is working.
Here we are dealing with a well known object created by people and
documented.
The "try-and-see" method is very unefficient. Why I should treat this as a
black box and guess how it is functioning wheraas this should be the
information available off the shelf?
Your argument - "go and buy a book" - does not seem to be valid - it defies the puposed of this news group.

Anyway thank you for this answer of yours - this is to the point and very
useful indeed. Although there are couple of things there which I would like to clear up, but not today - it's Xmas eve.

Have a cozy Xmas and happy New Year.

"Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...

aa wrote:

1. "Why haven't you simply opened you database in Access and tried
this query? You would see for yourself"

Because I am here not just to do a certain trick, but to understand
how it is working. That is why I am not asking to write a code for me.

My point was that doing it yourself would HELP you to understand much

better

than 20 long-winded explanations from me or anyone else.

2. "Why haven't you simply opened you database in Access and tried
this query? You would see for yourself."
Becuse I trust you and I see no need to run checks on you. I want you
to explain HOW is it working.

I thought I did.
I learn best when I try things for myself ... see them in action.

3. "There is also a section in Access online help called "Microsoft
Jet SQL Reference". You should start reading it :-)"

Is it necessary to be a smartarse?

?????

If I was trying to be a smartarse, I would not have included the smiley. I spent 20 min. of my time typing out that last reply, only to be called a
"smartarse"?!?!
I am finding it very hard to refrain from telling you to sod off at this point.

Instead could you please provide a link to this resource?

Access online help. Open Access, press F1, go to the Contents tab, scroll down till you see the node for Jet SQL reference, etc.

I searchedMS site against "Microsoft Jet SQL Reference" and fount nothing

The Office documentation does not appear to be available on the web. A

quick

Google search turned these up:
http://www.devguru.com/Technologies/...sql_intro.html
http://msdn.microsoft.com/library/en.../acfundsql.asp

directly relevant. Ideally iof you could let provide a link to the
article about COUNT usage.

You are not paying me enough to hold your hand through all this. You can
find your own frickin' links. Or buy a book.

4. Are you saying that the word COUNT in
SELECT COUNT (SSN) FROM table WHERE SNN="00000000"
modifies the SELECT statemet so dramatically that it does not return a
recordset with the data, but returns just one integer which is
wrapped into a recordset object?

The SELECT statement is NOT modified. A SELECT statement ALWAYS recturns

a resultset (data in a tabular format). ADO receives the resultset and
transforms it into a recordset object.

A SELECT can return:
1. The contents of column in a table
2. The result of a function, either a scalar function that returns a

result

for every row in the table, or an aggregate function. This result is
returned in a new column that does not exist in the source table. In fact, you do not even need to use a FROM clause:

SELECT Date() as [Today]

will return a recordset with a single record containing a single field
(called Today since I used a column alias) containing the result of the
Date() function.

Again, try it. Experiment. Open a database in Access and use the Query
Builder to create a few queries. You'll see: data returned from a query is always presented in a datasheet, which is the interface that Access uses

to

present resultsets.

This is all covered in online help, and in any number of books about SQL.

5. My original question was about avoiding overheads with creating and
destroying a recordset object.
Your variant does not avoid it.

The only way to get data back from a Jet database is via a recordset.With SQL Server, it is possible to create stored procedures with output
parameters that can return data without the overhead of a recordset.

If the recordset object is still
needed, then there are other ways to get the same result
For example, evaluate rsObj.BOF or rsObj.EOF

You'd rather scroll through all the records in a table instead of reading the value in a single record? Very efficient. (now I'm being a

"smartarse" -

deal with it)

6. There was no need for excurse into accesstin objects' properties -
I am familiar with the basics of OOP.

To put it bluntly: bullsh!t

If you were, then you would not have asked this: "I have not met such

thing

before - is it a standard ASP3 syntax?"

Again, I spent all that time writing that, only to be told it wasn't

needed?

I guess I can console myself with the thought that somebody else reading

it

may find it useful ...

Al I needed to know was the structire of the ASP recordset object -

OK - one link (but only because it was handy):
http://msdn.microsoft.com/library/en...m/mdaobj01.asp

My difficulty is in a diofferent field. In, say, VBscript, to assign
a value returned by a function you use
a=function()
I cannot understand how to assign a value returned by a function
which is part of an SQL statement, to a VBScript variable.

From the COUNT example I see that this value goes to a recordset.
What about the others - do they go to a recordset?

See above.
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Jul 19 '05 #15

Ray, being argumentative is a big sin in the Army. Yet I see nothing wrong
about it in a discussion forum as arguments are indispensible part of a
discussion.
Being unappreciative is a sin everywhere. I indeed was unappreciative when I
was loaded with free things I never ordered.
And I see nothing wrong telling people not to waste time on unwanted things.

Yet I am appreciative regarding replies to the point. And I stress again
that Bob's explanation about "A SELECT can return" were very good indeed.
On the other hand a smartarse reply remains a smartarse reply even if you
add a smiley to it.

On the third hand, the reply to my comment "I am familiar with the basics of
OOP" was (I quote):

"To put it bluntly: bullsh!t If you were, then you would not have asked
this: "I have not met such thing
before - is it a standard ASP3 syntax?"

If we ignore to shear rudeness of it, it is simply incorrect. Because my
question above discloses my ignorance about the recordset object which I
never tried to hide. But this has nothing to do with OOP basics.

Also in my previous message I appologised for everything which might me
regareded as an offence.


"Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
news:el**************@TK2MSFTNGP09.phx.gbl...

set /p holiday="Enter your holiday name here: "

Sigh... Where's the %holiday% spirit? Bob's trying to help you and he put some decent effort into a number of replies. Coming back all argumentative and unappreciative is not a way to encourage future help.

Ray at work

<aa> wrote in message news:uH**************@TK2MSFTNGP09.phx.gbl...

Hi, Bob, you do not have to get offended I've just returned the tone you
assumed to talk to me.
And I do not want you to spend your 20 min on explaining me things which I

did not asked and then expecting me praise you for that.
I just believe that it would be more productive to clear up as early as
possible what I need to be explained and what I don't, thus to use your time

more efficiently. I do appologize for saying something which could be
interpreted as an offence - that was not my intention.

Ragarding the way of learning you advocate - try-and-see - I am familiar
with this method from my scientific background.
This method is useful and the only method when you are studying an unknown object, like, say, the Universe, which you consider as a black box, send
signals to it, receive the feedback, analyse it, and draw conclusions and quess how it is working.
Here we are dealing with a well known object created by people and
documented.
The "try-and-see" method is very unefficient. Why I should treat this as a black box and guess how it is functioning wheraas this should be the
information available off the shelf?
Your argument - "go and buy a book" - does not seem to be valid - it

defies

the puposed of this news group.

Anyway thank you for this answer of yours - this is to the point and very useful indeed. Although there are couple of things there which I would

like

to clear up, but not today - it's Xmas eve.

Have a cozy Xmas and happy New Year.

"Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...

aa wrote:
> 1. "Why haven't you simply opened you database in Access and tried
> this query? You would see for yourself"
>
> Because I am here not just to do a certain trick, but to understand
> how it is working. That is why I am not asking to write a code for me.
My point was that doing it yourself would HELP you to understand much

better

than 20 long-winded explanations from me or anyone else.
>
> 2. "Why haven't you simply opened you database in Access and tried
> this query? You would see for yourself."
> Becuse I trust you and I see no need to run checks on you. I want you > to explain HOW is it working.

I thought I did.
I learn best when I try things for myself ... see them in action.

>
> 3. "There is also a section in Access online help called "Microsoft
> Jet SQL Reference". You should start reading it :-)"
>
> Is it necessary to be a smartarse?

?????

If I was trying to be a smartarse, I would not have included the smiley. I spent 20 min. of my time typing out that last reply, only to be called

a "smartarse"?!?!
I am finding it very hard to refrain from telling you to sod off at this point.

> Instead could you please provide a link to this resource?

Access online help. Open Access, press F1, go to the Contents tab, scroll down till you see the node for Jet SQL reference, etc.

> I searchedMS site against "Microsoft Jet SQL Reference" and fount nothing

The Office documentation does not appear to be available on the web. A

quick

Google search turned these up:
http://www.devguru.com/Technologies/...sql_intro.html
http://msdn.microsoft.com/library/en.../acfundsql.asp

> directly relevant. Ideally iof you could let provide a link to the
> article about COUNT usage.
You are not paying me enough to hold your hand through all this. You can find your own frickin' links. Or buy a book.

>
> 4. Are you saying that the word COUNT in
> SELECT COUNT (SSN) FROM table WHERE SNN="00000000"
> modifies the SELECT statemet so dramatically that it does not return a > recordset with the data, but returns just one integer which is
> wrapped into a recordset object?

The SELECT statement is NOT modified. A SELECT statement ALWAYS recturns a

resultset (data in a tabular format). ADO receives the resultset and
transforms it into a recordset object.

A SELECT can return:
1. The contents of column in a table
2. The result of a function, either a scalar function that returns a result

for every row in the table, or an aggregate function. This result is
returned in a new column that does not exist in the source table. In fact, you do not even need to use a FROM clause:

SELECT Date() as [Today]

will return a recordset with a single record containing a single field
(called Today since I used a column alias) containing the result of

the Date() function.

Again, try it. Experiment. Open a database in Access and use the Query
Builder to create a few queries. You'll see: data returned from a query is always presented in a datasheet, which is the interface that Access

uses to

present resultsets.

This is all covered in online help, and in any number of books about SQL.

>
> 5. My original question was about avoiding overheads with creating

and > destroying a recordset object.
> Your variant does not avoid it.

The only way to get data back from a Jet database is via a

recordset.With SQL Server, it is possible to create stored procedures with output
parameters that can return data without the overhead of a recordset.

> If the recordset object is still
> needed, then there are other ways to get the same result
> For example, evaluate rsObj.BOF or rsObj.EOF

You'd rather scroll through all the records in a table instead of reading the value in a single record? Very efficient. (now I'm being a

"smartarse" -

deal with it)

>
> 6. There was no need for excurse into accesstin objects' properties - > I am familiar with the basics of OOP.

To put it bluntly: bullsh!t

If you were, then you would not have asked this: "I have not met such

thing

before - is it a standard ASP3 syntax?"

Again, I spent all that time writing that, only to be told it wasn't

needed?

I guess I can console myself with the thought that somebody else

reading it

may find it useful ...

> Al I needed to know was the structire of the ASP recordset object -

OK - one link (but only because it was handy):
http://msdn.microsoft.com/library/en...m/mdaobj01.asp

> My difficulty is in a diofferent field. In, say, VBscript, to assign
> a value returned by a function you use
> a=function()
> I cannot understand how to assign a value returned by a function
> which is part of an SQL statement, to a VBScript variable.
>
> From the COUNT example I see that this value goes to a recordset.
> What about the others - do they go to a recordset?
>
>

See above.
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Jul 19 '05 #16

Bob,
Many thanks for your explanantion about the way SELECT works - I could not
find these explanation elsewhere including the links you provided.

Yet one thing ramains unclear:
To
"there are other ways to get the same result For example, evaluate
rsObj.BOF or rsObj.EOF"

You replied:

"You'd rather scroll through all the records in a table instead of reading
the value in a single record?"

I thought that rsObj.BOF or rsObj.EOF get their value without scrolling
through all the records in a table.

The link you refered me to says:
"When you open a Recordset, the current record is positioned to the first
record (if any) and the BOF and EOF properties are set to False. If there
are no records, the BOF and EOF property settings are True."

Why are you mentioning scrolling?

Jul 19 '05 #17

Bob Barrows

aa wrote:

Bob,
Many thanks for your explanantion about the way SELECT works - I
could not find these explanation elsewhere including the links you
provided.
Hmm - I could have sworn ... after re-reading them, I see that you're right.
When I read them the first time, I was reading things that I knew into the
explanations in the documents. Here is a newsgroup posting by Joe Celko, who
is the author of several books about SQL. This may prove to be helpful
despite its genericity:

http://groups.google.com/groups?hl=e...ing.google.com

Yet one thing ramains unclear:
To
"there are other ways to get the same result For example, evaluate
rsObj.BOF or rsObj.EOF"

You replied:

"You'd rather scroll through all the records in a table instead of
reading the value in a single record?"

I thought that rsObj.BOF or rsObj.EOF get their value without
scrolling through all the records in a table.

The link you refered me to says:
"When you open a Recordset, the current record is positioned to the
first record (if any) and the BOF and EOF properties are set to
False. If there are no records, the BOF and EOF property settings are
True."

Why are you mentioning scrolling?

Because I thought you were suggesting opening a recordset on a table and
scrolling through it to find a record containing your search value. I see
now that my assumption was wrong (I think). If you were really suggesting
opening a recordset using a sql statement to filter the returned results,
and then simply checking its EOF property, then yes, what you say is
correct, to a point.

The Jet query engine is designed to optimize aggregate functions. However,
assuming that you will use a single field name in your select statement
rather than using "Select *", then the two methods will probably be
equivalent (or at least very close), unless the filter criteria has the
potential to return more than one record. Then Count(*) will be superior,
even if you use "TOP 1" to limit the records returned. Using TOP carries its
own overhead.

My objective is to return only the data I need to use (to minimize network
traffic), while making the database engine do the least amount of processing
as possible.

Bob Barrows

--
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"

Jul 19 '05 #18

Bob Barrows

You know, I was all prepared to come back to this and carry on this really
intense argument, but, now, i find I cannot muster the sense of outrage I
felt the other day. After re-reading these posts a couple times, I simply
cannot believe my over-reaction. So I apologize as well.

aa wrote:

Being unappreciative is a sin everywhere. I indeed was unappreciative
when I was loaded with free things I never ordered.
I do not feel these newsgroups would be anywhere near as useful as they are
if people did not feel free to weigh in with advice that may not have been
solicited in the questions posted. I know I got my best help when people
went beyond what I was asking and answered the question they thought I
should be asking. And that is how I try to operate now.
. On the other hand a smartarse reply remains a smartarse
reply even if you add a smiley to it.
I've been in newgroups for many years, and even still, I sometimes forget
how easy it is to read unintended meanings into things. I should have let
this pass.

On the third hand, the reply to my comment "I am familiar with the
basics of OOP" was (I quote):

"To put it bluntly: bullsh!t If you were, then you would not have
asked this: "I have not met such thing
before - is it a standard ASP3 syntax?"
Damn! i wish I could take back that comment, but ...

If we ignore to shear rudeness of it, it is simply incorrect. Because
my question above discloses my ignorance about the recordset object
which I never tried to hide. But this has nothing to do with OOP
basics.

I have to disagree. I can't base my replies on what I think you may know, I
can only base them on what is said in the email. Your question implied a
lack of knowledge of the Item keyword, did it not? Item is not an ADO
recordset attribute. It is an attribute of all collections. That is why I
thought it might be necessary to cover the basics.

Bob Barrows
--
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"

Jul 19 '05 #19

Thanks, Bob.
I should admit that I myself was not quite reasonable. Sorry for that. And
again thanks for your comments of which I made greate use
"Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message
news:%2***************@tk2msftngp13.phx.gbl...

You know, I was all prepared to come back to this and carry on this really
intense argument, but, now, i find I cannot muster the sense of outrage I
felt the other day. After re-reading these posts a couple times, I simply
cannot believe my over-reaction. So I apologize as well.

aa wrote:

Being unappreciative is a sin everywhere. I indeed was unappreciative
when I was loaded with free things I never ordered.
I do not feel these newsgroups would be anywhere near as useful as they

are if people did not feel free to weigh in with advice that may not have been
solicited in the questions posted. I know I got my best help when people
went beyond what I was asking and answered the question they thought I
should be asking. And that is how I try to operate now.

. On the other hand a smartarse reply remains a smartarse
reply even if you add a smiley to it.
I've been in newgroups for many years, and even still, I sometimes forget
how easy it is to read unintended meanings into things. I should have let
this pass.

On the third hand, the reply to my comment "I am familiar with the
basics of OOP" was (I quote):

"To put it bluntly: bullsh!t If you were, then you would not have
asked this: "I have not met such thing
before - is it a standard ASP3 syntax?"

Damn! i wish I could take back that comment, but ...

If we ignore to shear rudeness of it, it is simply incorrect. Because
my question above discloses my ignorance about the recordset object
which I never tried to hide. But this has nothing to do with OOP
basics.

I have to disagree. I can't base my replies on what I think you may know,

I can only base them on what is said in the email. Your question implied a
lack of knowledge of the Item keyword, did it not? Item is not an ADO
recordset attribute. It is an attribute of all collections. That is why I
thought it might be necessary to cover the basics.

Bob Barrows
--
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"

Jul 19 '05 #20

Thanks, Bob

Jul 19 '05 #21