How to iterate a table in sqland delete a row upon matching

Question

0.00/5 (No votes)

See more:

i have an integer saved upon form load, when clicking a button i want to search the table in SQL and see if it has this integer i need to delete the entire row this is the code :

C#

private void button3_Click(object sender, EventArgs e)
       {
           try
           {

               using (var cmd106 = new SqlCommand("select * from [dbo].[incidentNoReserve]", cnn))
               {

                   cnn.Open();
                   SqlDataReader reader = cmd106.ExecuteReader();
                   while (reader.Read())
                   {
                       int commonNo = Convert.ToInt16(reader["incidentNoReserveId"]);
                       //Every new row will create a new dictionary that holds the columns
                       if (Convert.ToInt16(commonNo) == newLastIncidentNo)
                       {
                           newLastIncidentNo = (Convert.ToInt16(commonNo));
                           using (SqlCommand command = new SqlCommand("Delete from[dbo].[incidentNoReserve] where incidentNoReserveId = @newLastIncidentNo", cnn))
                           {
                               cnn.Open();
                               command.Parameters.AddWithValue("@newLastIncidentNo", newLastIncidentNo);
                               command.ExecuteNonQuery();
                               cnn.Close();
                           }
                       }
                   }
                   reader.Close();
                   cnn.Close();
               }
           }
           catch (Exception ex)
           {
               //If an exception occurs, write it to the console
               Console.WriteLine(ex.ToString());
           }
           finally
           {
               cnn.Close();
           }
           this.Close();
       }

What I have tried:

i have used this code its fine until it reaches the delete command,it stops on cnn.open() and jumps outside the scope dont know whats the problem.

Posted 20-Jun-16 10:59am

ramy nemer

Updated 20-Jun-16 11:23am

Add a Solution

Comments

FranzBe 20-Jun-16 17:07pm

in the block you posted, there are two cnn.Open() one after another.
the outer using block should be a "using connection" the inner using block than can be a "using command".
There is a blank missing after "delete from"

ramy nemer 20-Jun-16 17:21pm

it worked i erased the inner cnn.Open and close, but u mean adding in outer block a connection before using (var cmd106 = new SqlCommand("select * from [dbo].[incidentNoReserve]", cnn)) ??

FranzBe 20-Jun-16 17:27pm

for me that's the "normal" way to use it. the connection is your mayor object. so it comes first.

using (var connection = new SqlConnection(ConnectionString))
{
connection.Open();
using (var command = ... )
{
}
}

this assumes that you open and close the connection for every command.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Dave Kreskowiak · Accepted Answer · 2016-06-20T11:23:00

Solution 1

Where do I begin... There's a ton of problems with this code

First, the code is a bit difficult to follow, but it looks like you're enumerating over all of the records in the [incidentNoReserve] table deleting them all. There are better ways of doing this than deleting records one at a time.

Next, you only have one connection to the database, but how you're doing it requires two. A connection that has an active Reader on it cannot be used for anything else until that Reader is done and closed.

Along those same lines, you have the connection, "cnn", being opened TWICE. It's already open, you cannot open it again without closing it first.

Your SELECT statement is returning all columns (*) in the incidentNoReserve table but you're only using a single column from it. Never use the * specifier. Always specify every column you want. That way you limit the amount of work the SQL server has to do, limit the amount of information going "over the wire" to your code, make your code more resistent to schema changes and make it easier to debug.

You're converting a record Id to an Int16. This limits your record Id values to a maximum of 32767. In the grand scheme of things, that's not very many records.

What are you really trying to do with this code?

Posted 20-Jun-16 11:23am

Dave Kreskowiak

Comments

ramy nemer 20-Jun-16 17:48pm

very helpfull thanks , i am not deleting all rows,based on matching i am deleting a single row with the matching Id.
what am i trying to do is once running the application i bring the last Id +1 from incidentNo and then saving this id in incidentNoReserve tbl,if i exit the application this Id is deleted without saving anything but if application is saved this Id is saved in incidentNo tbl and deleted from incidentNoReserve ,by this work i can retrieve an Id upon form load and reserve a place for it in database. Now am changing and fixing the if statements to ensure safety in database.

ramy nemer 20-Jun-16 17:53pm

i have a question concerning iteration,while reading rows if there is a row having Id = newLastIncidentNo,i increment newLastIncidentNo,but i want the reader to start over again how should i do it ? this is the code :

using (var cmd105 = new SqlCommand("select incidentNoReserveId from [dbo].[incidentNoReserve]", cnn))
{
cnn.Open();

SqlDataReader reader = cmd105.ExecuteReader();

while (reader.Read())
{ //Every new row will create a new dictionary that holds the columns
if ((Convert.ToInt16(reader["incidentNoReserveId"])) == newLastIncidentNo)
{
newLastIncidentNo = newLastIncidentNo + 1;
//reader.Close();
}

}
cnn.Close();

}

Dave Kreskowiak 20-Jun-16 19:04pm

I don't know what's in the table, but the code is precariously close to removing every single record in the table.

What you're describing in your other comment sounds very much like you're trying to assign your own ID numbers to a table. That code will never work in a multiuser system where you have multiple copies of your application running against the same database.

You REALLY should be using autonumbered Id columns for this stuff. There is no such thing as "reserving" an Id number and having it work nicely.