Click here to Skip to main content
15,891,513 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I dont know what to do further. Please Help me

What I have tried:

using (SqlCommand readCompu = new SqlCommand("select [computer name] from computer", con))
            {
                SqlDataReader readComputers = readCompu.ExecuteReader();
                while (readComputers.Read())
                {
                    
                    commppName = readComputers[0].ToString();
                    if (listAllConnectedComputers.Items.Contains(commppName))
                    {
                        //Update Computer set status='Connected'
                        using (SqlCommand insertToDB = new SqlCommand("update computer set [Computer Status]='Connected' where [computer name]='" + commppName + "' ", con))
                        {
                            insertToDB.ExecuteNonQuery();
                        }
                    }
                    else
                    {
                        //Update Computer set status='Disconnected'
                        using (SqlCommand inseritToDB = new SqlCommand("update computer set [Computer Status]='Disconnected' where [computer name]='" + commppName + "' ", con))
                        {
                            inseritToDB.ExecuteNonQuery();
                        }
                    }
                } readComputers.Close();
            }
Posted
Updated 18-Apr-17 0:35am
Comments
F-ES Sitecore 17-Apr-17 18:30pm    
when you get an error always say which line the error occurs on.

The error is pretty specific. You have a Reader open on a connection already. You cannot use the connection for any other queries until the Reader is closed first.

The answer to that is quite simple. Create and open a second connection to the database to execute your UPDATE queries on.
 
Share this answer
 
Comments
Maciej Los 18-Apr-17 7:06am    
Yeah, it's quite easy to resolve, but OP have to be warned about... see my answer.
BTW: 4!
As Dave Kreskoviak mentioned in His answer, the error is very specific and easy to solve...

But... i need to warn you!

1)
Your code is SQL Injection[^] vulnerable!
Never use concatenated string as command text! Use parameterized queries[^] instead!

See:
How To: Protect From Injection Attacks in ASP.NET[^]
How to: Execute a Parameterized Query[^]

2)
I'd strogly recommend to NOT use such of software on your database, because it kills database performance and might the reason of serious troubles. Imagine, you have a list of 10K computers. Your software produces 10K queries in few seconds, which searches and changes the data in your database. Everytime when new query is runing, your software creates new connection. So, 10K connections in few seconds - this remainds me an attack on SQL Server, which you execute on your wish. OMG! Your SQL server will stop responding or will start rejecting another connection.

Please, read this:
Security Monitoring and Attack Detection[^]
Definition of a Security Vulnerability[^]
Chapter 14 - Improving SQL Server Performance[^] - old documentation, but very helpful in your case.

There's few possible solutions. One of them is:
Update your database on MS SQL server level using single update statement, for example:
SQL
UPDATE t1
SET t1.[Status]  = t2.[Status]
FROM Computer AS t1 INNER JOIN ConnectedComputers AS t2 ON t1.CompName = t2.CompName


More at: How to UPDATE from a SELECT in SQL Server? - Stack Overflow[^]

By The Way! This might be helpful too: Visual Representation of SQL Joins[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900