My Number Of Records Are Different From Views
Apr 20, 2008
Why is it my number of records are different from my view. this are the following code that that i used in my code and in my view.
my code in my program: this code show all the records i know
If iConn.State = ConnectionState.Open Then iConn.Close()
iConn.Open()
Dim rsBills As New Data.DataSet
rsBills = New DataSet
Dim daBills As New SqlDataAdapter
daBills = New SqlDataAdapter
rsBills.Clear()
Dim cmBills As New SqlCommand
cmBills = iConn.CreateCommand ' "Select U_Code, U_Name, U_Level, P_Word From EPassword ",
cmBills.CommandText = "Select OR_no, Billing_mo From dbo.vwBilling Where Month(dbo.vwBilling.Billing_mo)= " & Month(Me.dtto.Value) & " And Year(dbo.vwBilling.Billing_mo) = " & Year(Me.dtto.Value) & " ORDER BY OR_no "
daBills.SelectCommand = cmBills
rsBills.AcceptChanges()
rsBills.Clear()
daBills.Fill(rsBills, "Bills")
Me.DataGridView1.DataSource = Nothing
Me.DataGridView1.DataSource = rsBills.Tables("Bills")
my code in views:
SELECT OR_no, Billing_mo, Account_no, Name, Address, Cno, Pres_read, Mprev_read, Sub_read, Pres2, Prev2, SRead2, Mtr_cons, Amount, NPC, Dmem,
Cmem, Tot_bill, D_Pmnt, Class, Newbill, Prd_fr, Prd_to, Type_Pmnt, Type_Date, Type_Docs, wtax
FROM dbo.Billing
hope you can help me guys
View 1 Replies
ADVERTISEMENT
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
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
Apr 10, 2008
If iConn.State = ConnectionState.Open Then iConn.Close()
iConn.Open()
Dim rsBills As New Data.DataSet
rsBills = New DataSet
Dim daBills As New SqlDataAdapter
daBills = New SqlDataAdapter
rsBills.Clear()
Dim cmBills As New SqlCommand
cmBills = iConn.CreateCommand
cmBills.CommandText = "Select OR_no, Billing_mo From dbo.vwBilling Where Month(dbo.vwBilling.Billing_mo)= " & Month(Me.dtto.Value) & " And Year(dbo.vwBilling.Billing_mo) = " & Year(Me.dtto.Value) & " ORDER BY OR_no "
daBills.SelectCommand = cmBills
rsBills.AcceptChanges()
rsBills.Clear()
daBills.Fill(rsBills, "Bills")
Me.DataGridView1.DataSource = Nothing
Me.DataGridView1.DataSource = rsBills.Tables("Bills")
that is my code when this procedure called it shows the real data or records that inserted but when i run my view in server explorer the records that inserted is missing
here is my code in views
SELECT OR_no, Billing_mo, Account_no, Name, Address, Cno, Pres_read, Mprev_read, Sub_read, Pres2, Prev2, SRead2, Mtr_cons, Amount, NPC, Dmem,
Cmem, Tot_bill, D_Pmnt, Class, Newbill, Prd_fr, Prd_to, Type_Pmnt, Type_Date, Type_Docs, wtax
FROM dbo.Billing
same in my base table it seems that no records was inserted when i show the result table, hope you can help me snSQL
View 1 Replies
View Related
Jul 23, 2005
This is a general question regarding the use of view and storedprocedures. I'm fairly new to databases and SQL.I've created a SQL database using an Access Data Project ("ADP") andI'm satified with the table structure. I've moved on to building somefront ends for our users.I'm running into situations where I want subreports to be built fromqueries [views or stored procedures-I don't know which to use so I usethe term query] that are dependent on the values in other controls.I've played with stored procedures and I've figured out how to sendcriteria to a stored procedure and then dynamically change the recordsource of a subreport.However, I'm running into cases where I can't add records to theresults of a stored procedure. The table I'm running a stored procedureon has five fields: (1) Primary Key for each record, (2) FundID that'sa primary key in another table, (3) CompanyID that's a primary key inanother table, (4) Attribute 1 of the (Fund/Company) and (5) Attribute2 of the (Fund/Company).The stored procedure filters the set of Fund/Companies based on aFundID from a form. I can update this stored procedure. However, forusers, they would like to see the Fund Name from the table that hasunique FundIDs. As soon as I include that into the stored procedure, Ican no longer add records.My questions are many:1. Is there a primer online that discusses the theory behind myquestion? Recordsets, updatability, working with recordsets in forms?2. What are some best practices for developing subreports, combo boxes,list boxes, etc. where the data is dependent on the values in a control3. I'm struggling with the best ways to grab objects on a form. If I'mon the main form I'm comfortable working with theMe.__object__.__sub-oject routine. However, if I'm in one subform whereI need another subform to change based on the record I'm in, I feelthat my code to get at the subform is very klunky..forms.main form name.sub form name.form.record sourceI don't even know how I figured out the "form" part before recordsource. Again, are there some basic rules or guides about navigatingthrough forms in VBA?4. Should I be developing front ends in some other environment?I know it's a lot, but all the advice from the newsgroups seems topresuppose some knowledge about how ADP, ADO, ODBC..blah blah and Ican't seem to find any documents about ADP and SQL.
View 3 Replies
View Related
Apr 26, 2001
Hi,
I want to the users to specify the number of TOPn records to be returned by the query.It has to be a query and not a stored proceedure.
Thanx
View 3 Replies
View Related
Dec 2, 2006
HI EVERYBODY
This is my procedure
"
CREATE PROCEDURE SP_SAMPLE_SEARCH
@Title nvarchar(256)
AS
SELECT ID,Title,Price FROM [tbl_Sim] WHERE ([Title] LIKE '%' + @Title + '%') Order by Price desc
GO
"
I exec procedure and it returns 12 results with diffirents ID
and want to get these values 1,2,3....12
How do I get these...
I am a beginner.
Thanks for help..
View 6 Replies
View Related
May 29, 2015
I'm using SQLCE. I'm using this sql statement:
sSQL = "Select Top (5) fldStatus,
Count(fldStatus) As CountOfSummaryItem From tbl_Records_DR GROUP BY
fldStatus;"
It gets the top 5 of fldStatus but I need the top 5 of CountOfSummaryItem.
View 2 Replies
View Related
Feb 12, 2007
Okay..i have this problem ...i am using SQL server 2005 standard ,C#,VS2005
--i am inserting some record in DB .. using ExecuteNonQuery...i want to know how many records are getting inserted..so in my DB class i did something like this : numRecords = commandObject.ExecuteNonQuery() ,assuming that ExecuteNonQuery returns the number of affected records.i am retriving this numOfRecords in my code behind and printing it but it always prints 1,even though more then one records are inserted.What is wrong here?
-i also have returnValue defiend like this.Could this tell me anything about how many records are inserted or affected during update,select ?if so,how?
cmd.Parameters.Add(new SqlParameter("@returnVal", SqlDbType.Int));
cmd.Parameters["@returnVal"].Direction = ParameterDirection.ReturnValue;
Please help me out with this.Thanks
View 14 Replies
View Related
Feb 26, 2008
hi
can anyone tell me how to count number of records(rows) in a table without using "COUNT" function.for practise iam trying to implement it through queries.
View 6 Replies
View Related
May 13, 2008
Using linq what would be the most efficient method of counting the number of users in the users table?
View 2 Replies
View Related
Dec 16, 2005
Hi...
I just begining to use the asp.net 2.0 and have tow littel problems... in my project the user makes a "list of problems on a house" when hi post the house number the page shoud to generete a master recod with a keynumber, the date an hour and the state of problems... and a form to insert a list of problems... but if the house dosn´t exist this forms are hidden... then i need to know the nomber of records that gets a recordsource if its more than cero show the forms else hiden it... if show the forms i need to store in a variable the keynumber to store it in the problem details table... ¿How i can khow the number of records of a RecordSource gets?... and ¿How I can store in a variable a field value retrived for a RecordSource?... I´m using VWD and SQL Express that means ADO.NET 2.0...
cheers.
View 1 Replies
View Related
Sep 27, 2001
Hi everbody,
I want find out Total number records in one table without using select statment.
Some body as told to me there is system table you can find total number of records. Any body give me systable name.
Thanks
Jack
View 2 Replies
View Related
Sep 27, 2001
Hi everbody,
I want find out Total number records in one table without using select statment.
Some body as told to me there is system table you can find total number of records. Any body give me systable name.
Thanks
Jack
View 1 Replies
View Related
Oct 20, 2004
Hi all
My manager ask me to provide him with the total number of records which have been added, deleted or modified on a certain database in SQL Server 2000 during the month of September. Is there away to get that information from the transaction log or by any how?
please some one guide me how to do that?
Your help is highly appreciated
View 8 Replies
View Related
Apr 16, 2008
Hi,
Here is a basic example of the issue I am having:
Table 1 columns - name, address, zipcode, favorite food
For table 2 I want to find how many zipcodes exists and also take 20% of the count
Table 2 columns - based off Table 1 contains zipcode, count(zipcode) as ct, count(zipcode) * .20 as perc_ct
For example:
zipcode ct perc_ct
83746 10 2
93847 20 4
I want to run a query that will pull any 2 records for 83746 and any 4 records for 93847 from Table 1.
Is this possible?
View 1 Replies
View Related
Apr 21, 2008
is there a limitations of number of records that shows in the table when you use show data table?
View 1 Replies
View Related
Mar 19, 2007
With this algorithm you can sum up an unkown number of records, so that an aggregation matches a fixed value.
If there is not an exakt match available, the algorithm returns the nearest possible value!-- Initialize the search parameter
DECLARE@WantedValue INT
SET@WantedValue = 349
-- Stage the source data
DECLARE@Data TABLE
(
RecID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
MaxItems INT,
CurrentItems INT DEFAULT 0,
FaceValue INT,
BestUnder INT DEFAULT 0,
BestOver INT DEFAULT 1
)
-- Aggregate the source data
INSERT@Data
(
MaxItems,
FaceValue
)
SELECTCOUNT(*),
Qty
FROM(
SELECT 899 AS Qty UNION ALL
SELECT 100 UNION ALL
SELECT 95 UNION ALL
SELECT 50 UNION ALL
SELECT 55 UNION ALL
SELECT 40 UNION ALL
SELECT 5 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 50 UNION ALL
SELECT 250 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 90 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 50 UNION ALL
SELECT 350 UNION ALL
SELECT 450 UNION ALL
SELECT 450 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 50 UNION ALL
SELECT 50 UNION ALL
SELECT 50 UNION ALL
SELECT 1 UNION ALL
SELECT 10 UNION ALL
SELECT 1
) AS d
GROUP BYQty
ORDER BYQty DESC
-- Declare some control variables
DECLARE@CurrentSum INT,
@BestUnder INT,
@BestOver INT,
@RecID INT
-- If productsum is less than or equal to the wanted sum, select all items!
IF (SELECT SUM(MaxItems * FaceValue) FROM @Data) <= @WantedValue
BEGIN
SELECTMaxItems AS Items,
FaceValue
FROM@Data
RETURN
END
-- Delete all unworkable FaceValues
DELETE
FROM@Data
WHEREFaceValue > (SELECT MIN(FaceValue) FROM @Data WHERE FaceValue >= @WantedValue)
-- Update MaxItems to a proper value
UPDATE@Data
SETMaxItems =CASE
WHEN 1 + (@WantedValue - 1) / FaceValue < MaxItems THEN 1 + (@WantedValue - 1) / FaceValue
ELSE MaxItems
END
-- Update BestOver to a proper value
UPDATE@Data
SETBestOver = MaxItems
-- Initialize the control mechanism
SELECT@RecID = MIN(RecID),
@BestUnder = 0,
@BestOver = SUM(BestOver * FaceValue)
FROM@Data
-- Do the loop!
WHILE @RecID IS NOT NULL
BEGIN
-- Reset all "bits" not incremented
UPDATE@Data
SETCurrentItems = 0
WHERERecID < @RecID
-- Increment the current "bit"
UPDATE@Data
SETCurrentItems = CurrentItems + 1
WHERERecID = @RecID
-- Get the current sum
SELECT@CurrentSum = SUM(CurrentItems * FaceValue)
FROM@Data
WHERECurrentItems > 0
-- Stop here if the current sum is equal to the sum we want
IF @CurrentSum = @WantedValue
BREAK
ELSE
-- Update the current BestUnder if previous BestUnder is less
IF @CurrentSum > @BestUnder AND @CurrentSum < @WantedValue
BEGIN
UPDATE@Data
SETBestUnder = CurrentItems
SET@BestUnder = @CurrentSum
END
ELSE
-- Update the current BestOver if previous BestOver is more
IF @CurrentSum > @WantedValue AND @CurrentSum < @BestOver
BEGIN
UPDATE@Data
SETBestOver = CurrentItems
SET@BestOver = @CurrentSum
END
-- Find the next proper "bit" to increment
SELECT@RecID = MIN(RecID)
FROM@Data
WHERECurrentItems < MaxItems
END
-- Now we have to investigate which type of sum to return
IF @RecID IS NULL
IF @WantedValue - @BestUnder < @BestOver - @WantedValue
-- If BestUnder is closer to the sum we want, choose that
SELECTBestUnder AS Items,
FaceValue
FROM@Data
WHEREBestUnder > 0
ELSE
-- If BestOver is closer to the sum we want, choose that
SELECTBestOver AS Items,
FaceValue
FROM@Data
WHEREBestOver > 0
ELSE
-- We have an exact match
SELECTCurrentItems AS Items,
FaceValue
FROM@Data
WHERECurrentItems > 0With references to
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=73540
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=73610
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78015
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79505
Peter Larsson
Helsingborg, Sweden
View 3 Replies
View Related
Mar 19, 2008
Wondering if there is a way to do this...
I want to select the DISTINCT user name from each record in a table, and then have another field tell me how many records it found..
is there any way to do this with SQL?
SELECT DISTINCT user_Name FROM table ORDER BY user_Name
is what I have now....
need something that will return:
user_Name numRecs
bob 3
fred 6
sam 1
linda 2
View 1 Replies
View Related
Jun 14, 2006
I have a resultset that looks something like this:
Anzahl users_statdata_hobbies
---------------------
499 Andere
266 Essen
60 Essen,Andere
127 Essen,Musik
10 Essen,Musik,Party,Andere
30 Essen,Party
4 Essen,Party,Andere
51 Kunst
4 Kunst,Andere
13 Kunst,Essen
4 Kunst,Essen,Andere
I get this with this query which might be altered somehow:
SELECT COUNT(*) AS Anzahl, users_statdata_hobbies
FROM vgetAuswertung2
GROUP BY users_statdata_hobbies
ORDER BY users_statdata_hobbies
Of course this is not normalized but I can't change this.
Nevertheless I need to get the full number of each Hobby and not only the combination of them.
So instead or in addition to the existing recordset I need e.g
357 Essen which ist the sum of all records containing 'Essen' in the above example
The list of individual hobbies is defined therefor I could loop through the list manually and search for 'WHERE Hobbies LIKE '%ESSEN%' and count but since it's quiet a big resultset and there are several other similar tasks already I'm looking for a more performant way and I'm sure it could be done in SQL directly.
Any ideas someone?
View 4 Replies
View Related
May 19, 2006
I want to restrict the number of records coming from an OLEDB source. I have 500 records in my source table and I want to process one record at a time
I have I set the MaxBufferRows parameter to 1and it l sends 8 records from OLEDB source
Any help is appreciated.
View 9 Replies
View Related
Dec 21, 2007
Hi i have sql statement like this :
SELECT row_number() over (ORDER by a.empid) as rec_num, empname
FROM employee_a
UNION
SELECT row_number() over (ORDER by a.empid) as rec_num, empname
FROM employee_b
the problem is the rec_num repeat for each statement like this :
rec_num empname
1 john
2 maggy
1 lee
2 mary
3 louis
How do i make the rec_num continue for the next statement after union.
View 1 Replies
View Related
Nov 15, 2007
Hi...
I have Sql statement more like this
SELECT row_number() over (ORDER by a.employeeID) as rec_num, a.* FROM EmployeeA a
UNION
SELECT row_number() over (ORDER by a.employeeID) as rec_num, a.* FROM EmployeeB a
rec_num employeeID employeeName employeeDepartment
1 777 Mike HR
2 888 Susy HR
1 111 Smith TECH
2 222 John TECH
3 333 Lenny TECH
How do i get sequence number for all of this records. The rec_num reset for every statement. I want the records numbering for second statement continue from first statement so that it can be like this :
rec_num employeeID employeeName employeeDepartment
1 777 Mike HR
2 888 Susy HR
3 111 Smith TECH
4 222 John TECH
5 333 Lenny TECH
View 4 Replies
View Related
Aug 21, 2006
What is the easiest way to obtain number of records in SQLDataSource (using select statement)/GridView. All that I've found in forums seems to be very difficult for such trivial task... Thank you!
View 4 Replies
View Related
Aug 2, 2007
Hi all,For now I can use this code to display all the records that begins with a Letter:(WHERE SONG_TITLE LIKE @SONG_TITLE + '%') Now how do I search for records that begins with a number (from 0-9), as an add-on to the above query?Thank you very much,Kenny.
View 6 Replies
View Related
Feb 26, 2004
Can anyone tell me how I can obtain the number of records returned in a sqlDataReader recordset?
View 8 Replies
View Related
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
Nov 14, 2000
How do you limit the number of records returned in a recordset? I only want the 10 most recent and I've got a Date column in my database.
View 1 Replies
View Related
Jun 21, 2000
I'm trying to update every record with a incremental number. I wrote the following query but it updates the records with the same number. Could someone please tell me what I'm doing wrong? Thanks.
declare @NextKey int
SELECT @NextKey = NEXT_KEY FROM TABLE_KEYS
WHERE TABLE_NAME = "tblEmp"
set @NextKey = @NextKey + 1
DECLARE tblNewEmp_cursor CURSOR FOR
select emp_pk from tblNewEmp
open tblNewEmp_cursor
FETCH NEXT FROM tblNewEmp_cursor
WHILE @@FETCH_STATUS = 0
begin
update tblNewEmp
set emp_pk = @NextKey
set @NextKey = @NextKey + 1
FETCH NEXT FROM tblNewEmp_cursor
end
close tblNewEmp_cursor
View 1 Replies
View Related
Sep 12, 2013
There is something I don't understand. When I use join
SELECT r.CHECK_NUMBER, i.orig_file
from (AP_INVOICEDOCS i join AP_DETAIL_REG r on r.PAYABLE_ID= i.PAYABLE_ID)
I am getting 76 orig_file records
But when I do
SELECT r.CHECK_NUMBER, i.orig_file
from (AP_INVOICEDOCS i right outer join AP_DETAIL_REG r on r.PAYABLE_ID= i.PAYABLE_ID)
I am showing only 8 records under i.orig_file column and I am not sure why. What I need is to get all the AP_INVOICEDOCS in the matching orig_file records.
View 3 Replies
View Related
Oct 1, 2013
I have 2 tables:
Table 1 Customers with column CustomerId
Table 2 Purchases with columns PurchaseId and CustomerId
I want to select all customers who have made just 1 purchase, what would be the sql for that? I know I can join the tables to get the customers with purchases, but I don't know how to limit it to those with just 1 purchase (and exclude all the other customers).
View 9 Replies
View Related
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
Oct 29, 2013
I need to create a query that will select only one record out of x number of record per user id.
Sample Data
User ID | Date |Status
001 |10/1/2013 |01
001 |10/2/2013 |A1
002 |1/1/2013 |BB
003 |5/8/2013 |EE
003 |4/2/2013 |BB
Expected Result
User ID | Date |Status
001 | 10/2/2013 |A1
002 | 1/1/2013 |BB
003 | 5/8/2013 |EE
View 3 Replies
View Related