|
Hi
You can build a multi sql statement and execute it once!. you dont need to execute singel insert / update statements many times.
Ex. sSQL = "INSERT INTO .....;INSERT INTO ...;INSERT.."
executeQuery( sSQL );
HTH.,
D!shan 
|
|
|
|
|
I wish to count the number of records in my table and then display the number on the screen
So i done:
SQLQuery = "SELECT COUNT(*) FROM Log"<br />
Set RS = OBJdbConnection.Execute(SQLQuery)<br />
Response.Write(RS.RecordCount)
But i'm getting into a muddle on how to do it.
Can someone please show me the light.!!
---
Peter M
|
|
|
|
|
First of all, does the Log table have an primary key index, preferable an autoincrementing ID. It would be much faster as the log file table size increases to do a 'select count(ID) from log' than doing a count(*). Executing a count(*) may induce a table scan while count(ID) will only scan the primary key index.
2nd, you should probably execute a OBJdbConnection.ExecuteScalar rather than a execute. The executescalar is designed to return a single value which is what you are looking for.
Dim rowCount as object
SQLQuery = "SELECT COUNT(ID) FROM Log"
value = OBJdbConnection.ExecuteScalar(SQLQuery)
if not value is dbnull.value then
'Make sure you didn't receive a Null from the database.
'If not it should be an int or int32. Can't remember which one is actually returned.
Response.Write(value.tostring)
end if The above is not tested but you should get the idea.
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)
|
|
|
|
|
Yes it does have a primary index, which is just a auto number.
Before I recieved your message, I managed to get things working by doing the following
<br />
SQLQuery = "SELECT * FROM Log" <br />
Set RS = OBJdbConnection.Execute(SQLQuery)<br />
<br />
While (Not RS.EOF)<br />
sTotalpages = sTotalpages + 1<br />
RS.MoveNext<br />
Wend
Maybe its a crude way to count the pages.
I'll give your code a try now
---
Peter M
|
|
|
|
|
Well I tried your code but I get the error
"ADODB.Connection error '800a0bb9'
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
/Includes/paging.asp, line 12 "
I couldnt' get the tostring to work, so i took it out as well as the error checking, as they will not be blank fields (we are checking the primary after all)
So the code ended up
<br />
dim sTotalpages<br />
SQLQuery = "SELECT COUNT(ID) FROM Log"<br />
sTotalpages = OBJdbConnection.ExecuteScalar(SQLQuery) 'Line 12 - the error point<br />
Response.Write(sTotalpages)
I'm rather confused to why I am having problems, i mean it looks easy enough, there is obviously something I am doing wrong.
Well I'll go back to my old method for now, and have a break, and come back to it later
---
Peter M
|
|
|
|
|
Here this works:
Dim cn As New System.Data.SqlClient.SqlConnection(_connectString)
Dim value As Object
Dim cmd As New System.Data.SqlClient.SqlCommand("Select count(SampleID) from tblSamplesSample")
cmd.Connection = cn
cn.Open()
value = cmd.ExecuteScalar
Response.Write(value)Change the select statement and add your connect string.
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)
|
|
|
|
|
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
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
