Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > query to check if a certain value does exist in a field

Reply
Thread Tools

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

 
 
Guest
Posts: n/a
 
      12-21-2003
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?


 
Reply With Quote
 
 
 
 
Bob Barrows
Guest
Posts: n/a
 
      12-21-2003
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"


 
Reply With Quote
 
 
 
 
Guest
Posts: n/a
 
      12-22-2003
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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"
>
>



 
Reply With Quote
 
Bob Barrows
Guest
Posts: n/a
 
      12-22-2003
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"


 
Reply With Quote
 
Bob Barrows
Guest
Posts: n/a
 
      12-22-2003
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.


 
Reply With Quote
 
Guest
Posts: n/a
 
      12-22-2003
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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"
>
>



 
Reply With Quote
 
Ray at
Guest
Posts: n/a
 
      12-22-2003

<aa> wrote in message news:(E-Mail Removed)...
> 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


 
Reply With Quote
 
Bob Barrows
Guest
Posts: n/a
 
      12-22-2003
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"


 
Reply With Quote
 
aa
Guest
Posts: n/a
 
      12-23-2003
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$(E-Mail Removed)...
> 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?
>
>



 
Reply With Quote
 
Ray at
Guest
Posts: n/a
 
      12-23-2003
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" <(E-Mail Removed)> wrote in message
news:#$(E-Mail Removed)...
> 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$(E-Mail Removed)...
> > 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?
> >
> >

>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
javascript validation for a not required field, field is onlyrequired if another field has a value jr Javascript 3 07-08-2010 10:33 AM
Feed another field (or pop-up a choice) in JSP when a field isentered a certain value Sébastien de Mapias Java 5 05-26-2009 08:35 PM
Copy File Field Value to Dynamic File Field Value VUNETdotUS Javascript 25 11-10-2007 10:36 AM
field validation as float with certain integer and certain decimal SAN CAZIANO Javascript 8 10-15-2004 05:24 PM
Does my object exist? So why its HWND doesn't exist? That's a question... (CMonthCalCtrl control) LT C++ 7 07-25-2004 07:08 PM



Advertisments