|
Thanks for your reply 
I'm a bit of a SQL noob so I'll have to look into using a DataSet but doing a SELECT COUNT will be effectively calling the same query twice (apart from actually returning the columns). Won't that be a great deal of overhead?
I just thought the SQLDataReader must know how many rows were returned when it's created from calling the SQLCommand's ExecuteReader() method. Is that the case?
|
|
|
|
|
Member 6038196 wrote: Won't that be a great deal of overhead?
A great deal? Not unless you're going to call this query often, couting the rows in a table and returning the result is quite fast.
Member 6038196 wrote: I just thought the SQLDataReader must know how many rows were returned when it's created from calling the SQLCommand's ExecuteReader() method. Is that the case?
If memory serves correct, it didn't - it just keeps getting records until it runs out of records. If you want both the records and the count in a single SQL-statement, use the DataAdapter.Fill method
I are troll
|
|
|
|
|
Thanks - much appreciated
|
|
|
|
|
Member 6038196 wrote: I just thought the SQLDataReader must know how many rows were returned when it's created from calling the SQLCommand's ExecuteReader() method. Is that the case?
A datareader only ever knows about 1 row. It's what's known as a Firehose cursor, whereby data is squirted through as quickly as possible. You could always just increment a count on each read and use that.
"WPF has many lovers. It's a veritable porn star!" - Josh Smith As Braveheart once said, "You can take our freedom but you'll never take our Hobnobs!" - Martin Hughes.
My blog | My articles | MoXAML PowerToys
|
|
|
|
|
Another solution for you if the others do not work,
SqlDataReader reader = cmd.ExcecuteReader();
int count = 0;
while ( reader.read){
// do your normal code for gathering column info
count++;
}
when its done reading you'll have your data and your count.
Hope that helps.
|
|
|
|
|
I am trying to select data from tables A and B
SELECT A.* , B.*
FROM A INNER JOIN B ON A.Id = B.Id
WHERE (A.Id=@id) AND(A.Date BETWEEN B.Date1 AND B.Date2)
the problem appears that fields B.Date1 and B.Date2 are not necessary containing data,
they can be null. so, my solution is
WHERE (A.Id=@id) AND
(A.Date BETWEEN CASE B.Date1 WHEN null THEN @DateMin ELSE B.Date1 END
AND CASE B.Date2 WHEN null THEN @DateMax ELSE B.Date2 END)
when i run this select i get error:
"The version of sql server in use does not support datatype 'udt'"
i can't figure out why i get this error. is there something to do with
@DateMin and @DateMax format ?? i think i am using the right format
can anybody help me,
thanks
|
|
|
|
|
As far as I know, there is no such ptredefined variables as @DateMin and @DateMax - assuming you are using sql server.
Something like this should get you what you want
WHERE (A.Id=@id) AND
(A.Date BETWEEN coalesce(B.Date1,A.Date) AND coalesce(B.Date2,A.Date))
The coalesce means take the first non-null value
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
thanks or your answer
i have this problem sorted out with ISNULL()
WHERE (A.Id=@id) AND
(A.Date BETWEEN ISNULL(B.Date1,@DateMin) AND coalesce(B.Date2,@DateMax))
@DateMin = '01/01/1900'
@DateMax = '01/01/2079'
works perfectly. why i couldn't come out with this at first
|
|
|
|
|
Just for info, coalesce is the ansi standard and has the advantage of allowing more than 2 options eg
coalesce(val1,val2,val3, val4) etc wll take the first non-null value rather than just isnull(val1,val2)
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
good one , i'll remember this,
thanks
|
|
|
|
|
No problem, its a useful one to know when you have several values to work with.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
I've given a client the following query to delete duplicate phone no. records in an MSSQL database, but now they need to also do it on MySQL, and they report that MySQL complains about the format of the query. I've included the setup of a test table with duplicates for my code sample, but the actual delete query is what counts.
I'm asking this in ignorance and urgency, as I am still busy downloading and installing MySQL, and just maybe somebody can help in the mean time.
create table bkPhone
(
phoneNo nvarchar(20),
firstName nvarchar(20),
lastName nvarchar(20)
)
GO
insert bkPhone values('0783313780','Brady','Kelly')
insert bkPhone values('0845319792','Mark','Smith')
insert bkPhone values('0834976958','Bill','Jones')
insert bkPhone values('0845319792','Mark','Smith')
insert bkPhone values('0828329792','Mickey','Mouse')
insert bkPhone values('0834976958','Bill','Jones')
alter table bkPhone add phoneId int identity
delete from bkPhone
where phoneId not in
(
select min(phoneId)
from bkPhone
group by phoneNo,firstName,lastName
having count(*) >= 1
)
|
|
|
|
|
Hope this will help you.
<br />
where phoneId not in <br />
( <br />
select phoneId from bkPhone order by phoneid asc limit 1<br />
<br />
)
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
hi, anyone can help me installing SQL Server 2005 Developer Edtion.
I have a screenshot of the error after installing the SQL Server 2005.
http://s713.photobucket.com/albums/ww134/vhanjess/?action=view¤t=Step11.jpg[^]
Actually, i'm installing this for 1 week now and i don't know how to fixed it. Maybe one of you will help me to fixed this.
Thanks and Regards
klaydze
if(you type your code here) {
Messagebox.Show("You help me a lot!");
}
else {
You help me = null;
}
|
|
|
|
|
At a guess you have permissioning issues. Have you created a user for SQL and the services to use.
No one can really help you based on a screen shot of the installation summary. Study the detail of the errors, the first one probably caused all the others so fix it first. Go through the installation directions IN DETAIL, there are 1000s of developer versions installed out there so it is not rocket science.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hi, thanks for the reply. My OS is Windows Vista Home Premium Edition.
I read many instruction on how to properly install this, i also follow some KB but no one help me to install the SQL.
Thanks and Regards.
if(you type your code here) {
Messagebox.Show("You help me a lot!");
}
else {
You help me = null;
}
|
|
|
|
|
hi all,,,
i have a database with a large log file database and i don't know how to do to make it smaller,,,
the file have a large area space what can i do?
thanks in advance,,,
modified on Saturday, March 21, 2009 4:55 PM
|
|
|
|
|
You can shrink the logfile on demand. There's help on how to do that from the Management Studio here[^], or if you want to do this from SQL, look here[^].
That will only help until the file fills up again, or you'll have to hunt down the reason why the logfile is filling up. The documentation on the transaction log can be found here[^].
Good luck 
I are troll
|
|
|
|
|
|
Hi
How to activate Oracle server ?
and how to close ?
thank's
|
|
|
|
|
Hi All,
In general, Sql Server having its default password. We can change the password also. But if I want to set a password for individual Databases in SqlServer 2005,
is it possible ?
Thanks in advance.
|
|
|
|
|
I don't believe so. Logins and passwords aren't specified at the database level - but users are. I guess you'd say that logins are specified at the instance level. Logins are then mapped to users. You could create different logins and only give them access to specific databases.
Scott
|
|
|
|
|
scottgp is correct.
dont map the database(s) that you dont want the user to have access to for that login.
|
|
|
|
|
 Now days I m working to construct a Relational Database in SQL Server2005 for a school student's Results. in which I've to plase following columns...
Calss Name, Class Section, Student RollNumber, Student Name, Student's Father Name, Student Address, Student Registration Number, Subjects Names, Totla Marks Assigned to each subject, obtained marks from each subject, CGPA, Percentage, Exam Session.
those above all are the necessary columns those I've to add up in a sequence that different classes lets suppose class name= 1 & calss section=1 & then class name=1 & class section =2 similarly multiple sections can have in each class & also I've to use upto 12 classes having their multiple sections.
second thing where I m facing great problem while relating tables is that I can't understand that how to arrage exam session table??? in whitch multiple sessions can be added according to owners demand. & finally how subjects & their marks can be assigned & manuplated....
can anyone help me throughout in making this relational database or just to designing a database diagram on that I can simply use select update & delete queiries & othere quires such as selecting specific class data of students results for the specific exam session or for a specific student's result for specific exam session??? can anyone help me throughout as soon as possible??? coz I've just one day left all other things are finalized now I've to just construct database & their procedures that where what to retrieve. so plz help me throught...
waiting for your urgent & quick response
|
|
|
|
|
Having left it to the last day to try and learn and build this stuff. You are going to FAIL. You deserve to fail and I for one will breathe a sigh of relief, another lazy idiot that will not make it into the ranks of junior programmers.
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
