Stored Procedure with .NET Troubles

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Stored Procedure with .NET Troubles

Author

Topic

vbwizard
Starting Member

12 Posts

Posted - 2007-07-03 : 02:27:21

I have run into a brick wall, and was hoping someone here might be able to help. What I have is essentially this. I create a stored procedure in my database and test it using SQL Server Mgmt Studio.
(SQL 2005 as I know that will come up). The procedure works perfectly when called from the Studio GUI. I call @@Rowcount to determine if the stored procedure updated any records. This value would be 1 if the update worked. There is only a single update in the stored proc
so I am reasonably certain this would result in being a value of 1 if the record is updated, and as I have stated, it works perfectly from the SQL gui. The n-tier code however also returns a value of 1 (which when called using ExecuteScalar should be the value of @@Rowcount...)
Even though the code in the n-tier DAL layer is returning the correct value and no exceptions are thrown the data does in fact NOT get updated. I have tried removing the stored proc and re-creating it... I have deleted my DAL layer Dll and recompiled it.. and all my other procedures seem to work.

The code for the stored proc is


USE [hogwarts_tv]
GO
/****** Object:  StoredProcedure [hogwarts_admin].[Questions_Update]    Script Date: 07/02/2007 01:40:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [hogwarts_admin].[Questions_Update](@QuestionKey int, @QuestionCategory int, @QuestionType int, 
							@Questions text, @Answers text, @ManualMark bit, @TimeAllowed int, @Gryffindor bit, 
							@Hufflepuff bit, @Ravenclaw bit, @Slytherin bit, @SortingHat bit, @Defence text, 
							@DifficultyRating int, @Gold int, @WhichBook int, @WhichMovie int, @Points int, 
							@OWLPoints int, @NEWTPoints int, @Published bit) As
     Declare @Error as Integer
     UPDATE Questions 
         SET QuestionCategory = @QuestionCategory, 
			QuestionType = @QuestionType, Questions = @Questions, 
			Answers = @Answers, ManualMark = @ManualMark, TimeAllowed = @TimeAllowed, 
			Gryffindor = @Gryffindor, Hufflepuff = @Hufflepuff, Ravenclaw = @Ravenclaw, 
			Slytherin = @Slytherin, SortingHat = @SortingHat, Defence = @Defence, 
			DifficultyRating = @DifficultyRating, Gold = @Gold, WhichBook = @WhichBook, 
			WhichMovie = @WhichMovie, Points = @Points, OWLPoints = @OWLPoints, 
			NEWTPoints = @NEWTPoints, Published = @Published
         WHERE QuestionKey = @QuestionKey
     If @@Error = 0
     Begin
         SELECT @@RowCount
     End
     else
         RaisError('Error with Update in .Questions',16,-1)
Return @@Error

The code for the .NET side is:


Public Shared Function Questions_Update(ByVal fld_QuestionKey As Int32, 
ByVal fld_QuestionCategory As Int32, ByVal fld_QuestionType As Int32,
ByVal fld_Questions As String, ByVal fld_Answers As String, 
ByVal fld_ManualMark As Boolean, ByVal fld_TimeAllowed As Int32, 
ByVal fld_Gryffindor As Boolean, ByVal fld_Hufflepuff As Boolean,
ByVal fld_Ravenclaw As Boolean, ByVal fld_Slytherin As Boolean, 
ByVal fld_SortingHat As Boolean, ByVal fld_Defence As String, 
ByVal fld_DifficultyRating As Int32, ByVal fld_Gold As Int32, 
ByVal fld_WhichBook As Int32, ByVal fld_WhichMovie As Int32, 
ByVal fld_Points As Int32, ByVal fld_OWLPoints As Int32, 
ByVal fld_NEWTPoints As Int32, ByVal fld_Published As Boolean) As Boolean
        Dim ReturnRowCount As Integer
        Dim dbConn As New SqlConnection(RemoteConn)
        Dim ReturnValue As Boolean = False
        Dim SQLExec As String = "Exec Questions_Update @QuestionKey,
 @QuestionCategory, @QuestionType, @Questions, @Answers, @ManualMark,
 @TimeAllowed, @Gryffindor, @Hufflepuff, @Ravenclaw, @Slytherin,
 @SortingHat, @Defence, @DifficultyRating, @Gold, @WhichBook,
 @WhichMovie, @Points, @OWLPoints, @NEWTPoints, @Published"
        Dim rs As SqlCommand = New SqlCommand(SQLExec, dbConn)
        With rs.Parameters
            .Add("@QuestionKey", SqlDbType.Int).Value = fld_QuestionKey
            .Add("@QuestionCategory", SqlDbType.Int).Value = fld_QuestionCategory
            .Add("@QuestionType", SqlDbType.Int).Value = fld_QuestionType
            .Add("@Questions", SqlDbType.Text).Value = fld_Questions
            .Add("@Answers", SqlDbType.Text).Value = fld_Answers
            .Add("@ManualMark", SqlDbType.Bit).Value = fld_ManualMark
            .Add("@TimeAllowed", SqlDbType.Int).Value = fld_TimeAllowed
            .Add("@Gryffindor", SqlDbType.Bit).Value = fld_Gryffindor
            .Add("@Hufflepuff", SqlDbType.Bit).Value = fld_Hufflepuff
            .Add("@Ravenclaw", SqlDbType.Bit).Value = fld_Ravenclaw
            .Add("@Slytherin", SqlDbType.Bit).Value = fld_Slytherin
            .Add("@SortingHat", SqlDbType.Bit).Value = fld_SortingHat
            .Add("@Defence", SqlDbType.Text).Value = fld_Defence
            .Add("@DifficultyRating", SqlDbType.Int).Value = fld_DifficultyRating
            .Add("@Gold", SqlDbType.Int).Value = fld_Gold
            .Add("@WhichBook", SqlDbType.Int).Value = fld_WhichBook
            .Add("@WhichMovie", SqlDbType.Int).Value = fld_WhichMovie
            .Add("@Points", SqlDbType.Int).Value = fld_Points
            .Add("@OWLPoints", SqlDbType.Int).Value = fld_OWLPoints
            .Add("@NEWTPoints", SqlDbType.Int).Value = fld_NEWTPoints
            .Add("@Published", SqlDbType.Bit).Value = fld_Published
        End With
        dbConn.Open()
        Try
            ReturnRowCount = rs.ExecuteScalar()
            If ReturnRowCount > 0 Then ReturnValue = True
        Catch ex As Exception
            ReturnValue = False
        Finally
            dbConn.Close()
        End Try
        Return ReturnValue
    End Function

Please note that the context of the code is running on a hosted ASP.NET server. All of my stored procedure code and .net code was written using a code generator that I designed (why I haven't started using nHibernate.. too busy I guess) all the other procedures that I have generated using this generator work great on this sever, its only this single one.. Wait.. it gets better... when the code is called from a datagrid (asp.net2) it works. when called from the DAL.. it does not. If you need to see the other DAL layers and the calling code, I will post, but initially I did not think anyone would want them.

If you have any ideas.. great.. If I have managed to stump you all, pls.. let me know..
Cheers All
Brent Coppock

You should check out www.hogwarts.tv ...

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-07-03 : 02:56:20

Try changing your SP as follows:

ALTER Procedure [hogwarts_admin].[Questions_Update](@QuestionKey int, @QuestionCategory int, @QuestionType int, 
							@Questions text, @Answers text, @ManualMark bit, @TimeAllowed int, @Gryffindor bit, 
							@Hufflepuff bit, @Ravenclaw bit, @Slytherin bit, @SortingHat bit, @Defence text, 
							@DifficultyRating int, @Gold int, @WhichBook int, @WhichMovie int, @Points int, 
							@OWLPoints int, @NEWTPoints int, @Published bit) As
Declare 
	@Error as Integer,
	@Count as Integer

UPDATE Questions 
SET QuestionCategory = @QuestionCategory, 
	QuestionType = @QuestionType, Questions = @Questions, 
	Answers = @Answers, ManualMark = @ManualMark, TimeAllowed = @TimeAllowed, 
	Gryffindor = @Gryffindor, Hufflepuff = @Hufflepuff, Ravenclaw = @Ravenclaw, 
	Slytherin = @Slytherin, SortingHat = @SortingHat, Defence = @Defence, 
	DifficultyRating = @DifficultyRating, Gold = @Gold, WhichBook = @WhichBook, 
	WhichMovie = @WhichMovie, Points = @Points, OWLPoints = @OWLPoints, 
	NEWTPoints = @NEWTPoints, Published = @Published
WHERE QuestionKey = @QuestionKey

Select @Error = @@Error, @Count = @@RowCount

If @Error = 0
Begin
	SELECT @Count
End
else
	RaisError('Error with Update in .Questions',16,-1)

Return @Error

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

vbwizard
Starting Member

12 Posts

Posted - 2007-07-03 : 13:04:24

I made the changes you specified. The procedure still works in SQL Mgmt Studio, but when called via the .NET code, does not work at all. What you wrote for changes is very similar to the code I had before I stared messin with it trying to make work. That is why I had the extraneous @Error declaration there that was not being used. Any more ideas I would be most appreciative.

Cheers,
Brent

You should check out www.hogwarts.tv ...

cvraghu
Posting Yak Master

187 Posts

Posted - 2007-07-03 : 14:52:55

Are you using transaction anywhere in middle tier or SP? If so, you've to check on that.

vbwizard
Starting Member

12 Posts

Posted - 2007-07-03 : 16:08:15

You are correct. I was using a transaction. However, that transaction code does not occur at the Business Logic Layer as that would require that I pass the connection object down to the DAL layer which struck me as not really all that secure, passing around that kind of object. Hence, when I coded it, I coded the generic transaction code at the DAL layer which is posted in my first post. During my troubleshooting I completely removed all references to any transaction based code. This did not solve the problem. What is so weird about this problem is that the RESPONSE from the sql server when executed via the .net code indicates that the update occurred successfully (but a look at the data shows it did not), which if a transaction rollback took place would in fact not occur as the try / catch code would direct the code-flow to the catch statement which in the other procedures contains a trans.rollback statement. Having the transaction code in place or removed made absolutely no difference at all.

If required, (once I get home from work) I will create a new section of the website which you can all go to and see what is going on. This section will show the following points.
1. The update when performed via datagrid works like a charm.
2. The update when performed via a call to the dal object, fails, but reports success
3. The DAL object and the datagrid object both rely on the SAME stored procedure. The only difference is HOW that stored procedure is called.

Cheers,
Brent

You should check out www.hogwarts.tv ...

raminjoein
Starting Member

2 Posts

Posted - 2007-12-18 : 00:11:22

Spam for Oxygen Code generator removed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources