Select Count

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to find out if any records were returned after a select. The
following code is what I am doing:

strSQL = "SELECT [med-id], [animal-id], [animal-name], medication, date,
dosage, frequeny, [vet-id], [veterinarian-name], notes FROM [medication]
Count WHERE [animal-id] = " + anum1;

cmSQL = new SqlCommand(strSQL, cnKennel) if (cnKennel.State !=
ConnectionState.Open)
cnKennel.Open();

drSQL = cmSQL.ExecuteReader();

if(drSQL.Read())
{
txtMedID.Text = drSQL["med-id"].ToString();
txtAnimalID.Text = drSQL["animal-id"].ToString();
txtAnimalName.Text = drSQL["animal-name"].ToString();
txtMedication.Text = drSQL["medication"].ToString();
txtDate.Text = drSQL["date"].ToString();
txtDosage.Text = drSQL["dosage"].ToString();
txtFreq.Text = drSQL["frequency"].ToString();
txtVetID.Text = drSQL["vet-id"].ToString();
gbVetName.Text = drSQL["veterinarian-name"].ToString();
txtMedNotes.Text = drSQL["notes"].ToString();
}

drSQL.Close();

Int32 count = (Int32)cmSQL.ExecuteScalar();

What am I doing wrong???
 
When it comes to datareaders, you don't know the number of rows in total
until the reader's done reading. Instead of using if(drSQL.Read()), use a
while(...). In the while loop, add your counter so that it increments in
each iteration of the loop. That'll give you the number of records.

HTH
Alt
 
Thanks much
--
Norm Bohana


S.M. Altaf said:
When it comes to datareaders, you don't know the number of rows in total
until the reader's done reading. Instead of using if(drSQL.Read()), use a
while(...). In the while loop, add your counter so that it increments in
each iteration of the loop. That'll give you the number of records.

HTH
Alt
--------------------------------------------------------------------------------
All that glitters has a high refractive index.
www.mendhak.com



nbohana said:
I am trying to find out if any records were returned after a select. The
following code is what I am doing:

strSQL = "SELECT [med-id], [animal-id], [animal-name], medication, date,
dosage, frequeny, [vet-id], [veterinarian-name], notes FROM [medication]
Count WHERE [animal-id] = " + anum1;

cmSQL = new SqlCommand(strSQL, cnKennel) if (cnKennel.State !=
ConnectionState.Open)
cnKennel.Open();

drSQL = cmSQL.ExecuteReader();

if(drSQL.Read())
{
txtMedID.Text = drSQL["med-id"].ToString();
txtAnimalID.Text = drSQL["animal-id"].ToString();
txtAnimalName.Text = drSQL["animal-name"].ToString();
txtMedication.Text = drSQL["medication"].ToString();
txtDate.Text = drSQL["date"].ToString();
txtDosage.Text = drSQL["dosage"].ToString();
txtFreq.Text = drSQL["frequency"].ToString();
txtVetID.Text = drSQL["vet-id"].ToString();
gbVetName.Text = drSQL["veterinarian-name"].ToString();
txtMedNotes.Text = drSQL["notes"].ToString();
}

drSQL.Close();

Int32 count = (Int32)cmSQL.ExecuteScalar();

What am I doing wrong???
Click to expand...
Click to expand...
 
nbohana,

Assuming that you want to use a progressbar.

You can do in advance an exexutescalar to get the numbers of rows that will
be readed (assuming there is not one deleted or added between the two
actions)

"Select count from table ...........where..........."

integer Count = Cmd.ExecuteScalar(sqlString,conn)

I hope this helps,

Cor
 
I wouldn't recommend this way as the select statement will be executed
twice - a waste of resources, and there is always a possibility of changes
in between (like Cor mentioned).
As for OP: if you are trying only to find if there were any records then it
is easy - if the app steps in "if" block - then yes.

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Cor Ligthert said:
nbohana,

Assuming that you want to use a progressbar.

You can do in advance an exexutescalar to get the numbers of rows that
will be readed (assuming there is not one deleted or added between the two
actions)

"Select count from table ...........where..........."

integer Count = Cmd.ExecuteScalar(sqlString,conn)

I hope this helps,

Cor

nbohana said:
I am trying to find out if any records were returned after a select. The
following code is what I am doing:

strSQL = "SELECT [med-id], [animal-id], [animal-name], medication, date,
dosage, frequeny, [vet-id], [veterinarian-name], notes FROM [medication]
Count WHERE [animal-id] = " + anum1;

cmSQL = new SqlCommand(strSQL, cnKennel) if (cnKennel.State !=
ConnectionState.Open)
cnKennel.Open();

drSQL = cmSQL.ExecuteReader();

if(drSQL.Read())
{
txtMedID.Text = drSQL["med-id"].ToString();
txtAnimalID.Text = drSQL["animal-id"].ToString();
txtAnimalName.Text = drSQL["animal-name"].ToString();
txtMedication.Text = drSQL["medication"].ToString();
txtDate.Text = drSQL["date"].ToString();
txtDosage.Text = drSQL["dosage"].ToString();
txtFreq.Text = drSQL["frequency"].ToString();
txtVetID.Text = drSQL["vet-id"].ToString();
gbVetName.Text = drSQL["veterinarian-name"].ToString();
txtMedNotes.Text = drSQL["notes"].ToString();
}

drSQL.Close();

Int32 count = (Int32)cmSQL.ExecuteScalar();

What am I doing wrong???
Click to expand...
Click to expand...
 
Norm,

ExecuteScalar returns a scalar value - your query doesn't return a scalar
value, it returns a tabular resultset. You need DataReader.HasRows for that
job.

Also, I see two other big problems with your code below

a) String concatenation to form your SQL - use parameters instead.
b) Not closing connection once you are done with it.
 
Miha,

Rub your eyes, pinch yourself, but no you are not dreaming. I am going to
argue on favor of Cor for a change here.

Select Count(*) is nearly not as expensive as a regular select :). So a
progress bar is totally possible. But this is where the dream ends.

To show a progress bar, you have to do CommandBehavior.SequentialRead -
which is hellaciously slower than normal execution of DataReader .. :)

Also it is true that changes could occur in the midst - but it's good enough
for a progressbar.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------


Miha Markic said:
I wouldn't recommend this way as the select statement will be executed
twice - a waste of resources, and there is always a possibility of changes
in between (like Cor mentioned).
As for OP: if you are trying only to find if there were any records then
it is easy - if the app steps in "if" block - then yes.

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Cor Ligthert said:
nbohana,

Assuming that you want to use a progressbar.

You can do in advance an exexutescalar to get the numbers of rows that
will be readed (assuming there is not one deleted or added between the
two actions)

"Select count from table ...........where..........."

integer Count = Cmd.ExecuteScalar(sqlString,conn)

I hope this helps,

Cor

nbohana said:
I am trying to find out if any records were returned after a select. The
following code is what I am doing:

strSQL = "SELECT [med-id], [animal-id], [animal-name], medication, date,
dosage, frequeny, [vet-id], [veterinarian-name], notes FROM [medication]
Count WHERE [animal-id] = " + anum1;

cmSQL = new SqlCommand(strSQL, cnKennel) if (cnKennel.State !=
ConnectionState.Open)
cnKennel.Open();

drSQL = cmSQL.ExecuteReader();

if(drSQL.Read())
{
txtMedID.Text = drSQL["med-id"].ToString();
txtAnimalID.Text = drSQL["animal-id"].ToString();
txtAnimalName.Text = drSQL["animal-name"].ToString();
txtMedication.Text = drSQL["medication"].ToString();
txtDate.Text = drSQL["date"].ToString();
txtDosage.Text = drSQL["dosage"].ToString();
txtFreq.Text = drSQL["frequency"].ToString();
txtVetID.Text = drSQL["vet-id"].ToString();
gbVetName.Text = drSQL["veterinarian-name"].ToString();
txtMedNotes.Text = drSQL["notes"].ToString();
}

drSQL.Close();

Int32 count = (Int32)cmSQL.ExecuteScalar();

What am I doing wrong???
Click to expand...
Click to expand...
Click to expand...
 
Doesn't ExecuteScalar return an object?

- SM

Cor Ligthert said:
nbohana,

Assuming that you want to use a progressbar.

You can do in advance an exexutescalar to get the numbers of rows that
will be readed (assuming there is not one deleted or added between the two
actions)

"Select count from table ...........where..........."

integer Count = Cmd.ExecuteScalar(sqlString,conn)

I hope this helps,

Cor

nbohana said:
I am trying to find out if any records were returned after a select. The
following code is what I am doing:

strSQL = "SELECT [med-id], [animal-id], [animal-name], medication, date,
dosage, frequeny, [vet-id], [veterinarian-name], notes FROM [medication]
Count WHERE [animal-id] = " + anum1;

cmSQL = new SqlCommand(strSQL, cnKennel) if (cnKennel.State !=
ConnectionState.Open)
cnKennel.Open();

drSQL = cmSQL.ExecuteReader();

if(drSQL.Read())
{
txtMedID.Text = drSQL["med-id"].ToString();
txtAnimalID.Text = drSQL["animal-id"].ToString();
txtAnimalName.Text = drSQL["animal-name"].ToString();
txtMedication.Text = drSQL["medication"].ToString();
txtDate.Text = drSQL["date"].ToString();
txtDosage.Text = drSQL["dosage"].ToString();
txtFreq.Text = drSQL["frequency"].ToString();
txtVetID.Text = drSQL["vet-id"].ToString();
gbVetName.Text = drSQL["veterinarian-name"].ToString();
txtMedNotes.Text = drSQL["notes"].ToString();
}

drSQL.Close();

Int32 count = (Int32)cmSQL.ExecuteScalar();

What am I doing wrong???
Click to expand...
Click to expand...
 
Sahil,
Rub your eyes, pinch yourself, but no you are not dreaming. I am going to
argue on favor of Cor for a change here.
Click to expand...

When I disagree with Miha on what he replies to me, than there is probably
nobody long time active in these dotNet newsgroup, who has the idea that I
won't write that.

See this sample that I once made, it is working and gives therefore as well
answers on your other questions.

http://www.windowsformsdatagridhelp.com/default.aspx?ID=49f2cff5-56ad-44fc-a4c6-fc0d5c470f53

It is surely not the most efficient way to do things, however when you want
by instance the progressbar than it is a way to go (In my opinion is (if it
really would take more than 10 seconds) a simple warning box much better).

I hope this helps,

Cor

Sahil Malik said:
Miha,

Rub your eyes, pinch yourself, but no you are not dreaming. I am going to
argue on favor of Cor for a change here.

Select Count(*) is nearly not as expensive as a regular select :). So a
progress bar is totally possible. But this is where the dream ends.

To show a progress bar, you have to do CommandBehavior.SequentialRead -
which is hellaciously slower than normal execution of DataReader .. :)

Also it is true that changes could occur in the midst - but it's good
enough for a progressbar.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------


Miha Markic said:
I wouldn't recommend this way as the select statement will be executed
twice - a waste of resources, and there is always a possibility of changes
in between (like Cor mentioned).
As for OP: if you are trying only to find if there were any records then
it is easy - if the app steps in "if" block - then yes.

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Cor Ligthert said:
nbohana,

Assuming that you want to use a progressbar.

You can do in advance an exexutescalar to get the numbers of rows that
will be readed (assuming there is not one deleted or added between the
two actions)

"Select count from table ...........where..........."

integer Count = Cmd.ExecuteScalar(sqlString,conn)

I hope this helps,

Cor

"nbohana" <[email protected]> schreef in bericht
I am trying to find out if any records were returned after a select. The
following code is what I am doing:

strSQL = "SELECT [med-id], [animal-id], [animal-name], medication,
date,
dosage, frequeny, [vet-id], [veterinarian-name], notes FROM
[medication]
Count WHERE [animal-id] = " + anum1;

cmSQL = new SqlCommand(strSQL, cnKennel) if (cnKennel.State !=
ConnectionState.Open)
cnKennel.Open();

drSQL = cmSQL.ExecuteReader();

if(drSQL.Read())
{
txtMedID.Text = drSQL["med-id"].ToString();
txtAnimalID.Text = drSQL["animal-id"].ToString();
txtAnimalName.Text = drSQL["animal-name"].ToString();
txtMedication.Text = drSQL["medication"].ToString();
txtDate.Text = drSQL["date"].ToString();
txtDosage.Text = drSQL["dosage"].ToString();
txtFreq.Text = drSQL["frequency"].ToString();
txtVetID.Text = drSQL["vet-id"].ToString();
gbVetName.Text = drSQL["veterinarian-name"].ToString();
txtMedNotes.Text = drSQL["notes"].ToString();
}

drSQL.Close();

Int32 count = (Int32)cmSQL.ExecuteScalar();

What am I doing wrong???
Click to expand...
Click to expand...
Click to expand...
 
Ah, no. SELECT Count(*) returns an approximate count and still forces the SQL Engine to do all of the membership selection work. Yes, you save the "transport the rows" step so it does not take quite as long--but it's still expensive. The count won't be exact unless the database is static and does not change before the rowset-returning SELECT has fetched its membership.

If your strategy must have an exact count each time, this approach simply won't work without a bunch of extra locking--which kills performance and scalability.

I've always suggested that developers avoid depending on a specific number of rows returned from a query. Sure, because we are smart and familiar with our databases we know about how many rows are going to be returned based on history. We can also limit that number artificially using TOP or bracketed WHERE clauses. However, in a general sense we need to be prepared for "N" rows (within reason). The "reason" comes from these artificial limits imposed on the query. If you're making a go-no-go decision based on the Count (that is, whether or not to perform the rowset-returning SELECT) I would probably try to figure out a more scalable approach.

hth
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________


Sahil Malik said:
Norm,

ExecuteScalar returns a scalar value - your query doesn't return a scalar
value, it returns a tabular resultset. You need DataReader.HasRows for that
job.

Also, I see two other big problems with your code below

a) String concatenation to form your SQL - use parameters instead.
b) Not closing connection once you are done with it.


--

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------



nbohana said:
I am trying to find out if any records were returned after a select. The
following code is what I am doing:

strSQL = "SELECT [med-id], [animal-id], [animal-name], medication, date,
dosage, frequeny, [vet-id], [veterinarian-name], notes FROM [medication]
Count WHERE [animal-id] = " + anum1;

cmSQL = new SqlCommand(strSQL, cnKennel) if (cnKennel.State !=
ConnectionState.Open)
cnKennel.Open();

drSQL = cmSQL.ExecuteReader();

if(drSQL.Read())
{
txtMedID.Text = drSQL["med-id"].ToString();
txtAnimalID.Text = drSQL["animal-id"].ToString();
txtAnimalName.Text = drSQL["animal-name"].ToString();
txtMedication.Text = drSQL["medication"].ToString();
txtDate.Text = drSQL["date"].ToString();
txtDosage.Text = drSQL["dosage"].ToString();
txtFreq.Text = drSQL["frequency"].ToString();
txtVetID.Text = drSQL["vet-id"].ToString();
gbVetName.Text = drSQL["veterinarian-name"].ToString();
txtMedNotes.Text = drSQL["notes"].ToString();
}

drSQL.Close();

Int32 count = (Int32)cmSQL.ExecuteScalar();

What am I doing wrong???
Click to expand...
Click to expand...
 
Sure .. but isn't an approximate number of results good enough for a progressbar?

- Sahil Malik [MVP]
ADO.NET 2.0 book - http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------

Ah, no. SELECT Count(*) returns an approximate count and still forces the SQL Engine to do all of the membership selection work. Yes, you save the "transport the rows" step so it does not take quite as long--but it's still expensive. The count won't be exact unless the database is static and does not change before the rowset-returning SELECT has fetched its membership.

If your strategy must have an exact count each time, this approach simply won't work without a bunch of extra locking--which kills performance and scalability.

I've always suggested that developers avoid depending on a specific number of rows returned from a query. Sure, because we are smart and familiar with our databases we know about how many rows are going to be returned based on history. We can also limit that number artificially using TOP or bracketed WHERE clauses. However, in a general sense we need to be prepared for "N" rows (within reason). The "reason" comes from these artificial limits imposed on the query. If you're making a go-no-go decision based on the Count (that is, whether or not to perform the rowset-returning SELECT) I would probably try to figure out a more scalable approach.

hth
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________


Sahil Malik said:
Norm,

ExecuteScalar returns a scalar value - your query doesn't return a scalar
value, it returns a tabular resultset. You need DataReader.HasRows for that
job.

Also, I see two other big problems with your code below

a) String concatenation to form your SQL - use parameters instead.
b) Not closing connection once you are done with it.


--

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------



nbohana said:
I am trying to find out if any records were returned after a select. The
following code is what I am doing:

strSQL = "SELECT [med-id], [animal-id], [animal-name], medication, date,
dosage, frequeny, [vet-id], [veterinarian-name], notes FROM [medication]
Count WHERE [animal-id] = " + anum1;

cmSQL = new SqlCommand(strSQL, cnKennel) if (cnKennel.State !=
ConnectionState.Open)
cnKennel.Open();

drSQL = cmSQL.ExecuteReader();

if(drSQL.Read())
{
txtMedID.Text = drSQL["med-id"].ToString();
txtAnimalID.Text = drSQL["animal-id"].ToString();
txtAnimalName.Text = drSQL["animal-name"].ToString();
txtMedication.Text = drSQL["medication"].ToString();
txtDate.Text = drSQL["date"].ToString();
txtDosage.Text = drSQL["dosage"].ToString();
txtFreq.Text = drSQL["frequency"].ToString();
txtVetID.Text = drSQL["vet-id"].ToString();
gbVetName.Text = drSQL["veterinarian-name"].ToString();
txtMedNotes.Text = drSQL["notes"].ToString();
}

drSQL.Close();

Int32 count = (Int32)cmSQL.ExecuteScalar();

What am I doing wrong???
Click to expand...
Click to expand...
 
It's certainly overkill. Access and lots of other applications "fake" the progress using a couple of techniques that are far more efficient--and work just as well. One approach is to just start incrementing every second and when they get to the end, they start over. Another technique is to move 1/4 the way remaining every second. This approach works best for shorter waits.


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Sure .. but isn't an approximate number of results good enough for a progressbar?

- Sahil Malik [MVP]
ADO.NET 2.0 book - http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------

Ah, no. SELECT Count(*) returns an approximate count and still forces the SQL Engine to do all of the membership selection work. Yes, you save the "transport the rows" step so it does not take quite as long--but it's still expensive. The count won't be exact unless the database is static and does not change before the rowset-returning SELECT has fetched its membership.

If your strategy must have an exact count each time, this approach simply won't work without a bunch of extra locking--which kills performance and scalability.

I've always suggested that developers avoid depending on a specific number of rows returned from a query. Sure, because we are smart and familiar with our databases we know about how many rows are going to be returned based on history. We can also limit that number artificially using TOP or bracketed WHERE clauses. However, in a general sense we need to be prepared for "N" rows (within reason). The "reason" comes from these artificial limits imposed on the query. If you're making a go-no-go decision based on the Count (that is, whether or not to perform the rowset-returning SELECT) I would probably try to figure out a more scalable approach.

hth
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________


Sahil Malik said:
Norm,

ExecuteScalar returns a scalar value - your query doesn't return a scalar
value, it returns a tabular resultset. You need DataReader.HasRows for that
job.

Also, I see two other big problems with your code below

a) String concatenation to form your SQL - use parameters instead.
b) Not closing connection once you are done with it.


--

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------



nbohana said:
I am trying to find out if any records were returned after a select. The
following code is what I am doing:

strSQL = "SELECT [med-id], [animal-id], [animal-name], medication, date,
dosage, frequeny, [vet-id], [veterinarian-name], notes FROM [medication]
Count WHERE [animal-id] = " + anum1;

cmSQL = new SqlCommand(strSQL, cnKennel) if (cnKennel.State !=
ConnectionState.Open)
cnKennel.Open();

drSQL = cmSQL.ExecuteReader();

if(drSQL.Read())
{
txtMedID.Text = drSQL["med-id"].ToString();
txtAnimalID.Text = drSQL["animal-id"].ToString();
txtAnimalName.Text = drSQL["animal-name"].ToString();
txtMedication.Text = drSQL["medication"].ToString();
txtDate.Text = drSQL["date"].ToString();
txtDosage.Text = drSQL["dosage"].ToString();
txtFreq.Text = drSQL["frequency"].ToString();
txtVetID.Text = drSQL["vet-id"].ToString();
gbVetName.Text = drSQL["veterinarian-name"].ToString();
txtMedNotes.Text = drSQL["notes"].ToString();
}

drSQL.Close();

Int32 count = (Int32)cmSQL.ExecuteScalar();

What am I doing wrong???
Click to expand...
Click to expand...
 
Okay but those are not true progress bars :). For a true progress bar, you go start to finish - as the work completes. If there wasn't a requirement to show a true progress bar, an hour glass or it's equivalent would have been enough.

Also, Select count(*) uses the narrowest index available on the table, it is not as inefficient as a select *. (And thats not even considering the lesser data it needs to transfer). Also, you can use query hints to speed up select count(*) at the risk of relative inaccuracy (which again is not a big deal).

- SM

It's certainly overkill. Access and lots of other applications "fake" the progress using a couple of techniques that are far more efficient--and work just as well. One approach is to just start incrementing every second and when they get to the end, they start over. Another technique is to move 1/4 the way remaining every second. This approach works best for shorter waits.


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Sure .. but isn't an approximate number of results good enough for a progressbar?

- Sahil Malik [MVP]
ADO.NET 2.0 book - http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------

Ah, no. SELECT Count(*) returns an approximate count and still forces the SQL Engine to do all of the membership selection work. Yes, you save the "transport the rows" step so it does not take quite as long--but it's still expensive. The count won't be exact unless the database is static and does not change before the rowset-returning SELECT has fetched its membership.

If your strategy must have an exact count each time, this approach simply won't work without a bunch of extra locking--which kills performance and scalability.

I've always suggested that developers avoid depending on a specific number of rows returned from a query. Sure, because we are smart and familiar with our databases we know about how many rows are going to be returned based on history. We can also limit that number artificially using TOP or bracketed WHERE clauses. However, in a general sense we need to be prepared for "N" rows (within reason). The "reason" comes from these artificial limits imposed on the query. If you're making a go-no-go decision based on the Count (that is, whether or not to perform the rowset-returning SELECT) I would probably try to figure out a more scalable approach.

hth
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________


Sahil Malik said:
Norm,

ExecuteScalar returns a scalar value - your query doesn't return a scalar
value, it returns a tabular resultset. You need DataReader.HasRows for that
job.

Also, I see two other big problems with your code below

a) String concatenation to form your SQL - use parameters instead.
b) Not closing connection once you are done with it.


--

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------



nbohana said:
I am trying to find out if any records were returned after a select. The
following code is what I am doing:

strSQL = "SELECT [med-id], [animal-id], [animal-name], medication, date,
dosage, frequeny, [vet-id], [veterinarian-name], notes FROM [medication]
Count WHERE [animal-id] = " + anum1;

cmSQL = new SqlCommand(strSQL, cnKennel) if (cnKennel.State !=
ConnectionState.Open)
cnKennel.Open();

drSQL = cmSQL.ExecuteReader();

if(drSQL.Read())
{
txtMedID.Text = drSQL["med-id"].ToString();
txtAnimalID.Text = drSQL["animal-id"].ToString();
txtAnimalName.Text = drSQL["animal-name"].ToString();
txtMedication.Text = drSQL["medication"].ToString();
txtDate.Text = drSQL["date"].ToString();
txtDosage.Text = drSQL["dosage"].ToString();
txtFreq.Text = drSQL["frequency"].ToString();
txtVetID.Text = drSQL["vet-id"].ToString();
gbVetName.Text = drSQL["veterinarian-name"].ToString();
txtMedNotes.Text = drSQL["notes"].ToString();
}

drSQL.Close();

Int32 count = (Int32)cmSQL.ExecuteScalar();

What am I doing wrong???
Click to expand...
Click to expand...
 
Back
Top