Update transaction in SQL Server 2008 R2 from ASP.Net not working
- by Amarus
Hello!
Even though I've been a stalker here for ages, this is the first post I'm making. Hopefully, it won't end here and more optimistically future posts might actually be me trying to give a hand to someone else, I do owe this community that much and more.
Now, what I'm trying to do is simple and most probably the reason behind it not working is my own stupidity. However, I'm stumped here.
I'm working on an ASP.Net website that interacts with an SQL Server 2008 R2 database. So far everything has been going okay but updating a row (or more) just won't work. I even tried copying and pasting code from this site and others but it's always the same thing.
In short: No exception or errors are shown when the update command executes (it even gives the correct count of affected rows) but no changes are actually made on the database.
Here's a simplified version of my code (the original had more commands and tons of parameters each, but even when it's like this it doesn't work):
protected void btSubmit_Click(object sender, EventArgs e)
{
using (SqlConnection connection =
new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString))
{
string commandString = "UPDATE [impoundLotAlpha].[dbo].[Vehicle]" +
"SET [VehicleMake] = @VehicleMake" +
" WHERE [ComplaintID] = @ComplaintID";
using (SqlCommand command = new SqlCommand(commandString, connection))
{
SqlTransaction transaction = null;
try
{
command.Connection.Open();
transaction = connection.BeginTransaction(IsolationLevel.Serializable);
command.Transaction = transaction;
SqlParameter complaintID = new SqlParameter("@complaintID", SqlDbType.Int);
complaintID.Value = HttpContext.Current.Request.QueryString["complaintID"];
command.Parameters.Add(complaintID);
SqlParameter VehicleMake = new SqlParameter("@VehicleMake", SqlDbType.VarChar, 20);
VehicleMake.Value = tbVehicleMake.Text;
command.Parameters.Add(VehicleMake);
command.ExecuteNonQuery();
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
finally
{
connection.Close();
}
}
}
}
I've tried this with the "SqlTransaction" stuff and without it and nothing changes.
Also, since I'm doing multiple updates at once, I want to have them act as a single transaction. I've found that it can be either done like this or by use of the classes included in the System.Transactions namespace (CommittableTransaction, TransactionScope...).
I tried all I could find but didn't get any different results.
The connection string in web.config is as follows:
<connectionStrings>
<add name="ApplicationServices"
connectionString="Data Source=localhost;Initial Catalog=ImpoundLotAlpha;Integrated Security=True"
providerName="System.Data.SqlClient"/>
</connectionStrings>
So, tldr; version:
What is the mistake that I did with that record update attempt? (Figured it out, check below if you're having a similar issue.)
What is the best method to gather multiple update commands as a single transaction?
Thanks in advance for any kind of help and/or suggestions!
Edit:
It seems that I was lacking some sleep yesterday cause this time it only took me 5 minutes to figure out my mistake.
Apparently the update was working properly but I failed to notice that the textbox values were being overwritten in Page_Load. For some reason I had this part commented:
if (IsPostBack)
return;
The second part of the question still stands. But should I post this as an answer to my own question or keep it like this?
