Returning Number Of Records Found In Query

Jan 24, 2008

I am trying to return the number of records found by the query but keep seeing -1 in label1. This query should return many records.

         sub findcustomers(sender as object,e as eventargs)
            dim connection1 as sqlconnection=new sqlconnection(...)
            dim q1 as string="select * from tblcustomers where store='65'"
            dim command1 as sqlcommand=new sqlcommand(q1,connection1)
            dim a as integer
            command1.connection.open()
            a=command1.executenonquery()
            label1.text=a.tostring()
            command1.connection.close()
         end sub

What am I doing wrong?

View 8 Replies


ADVERTISEMENT

Returning Limited Number Of Records!

Jul 8, 2004

I am using ORDER BY NEWID() to return random record from sql database. how do i go about returning only 5 random records instead of all records.

Thanks.

View 2 Replies View Related

Stored Proc Returning Dataset && Number Of Records Not Working

Jul 7, 2004

Hi

I've created a sproc in SQL2000 that returns a dataset from a temp table & the number of records it's returning as an output parameter, although I can't seem to retrieve the value it's returning in asp.net although I get the dataset ok.

This is my sproc
create procedure return_data_and_value
@return int output
as
set nocount on
...
...
select * from #Table
select @return = count(*) from #Table
drop table #Table
go

This is asp.net code

Dim nRecords as Int32
Dim cmd As SqlCommand = New SqlCommand("return_data_and_value", conn)
cmd.CommandType = CommandType.StoredProcedure

Dim prm As SqlParameter = New SqlParameter("@return", SqlDbType.Int)
prm.Direction = ParameterDirection.Output
cmd.Parameters.Add(prm)

conn.Open()

dr = cmd.ExecuteReader

nRecords = convert.int32(cmd.parameters(@return).value)

conn.close


Thanks
Lbob

View 1 Replies View Related

Returning A Row Number In A Query

Oct 24, 2006

how can i get a row number in a query in ms/sql

View 7 Replies View Related

Returning ALL Records In A Query

Aug 18, 2006

I'm building a db to collect equip fault data in SQL 2005 and need to modify my query to be able to select/display "ALL" records. I'm currently using a sp to assign a shift to each record and then have a query to select all records by shift. I'd like to add an option to the query to be able to select ALL records, regardless of shift. I've included the sp & query I am currently using. Any help would be appreciated.

Thanks

ALTER PROCEDURE [dbo].[p_dtu_Store_Line_Fault_Data]
-- Add the parameters for the stored procedure here
@AssetID int,
@Timestamp datetime,
@FaultCode int,
@State int
AS
BEGIN
SET NOCOUNT ON;
IF @State = 3
BEGIN
INSERT LineFaultData (FaultCode, AssetID, StartTime, Duration, Shift)
VALUES (@FaultCode, @AssetID, @Timestamp, 0,
CASE WHEN DATEPART(hh,@Timestamp) BETWEEN 7 AND 14 THEN 'DAYS'
WHEN DATEPART(hh,@Timestamp) BETWEEN 15 AND 22 THEN 'AFTERNOONS'
ELSE 'NIGHTS'
END)
END

IF @State <> 3
BEGIN
DECLARE @Count int
SET @Count = (SELECT Count(*) FROM LineFaultData WHERE AssetID = @AssetID AND Duration = 0)
IF @Count <> 0
BEGIN
DECLARE @StartTime datetime
SET @StartTime = (SELECT Top 1 StartTime FROM LineFaultData WHERE AssetID = @AssetID and Duration = 0)
UPDATE LineFaultData
SET Duration = DateDiff(s,@StartTime, @Timestamp)
WHERE AssetID = @AssetID and Duration = 0 and StartTime = @StartTime

END
END

END




SELECT TOP (1000) dbo.LineFaultDescription.Station, dbo.LineFaultData.StartTime, dbo.LineFaultData.Duration, dbo.LineFaultDescription.FaultDescription,
dbo.LineFaultDescription.FaultCategory, dbo.LineFaultData.Shift
FROM dbo.LineFaultDescription INNER JOIN
dbo.LineFaultData ON dbo.LineFaultDescription.FaultCode = dbo.LineFaultData.FaultCode AND
dbo.LineFaultDescription.AssetID = dbo.LineFaultData.AssetID
and (StartTime < '{@End Date}' and StartTime > '{@Start Date}')

WHERE (dbo.LineFaultData.AssetID = {Asset_ID})
AND (dbo.LineFaultData.Shift = '{@Shift}')
ORDER BY dbo.LineFaultData.StartTime DESC

View 4 Replies View Related

Query Returning 1.7million Records Slow

Nov 23, 2005

Hi,I have a sql server database with 1.7 million records in a table, withabout 30 fieldsWhen I run select * from tablename it can take over 5 minutes.How can I get this time down or is it normal?ThanksJerry

View 4 Replies View Related

Restrict The Number Of Records From Query

Feb 9, 2004

Hi,

ive a table of over 90,000 records . is ther any method for restricting the number of records returned by a SELECT query

i want to implement a query system which will give me the first 50 records, next 50 ,... and so on


can ne body help ?

View 4 Replies View Related

Transact SQL :: How To Return Number Of Records In Query As If TOP Was Not Used

Jul 21, 2015

What I would like to do is to have a TSQL Select return the number of records in the Result as if TOP (n) had not been used. Example:I have a table called Orders containing more than 1.000 records with OrderDate = '2015/07/21' and my client application has a threshold for returning records at 100  and therefore the TSQL would look like

SELECT TOP (100) * FROM Orders Where OrderDate = '2015/07/21'  ORDER by OrderTime Desc

Now I would like to "tell" the client that only 100 of 1.000 records are shown in the client application grid. Is there a way to return a value indicating that if TOP (100) had not been used the resultset would have been 1.000. I know I could create the same TSQL using COUNT() (SELECT COUNT(*) FROM Orders Where OrderDate = '2015/07/21'  ORDER by OrderTime Desc) and return that in a variable in the SELECT statement or even creating the COUNT() as a subquery and return it as a column, but I would like to avoid running multiple TSQL's. Since SQL Server already needs to select the entire recordset and sort it (ORDER BY) and return only the first 100 the total number of records in the initial snapshot must somehow be available.

View 6 Replies View Related

How To Get Number Of Records A Query Has Returned Using SqlDataSource Control?

May 17, 2007

Hi,
I'm using SqlDataSource control.
Is there a way to know how many records a query has returned?

View 3 Replies View Related

How Display Number Records In Query Designer VisualStudio.NET

Jul 23, 2005

I'm using the query desinger in ASP.NET , however the number of recordsin the resultset are not displaying, so I cut and paste it into Queryanalyzer which is silly.How do I set this in the output window, or result grid?Thanks Moe

View 1 Replies View Related

SQL 2012 :: Query To Understand Names Of All Available Tables / Number Of Records

Aug 31, 2014

SQL query to understand the names of all the available tables , number of records in these tables and size of these tables?

View 4 Replies View Related

Power Pivot :: Divide Number Of Records By End Date Of Month Using DAX Query

Aug 4, 2015

We have some requirement in PowerBI reports. Here we have a table and having Date, Events columns. Below is the sample data..we are creating a measure to calculate the average of the event count for month.We need a measure for calculating Average of Event count per month= sum(Events for a month)/numberofdays in the month.Example for January month : sum(343423)/31 (31 number of days in January) 

When we write this measure using DAX query in Excel we are getting semantic error.Tried sample formula : Average:=SUM([Events])/EOMONTH([EventDate],1)

writing this DAX command for measure.After having this data ready, we are creating PowerBI reports on this data.

View 6 Replies View Related

Display The Number Of Results Found For A Subcategory

Oct 13, 2004

Hello,

I am working on a project that displays images. Before you can get to the image, you have to select a category that the image may be in. After that, you select the sub categories. I am trying to display a count of the number of records that the subcategories contain. Here is an example:

The user can make a selection from the categories listed below:

Geographic Area
Time Period
Topic
Record Type


If the user selects time period, he/she is taken to a list of subcategories. I would like to display the subcategories with a count of the number of records that will be displayed if it is selected. Listed below is an example of what this would look like:

Colonial (10) -----the number in parenthesis is the number of records that will be displayed if selected------
Gilded Age (12)
Revolutionary (9)
Progressive Era (22)


Is there a way to display the number in parenthesis using ASP.Net and SQL Server 2000? Any clues will be greatly appreciated.

Thanks

View 6 Replies View Related

How To Number Records / Duplicates Receiving Same Number

Feb 19, 2013

I have a large table of customers. I would like to add a column that contains an integer, unique to that customer. The trick is that this file contains many duplicate customers, so I want the duplicates to all have the same number between them.the numbers dont have to be sequential or anything, just like customers having the same one.

View 8 Replies View Related

Chalenge Returning The First Available Number In A Sequence

Aug 8, 2007

 Hi all,Ive been trying to figure this out and has proven to be quite difficult for me. Lets say i do a select on all client numbers from a clients table i would want the first available number returned.Given the client table below my query would return 4 because its the lowest number availeble. Thanks. client tableclnum12367 

View 4 Replies View Related

Returning Number Of Tables In SQL Server2000 Using ADO In VB6

Jan 11, 2004

I'm using a VB6 application to access data from an SQL Server 2000 database. There are a few things I would like to do:

1. Get the number of tables in the database
2. Automatically begin reading data from each table in the database. Is it possible to write some code that will cause the application to start on first table retrieve all the data, then move to next table (similar to moving to next row)?

Thanks,
Joey

View 6 Replies View Related

Incorrect Host-column Number Found In BCP Format-file

Jul 20, 2005

Hi guys, would appreciate if you can shed some light on this.Sorry to be a pain, can you tell me what is wrong with the following:for /F %%i in ('dir /b /on c:cppc*.txt') do bcp Inventory..pc in%%i -fc:cpcp.fmt -T -S CHICKYywhere CHICKYy is the serverbcp.fmt8.00.19461 SQLCHAR 0 20 ", " 0 filler_1 ""2 SQLCHAR 0 8 "
" 1 computer_name ""3 SQLCHAR 0 20 ", " 0 filler_2 ""4 SQLCHAR 0 16 "
" 2 ip_address ""5 SQLCHAR 0 20 ", " 0 filler_3 ""6 SQLCHAR 0 60 "
" 3 operating_system ""pc1.txt and other *.txt format is:JW_193801,192.168.1.1,Windows XP,when I run it I get:C:cp>for /F %i in ('dir /b /on c:cppc*.txt') do bcp Inventory..pc in%i -fc:cpcp.fmt -T -S CHICKYyC:cp>bcp Inventory..pc in pc1.txt -fc:cpcp.fmt -T -S CHICKYySQLState = S1000, NativeError = 0Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column numberfound in BCP format-fileC:cp>bcp Inventory..pc in pc2.txt -fc:cpcp.fmt -T -S CHICKYySQLState = S1000, NativeError = 0Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column numberfound in BCP format-fileC:cp>bcp Inventory..pc in pc3.txt -fc:cpcp.fmt -T -S CHICKYySQLState = S1000, NativeError = 0Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column numberfound in BCP format-fileC:cp>bcp Inventory..pc in pc4.txt -fc:cpcp.fmt -T -S CHICKYySQLState = S1000, NativeError = 0Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column numberfound in BCP format-fileC:cp>bcp Inventory..pc in pc5.txt -fc:cpcp.fmt -T -S CHICKYySQLState = S1000, NativeError = 0Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column numberfound in BCP format-fileThe sql table has 3 columns:Sorry to be a pain.-----------------------------------------------------------------------"Are you still wasting your time with spam?...There is a solution!"Protected by GIANT Company's Spam InspectorThe most powerful anti-spam software available.http://mail.spaminspector.com

View 1 Replies View Related

Update Records With First Found Data

Dec 20, 2004

I am trying to combine 2 tables, one is an event table, the other is a system table.

I would like to update field system.transaction with the first event.transaction

Where event.account = system.account
and event.effdt > system.effdt
and event.effdt <= system.effdt + 30

It's not really a join so I'm not sure how to write it.

Thanks,
Doug

View 2 Replies View Related

Fix Failure Result Due To No Records Found

May 7, 2007

I have a query that takes blobs that have been created within the lastday out of sql server and places them on a disk drive. The query is astored proc which runs in a DTS job. The job shows failure when thereare no pdf's created in the last day, how do I correct this??Here is my stored proc code:CREATE PROCEDURE [dbo].[sp_PDFExport] ASbeginset quoted_identifier offdeclare @pk intdeclare @where_clause varchar(100)declare @file_name varchar (50)declare @debug varchar (50)Declare @cmd varchar (50)--debug/*if @Debug = 1print @cmdexec Master..xp_cmdShell @cmd */-- begin cursorDECLARE LOOKUP CURSOR FOR select pr.[id]from plan_report pr, plan_version pvwhere pv.plan_id = pr.plan_id and pv.status = '30' and pr.create_time


Quote:

View 4 Replies View Related

Can SQL Server Return A 0 (zero) When No Records Are Found?

Apr 26, 2008

Can someone help to fix this query so that it returns a 0 (zero), as opposed to a blank or null value, when case_id # 1049 record is not found. And if the record is indeed found it should return the case ID (numeric value).

SELECT CASE
WHEN count(*) = 0 THEN 0
ELSE a.CASE_ID
END
FROM (SELECT CASE_ID FROM CASE_DETAIL WHERE CASE_ID = 1049) a
GROUP BY CASE_ID
GO

Thank you in advance!

View 17 Replies View Related

Set Amounts To Zero In Addition If No Records Found

May 9, 2006

How can I check for Null for the amounts if no records are returned in either select.  Basically it errors out if one or both of the Amounts return no records.  I need to do some sort of IF statement to set one of the amounts or both amounts to zero in those cases so it doesn't error out on me

   SELECT (Coalesce(pd1_Amount, 0) + Coalesce(PD2_Amount, 0)) as Amount
   FROM    
     (

     SELECT pd.Amount as pd1_Amount
          FROM Master m (NOLOCK)
          LEFT JOIN dbo.pdc pd ON pd.number = m.number
          INNER JOIN dbo.Customer c ON c.Customer = m.Customer

          WHERE     pd.Active = 1
                    AND pd.Entered BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate))
     AND pd.Entered <> '1900-01-01 00:00:00.000'
                    AND pd.Deposit BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate))
                    --AND pd.Deposit IS NOT NULL    
                    --AND pd.OnHold IS NULL
                    AND c.customer <> '9999999'
          
          UNION

          SELECT   pdd.Amount as PD2_Amount
          FROM Master m (NOLOCK)
          LEFT JOIN dbo.pdcdeleted pdd ON pdd.number = m.number
          INNER JOIN dbo.Customer c ON c.Customer = m.Customer

          WHERE     pdd.Entered BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate))
              AND pdd.Entered <> '1900-01-01 00:00:00.000'
                    AND pdd.Deposit BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate))
                    --AND pdd.Deposit IS NOT NULL    
                    --AND pdd.OnHold IS NULL
                    AND c.customer <> '9999999'
) as PDC_Main

View 3 Replies View Related

Returning The Identity Of The Auto Number Column

Jan 24, 2005

Hi ,,


How to write the Sql Query to return the next generated Identity from the Sql server database.

View 1 Replies View Related

Returning A Value That Has The Smallest Number Of Instances In A Table

Nov 5, 2007



Hi

Please help! I'm really stuck. I have a table (AssessorAllocations) that stores the data of students who have submitted assessments for marking by an Assessor. Each time an assessment is submitted by a student my program must determine which Assessor has the smallest workload and then assign the current assessment to that Assessor. My problem is how do I determine which Assessor has the smallest number of assessments assigned to him/her.

The table columns are:

AssessorRegistrationNo
LearnerRegistrationNo
QualficationName
SubjectName
AssessmentName
DateAssigned
Marked
DateMarked
Price

So I'm looking for something like this:
Select AssessorRegistrationNo from AssessorAllocations where {Select the Assessor who has the smallest number of AssessmentNames assigned to him/her where Marked='0' and DateAssigned<>''}

Thanks
Neil

View 4 Replies View Related

Returning Middle Records

Dec 7, 2006

I'm writing a page that will return data from my database to the user based on their search paramaters, over several pages with 20 matching records showing per page, and a next button on the bottom. Similar to the format of any search engine.
However, I'd like to write this into the query, and I'm not sure how I would go about doing so. For example:
 "SELECT TOP 20 to 40 * FROM Northwind"
 Hopefully this makes sense. Is there any way of doing this?
Thanks in advance,Russ

View 2 Replies View Related

Sqldatareader Not Returning Records

Feb 13, 2007

My query is as follows:Dim CurrentDate As DateCurrentDate = "09/02/2007" MyCommand = New SqlCommand("SELECT RegisterID FROM Registers WHERE RegisterDate = @RegisterDate AND IsMorningRegister = 1", MyConn)MyCommand.Parameters.Add("@RegisterDate", Data.SqlDbType.DateTime)MyCommand.Parameters("@RegisterDate").Value = CurrentDate My DB table is called RegisterDate and is of type DateTime. The record that should be matched is: Register ID: 13 RegisterDate: 09/02/2007 09:00:00IsMorningRegister: TrueIsAfternoonRegister: False But no records are returned. Any idea why?    

View 4 Replies View Related

Returning The Last Row From A Set Of Duplicate Records

Feb 27, 2002

Any information as to how to handle this?

Thanks.

View 1 Replies View Related

Returning A Specified Range Of Records

Jul 14, 2005

Hi,

I have a SQL question which I suspect is very easy to answer but can't seem to find one for.

I have a table which contains about 500 records. i would like to display these records on a web page, but paginated, showing only 20 records per page. I have in the past returned a recordset containing all the records and paginated programmatically in ASP. In this instance I would like to be able to pass an upper and lower bound into my stored proc and return only those records I want to display. So on page 4 I would want to display only records 61-80. I can pass in the upper and lower bound to the SP as parameters but is there some T-SQL i can use to return this range of records only.

So, the SP would for example accept the parameters

@Upperbound = 80
@Lowerbound = 61

and would return a recordset of 20 records being the 61st thru 80th record from the queried table.

Thanks for the help
Guy

PS. I asked someone at work and they suggested using OFFSET and LIMIT. It seems to me as if these are only available in PostgreSQL or MySQL and not T-SQL. I guess I am looking for an equivalent I can use with SQL Server.

View 4 Replies View Related

Proc Returning All Records

Nov 17, 2006

Following Proc is returning all records.I have passed any value to parameters but no luck.


CREATE PROCEDURE GetAllUsers(
@persontype varchar(100)="",
@name varchar(100)="",
@adddatetime datetime="",
@activeaccount int =-1,
@country varchar (20)=""
)
AS
declare @cond varchar(1000) ;

if @persontype<>""
begin
set @cond= @cond+"and persontype="+@persontype
end
if @name<>""
begin
set @cond= @cond+"and (charindex("+@name+",x_firstname)>0 or charindex("+@name+",x_lastname)>0 or charindex("+@name+",x_email)>0) "
end
if @activeaccount<>-1
begin
set @cond= @cond+'and activeaccount='+@activeaccount
end
if @adddatetime<>""
begin
set @cond= @cond+'and adddatetime='+@adddatetime
end
if @country<>""
begin
set @cond= @cond+'and x_country='+@country
end
print @cond
exec( " select * from users where 1=1 "+@cond)
GO



zx

View 2 Replies View Related

Help With Returning A Certain # Of Records From A View.

Jul 20, 2005

I have a view that will return say 5000 records when I do a simpleselect query on that view like.select *from vw_test_viewHow can I set up my query to only return a certain # of records, saythe first 300?Here is what is going on, we have a large amount of data that returnsin a view and we need to work with all of it eventually, However wewant to do it in chunks. So my thoughts were as follows:1. To run a query to return X amount of the total data for us to workwith.2. Update these records with a flag in a table that the vw_test_viewfilters out.3. The next time I run the query to pull data from the view it willskip the records that I have already looked at (because of step 2) andpull the next X amount of records.Thanks in advance,Mike

View 3 Replies View Related

Returning Billable Records.

May 15, 2008

Hello -

I'm trying to write a select statement that will return only "billable" records.
A record is billable if:
- it is the only record for a Case (Case is an FKey ID column in my records table)
OR
- the last billable record for Case X has a DateTime > 24 hours before the record in question.

Getting records for the first condition is easy:

SELECT ID FROM Records r
LEFT OUTER JOIN Records r2 ON r2.[Case] = r.[Case]
WHERE r2.[Case] IS NULL

It's the second part I'm having trouble with.

UNION
SELECT ID FROM Records
JOIN ?
....
WHERE DATEDIFF(hh, r.DateTime, r2.DateTime) > 24 ??

I have to assume there can be any number of records for a case.

Thanks in advance!

View 10 Replies View Related

Returning 30% Random Records

Sep 27, 2007

HI,

If i have the following data




Code Block

Create Table #Request (
[requestid] int ,
[customername] Varchar(30) ,
[age] int ,
[sex] char(1) ,
[address] Varchar(30) ,
[status] int
);

Insert Into #request Values('2342','Jack','23','M','Texas','0');
Insert Into #request Values('223452','Tom','45','M','Ohio','1');
Insert Into #request Values('22353','Bobby','23','M','Austin','0');
Insert Into #request Values('22362','Guck','23','M','Austin','0');
Insert Into #request Values('22392','Luck','23','M','Austin','1');
Insert Into #request Values('22362','Buck','23','M','Austin','0');
Insert Into #request Values('2564392','Jim','23','M','Austin','1');
Insert Into #request Values('2342','Jasm','23','M','Austin','0');
Insert Into #request Values('2765492','Chuck','23','M','Austin','1');





How can i return 30% random requestid's from this table?

thanks.

View 9 Replies View Related

SQL 2012 :: Query To Make Single Records From Multiple Records Based On Different Fields Of Different Records?

Mar 20, 2014

writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.

ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29

output should be ......

ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29

View 0 Replies View Related

Page Number &&amp; Records Number

Jun 20, 2006

1. how to show page number & total page number in report body?

2. how to show total records number?

View 25 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved