Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
hello

to delete all record in the database I use this code

C#
public static void RemoveOnline()
       {
           try
           {

               SqlConnection connection = new SqlConnection();
               connection.ConnectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename='D:\New Code\WinDB.mdf';Connect Timeout=30;User Instance=True;Integrated Security=SSPI";
               connection.Open();


               SqlCommand command1 = new SqlCommand("DELETE FROM Online  ", connection);

               command1.ExecuteNonQuery();




               connection.Close();


           }
           catch (SqlException e)
           {
               MessageBox.Show(e.Message);
           }
       }


I call previous code when close widow is happened
as follow
C#
private void Form1_FormClosing(Object sender, FormClosingEventArgs e)
        {
            StopServer();
            ConnectToDataBase.RemoveOnline();
        }



but its dose not work
and delete nothing
what is the wrong ? :S

thanks
Posted

Your code looks valid at a glance. When you call the ExecuteNonQuery, take the return value into a variable. So modify your code to
C#
...
int rowsaffected = command1.ExecuteNonQuery();
...

Now using debugger, check what is the value of rowsaffected. It should be the total amount of rows.

If that's true then you're actually deleting all of the rows so the next question in that case would be, do you copy the mdf file as part of your application to the directory D:\New Code\? For example is the mdf file part of your project and defined to be copied to the output directory when you compile the app? If that is the case, then you actually 'reset' the database every time you compile and that could be the reason why the modification is 'lost'.

Also note the answer from Mark Nischalke. It's better to use TRUNCATE, but make that modification after you have tested how many rows are actually deleted using the DELETE statement.
 
Share this answer
 
Comments
Shahin Khorshidnia 24-May-12 14:51pm    
+5
Wendelius 24-May-12 15:28pm    
Thanks :)
Maciej Los 24-May-12 16:56pm    
Good point, my 5!
Wendelius 25-May-12 0:26am    
Thanks :)
The command
SQL
DELETE FROM Online

is correct, but i prefer to use TRUNCATE[^] (see my comment to Mark Nischalke anser).

In my opinion, the problem is in the below procedure:
C#
private void Form1_FormClosing(Object sender, FormClosingEventArgs e)
        {
            StopServer();
            ConnectToDataBase.RemoveOnline();
        }

When you stop server, how do you want to connect to it and delete data?
 
Share this answer
 
Comments
Shahin Khorshidnia 24-May-12 14:48pm    
+5
Maciej Los 24-May-12 14:51pm    
Thank you, Shahin ;)
Wendelius 25-May-12 0:27am    
That could also be the problem
Maciej Los 25-May-12 3:53am    
Thank you, Mika ;)
It would be better to use TRUNCATE[^]. It is faster, uses less resources and is the recommended practice for removing all rows from a table.
 
Share this answer
 
Comments
Shahin Khorshidnia 24-May-12 14:30pm    
It's a real Comment!
Not an answer. Truncate is faster but doesn't solve op's problem.
[no name] 24-May-12 14:40pm    
Thanks for the continued pettiness of deleting and down voting. You have been reported.
Shahin Khorshidnia 24-May-12 14:44pm    
And Thank you for Deleting my Solution and Down Voting my correct answer and using abusing words like "Go Away". You've been reported first :)
Shahin Khorshidnia 24-May-12 15:43pm    
Sorry Mark.
I'm going to reconcile. I never harbor a grudge against anybody. I've checked the past and I've seen that I voted +5 many times and I never downvoted to your answers up to now. I also didn't now, (contented to a simple criticism) but you ... Ok, Forget it.
Sorry again.
Hi,

please try to delete space after your table name.

C#
new SqlCommand("DELETE FROM Online", connection);

also try
C#
new SqlCommand("DELETE * FROM Online", connection);



Does it work by invoking it without FormClosing-Event, i.e. onButtonClick Event?

Regards
 
Share this answer
 
v3
Comments
[no name] 24-May-12 14:25pm    
"DELETE * FROM Online" is not correct syntax

"try to delete space after your DELETE Statement"
Doing this will also make the syntax invalid
El_Codero 24-May-12 14:30pm    
hm, why it's not correct to use "DELETE * FROM Online"? see http://www.w3schools.com/sql/sql_delete.asp
my mistake, I expressed wrong, what I pointed to was to delete the spaces after table name, not straight after DELETE Statement,sorry for this. Regards
[no name] 24-May-12 14:32pm    
Not valid in SQL Server. Try it.

The spaces after the table name don't matter. The command is trimmed.
El_Codero 24-May-12 14:40pm    
oh ok, just checked it and you're right. I didn't thought about that * in Delete-Syntax isn't supported by SQL Server, thanks for correcting. Regards
Maciej Los 24-May-12 14:40pm    
First of all, command DELETE * FROM Online wouldn't work and DELETE FROM Online is totally correct. Second, space after command has no matter. See my anser.

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