«

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

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" <> wrote in message
news:...
> 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"
>
>

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"

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.

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" <> wrote in message
news:...
> 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"
>
>

<aa> wrote in message news:...
> 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

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"

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:eKYE$...
> 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?
>
>

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" <> wrote in message
news:#$...
> 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:eKYE$...
> > 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?
> >
> >
>
>