1

When I try to select values from a local database it executes without any issue. But when I try to insert and delete it's executing the query but it's not affecting any rows.

This is the code I'm using to delete row from my local database:

        SqlCeConnection sqlConnection1 = new SqlCeConnection();
        sqlConnection1.ConnectionString = "Data Source = Database1.sdf";

        SqlCeCommand cmd = sqlConnection1.CreateCommand();
        cmd.CommandType = System.Data.CommandType.Text;
        cmd.CommandText = "DELETE FROM table1 WHERE slno=2";
        cmd.Connection = sqlConnection1;

        sqlConnection1.Open();
        cmd.Prepare();
        int aff=cmd.ExecuteNonQuery();//here its returning '0'
        MessageBox.Show(aff.ToString());
        sqlConnection1.Dispose();
        sqlConnection1.Close();
Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
deepi
  • 1,081
  • 10
  • 18
  • Could you try to handle the exception please? put your code in a try catch block and try to see what the exception is thrown, it might help to diagnose the issue. – AnarchistGeek Jul 20 '11 at 10:58
  • silly question but are you sure you have a row in table1 with slno = 2? – Richard Blewett Jul 20 '11 at 10:58
  • Are you sure, that the data is really in the database? Are you sure, you are accessing the correct .sdf file? The problem could be that there is one in your project folder and one in bin\Debug. – Daniel Hilgarth Jul 20 '11 at 10:58
  • ya with the same table i'm executed 'select' command it's working but for insert and delete it's not working – deepi Jul 20 '11 at 11:02
  • @AnarchistGeek : it's not giving any exception but it not executing delete – deepi Jul 20 '11 at 11:03

1 Answers1

1

It is possible that the delete won't affect any rows.

As an aside, I would advise using the using statement in your code:

using (SqlCeConnection conn = new SqlCeConnection("Data Source = Database1.sdf"))
using (SqlCeCommand comm = new SqlCeCommand("DELETE FROM table1 WHERE slno = 2", conn))
{
    conn.Open();
    comm.CommandType = CommandType.Text;
    comm.ExecuteNonQuery();
}

This will handle disposal of relevant objects for you.

Assuming that the SQL is relevant to your database and that you have successfully connected using the connection string beforehand, then the above could will perform a delete action against your database.

Adam Houldsworth
  • 63,413
  • 11
  • 150
  • 187
  • If you are basing that entirely on the returned rows I would suggest executing the same SQL in management studio to confirm that the delete itself does anything. – Adam Houldsworth Jul 20 '11 at 11:09