[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

check a db for more than one occurance?

Posted on 2005-04-27
7
Medium Priority
?
236 Views
Last Modified: 2011-09-20
I need a little code that will check a db called configs for a field within the db called config.

lets say I am checking the DB for the value "abc" in the field config withing table configs.  I want to know if there is 0 hits, 1 hit or more than 1 hit.

How is this most efficiently done?
0
Comment
Question by:pinkstonm
  • 4
  • 2
7 Comments
 
LVL 6

Expert Comment

by:Dirar Abu Kteish
ID: 13877262
if you want to use ASP, you can submit the value you want to check, let's say to the same page,  at the top of the page you can write:
if Request("CHECKVAL") then
        found = false
         query= "select 1 from tablename where filed = " & request("CHECKVAL")
         execute query
         if not recordset .eof then found = true
end if

then you can ask about the found variable value, this has to be on the server side, but you can still use HTTP Request, for info check : http://jibbering.com/2002/4/httprequest.html
           
0
 

Author Comment

by:pinkstonm
ID: 13877310
Well I am actually at that point already in  a read loop in the db Reading records and as I read a record from one table I want to see:

A. if it exists in another
B. if it is just once
C. if it is multiple times
0
 
LVL 6

Expert Comment

by:Dirar Abu Kteish
ID: 13877422
ok, then do somthing like this:
loop
   doing something...

   here you have the value you want to check,
   write this query: " select count(fieldtocheck) as TimesExist from configwhere fieldtocheck = " & valueToCheck
  after executing the quert it will return 1 result and the result will be in a field called TimesExist, then you ask about this value if it's 0, 1 or more.

  do something...

continue looping

p.s "fieldtocheck" is here "config" and "configs" is the table I gess
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 6

Expert Comment

by:Dirar Abu Kteish
ID: 13877457
" select count(fieldtocheck) as TimesExist from table where fieldtocheck = " & valueToCheck
0
 

Author Comment

by:pinkstonm
ID: 13877970
select count(capability_configurations.capability_configuration) as TimesExist, capability_configurations.*, config2packg.*, packages.offering, packages.serviceline, packages.SDA from ((capability_configurations INNER JOIN config2packg ON capability_configurations.Capability_Configuration = CONFIG2PACKG.Capability_Configuration) LEFT JOIN packages ON packages.package = CONFIG2PACKG.package) where capability_configuration = 'Accounts Receivable - allocation of receipts'
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Cannot group on fields selected with '*' (capability_configurations).

/sitelist/capacity/inc_configs.asp, line 91

0
 
LVL 2

Expert Comment

by:Coolhand2120
ID: 13882899
Write out all the columns, add any column in your query to the group by statment.  Sounds like a lot of work, and it is.  But thats the only way you can group columns.  Don't accept this as an answer.

-Coolhand
0
 
LVL 6

Accepted Solution

by:
Dirar Abu Kteish earned 200 total points
ID: 13883511
don't do it in one query, but use two, the first one to get all the data without the count then comes the count query:
execute(select capability_configurations.*, config2packg.*, packages.offering, packages.serviceline, packages.SDA from ((capability_configurations INNER JOIN config2packg ON capability_configurations.Capability_Configuration = CONFIG2PACKG.Capability_Configuration) LEFT JOIN packages ON packages.package = CONFIG2PACKG.package)
)
from this you get a recordset1, from this recordset you get 'Accounts Receivable' and allocation of receipts'
no you can make the subtraction in the script, or in the query, but better to do it in the script. so val = CINT(recordset1("Accounts Receivable")) -  CINT(recordset1("allocation of receipts"))  - if you get these values from the query -
Select count(Select count(capability_configurations.capability_configuration) as TimesExist from capability_configurations where capability_configuration =  val
execute second query and check count
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question