CODEWITHSUNDEEP
c#sqlsql-server-express
user1401335
user1401335

Reputation: 9

Why doesn't it work (SQL command in C#?

I'd like to ask you why doesn't this code work? It goes without any error, even cmd.ExecuteNonQuery(); returns 1 (is it if changes one row in the database), but in the actual database, there's absolutely no change. With other database tables, this code works properly, but I'm also not able to remove a row from this table - it behaves asi it if was "read-only", but I have no idea why - yesterday, everything worked fine and now, it's suddenly stopped working :-(

string sConnectionString;

                sConnectionString = "Data Source=.\\SQLEXPRESS; AttachDbFilename=\"" + zdielaneInfo.Adresar + "\\rozvrh.mdf\";";
                sConnectionString += "Integrated Security=True;User Instance=True";
                SqlConnection objConn
                    = new SqlConnection(sConnectionString);
                objConn.Open();



                SqlCommand cmd = new SqlCommand("", objConn);
                if (zdielaneInfo.Edit)

                    cmd.CommandText = "UPDATE subject " +
                                        "SET name = @name, day = @day, timeStart = @timeStart, timeEnd = @timeEnd "
                                        + "WHERE id = @id";
                else
                    cmd.CommandText = "INSERT INTO subject (name, day, timeStart, timeEnd) " +
                                         "Values (@name, @day, @timeStart, @timeEnd)";
                cmd.Parameters.Add(new SqlParameter("@name", txbName.Text));
                cmd.Parameters.Add(new SqlParameter("@day", dniNaInt(cbDen.Text)));
                cmd.Parameters.Add(new SqlParameter("@timeStart", DateTime.Parse(txbStart.Text)));
                cmd.Parameters.Add(new SqlParameter("@timeEnd", DateTime.Parse(txbEnd.Text)));
                cmd.Parameters.Add(new SqlParameter("@id", zdielaneInfo.Id));
                cmd.ExecuteNonQuery();
                objConn.Close();

Upvotes: 0

Views: 682

Answers (3)

Aaron Bertrand
Aaron Bertrand

Reputation: 280615

STOP using the user instance / attachdbfilename options (user instance is deprecated!). Create your database on a real SQL Server, then connect to it directly with your connection string. Using this deprecated feature means that every time you start up your program you're starting with a new copy of the database, and what you inserted yesterday is no longer there - and if you connect to the database using that connection string from two different applications, one is not going to see the data that the other one is changing.

Upvotes: 0

aF.
aF.

Reputation: 66747

Maybe the table is locked.

From HERE, try this:

select  
    object_name(P.object_id) as TableName, 
    resource_type, resource_description
from
    sys.dm_tran_locks L
    join sys.partitions P on L.resource_associated_entity_id = p.hobt_id

If your table is in the result set you have your answer.

Another possibility is the user that you are using to run. Maybe he got privileges revoked.

Upvotes: 1

hjgraca
hjgraca

Reputation: 1713

Your problem looks like mdf file overwrite problem.

You are accessing mdf files that are put in the debug folder and replaced every time you run the application.

Be sure that in your project, if you have the database attached within your solution that you are not overwriting it. So select the mdf file in your solution explorer and make sure that its "Copy to output" is set to "Do not copy", then manually copy over the mdf file to the project\bin\debug folder then run the application.

Hope it helps.

Upvotes: 1

Related Questions