CODEWITHSUNDEEP
sql-servervb.net
ThebigD
ThebigD

Reputation: 25

SQL SELECT COUNT(*) sometimes returns data

Im grateful for some assistance for a bug in my application that is puzzling me !!

I check the number of rows in a table that contain the PersonID of the person i am looking up using:

cmd.commandtext = "SELECT Count (*) from PeopleDetails where PersonID = '11111'"
count = cmd.executescalar()

This works 98% of the time. Occasionally the count variable has data from the first column of the row being queried which is a GUID in the table called rowid. It does not have the expected integer of the number of rows that met the condition. The connection is live and if the query is repeated it returns the correct count value.

This is seen most when the server is busiest first thing in the morning when there are multiple simultaenous queries to the the database.

Help most welcome !!

SQL server 2003, vb.net

Thanks for looking !

Upvotes: 0

Views: 142

Answers (1)

Luaan
Luaan

Reputation: 63772

Are you creating a new SqlConnection for each of the commands? This looks like you're reusing the same SqlConnection from multiple threads.

SqlConnection/SqlCommand isn't thread-safe, so you really want to avoid using it concurrently from different threads.

In general, don't be afraid of creating a new SqlConnection for each command you send (or at least for a given method scope); with connection pooling enabled, it's almost free to do so, and it saves you a lot of hurt on concurrency issues.

Upvotes: 1

Related Questions