Re: Is SQL 2000 tran working with VS2008?

Discussion in 'ASP .Net' started by Alexey Smirnov, Feb 5, 2010.

  1. On Feb 5, 10:15 pm, "aspfun via DotNetMonster.com" <u53138@uwe> wrote:
    > 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.comhttp://www.dotnetmonster.com/Uwe/Forums.aspx/asp-net/201002/1


    Are you sure that you execute SP from ASP.NET, does it work when table
    is not renamed?
    Alexey Smirnov, Feb 5, 2010
    #1
    1. Advertising

  2. Alexey Smirnov

    Mr. Arnold Guest

    aspfun via DotNetMonster.com wrote:
    > Mark Rae [MVP] wrote:
    >>>> Are you sure that you execute SP from ASP.NET, does it work when table
    >>>> is not renamed?
    >>> Yes, I try to figure out the problem for two days but no luck.

    >> 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/lifecy...sort=PN&alpha=SQL Server 2000&Filter=FilterNO
    >>

    >
    > 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
    >


    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.
    Mr. Arnold, Feb 7, 2010
    #2
    1. Advertising

  3. Alexey Smirnov

    Mr. Arnold Guest

    aspfun via DotNetMonster.com wrote:
    > Mark Rae [MVP] wrote:
    >>>> Please show your code.
    >>> Here is the code
    >>>
    >>> CREATE PROCEDURE DeleteDepartment

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

    > 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)
    >


    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 doesn't seem you're trapping the @@error at the right time possibly.
    Mr. Arnold, Feb 7, 2010
    #3
  4. On Feb 7, 10:03 am, "aspfun via DotNetMonster.com" <u53138@uwe> wrote:
    > Mark Rae [MVP] wrote:
    > >>> Are you sure that you execute SP from ASP.NET, does it work when table
    > >>> is not renamed?

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

    >
    > >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&al....

    >
    > Here is the code copied fromhttp://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.comhttp://www.dotnetmonster.com/Uwe/Forums.aspx/asp-net/201002/1


    Here's the code that works

    SqlConnection conn = null;

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

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

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

    Hope this helps
    Alexey Smirnov, Feb 8, 2010
    #4
  5. On 8 Feb., 17:08, "aspfun via DotNetMonster.com" <u53138@uwe> wrote:
    > Alexey Smirnov wrote:
    > >> >>> Are you sure that you execute SP from ASP.NET, does it work when table
    > >> >>> is not renamed?

    > >[quoted text clipped - 46 lines]
    > >> --
    > >> Message posted via DotNetMonster.comhttp://www.dotnetmonster.com/Uwe/Forums.aspx/asp-net/201002/1

    >
    > >Here's the code that works

    >
    > > SqlConnection conn = null;

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

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

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

    >
    > >Hope this helps

    >
    > Thank you. I'll 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.comhttp://www.dotnetmonster.com/Uwe/Forums.aspx/asp-net/201002/1


    Well, I don't think that this is a server issue. I think your code was
    wrong
    Alexey Smirnov, Feb 8, 2010
    #5
  6. On Feb 8, 7:46 pm, "aspfun via DotNetMonster.com" <u53138@uwe> wrote:
    > Alexey Smirnov wrote:
    > >> >> >>> Are you sure that you execute SP from ASP.NET, does it work when table
    > >> >> >>> is not renamed?

    > >[quoted text clipped - 42 lines]
    > >> --
    > >> Message posted via DotNetMonster.comhttp://www.dotnetmonster.com/Uwe/Forums.aspx/asp-net/201002/1

    >
    > >Well, I don't think that this is a server issue. I think your code was
    > >wrong

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


    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 don't 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.
    Alexey Smirnov, Feb 9, 2010
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?Z2F1cmF2?=

    Uploading Data From MS Acess 2000 ti SQL server 2000

    =?Utf-8?B?Z2F1cmF2?=, Jan 9, 2006, in forum: ASP .Net
    Replies:
    2
    Views:
    783
    Mary Chipman [MSFT]
    Jan 9, 2006
  2. =?Utf-8?B?SmltIEhlYXZleQ==?=

    Oracle - cannot ROLLBACK in a distributed tran

    =?Utf-8?B?SmltIEhlYXZleQ==?=, Feb 3, 2005, in forum: ASP .Net
    Replies:
    0
    Views:
    940
    =?Utf-8?B?SmltIEhlYXZleQ==?=
    Feb 3, 2005
  3. Replies:
    1
    Views:
    656
    Esmond Pitt
    Mar 27, 2005
  4. sloan
    Replies:
    2
    Views:
    1,107
  5. Replies:
    0
    Views:
    270
Loading...

Share This Page