G
Guest
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?
either "yes" or "not" which query would be the most effestive?
aa said: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?
aa said: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.
This should be:Bob said:Create a saved query called qCheckCol with this sql:
Select count(*) from table WHERE search_column = pSearchValue
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
aa said: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
aa said: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 said: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 searchedMS 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
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 -
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 said:aa said: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.
nothingI searchedMS site against "Microsoft Jet SQL Reference" and fount
The Office documentation does not appear to be available on the web. A quick
Google search turned these up:
http://www.devguru.com/Technologies/jetsql/quickref/jet_sql_intro.html
http://msdn.microsoft.com/library/en-us/dnacc2k/html/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-us/ado270/htm/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.
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 said:aa said: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.
nothingI searchedMS site against "Microsoft Jet SQL Reference" and fount
The Office documentation does not appear to be available on the web. A quick
Google search turned these up:
http://www.devguru.com/Technologies/jetsql/quickref/jet_sql_intro.html
http://msdn.microsoft.com/library/en-us/dnacc2k/html/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-us/ado270/htm/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.
Ray at said: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
smiley.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.recturnsqueryaresultset (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 ais uses
to reading
it
aa said: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?
Being unappreciative is a sin everywhere. I indeed was unappreciative
when I was loaded with free things I never ordered.
. 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.
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.