×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Call to stored procedure does nothing

Call to stored procedure does nothing

Call to stored procedure does nothing

(OP)
I'm working with an ASP.Net 4.0 project, and I'm calling a stored procedure on a SQL 2008 64-bit server. When I call the stored procedure, I don't get an error message, but the stored procedure doesn't do anything. When I call the stored proc natively in SQL Management Studio, it works fine. I've walked through the code in the debugger, and all the parameters are getting the right values, so I don't really know what's going on here.

Here's my code:

CODE -->

UserID = MoveRow["UserID"].ToString();
                     string strSelect = "SELECT * FROM Tasks WHERE UserID = " + UserID + " AND ClsdDate IS NULL AND Task LIKE 'Employee Move - " + RequestItemName + "%'";
  
                     DataSet dsTrackit = new DataSet();
                     SqlConnection dsConn = new SqlConnection(ConfigurationManager.AppSettings["DSN_TRACKIT"]);
                     SqlTransaction tran = null;
                     SqlDataAdapter dsDataAdapter = new SqlDataAdapter(strSelect, dsConn);
                     SqlCommandBuilder dsCommandBuilder = new SqlCommandBuilder(dsDataAdapter);
                     dsDataAdapter.UpdateCommand = dsCommandBuilder.GetUpdateCommand();
                     dsDataAdapter.Fill(dsTrackit, "DelTasks");
                     DataTable dtTrackit = dsTrackit.Tables[0];
  
                     string DBString = ConfigurationManager.AppSettings["DSN_TRACKIT"];
  
                     using (SqlConnection conn = new SqlConnection(DBString))
                     {
                         try
                         {
                             conn.Open();
                             SqlDataAdapter da = new SqlDataAdapter("", conn);
                             tran = conn.BeginTransaction();
                             SqlCommand delTASKCommand = new SqlCommand();
                             SqlParameter TicketParam = null;
                             delTASKCommand.CommandType = CommandType.StoredProcedure;
                             delTASKCommand.CommandText = ConfigurationManager.AppSettings["TrackitDB"].ToString() + ".dbo.[rems_CloseIndivTask]";
  
                             foreach (DataRow row in dtTrackit.Rows)
                             {
                                 TicketParam = new SqlParameter("@WO_NUM", SqlDbType.Int);
                                 TicketParam.Value = Convert.ToInt32(row["WO_NUM"]);
                                 delTASKCommand.Parameters.Add(TicketParam);
  
                                 TicketParam = new SqlParameter("@REQUESTORID", SqlDbType.NVarChar, 6);
                                 TicketParam.Value = System.Web.HttpContext.Current.Session["RequestorID"].ToString();
                                 delTASKCommand.Parameters.Add(TicketParam);
  
                                 TicketParam = new SqlParameter("@COMMENTS", SqlDbType.NVarChar, 4000);
                                 TicketParam.Value = MoveRow["End_Comments"].ToString();
                                 delTASKCommand.Parameters.Add(TicketParam);
  
                                 delTASKCommand.Connection = conn;
                                 //delTASKCommand.Transaction = tran;
                                 da.InsertCommand = delTASKCommand;
                                 da.AcceptChangesDuringUpdate = false;
                                 da.Update(dtTrackit);
                                 dtTrackit.AcceptChanges();
                             }
                             tran.Commit();
                         }
                         catch (System.Exception ex)
                         {
                             EmailUtils.ExceptionNotification(CallingPage, ex.Message, ex.StackTrace);
                             tran.Rollback();
                             throw ex;
                         }
                         finally
                         {
                             conn.Close();
                         }
                     } 

And here's the stored procedure:

CODE -->

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE  PROCEDURE [dbo].[rems_CloseIndivTask]
	@WO_Num as int,
	@RequestorID as nvarchar(6),
	@Comments as nvarchar(4000)
As
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION

--CLOSE THE TASKS RECORD USING THE USERID
UPDATE Tasks
SET CLSDBY = @RequestorID,
CLSDDATE = getdate(),
COMPLETED = getdate(),
STATUS = 'Completed',
WORKORDERSTATUSID = 2
WHERE WO_Num = @WO_Num

DECLARE @CursorVar1 CURSOR

SET @CursorVar1 = CURSOR LOCAL FAST_FORWARD
FOR
SELECT TEXTPTR(Descript), WOID
FROM Tasks
WHERE WO_NUM = @WO_Num

DECLARE @ptrval binary(16)
DECLARE @ptrval2 binary(16)
DECLARE @NewTaskNoteID AS int
    
OPEN @CursorVar1

FETCH NEXT FROM @CursorVar1 INTO @ptrval, @ptrval2

WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATETEXT Tasks.Descript @ptrval 0 0 @Comments

	IF @Comments <> ''
	BEGIN
		SELECT @NewTaskNoteID = MAX(TaskNoteID)+1 FROM TaskNote
		UPDATE TI_IdGenerator
			SET NextIDValue = @NewTaskNoteID
			WHERE [ID] = 46

		INSERT INTO TaskNote
		VALUES(@NewTaskNoteID, @Comments, @ptrval2, 0, NULL, 0, getdate(), @RequestorID, getdate(), @RequestorID)
	END

    FETCH NEXT FROM @CursorVar1 INTO @ptrval, @ptrval2
END

CLOSE @CursorVar1
DEALLOCATE @CursorVar1

IF @@ERROR = 0
	BEGIN
		COMMIT TRANSACTION
		SET TRANSACTION ISOLATION LEVEL READ COMMITTED
	END
ELSE
	BEGIN
		ROLLBACK TRANSACTION
		SET TRANSACTION ISOLATION LEVEL READ COMMITTED
		RETURN (1)
	END

SET NOCOUNT OFF
RETURN(0)
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO 

What should happen, at the very least, is the fields CLSDBY, CLSDDATE, COMPLETED, STATUS and WORKORDERSTATUSID should be modified in the database, but no modification takes place when the code is run via .Net. In looking at this, I noticed that there are two transactions, one in the .Net code and one in the T-SQL, so I tried removing them one at a time, first the one in the T-SQL, then the one in the .Net code. This had no effect. I also commented out the entire CURSOR section of the stored proc, since this isn't what I'm concerned with right now and I've had experiences with weird cursor behavior. Still no effect.

I really don't know what's going on here. Can anyone give me a suggestion as to where to look?

Thanks!

RE: Call to stored procedure does nothing

Check what is sent to SQL Server with SQL Server profiler.
Put a breakpoint just before .Update() and run Profiler.
Clear the Profiler window and execute .Update().
Then check in Profiler what is sent to SQL Server and try to execute this code in SSMS New Query Window.

Borislav Borissov
VFP9 SP2, SQL Server

RE: Call to stored procedure does nothing

(OP)
Thank you Borislav. I tried your suggestion, and nothing is sent to SQL Server. I did discover, however, that if I do the following, it works:

CODE

string DBString = ConfigurationManager.AppSettings[DSN].ToString();
        SqlConnection conn = new SqlConnection(DBString);
        conn.Open();
        DataSet ds = null;

        try
        {
            ds = SqlHelper.ExecuteDataset(conn,
                CommandType.StoredProcedure,
                SqlCmdText,
                SqlParams);

        }
        catch (Exception e)
        {
            throw e;
        }
        finally
        {
            conn.Close();
        } 

I find this very curious. I have a solution now, but I'm still not sure I understand why the other way doesn't work. Hmmmm......

At any rate, thanks for your help!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close