|
Definitely give it a try. It probably has bugs since it was written on-the-fly but looping through the recordset is definitely an insufficient way of doing it. ExecuteScalar with the count being on the primary key is probably the most efficient way of obtaining the count.
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
Tried again, but could not get it to work, I had a feeling that the code you submitted was in ASP.NET, which my friend David Wulff confirmed for me.
My host (oneandone) does not allow me to use .NET, which explains things, i've managed to get things to work now with the following code, incase anyone else reads this in the future
set rs = OBJdbConnection.execute("SELECT count(ID) as total from Log")<br />
sTotalpages = rs("total")
Thanks for your time, its appeciated!
---
Peter M
|
|
|
|
|
SQLQuery = "SELECT COUNT(*) AS 'NoOfRows' FROM Log" and use executeQuery
D!shan
|
|
|
|
|
Could not get this to work with executeQuery, as mentioned with Dr X, this might be because the code is ADO.NET?
Your all using space age technology compared to me, i'm not on the .NET train yet 
Thanks for your time otherwise
---
Peter M
|
|
|
|
|
OK, now i have working code to count all the records thanks for all your suggestions, that’s all fine now.
The other thing that I wanted to clear up was this, and i'll do my best at explaining!
I wanted to select all the data in a table, then sort in descending order (the primary being a Autonumber called ID), and then select a range of records for paging.
Now i can do the paging fine, the the first record is shown first, and wanted everything to be in reverse order. So my first bit of code works, in ascending order. (ignore the maths bit its used to select ie records 1-5 for page 1 and 6-10 for page 2 ...)
SQLQuery = "SELECT * FROM Log WHERE Log.ID BETWEEN " & (((sCurrentpage-1)*5)+1) & " AND " & (sCurrentpage*5)
This works fine, but I wanted it to have all the records in reverse order, so I thought ah ha, i'll just put in order by:
SQLQuery = "SELECT * FROM Log ORDER BY ID DESC WHERE Log.ID BETWEEN " & (((sCurrentpage-1)*5)+1) & " AND " & (sCurrentpage*5)
But this doesn't work.
If I were to put ORDER BY ID DESC at the end of the SQL statement it will work, but it will only reverse order of the selected records.
But i wanted to reverse order of everything and then select..understand the problem??
Suggestions on a post card.. Please note, I am not able to use anything to do with .NET, so i need .NETless code  ..cheers!
---
Peter M
|
|
|
|
|
Order by goes at the end of the statement.
SQLQuery = "SELECT * FROM Log WHERE Log.ID BETWEEN " & (((sCurrentpage-1)*5)+1) & " AND " & (sCurrentpage*5) & " ORDER BY ID DESC" Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
what is the sql statement to get the name of all tables exiting in a database
thank you
|
|
|
|
|
USE db;
GO
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';
For instance. Presuming, of course, you're talking about MSSQL Server.
--
Henrik Stuart (http://www.unprompted.com/hstuart/)
|
|
|
|
|
SELECT * FROM sysobjects WHERE type = 'U'
D!shan
|
|
|
|
|
Use the INFORMATION_SCHEMA approach that Henrik Stuart described, since MS can and probably will change the structures many of its system tables in future versions of SQL Server.
The results of using the INFORMATION_SCHEMA viewvs are guaranteed to stay the same in all never versions of SQL Server.
Have a look at my latest article about Object Prevalence with Bamboo Prevalence.
|
|
|
|
|
Hello,
I've the following problem to solve: my application store 1.000.000 records on an Oracle DB.
The data are retrieved into a CRecordset class with which the query to the DB is performed. After performing the Open, I've done a while loop until EOF and the result is surprising not 1.000.000 records but something less (275.000).
Consider that I've already checked that in the DB the data are correctly stored and all the queries are corrected 'cause I've performed the same operation directly with a DB tool that gives the good results.
One more hint, if I do a query to count the records in my application the result is good: 1.000.000.
Now my question is: is there any limitation on the data I have to fetch from the DB? Or I'm missing something else?
Thanx for your help!
Fabio
|
|
|
|
|
u want to read 1 million records into memory at one go?
"there is no spoon"
biz stuff about me
|
|
|
|
|
Hi everyone,
I would like to know if I can create a new Microsoft Access file using ADO.NET. I have done this in the past using ADOX and it was quite easy.
However, is it possible to do something similar using ADO.NET. I would like to create the database and add tables, reports to it.
Thanks for any help that you might give me.
Xargon
Without struggle, there is no progress
|
|
|
|
|
Hi Xargon. At the moment, your best bet may be to continue using the ADOX library through COM Interop. Here's a link to an article on CodeProject that describes how to use ADOX this way:
http://codeproject.com/books/186100558x_16.asp[^]
|
|
|
|
|
Perfect! Exactly what I needed.
Thanks a ton Mike!
Pankaj
Without struggle, there is no progress
|
|
|
|
|
Is there a way to grant rights to Create and Drop Views but not Tables. All the search I've done seems to indicate that they're both mutually inclusive.
"if you vote me down, I shall become more powerful than you can possibly imagine" - Michael P. Butler.
Support Bone
It's a weird Life
|
|
|
|
|
Books Online[^] seems to indicate that you can grant rights to create tables and views separately. IIRC, you can only Drop an object if you're a member of the db_owner or db_ddladmin roles for the database, or if you own the object. These roles are fixed: you cannot alter the privileges of these roles and you cannot set permissions for user-defined roles.
You can use
GRANT CREATE TABLE TO public to allow all members of the public role to create tables.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Mike Dimmick wrote:
you can only Drop an object if you're a member of the db_owner or db_ddladmin roles for the database
That's what I was afraid of. Thanks, anyway.
"if you vote me down, I shall become more powerful than you can possibly imagine" - Michael P. Butler.
Support Bone
It's a weird Life
|
|
|
|
|
Hi,
im not so good with SQL so pls bear with me if this is too easy...  anyways i'd like to display information from two tables "Diagnosis" and "DiagPhar" in a datagrid...my question is how do i code the SQL command text for this...i tried using ways i got from the net but its not working. also how do i setup the datagrid to display the information from two tables.
CODER
|
|
|
|
|
Hi
Try : Select * from Diagnosis,Diagnosis
this brings the two table in one result set and you can show the result set in one grid
|
|
|
|
|
Hi MasudM
thx for the hint. anyways how can i display the data using ONE datagrid?..i would appreciate it if u showed me how...
thx again!
CODER
|
|
|
|
|
Hi
As Mike Said below a full description if need join ! I do not exactly
know what are you after if these two tables have relation you probably need kind of join ! but I can give another hint if you don't know it before !
when you use SqlDataAdapter (or Command) it only turns SqlData to some format that visul Studio can show . then you fill the DataSet
SqlDataAdapter.Fill(DataSet,"TableName"); it brings the sql Data to a format Like Sql But Viewable with visual Sudio Tools !
so you can add two SqlTable to one Table in DataSet then can show that table with a grid .
But Mike's message is very good for joinning Two tables.it works at the SqlServer Level .
|
|
|
|
|
Sounds like you need to JOIN your tables together.
If you want only rows that appear in both tables, use an INNER JOIN. If you want all rows that appear on the left-hand side of the JOIN, with any data appearing on the right-hand side if available, use a LEFT JOIN. Vice-versa, use a RIGHT JOIN. If you want all rows from both tables, regardless of whether corresponding data appears on the other side, use a FULL OUTER JOIN. Finally, if you don't want to use a join condition (the ON clause) and want to output every row of your left-hand table joined to every row of your right-hand table, use a CROSS JOIN.
Example: you have tables OrderLine and Product. An OrderLine maps between an order and the products on that order, and includes the quantity ordered. You want to print an invoice, listing the product details and total prices. You might write something like:
SELECT
OrderLine.Sequence,
OrderLine.Quantity,
Product.ShortDesc,
Product.Price AS ItemPrice,
OrderLine.Quantity * Product.Price AS LinePrice
FROM
OrderLine INNER JOIN Product
ON OrderLine.ProductID = Product.ProductIDIf, somehow, we had OrderLine rows with ProductID not in the Product table, and we wanted to show those anyway, we would use a LEFT JOIN in the above query. Any fields we refer to from Product, for OrderLine rows with no matching ProductID, will be NULL (e.g. ShortDesc and Price will be NULL for the above query). The INNER JOIN omits these rows.
Note that if there is more than one row on each side that matches the join condition, all those rows will be output, joined to each other. For the sake of argument, let's say that we have two OrderLine rows which refer to ProductID 10, and there are two rows in Product where ProductID is 10 (you would try to avoid this in an ordering system!). Let's give them descriptions ProductA and ProductB. You might get the result
Sequence Quantity ShortDesc ItemPrice LinePrice
-----------------------------------------------
1 2 ProductA 5 10
1 2 ProductB 6 12
2 1 ProductA 5 5
2 1 ProductB 6 6
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
thx Mike n Masud!
with your help, i was able to do what i wanted to. thx again!
CODER
|
|
|
|
|
Is it possible to have a DataView that do not contain all the columns of the corresponding DataTable? If so, how do I do it?
--------
"I say no to drugs, but they don't listen."
- Marilyn Manson
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
