sqlclient fails to execute anything except select statements

Question

Given that query is a valid update statement, the following code executes to completion, no errors. But no changes are made to the db. It fails silently.

            cmdSQL.CommandText = query;
            Con.Open();
            cmdSQL.Transaction = Con.BeginTransaction();
            cmdSQL.ExecuteNonQuery();    //returns 1 where expected
            cmdSQL.Transaction.Commit();
            Con.Close();

so does this:

            cmdSQL.CommandText = query;
            Con.Open();
            cmdSQL.Transaction = Con.BeginTransaction();

            SqlDataReader reader;
            reader = cmdSQL.ExecuteReader();

            int fields = reader.FieldCount;
            while (reader.Read())
            {
                for (int i = 0; i < fields; i++)
                    details.Add("" + reader[i]);
            }

            reader.Close();
            cmdSQL.Transaction.Commit();
            Con.Close();

Here's the connection string:

Data Source=(LocalDB)\\v11.0;AttachDbFilename=|DataDirectory|\\LocalDB.mdf;Integrated Security=True

So the question is: How do I get updates to work?

PS: select statements work fine.

PPS. The database was created through visual studio 2013... It's a service based database

PPPS. I asked this question too... I assume they have a similar answers. Same problem, different approach: TableAdapter.Update returns 1 but no changes in db

What do you mean with _code fails silently_? Did you follow the code with a debugger and did you see the code executed without any error message? Neither an exception raised and catched by a try/block? — Steve, Jun 25 '14 at 12:41
You should generally check the return code to see if an error occurred. You may lack update permission in SQL Server for example. — JonnyBoats, Jun 25 '14 at 12:41
@JonnyBoats--the ExecuteNonQuery return code is the number of rows updated (so check if > 0), whereas insufficient permissions will result in an exception being thrown. — Polyfun, Jun 25 '14 at 12:44
@Steve: I mean there were no errors. Everything executed. No data was changed in the database. — Sheena, Jun 25 '14 at 13:17
@ShellShock: I forgot to mention, I get `1` when I do the update... — Sheena, Jun 25 '14 at 13:18
@KevinCook: Not that I know of. Unless VisualStudio made some behind my back — Sheena, Jun 25 '14 at 13:25
A possible common scenario with these symptoms is using DataDirectory in the connection string. Do you use it? — Steve, Jun 25 '14 at 13:26
@Steve: Yes. But using the static directory instead of the data directory it still does it. I've edited the question to include the connection string — Sheena, Jun 25 '14 at 13:30
@Steve: The reason I'm using the dynamic directory is because the application is to be deployed with a local database. — Sheena, Jun 25 '14 at 13:31
I can point you to this question/answer and let you check if this is your case http://stackoverflow.com/questions/17147249/why-dont-changes-to-database-save/17147460#17147460 — Steve, Jun 25 '14 at 13:32
@Steve: you rock my world!!! If you put in an answer with a link to your other answer I'll give you some more points :) — Sheena, Jun 25 '14 at 13:35
@Sheena, if that answer is the right solution for your problem, then the correct way to handle the situation is `closing as duplicate` Of course if instead that's not the answer let me know and I could retract the closing vote. — Steve, Jun 25 '14 at 13:37

sqlclient fails to execute anything except select statements

0 Answers0