473,698 Members | 1,884 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

counting records in a recordset

mgm
hello,

I have a query that is supposed to return only 1 record, however I recently
found that because of an error in the database it can return more than 1.
So what I need to do is capture if it is more than 1 record and alert the
user to contact the database administrator. How can I get the count of
records returned by a recordset? Is there no way besides looping through
with a counter variable?

Jul 21 '05 #1
8 1784
mgm wrote:
hello,

I have a query that is supposed to return only 1 record, however I
recently found that because of an error in the database it can return
more than 1. So what I need to do is capture if it is more than 1
record and alert the user to contact the database administrator. How
can I get the count of records returned by a recordset? Is there no
way besides looping through with a counter variable?


A better plan is to set up a unique index or primary key so that the
duplicates cannot occur. I cannot get into specifics because you did not
specify your database type and version, as well as the columns in your table
that are supposed to identify a unique record.

In the meantime, a grouping query will reveal the duplicate records:

select col1,col2
from tablename
group by col1,col2
having count(*) > 1

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 21 '05 #2
You could use methods decribed here:
http://www.aspfaq.com/show.asp?id=2193.

Or you could use .GetRows on your recordset and take the UBound of dimension
2 and add 1 to it.

Or you could fix the "error" in the database!

Ray at work

"mgm" <ma********@hot mail.com> wrote in message
news:OF******** ******@TK2MSFTN GP10.phx.gbl...
hello,

I have a query that is supposed to return only 1 record, however I
recently
found that because of an error in the database it can return more than 1.
So what I need to do is capture if it is more than 1 record and alert the
user to contact the database administrator. How can I get the count of
records returned by a recordset? Is there no way besides looping through
with a counter variable?

Jul 21 '05 #3
mgm
Well, the error in the database was someone's input error so I need to catch
the error because it could happen again, can't fix it (plus it's not my
database).

But thanks for the info, i was wondering why recordcount didn't work :)

mgm

"Ray Costanzo [MVP]" <my first name at lane 34 dot commercial> wrote in
message news:O5******** ******@TK2MSFTN GP11.phx.gbl...
You could use methods decribed here:
http://www.aspfaq.com/show.asp?id=2193.

Or you could use .GetRows on your recordset and take the UBound of dimension 2 and add 1 to it.

Or you could fix the "error" in the database!

Ray at work

"mgm" <ma********@hot mail.com> wrote in message
news:OF******** ******@TK2MSFTN GP10.phx.gbl...
hello,

I have a query that is supposed to return only 1 record, however I
recently
found that because of an error in the database it can return more than 1. So what I need to do is capture if it is more than 1 record and alert the user to contact the database administrator. How can I get the count of
records returned by a recordset? Is there no way besides looping through with a counter variable?


Jul 21 '05 #4
mgm
Sorry I didn't explain thoroughly before. The SQL database is not returning
"duplicate" rows, just 2 rows. My query is searching for a serial number
that is within a specific range - this serial number range should only exist
once in the database, but somehow, because of someone else's input error I
have overlapping ranges which should never happen. This is an adhoc
database that is fed by other databases, used by many many people for our
own reporting purposes.

I was just thinking recordcount should work somehow, but I guess it doesn't
in my case.

Thanks Bob!

"Bob Barrows [MVP]" <re******@NOyah oo.SPAMcom> wrote in message
news:uu******** ******@tk2msftn gp13.phx.gbl...

A better plan is to set up a unique index or primary key so that the
duplicates cannot occur. I cannot get into specifics because you did not
specify your database type and version, as well as the columns in your table that are supposed to identify a unique record.

In the meantime, a grouping query will reveal the duplicate records:

select col1,col2
from tablename
group by col1,col2
having count(*) > 1

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 21 '05 #5
Recordcount will work if you specify the proper type of cursor (the default
cursortype will not produce a bookmarkable recordset). If you specify a
client-side cursor, recordcount will work (do you have the ADO documentation
handy? If not, go to msdn.microsoft. com/library and drill down into the Data
Access node in the TOC)

I am positive that there is a way to build a query that will give you your
overlapping ranges. Since this is a SQL database (MS SQL Server, I assume -
what version?), I am sure it is possible to use either constraints or a
trigger to prevent the invalid data. It is probably possible to prevent the
invalid entry in your stored procedure, or if you aren't using a stored
procedure, in your vbscript code. You should not resort to checking the data
after it is entered using an expensive client-side cursor.

Bob Barrows

mgm wrote:
Sorry I didn't explain thoroughly before. The SQL database is not
returning "duplicate" rows, just 2 rows. My query is searching for a
serial number that is within a specific range - this serial number
range should only exist once in the database, but somehow, because of
someone else's input error I have overlapping ranges which should
never happen. This is an adhoc database that is fed by other
databases, used by many many people for our own reporting purposes.

I was just thinking recordcount should work somehow, but I guess it
doesn't in my case.

Thanks Bob!

"Bob Barrows [MVP]" <re******@NOyah oo.SPAMcom> wrote in message
news:uu******** ******@tk2msftn gp13.phx.gbl...

A better plan is to set up a unique index or primary key so that the
duplicates cannot occur. I cannot get into specifics because you did
not specify your database type and version, as well as the columns
in your table that are supposed to identify a unique record.

In the meantime, a grouping query will reveal the duplicate records:

select col1,col2
from tablename
group by col1,col2
having count(*) > 1

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.


--
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 21 '05 #6
mgm
Well this is how it works: the data is entered into a database somewhere, I
have no access to it, that data is then replicated to another database that
I have limited access to but can't change anything there, that is then
replicated to another database by my boss, it is that data that I have to
work with. Right now I simply want to capture when there is this overlap so
I can let my boss know. I'll just display the information and contact
information to the user. I have figured how I can do that now, I will just
use getrows() method. I will mention it to my boss that this is happening
so that maybe the error can be caught by his replication -- he's away on
vacation now.

I do not know how to find the version of the database that I have limited
access to, but the version we are using is SQL Server Enterprise Edition
8.00.760(sp3).

I will have to read up on cursors however, I've read before but didn't quite
understand, thanks.

I was wondering if you may have an answer to another question I posted in
inetexplorer.sc ripting (no one is answering it there, maybe wrong
newsgroup?) This is the text from that posting:
---
I have a intranet web app. I built at work. On one person's machine, when
they hit the back button after filling a form and submitting, they get the
warning 'page has expired...hit refresh, etc.' and then the refresh
requeries the database instead of just displaying the data in the form as it
was.

This doesn't happen on my machine. I searched knowledgebase and the other
person does not have "Do not
save encrypted pages to disk" checked and has IE
6.0.2900.2180.x psp_sp2_rtm.040 803-2158. I have 6.0.3790.0 update version
3283 (i'm running server 2003).

Does anyone know how to fix the problem on the other person's machine? I
want anyone who uses it to be able to enter data, submit it, go back and
make a few changes and submit it again as a new record, so they don't have
to refill a bunch of data that should still be there.
Thanks!

"Bob Barrows [MVP]" <re******@NOyah oo.SPAMcom> wrote in message
news:eP******** *****@TK2MSFTNG P12.phx.gbl...
Recordcount will work if you specify the proper type of cursor (the default cursortype will not produce a bookmarkable recordset). If you specify a
client-side cursor, recordcount will work (do you have the ADO documentation handy? If not, go to msdn.microsoft. com/library and drill down into the Data Access node in the TOC)

I am positive that there is a way to build a query that will give you your
overlapping ranges. Since this is a SQL database (MS SQL Server, I assume - what version?), I am sure it is possible to use either constraints or a
trigger to prevent the invalid data. It is probably possible to prevent the invalid entry in your stored procedure, or if you aren't using a stored
procedure, in your vbscript code. You should not resort to checking the data after it is entered using an expensive client-side cursor.

Bob Barrows

mgm wrote:
Sorry I didn't explain thoroughly before. The SQL database is not
returning "duplicate" rows, just 2 rows. My query is searching for a
serial number that is within a specific range - this serial number
range should only exist once in the database, but somehow, because of
someone else's input error I have overlapping ranges which should
never happen. This is an adhoc database that is fed by other
databases, used by many many people for our own reporting purposes.

I was just thinking recordcount should work somehow, but I guess it
doesn't in my case.

Thanks Bob!

"Bob Barrows [MVP]" <re******@NOyah oo.SPAMcom> wrote in message
news:uu******** ******@tk2msftn gp13.phx.gbl...

A better plan is to set up a unique index or primary key so that the
duplicates cannot occur. I cannot get into specifics because you did
not specify your database type and version, as well as the columns
in your table that are supposed to identify a unique record.

In the meantime, a grouping query will reveal the duplicate records:

select col1,col2
from tablename
group by col1,col2
having count(*) > 1

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.


--
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 21 '05 #7
mgm wrote:
Well this is how it works: the data is entered into a database
somewhere, I have no access to it, that data is then replicated to
another database that I have limited access to but can't change
anything there, that is then replicated to another database by my
boss, it is that data that I have to work with. Right now I simply
want to capture when there is this overlap so I can let my boss know.
I'll just display the information and contact information to the
user. I have figured how I can do that now, I will just use
getrows() method. I will mention it to my boss that this is
happening so that maybe the error can be caught by his replication
-- he's away on vacation now.

I do not know how to find the version of the database
I sometimes use "database" as shorthand for "rdbms" or "database server"
that I have
limited access to, but the version we are using is SQL Server
Enterprise Edition
8.00.760(sp3).

I will have to read up on cursors however, I've read before but
didn't quite understand, thanks.
Don't misunderstand: I was not talking about T-SQL cursors. I was referring
to the cursorlocation and cursortype properties of ADO cursors. GetRows is a
good method for both determining the record count, and displaying your data.
See:
http://www.aspfaq.com/show.asp?id=2467


I was wondering if you may have an answer to another question I
posted in inetexplorer.sc ripting (no one is answering it there, maybe
wrong newsgroup?) This is the text from that posting:
---
I have a intranet web app. I built at work. On one person's machine,
when they hit the back button after filling a form and submitting,
they get the warning 'page has expired...hit refresh, etc.' and then
the refresh requeries the database instead of just displaying the
data in the form as it was.


AFAIK, there is no way to prevent this security message from occurring.

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 21 '05 #8
Try adding TOP 1 to your query, e.g.

select TOP 1 field1 from table1

--
Ben Strackany
www.developmentnow.com
"mgm" <ma********@hot mail.com> wrote in message
news:OF******** ******@TK2MSFTN GP10.phx.gbl...
hello,

I have a query that is supposed to return only 1 record, however I recently found that because of an error in the database it can return more than 1.
So what I need to do is capture if it is more than 1 record and alert the
user to contact the database administrator. How can I get the count of
records returned by a recordset? Is there no way besides looping through
with a counter variable?

Jul 21 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
3225
by: shank | last post by:
I have a recordset that contains multiple records of product a user is purchasing. For clarity, I converted the recordset fields to variables. I need to take that entire recordset and insert it into another table on a remote server. The below code only inserts 1 record. How do I change the code to get all records inserted? thanks! <% Dim DataConn2 Set DataConn2 = Server.CreateObject("ADODB.Connection")
1
1926
by: Samuel Hon | last post by:
I seem to remember reading many moons ago about a function where you can retrieve a count of the last recordset you opened. For example: I've got a stored procedure that returns a recordset using the TOP 10 so I only get the top 10 records. I need to know the recordcount but I dont want to reuse the SELECT statement because its quite complex. Any ideas?
13
3469
by: Jan | last post by:
Hi I have a database that I use to keep track of the sales promotions that we send to companies. I normally send a mailing based on a subset of the companies in the database (found using the query: QryICTMassDistribution3) , I then use a form and the code below to create a new record in the corrispondence table to show what corrispondence has been sent to various companies.
2
4341
by: mattytee123 | last post by:
I have about 20 tables, of which I would like to do a union query and count of how many of each different code there is? The simplified verson of the table is structured like this. Code Count 1234 1 2468 1 1234 1 2468 1
13
3165
by: Regnab | last post by:
The problem I'm having is the rsa.RecordCount always returns a '1' when I know there should be about 1600 records returned. I read somewhere that one should use '.MoveLast' before recordcount but that returns ar error 'Invalid Operation'. And when I do the bottom loop, the counter happily clicks over the right number of times. Me no understand... I've got the following code (this is just a simplified example - I want to use the...
2
6334
by: allyn44 | last post by:
Hello, I have built a serch form for users to edit records. I only want them to pull up the record they need, and I want to check for nulls. There should not be dupes becasue the underlying tables are indexed. So the user will pull up either one record or none. Here is my problem: I pass the form name through a tmpvariable and ther other search parameters through unbound fields on an unbound form--this works fine unless the form opens...
10
6725
by: Robert | last post by:
How do you get an accurate count of the number of records returned from a query when using linked tables. I have an access 2003 database as a front end to another access 2003 database that contains only tables. I have linked the tables for the front end to the back end database. I am trying to set the recordsource of a form to a query established by the user to narrow the scope but I don't want to display the form if there are no...
6
11787
by: MLH | last post by:
If you're only furnished the table name of a table in your database, what can I do in VBA to count tablle records in a given table? Seeking simplest method. In other words, I don't have the name of a field to furnish to DLookUp - all I have is a table name.
3
1883
by: JacobZ | last post by:
Hey all, I've been struggling with this problem for a few days now. The table looks like this T_PROJECT_FILES fields: projectId, unitId Now, I want to count the number of records with different values in unitId, but with a specific value (e.g. 1) in projectId. The value of 'projectId' is coming from another form using e.g. Forms!F_Start.lblActiveProjectId.caption
0
8668
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8597
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9148
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9012
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8884
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8855
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7708
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
2
2319
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1992
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.