0

I have a SQL Server database in a C# project.

I use a connection string to connect to it.. I can use the method ExecuteNonQuery to insert data, no problem there.

But when I delete, it only deletes it momentarily, as soon as I restart the application it kind of rolls back the deletion.. Any ideas?

PS: I tested the string in a direct query, and it worked fine there.

public void executeNonQuery(string input)
{
    db.Open();
    SqlCommand cmd = new SqlCommand(input, db);
    cmd.ExecuteNonQuery();
    db.Close();
}

EDIT: DELETION CODE:

private void buttonSletPost_Click(object sender, EventArgs e)
    {
        if(dataGridView1.GetCellCount(DataGridViewElementStates.Selected)>0){
            for (int i = 0;i < dataGridView1.GetCellCount(DataGridViewElementStates.Selected); i++)
            {
                String str1 = String.Format("WARNING about to DELETE:\n {0} \n BE CAREFULL NO TURNING BACK NOW!", Regnskab.getInstance().dbSelectPostID(dataGridView1.SelectedCells[i].Value.ToString())[0].ToString());
                if (MessageBox.Show(str1, "Confirm Deletion", MessageBoxButtons.YesNo) == DialogResult.Yes)
                {

                    string str = String.Format("DELETE FROM PostTable WHERE PostID={0}", dataGridView1.SelectedCells[i].Value.ToString());
                    Database.getInstance().executeNonQuery(str);
                    Console.WriteLine(str);
                }
            }
        }else {MessageBox.Show("No cells selected");}
    }

Which will give following output:

DELETE FROM PostTable WHERE PostID=7

Connection string in app.config:

<connectionStrings>
    <add name="EndProject.Properties.Settings.DBtestConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\DBtest.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True" providerName="System.Data.SqlClient" />


 private Database() 
 {
        string connStr = ConfigurationManager.ConnectionStrings["EndProject.Properties.Settings.DBtestConnectionString"].ConnectionString;
        db = new SqlConnection(connStr);
 }

And then I open and close it, so the connection ain't open each time.

To be honest:

I don't exactly know where to see my DB info, here's some info from properties in VS2010.

Provider: .NET Framework Data Provider for SQL Server

Type: Microsoft SQL Server

Andrew's 3rd: I think they are deleted momentarily because I reloaded the information in my datagridview and from there it is gone. But then when I close the application and start it again, it is back...

Also i just checked with VS2010's server explorer and did a "Show Data" after I deleted (before I shut it down) and it wasn't deleted.

But I'm totally clueless now. :-(

Improve this question
6
  • 1
    could you please provide the code that you are using to delete the table(s) or database(s)? Commented Dec 16, 2010 at 21:10
  • 1
    Are you working with Visual Studio and are overseeing that you have set "Copy to Output Directory" to "Copy always" or "Copy if newer"? (Although that contradicts your statement that inserting is no problem - but check anyway). Commented Dec 16, 2010 at 21:11
  • 2
    I would like to see 1) where you are creating and disposing the connection (I hope you aren't caching it or keeping it alive for multiple commands) 2) exactly what SQL you are using 3) what makes you think the rows are 'deleted momentarily' 4) what makes you think they are 'rolled back' Commented Dec 16, 2010 at 21:12
  • Can show us the connection string you're using?? I'm suspecting you might be using a "user-instance" type database - those get created in your directory where the app runs, and are "disposed" of when the app is done.... Commented Dec 16, 2010 at 21:30
  • marc_s > I am using the connectiong string, have been all the time, i use it to insert and select with no problems. which stays there for all time. Andrew Barber >> Ill update the main thread with that information in a sec :-) Olaf > Where to see those? :-) Commented Dec 16, 2010 at 22:01

4 Answers 4

Reset to default
3

Many applications use Transactions to manage db connections. SQL Server doesn't do it by default, but other factors in your application may be doing this.

Also, if you're really doing this, and your input is coming from a user interface, I can't wait to introduce you to Little Bobby Tables

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Hehe that might be a problem at some point, thanks for noticing it to me.. More validatings on its way ;-) Can you tell me what other factors in my application could do this... And wouldn't the inserts also rollback if it was transactions, since i am using same method to do it?
We might be able to tell you, if you add the information I requested in my comment to your question. That's really not enough code to hazard much of a guess at all. Need More Input...
Its up there now And its not transactions, i have checked that below by trying to do so. And it should also rollback my inserts if it were.
0

Use the SQL Server Profiler to run a trace and capture the actual T-SQL statements that are getting executed on the database.

This is the easiest solution.

Improve this answer

Comments

0

The behavior you're describing sounds like autocommit is off. Try the following and see if the record(s) stays deleted:

public void executeNonQuery(string input)
{
         db.Open();
         using (var txn = db.BeginTransaction())
         {
             SqlCommand cmd = new SqlCommand(input, db);
             cmd.Transaction = txn;
             cmd.ExecuteNonQuery();
             db.Close();
             txn.Commit();
        }
}
Improve this answer

1 Comment

Additional information: ExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized. -------------------------------------------------------------------- Seems no transactions is on
0

Ok it was apperently me who was mistaken on the inserting part... Someone suggested that i was because of the Visual Studio databases created every time the applikation ran. So i installed MS SQL 2008 R2. Created a new DB with same layout. Changed the connection string And wuupti woo it seems to work, ill come back to this thread if it breaks down later.. But delete + insert both working greatly now :-)

Thanks to all who tried to help!

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.