|
Which is the easiest way to get the number of records in a table?
Rickard Andersson@Suza Computing
C# and C++ programmer from SWEDEN!
UIN: 50302279
E-Mail: nikado@pc.nu
Speciality: I love C#, ASP.NET and C++!
|
|
|
|
|
SELECT COUNT(*) AS [Count]
FROM Table
Who is this miscrosoft, and what devilish plans have they for us?
|
|
|
|
|
Thaaank you so much!
5p!
Rickard Andersson@Suza Computing
C# and C++ programmer from SWEDEN!
UIN: 50302279
E-Mail: nikado@pc.nu
Speciality: I love C#, ASP.NET and C++!
|
|
|
|
|
leppie wrote:
AS [Count]
Hey leppie!
I need more help about what you said... I wrote my SQL query like this:
SELECT COUNT(*) AS NumberOfRecords FROM Login
But how do I get the field value (NumberOfRecords)!?!?!?!
Rickard Andersson@Suza Computing
C# and C++ programmer from SWEDEN!
UIN: 50302279
E-Mail: nikado@pc.nu
Speciality: I love C#, ASP.NET and C++!
|
|
|
|
|
public static int GetRecordsInTable(string table, SqlConnection con)
{
SqlCommand cmd = new SqlCommand(
String.Format("SELECT COUNT(*) AS X FROM [{0}]", table), con);
con.Open();
object r = cmd.ExecuteScalar();
con.Close();
return (int) r;
}
Who is this miscrosoft, and what devilish plans have they for us?
|
|
|
|
|
leppie wrote:
String.Format("SELECT COUNT(*) AS X FROM [{0}]", table), con);
Dooh!
Tackar så mycket!
(thank you! )
Rickard Andersson@Suza Computing
C# and C++ programmer from SWEDEN!
UIN: 50302279
E-Mail: nikado@pc.nu
Speciality: I love C#, ASP.NET and C++!
|
|
|
|
|
SELECT TOP 10 *
FROM Table
ORDER BY ID DESC
Or a timestamp...
Who is this miscrosoft, and what devilish plans have they for us?
|
|
|
|
|
Is there any EASY way to write out to a debug lob from a SQL 2000 sproc?
I am seeeing all kinds of kludgy looking things like BCP isql\osql, xp_cmdshell.
Paul Watson wrote:
"At the end of the day it is what you produce that counts, not how many doctorates you have on the wall."
George Carlin wrote:
"Don't sweat the petty things, and don't pet the sweaty things."
|
|
|
|
|
Hi!
I'm trying to display the statistics for my site; at each visit I add a record to a table (Accessi ) which contains two fields, one is a DATETIME with the current time (Data ) and the other is a VARCHAR(15) containing the ip address (ip ).
I want to display the total visit count for a certain month.
I created this query, that seems to work correctly, but is really slow!
(it runs against ~60000 records, but since I select a month at a time it shouldn't be SO slow, and since I'm porting this query from access, at least it shouldn't take more time than it used to do before...)
SELECT DATE_FORMAT(Accessi.Data, '%Y/%m/%d') AS Giorno, Count(DATE_FORMAT(Accessi.Data, '%Y/%m/%d')) AS Accessi
FROM Accessi
GROUP BY Giorno
HAVING Giorno BETWEEN '2002/12/1' AND '2002/12/31'
Can anyone help me in improving this query?
Luca Leonardo Scorcia
http://zip.to/kojak (only in Italian)
|
|
|
|
|
Two initial things to check:
1. Do you have a clustered index / primary key on the DATETIME column?
2. Do you have an index on the IP address?
Also, make sure you're not avoiding the index by using a function on the column - try converting the between clause to use dates.
Lastly, try group only what you want by using a where clause, so:
SELECT DATE_FORMAT(Accessi.Data, '%Y/%m/%d') AS Giorno, Count(DATE_FORMAT(Accessi.Data, '%Y/%m/%d')) AS Accessi
FROM Accessi
WHERE Giorno BETWEEN convert(datetime,'2002/12/1') AND convert(datetime,'2002/12/31')
GROUP BY Giorno
Please let me know if this helps.
|
|
|
|
|
Well, I was using mySQL, not SQL Server, so the convert function is not available to me... but I figured out how to solve it.
SELECT DayOfYear(Accessi.Data) as numGiorno, Accessi.Data, Count(Accessi.Data) AS Accessi
FROM Accessi GROUP BY numGiorno
HAVING numGiorno BETWEEN DayOfYear('2002/12/1') AND DayOfYear('2002/12/31') AND Year(Accessi.Data) = 2002
Thanks for your effort anyway! Bye!
Luca Leonardo Scorcia
http://zip.to/kojak (only in Italian)
|
|
|
|
|
Environemnt: VC++6,Access DB via ODBC.
How would one determine if a table exists in the database.
I have tried "SELECT * FROM TABLE_NAME"
where TABLE_NAME is the table I want to check for existence.
Thinking I would get an error or invalid return, Yet the the error gets trap and an caught before I can get to it or test for a return condition?
There has to be an easy way to check for table existance. RIGHT?
Thanks for any help
--Errol
|
|
|
|
|
Try this:
IF EXISTS (SELECT name FROM sysobjecs WHERE id = object_id(N'tablename')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
SELECT * FROM tablename
Or to get a list of tables (using this query in the EXISTS clause above also works), this is even easier:
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
"Well, I wouldn't say I've been missing it, Bob." - Peter Gibbons
|
|
|
|
|
I have a remote MySQL database and I'm trying to connect to it via MFC. I've tried using CDatabase but whatever I try, it never connects. I'm really new at this. Any help is appreciated.
Thanks in advanced,
Steve
|
|
|
|
|
is there a way of getting all but One column values for a table rather than enumerating the entire list of parameters .
|
|
|
|
|
|
try 2.20 instead of 2,20
cheers,
_____________
stefan bornuz
|
|
|
|
|
We can create users to databases in MS SQL, apart from the default username 'sa'. I want users to login using a form that validates the username and password not from a table but from the users created for that particular database. How should I go about it using ADO.
Sreejit
|
|
|
|
|
Why don´t you use it in the User ID and Password properties of the connection object ? If you got an error when you access the database, the name and password would probably be wrong.
Mauricio Ritter - Brazil
Sonorking now: 100.13560 MRitter
Life is a mixture of painful separations from your loved ones and joyful reunions, without those two we'd just be animals I guess. The more painful the separation, that much more wonderful will be the reunion - Nish
"Th@ langwagje is screwed! It has if's but no end if's!! Stupid php cant even do butuns on forms! VISHAUL BASICS ARE THE FUTSHURE!" - Simon Walton
|
|
|
|
|
thank you,
I never thought of that. That shuld wrk. I will try it out.
Sreejit
|
|
|
|
|
Hi:
I have this challenge that i am sure many of us would have faced.
I want to write a stored procedure that has to list out all company names based on whether one of the characteristic has value 1..
For eg:
create procedure (proc_name) @paramcharacteristic varchar(40) as
select * from tblcompany where @paramCharacteristic=1
Now this @paramcharacteristic will always match one of the column names in the table like Performance,Satisfaction,Profits and so on.But this does not seem to work.
When i run the stored procedure from an application i get the error
Cannot convert varchar 'Satisfaction' to integer.So i think what it does is try to compare the value of @paramcharacteristic variable and 1.But instead i want SQL to understand the statement as
select * from tblcompany where Satisfaction=1
Where do u think should i make the change.In the definition of paramcharacteristic in the stored procedure??Pls give me some suggestions.On how to deal this situation..
Thanks in advance for the help..
Regards
Pradhip.S
If a Building is Completed then why do they call it BUILDING ??
|
|
|
|
|
One option is to build up the T-SQL statement dynamically, like:
exec('select * from sysobjects where ' + @myColumnName + ' >5')
Just watch out for SQL injection problems.
Cheers,
Simon
"The day I swan around in expensive suits is the day I hope someone puts a bullet in my head.", Chris Carter.
my svg article
|
|
|
|
|
You just saved me at least 3 days work.
Cheers mate!
|
|
|
|
|
Hi all..
I use SQL Server 2000 and i wonder how to connect to a remote SQL server (on a site not network) using query analyzer..
can I make that just the same way i do when i connect to a server on a network ?
|
|
|
|
|
You need to go into Client Network Utility and enter the connection parametes for the remote server ie.; TCP IP, IP address,server name) then go back into Query Analyzer and select the server from the list.
|
|
|
|