Error in Checking Conditions

[S2009]

Hi

all,


I am creating a project for Library System(WINDOWS APPLICATION ).

Front End: Visual Studio - C# Programming Language
Back End : SQL SERVER 2005.

I have a table in which I maintain Inventory and Books related Details. Now at the time of issuing book I want to check whether the Inventory of the book is greater than 1.

If the Inventory is Greater than 1, it should add NumberOfBooks Issued by 1 and Subtract Inventory by 1. To perform this task I have created the following Trigger.

TRIGGER CREATION:

    create TRIGGER trg_InsertInBookIssuedDetails ON BOOKISSUEDDETAILS
    FOR INSERT
    AS
    begin
    declare
    @UserName nvarchar(50),
    @BookID int,     
    @Inventory int
    begin try
    begin tran
    if exists(SELECT * FROM LIBRARYBOOKDETAILS WHERE BookID = @BookID and Inventory >=1)    
    begin   
    SELECT @UserName = IssuedTo,@BookID = BookID FROM BOOKISSUEDDETAILS
    UPDATE NewUserRegister SET NumberOfBooksIssued = NumberOfBooksIssued+1 WHERE UserName = @UserName
    UPDATE LIBRARYBOOKDETAILS SET Inventory = Inventory - 1 WHERE BookID= @BookID
    commit tran
    end
    else
    begin
    raiserror('Books Unavailable',16,-1)
    end
    end try   
    begin catch
    raiserror('Books UnAvailable Or Cannot Issue More Than 3 Books',16,-1)
    rollback tran
    end catch
    end

When I execute the above statements I am getting commands executed Successfully.

And the Coding for Issue Button is as follows:

        SqlConnection objSqlConnection = new SqlConnection();
        string connectionStringSettings = "Data Source = .; Initial Catalog = LibrarySystemManagement; Integrated Security = SSPI";
        private void btnIssue_Click(object sender, EventArgs e)
        {
            if (txtIssuedTo.Text.Trim() == "")
            {
                MessageBox.Show("Issued To Field Cannot be Blank", "Error Information", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            else
            {
                try
                {
                    objSqlConnection.ConnectionString = connectionStringSettings;
                    objSqlConnection.Open();
                    SqlDataAdapter objSqlDataAdapter = new SqlDataAdapter("select * from BOOKISSUEDDETAILS", objSqlConnection);
                    SqlCommandBuilder objSqlCommandBuilder = new SqlCommandBuilder(objSqlDataAdapter);
                    DataSet objDataSet = new DataSet("BOOKISSUEDDETAILS");
                    objSqlDataAdapter.Fill(objDataSet, "BOOKISSUEDDETAILS");
                    DataRow objDataRow = objDataSet.Tables["BOOKISSUEDDETAILS"].NewRow();
                    objDataRow[1] = txtBookID.Text;
                    objDataRow[2] = System.DateTime.Now;
                    objDataRow[3] = txtIssuedTo.Text;
                    objDataRow[4] = System.DBNull.Value;
                    objDataSet.Tables["BOOKISSUEDDETAILS"].Rows.Add(objDataRow);
                    objSqlDataAdapter.Update(objDataSet, "BOOKISSUEDDETAILS");
                    objSqlConnection.Close();
                    MessageBox.Show("Books Issued Successfully", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                catch (SqlException e1)
                {
                    MessageBox.Show(e1.Source + "\n" + e1.Message);
                    objSqlConnection.Close();
                }
                catch (Exception e2)
                {
                    MessageBox.Show(e2.Message + "\n" + e2.Source);
                    objSqlConnection.Close();
                }
            }
        }

Now when I execute the Windows Application I m getting the Error Message irrespective of the inventory and NumberOfBooksIssued:

.Net SqlClient DataProvider
Books UnAvailable or cannot Issue morethan 3 books.
The transaction has ended in Trigger. Batch has been aborted.

For example.

Even if the number of books issued is lessthan 3 and inventory is Greater Than 1 I am getting the Error Message as mentioned in the raiserror statement.

Can anyone tell me what changes I should make to perform the desired task?

Please help me out!!

Thanks in advance!!