0

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 !

ThebigD
  • 25
  • 6
  • Do you have any other sql statement in the same method that cmd.commandtext which probably returns first column? – Adil Mar 13 '15 at 12:56
  • 3
    This is not your real code. How do you pass the PersonID value? There are other lines of code that interact with the db? Please show more context. With so few lines of code there is no possibility that you get the behavior documented – Steve Mar 13 '15 at 13:00
  • Are you creating a new `SqlConnection` for each of the commands? This looks like you're reusing the same `SqlConnection` from multiple threads. – Luaan Mar 13 '15 at 13:07
  • Are you using the same cmd instance for multiple queries? You should only use one per query. – the_lotus Mar 13 '15 at 13:23
  • Use "SELECT COUNT(1)...." instead of "SELECT COUNT(*)..." as a best practice. Even though SQL Server is generally smart enough to optimize away the "*" so you're not pulling back every single column value of every single row that matches, it's more clear that you're just interested in the count. And it's a good practice to avoid the * in shipping code anyway (always list explicitly the columns you're interested in). While that might eliminate your problem, it doesn't explain it... – pmbAustin Mar 13 '15 at 13:29
  • 1
    @pmbAustin [count(1) and count(*) are the same](http://stackoverflow.com/questions/1221559/count-vs-count1) – the_lotus Mar 13 '15 at 13:43
  • I believe I acknowledged that in my comment, but gave a good reason to choose select COUNT(1) over select COUNT(*) anyway... get out of the habit of using * everywhere. Understand what * means, even if the optimizer optimizes it away. – pmbAustin Mar 13 '15 at 14:01
  • Sorry.... To clarify I am reusing the connection but not the command.... Which is new and simply created using the cmd = myconn.createcommand syntax. Personid is passed as personid=@personid And then Personid is declared as a parameter The strange thing is that 98 percent of the time it returns the correct value.... Thanks @steve – ThebigD Mar 17 '15 at 23:53
  • Thanks @pmbAustin I have updated the code to try count(1) and as I have a sporadic problem will need to wait to see if it recurs..... Would be awfully strange if select count(*) return row 1 column 1 data.... Surely either way should only return an integer... Thanks for help... – ThebigD Mar 17 '15 at 23:55
  • @adil nope this cmd is created new but the connection is reused.....i understood each command is uniquely identified by the sql and the 'answer' is returned to the specific command even if two used simultaneously... But then I understand many things wrongly....Thanks for suggestions – ThebigD Mar 17 '15 at 23:57
  • I have tracked down the issue.... As suggested by @luaan I was inadvertently passing a connection to a different thread..... Many thanks, – ThebigD Mar 18 '15 at 13:42
  • @ThebigD I posted that as an answer, so you can accept it. I'm not sure if the question fits SO well, but maybe it will help someone. – Luaan Mar 18 '15 at 13:45
  • SQL 2003? Did I miss something? – cypizek Mar 18 '15 at 13:56

1 Answers1

1

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.

Luaan
  • 62,244
  • 7
  • 97
  • 116