Getting Errors with Transaction

[Jonathan Wood]

I wrote code to insert a record into a table. It worked fine. Then I added a
transaction using code essentially like this:

using (SqlConnection conn = new
SqlConnection(ConfigurationManager.ConnectionStrings["DTSConnectionString"].ToString()))
{
conn.Open();
trans = conn.BeginTransaction();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "INSERT INTO Contracts (Title, Description, StartDate,
EndDate) VALUES (@Title, @Description, @StartDate, @EndDate)";
cmd.Parameters.Add(new SqlParameter("@Title", txtTitle.Text));
cmd.Parameters.Add(new SqlParameter("@Description",
txtDescription.Text));
cmd.Parameters.Add(new SqlParameter("@StartDate", start));
cmd.Parameters.Add(new SqlParameter("@EndDate", end));
int contractId = (int)cmd.ExecuteScalar();
}

With this code, the call to ExecuteScaler() gives me the error
"ExecuteScalar 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."

So, I tried adding the following line just before the call to
ExecuteScaler():

cmd.Transaction = trans;

Now, the same call gives me the error "Object reference not set to an
instance of an object."

I'm still a little new to this and just couldn't figure out exactly what I'm
supposed to be doing here. I'd appreciate any help on this.

Thanks.

Jonathan

 

[Jonathan Wood]

I see now the error is due to the fact that ExecScalar() returns null. I
found several references that show that is the way to return the
SCOPE_IDENTITY() of an inserted row. Guess I'll keep researching that one.

Jonathan

 

[Jim Rand]

If you use a data adapter and you change your sql to:

....@EndDate); SELECT ContractID WHERE ContractID = SCOPE_IDENTITY();

the data adapter's Update() method will retrieve the new key.

I see now the error is due to the fact that ExecScalar() returns null. I
found several references that show that is the way to return the
SCOPE_IDENTITY() of an inserted row. Guess I'll keep researching that one.

Jonathan

I wrote code to insert a record into a table. It worked fine. Then I added
a transaction using code essentially like this:

using (SqlConnection conn = new
SqlConnection(ConfigurationManager.ConnectionStrings["DTSConnectionString"].ToString()))
{
conn.Open();
trans = conn.BeginTransaction();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "INSERT INTO Contracts (Title, Description,
StartDate, EndDate) VALUES (@Title, @Description, @StartDate, @EndDate)";
cmd.Parameters.Add(new SqlParameter("@Title", txtTitle.Text));
cmd.Parameters.Add(new SqlParameter("@Description",
txtDescription.Text));
cmd.Parameters.Add(new SqlParameter("@StartDate", start));
cmd.Parameters.Add(new SqlParameter("@EndDate", end));
int contractId = (int)cmd.ExecuteScalar();
}

With this code, the call to ExecuteScaler() gives me the error
"ExecuteScalar 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."

So, I tried adding the following line just before the call to
ExecuteScaler():

cmd.Transaction = trans;

Now, the same call gives me the error "Object reference not set to an
instance of an object."

I'm still a little new to this and just couldn't figure out exactly what
I'm supposed to be doing here. I'd appreciate any help on this.

Thanks.

Jonathan

 

[Jonathan Wood]

Jim,

If you use a data adapter and you change your sql to:

...@EndDate); SELECT ContractID WHERE ContractID = SCOPE_IDENTITY();

the data adapter's Update() method will retrieve the new key.

Well, thanks, although I don't really understand this reply. Actually, I
found that returning SCOPE_IDENTITY() works fine if I run the result through
Convert.ToInt32().

I don't really understand the advantage of using a data adapter, or the
reason for the query above.

I see now the error is due to the fact that ExecScalar() returns null. I
found several references that show that is the way to return the
SCOPE_IDENTITY() of an inserted row. Guess I'll keep researching that one.

Jonathan

I wrote code to insert a record into a table. It worked fine. Then I
added a transaction using code essentially like this:

using (SqlConnection conn = new
SqlConnection(ConfigurationManager.ConnectionStrings["DTSConnectionString"].ToString()))
{
conn.Open();
trans = conn.BeginTransaction();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "INSERT INTO Contracts (Title, Description,
StartDate, EndDate) VALUES (@Title, @Description, @StartDate,
@EndDate)";
cmd.Parameters.Add(new SqlParameter("@Title", txtTitle.Text));
cmd.Parameters.Add(new SqlParameter("@Description",
txtDescription.Text));
cmd.Parameters.Add(new SqlParameter("@StartDate", start));
cmd.Parameters.Add(new SqlParameter("@EndDate", end));
int contractId = (int)cmd.ExecuteScalar();
}

With this code, the call to ExecuteScaler() gives me the error
"ExecuteScalar 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."

So, I tried adding the following line just before the call to
ExecuteScaler():

cmd.Transaction = trans;

Now, the same call gives me the error "Object reference not set to an
instance of an object."

I'm still a little new to this and just couldn't figure out exactly what
I'm supposed to be doing here. I'd appreciate any help on this.

Thanks.

Jonathan