.NET Framework - Is SQL 2000 tran working with VS2008?

Asked By aspfun via DotNetMonster.com

05-Feb-10 09:15 PM

I code a store procedure mySP in SQL 2000 in which using transaction.
I used two ways to test after saving sp.

1) rename one table
or
2) rename column name

If I use "exec mySP" in SQL 2000 query window, error will catch but ASP.NET
try-catch will catch nothing, it always return no error.

Is SQL 2000 tran working with VS2008?

--
Message posted via DotNetMonster.com
http://www.dotnetmonster.com/Uwe/Forums.aspx/asp-net/201002/1

05-Feb-10 05:52 PM

ET
ms.aspx/asp-net/201002/1

Are you sure that you execute SP from ASP.NET, does it work when table
is not renamed?

05-Feb-10 11:32 PM

Yes, I try to figure out the problem for two days but no luck.

--
Message posted via DotNetMonster.com
http://www.dotnetmonster.com/Uwe/Forums.aspx/asp-net/201002/1

06-Feb-10 08:40 AM

Please show your code.

Also, FYI, SQL Server 2000 has not been supported by Microsoft for nearly
two years unless you have taken out an extended support contract:
http://support.microsoft.com/lifecycle/search/default.aspx?sort=PN&alpha=SQL+Server+2000&Filter=FilterNO


--
Mark Rae
ASP.NET MVP
http://www.markrae.net

07-Feb-10 09:03 AM

Here is the code copied from
http://www.4guysfromrolla.com/webtech/080305-1.shtml
In asp.net code behind, I use try-catch try to catch any error but never
catch it.
In SQL database, if I rename Employees to Employeesx or change column
DepartmentID to DepartmentIDx, record will not be deleted (it is right)
without any error (it is wrong, suppose catch an error).

CREATE PROCEDURE DeleteDepartment
(
@DepartmentID    int
)
AS

BEGIN TRANSACTION

DELETE FROM Employees
WHERE DepartmentID = @DepartmentID

IF @@ERROR <> 0
BEGIN
ROLLBACK
RAISERROR ('Error', 16, 1)
RETURN
END

DELETE FROM Departments
WHERE DepartmentID = @DepartmentID

IF @@ERROR <> 0
BEGIN
ROLLBACK
RAISERROR ('Error', 16, 1)
RETURN
END

OMMIT

--
Message posted via DotNetMonster.com
http://www.dotnetmonster.com/Uwe/Forums.aspx/asp-net/201002/1

07-Feb-10 05:37 AM

Well, you would 'return' @@ERROR as an Output parm from the sproc and
check the Output Error code in your code that called the sproc.

If Output Return code <> 0, then the sproc aborted.

You should look-up how to return an output parm from a SQL Server Stored
Procedure.

So, on one hand, you have try/catch looking for SQL exception, but on
the other hand, you are looking at the Output Parm Error code. If Output
Parm Error code = 0, then ok, else terminate program.

07-Feb-10 05:54 AM

That's great, but now please show your code...


--
Mark Rae
ASP.NET MVP
http://www.markrae.net

07-Feb-10 11:06 AM

I did use output parameter as set @myerror = @@error.
In ASP.NET catch block, @myerror is cought but always = 0 no matter how to
test, such as rename table or rename column name. (both tests should return
@myerror <> 0 but not)

--
Message posted via DotNetMonster.com
http://www.dotnetmonster.com/Uwe/Forums.aspx/asp-net/201002/1

07-Feb-10 06:30 AM

Well, if any T-SQL statement is executed after the error and the
statement is successful, then @@error is going to equal 0. Each T-SQL
statement that is successfully executed after the error is going to
override what ever was in @@error when the error occurred.

The error was not critical enough to throw a SQL exception, and the
sproc executed other T-SQL statements after the error, which resulted in
@@error being set to 0.

It does not seem you are trapping the @@error at the right time possibly.

08-Feb-10 03:45 AM

e
y
..
1.shtml
ms.aspx/asp-net/201002/1

Here is the code that works

SqlConnection conn =3D null;

try
{
conn =3D new SqlConnection("....");
conn.Open();

SqlCommand cmd =3D new SqlCommand("DeleteDepartment",
conn);
SqlParameter p =3D new SqlParameter("@DepartmentID",
xxx);
cmd.Parameters.Add(p);
cmd.CommandType =3D CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Response.Write("Error: " + ex.Message);
}
finally
{
if (conn !=3D null)
{
conn.Close();
}
}

Tested against SQL Server  2000 - 8.00.2187 (x86) Enterprise Edition
(Build 2195: Service Pack 4)

Hope this helps

08-Feb-10 04:08 PM

Thank you. I will test it.
I just check sql server in our company, they are only have sp2 installed.
(sp4 is available for a long time). Is it cause problem of not catching error?


--
Message posted via DotNetMonster.com
http://www.dotnetmonster.com/Uwe/Forums.aspx/asp-net/201002/1

08-Feb-10 01:13 PM

able
orums.aspx/asp-net/201002/1
eDepartment",
partmentID",
ocedure;
rror?
ms.aspx/asp-net/201002/1

Well, I do not think that this is a server issue. I think your code was
wrong

08-Feb-10 06:46 PM

I checked my code, it is the same as yours.

Protected Function Test(ByVal _id As Integer) As Boolean
Dim myDSN As String = ConfigurationManager.AppSettings("ConnectionString")
//get connection from web.config
Dim myConn As New SqlConnection(myDSN)
Dim mySQLCommand As New SqlCommand("sSQLTransation", myConn)
mySQLCommand.CommandType = CommandType.StoredProcedure

Dim prmid As New SqlParameter("@DepartmentID", SqlDbType.Int, 4)
prmid.Value = _id
mySQLCommand.Parameters.Add(prmid)

Using myConn
Try
myConn.Open()
mySQLCommand.ExecuteNonQuery()
myConn.Close()
Return True
Catch ex As SqlException
Me.lblMessagebar.Text = ex.Message
Return False
Finally
If myConn IsNot Nothing Then
myConn.Close()
End If
End Try
End Using
End Function

--
Message posted via DotNetMonster.com
http://www.dotnetmonster.com/Uwe/Forums.aspx/asp-net/201002/1

08-Feb-10 07:05 PM

Hear! Hear! ADO.NET and SQL server have been around a long time for it
to be a SQL server issue.

The OP is doing something wrong in code.

09-Feb-10 02:42 AM

n table
orums.aspx/asp-net/201002/1

There are few things in your code where it might be wrong.

1) sSQLTransation is not the same stored procedure
2) check if ConfigurationManager.AppSettings("ConnectionString")
returns correct database
3) check if lblMessagebar is visible and you do not change its text
after calling the Test() function

It must be something in the code. Try to simplify it as much as
possible (for example, create a test page where you run only one
function and see what happens)

Hope this helps.