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:
And here's the stored procedure:
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!
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
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
CODE
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!